Pass-Through Query in VB Works but seems to duplicate some code

[raynman1972]

Hello, all. I have the following code in Access that allows me to bring in some pass-through data and, while it works, it is a pain to list out my SQL code (see strSQL) at great length with quotes and line continues when I think I should be able to just refer to the saved pass-through query (PassThroughAgents) that I am copying out the SQL from in the first place. I apologize as I am sure there is a simple answer to all this, but I can't seem to make this work without having the SQL code listed.



Also, if it has any bearing, I need this to be part of an automated process- so no specifying data connection parameters or the like at runtime.

Thanks in advance!

Public Sub AgentsPassThrough()

On Error GoTo Error_Handler

Dim qdfPassThrough As DAO.QueryDef, MyDB As Database
Dim strConnect, strSQL As String

DoCmd.SetWarnings False 'Turn off confirmation prompt to user

If Not IsNull(CurrentDb.QueryDefs("PassThroughAgents").SQL) Then 'doesn't exist
CurrentDb.QueryDefs.Delete "PassThroughAgents"
End If

Set MyDB = CurrentDb()

strSQL = Same SQL that is in "PassThroughAgents"

Set qdfPassThrough = MyDB.CreateQueryDef("PassThroughAgents")

strConnect = "blah;"

qdfPassThrough.Connect = "ODBC;" & strConnect
qdfPassThrough.SQL = strSQL
qdfPassThrough.ReturnsRecords = True
qdfPassThrough.Close

Application.RefreshDatabaseWindow

DoCmd.OpenQuery "PassThroughAgents", acViewNormal, acReadOnly
DoCmd.Maximize
   
DoCmd.RunSQL ("DELETE DEF_AGENT_TO_SITE_MAPPING.* FROM DEF_AGENT_TO_SITE_MAPPING;")
DoCmd.RunSQL ("INSERT INTO DEF_AGENT_TO_SITE_MAPPING SELECT * FROM PassThroughAgents;")

DoCmd.Close acQuery, "PassThroughAgents"

DoCmd.SetWarnings True  'Turn back on confirmation prompt to user

If Err.Number = 0 Then Exit Sub

Error_Handler:
DisplayError Err.Number, Err.Description
Exit Sub

End Sub

[pbaldy]

I'm confused as to why you would delete and then recreate the query with the same SQL (or at all). That said, you should be able to grab the SQL of the query to a variable before you delete it, and then use that when you recreate it.

[raynman1972]

Sorry, I didn't mean to imply that I delete the code. My issue is that I have the same code in my script as what is saved as an Access Query and I want to see if I can simply refer to the saved query without having to have the SQL text listed out in the Sub. I guess I am confused why the code points to this saved query (PassThroughAgents) for the querydef but I still need the SQL code inline in the sub. Basically every time I need to tweak something in the SQL, I have to do it 2x and it is a bit of a pain to do it in the Sub because of all the quotes and line continues- it gets a bit messy.

[pbaldy]

What I'm not clear on is why you delete the query at all? At most I just replace the SQL of a pass-through. If I have to change the SQL, I change it in the code. Why change it in the pass-through if it's just going to get deleted or replaced anyway (though I may test there first to ensure I have the correct SQL)?

An alternative might be to create a view in the back end (SQL Server?) that has all the joins and such, and then have your pass-through SQL just be "SELECT * FROM ViewName WHERE..."

[raynman1972]

I don't want to delete the query, I am not sure how we got on that page. I simply want my Sub to point to the saved pass through query instead of having to have the SQL embedded in the script of the sub.

[raynman1972]

Hmm, looking through my code that I stole, there is the following piece that might be what you are referring to:

If Not IsNull(CurrentDb.QueryDefs("PassThroughAgents").SQL) Then 'doesn't exist
CurrentDb.QueryDefs.Delete "PassThroughAgents"
End If

I may have not emphasized my noob status enough, but I stole this code and I am not entirely certain what the portion does or why I need it in there.

[raynman1972]

Here is the original code that I had that works except for the fact that it prompts me to verify my ODBC each time I run it and I can't have this as I need it run and select the connection (same one every time) on its own:

Public Sub PassThroughQuery()

Dim qdfPassThrough As DAO.QueryDef, MyDB As Database

Set MyDB = CurrentDb()

DoCmd.OpenQuery "PassThrough_ViewQuery", acViewNormal, acReadOnly
DoCmd.Maximize
   
End Sub

[pbaldy]

Unless I'm having brain cramps, this line deletes the query:

CurrentDb.QueryDefs.Delete "PassThroughAgents"

and this line creates it a new one:

Set qdfPassThrough = MyDB.CreateQueryDef("PassThroughAgents")

If the query doesn't need to change, you can dump all of that and simply have

DoCmd.OpenQuery "PassThroughAgents", acViewNormal, acReadOnly

What you're doing would be more appropriate to a situation where the SQL of the query would change each time it was used (and even then I'd just change its SQL property).

Based on your last post, what you might want to do is open the query in design view, go into its properties and enter the ODBC connection info there, then save it. Then you shouldn't be prompted every time it's run.

[raynman1972]

Ah, that worked. Thanks for hanging in there with me

[pbaldy]

No problem, sorry I misunderstood the problem at the beginning.