Querydef Help

[ZMAN2]

I have some code that updates the sql in an existing pass-thru query, my problem is when I attempt to run the query it prompts me for ODBC parameters. Any help would be appreciated.

Dim db As DAO.Database
Dim qdf As DAO.Querydef
Dim strSQL as String

Set db = CurrentDB
Set qdf = db.QueryDefs("myquery")

strSQL = qdf.SQL
strSQL = "SELECT....."
qdf.SQL = strSQL

My pass-thru properties for ODBC Connect Str are

ODBC; SERVERNAME=myserver; DSN=connection; UserID=myid; PW=cheese;


Thanks again.

 

[ReAn]

Have you tried re-setting the Pass Through Connect String before you set the SQL.

qdf.Connect = "....."

 

[ZMAN2]

Thanks ReAn.

Yes, I have tried resetting the connect string, but I am still experiencing the same results. Perhaps a syntax problem, see below.

qdf.Connect = "ODBC; SERVERNAME=myserver; DSN=connection; UserID=myid; PW=cheese;"

 

[ReAn]

Here is some code i use to update connect strings for linked tables. Perhaps you need to .RefreshLink the QueryDefs too.

Public Function SetLinks(ByVal oUser As String, ByVal oPass As String, ByVal oServer As String)
    Dim i As Integer
    i = 0
    
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    
    On Error Resume Next
    
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
        If Left(tdf.NAME, 3) = "MAX" Then
            'match
            Form_frmSplash.UpdateProgress "Updating: '" & tdf.NAME & "'", 5, 6
            tdf.Connect = "ODBC;DSN=DatabaseDSN;UID=" & oUser & ";PWD=" & oPass & ";driver={SQL Server};server=" & oServer & ";"
            tdf.RefreshLink
        End If
    Next tdf
End Function

 

[ZMAN2]

Thanks

ReAn,

Thank you for all your help. I have found the error in my ways, thanks to your sample code. Syntax, syntax, syntax.......

 

[Colby]

ReAn, thanks for this post. Just found out my comp uses a different definitions for my pass thru's depending on if I am local or vpn client.
This is 'xactly what I need.

Thanks again.