ACCESS FEED: RE: [MS_AccessPros] Pass-Through Query

Rabu, 06 Juli 2011

RE: [MS_AccessPros] Pass-Through Query

CC-

That's not a Pass-through query; it's just a plain old SELECT query, and you
cannot EXECUTE a SELECT qiery that way. It does look like you're missing a
blank at the end of the first line after "s.district," ...

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc
Sent: Wednesday, July 06, 2011 9:26 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Pass-Through Query

Hello everyone

I created a Pass Through query, it's error free and been running ok. And a table
in the db is based on the query. Now I try to automate it a bit. I placed the
query in the Form_Open event, so each time when the db's open, the query will
run and the table will be refreshed or up to date. However, each time when the
query's executed, a "3075 syntax error (missing operator) in query expression."
will occur.

It's that because Pass Through query cannot be run via VBA???

Here I included my query (below)

Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tbf As DAO.TableDef

Dim strQryPassThru

Set db = CurrentDb

On Error GoTo ErrorHandler

strQryPassThru = "SELECT s.id, s.lname, s.city, s.state, s.region, s.district,"
& _
"p1.bh71, p1.bh73, p1.bh65, f1.bh30, p1.cp66, " & _
"p1.cp36, p1.cp12, p1.cp55, p1.cp47, p1.bh94, " & _
"f17.kc699, f17.ckg215, f7.ckb594, p1.bcp05, p1.bpc254,
f2.bhc98, " & _
"p1.BHC75 , f2.HCKB47, p1.DT " & _
"FROM Na.dbo_ATTR as s " & _
"INNER JOIN FD.dbo_BHF01 as f1 ON s.id = f1.ID " & _
"INNER JOIN FD.dbo_BHF02 as f2 ON f1.ID = f2.ID " & _
"INNER JOIN FD.dbo_BHF07 as f7 ON f2.ID = f7.ID " & _
"INNER JOIN FD.dbo_BHF17 as f17 ON f7.ID = f17.ID " & _
"INNER JOIN FD.dbo_BHP01 as p1 ON f17.ID = p1.ID " & _
"WHERE s.region = 1 AND s.district =1 AND s.end_dt=99991231 ;"

DoCmd.Hourglass True

db.TableDefs.Delete "tblNewPassThru"
db.Execute strQryPassThru, dbFailOnError

DoCmd.Hourglass False

ExitProcedure:
Set rs = Nothing
Set db = Nothing
Exit Sub

ErrorHandler:

MsgBox Err.Number & vbCrLf & Err.Description

Resume ExitProcedure
Resume

End Sub

------------------------------------

Yahoo! Groups Links