Change passthrough query connection string

[Julian]

Hi All,

I have a passthrough query where I would like to change the connection string using vba:

qrydef.Connect = "xxxxx"

After changing the connection string, although the query has a new query string, the query returns records from the old query connection string.

Only by deleting the original query, shutting down Access (not just the database) & compacting am I able to recreate the passthrough query with the new connection string.

The ODBC driver is Sage.

 

[Scooterbug]

I've started to play around with Sql Server...and found this function (I believe on MS website)

Function CreateSPT(SPTQueryName As String, SQLString As String)
On Error Resume Next
      '-----------------------------------------------
      ' FUNCTION: CreateSPT()
      ' PURPOSE:
      '   Creates an SQL pass-through query using the supplied arguments:
      '      SPTQueryName: the name of the query to create
      '      SQLString: the query's SQL string
      '      ConnectString: the ODBC connect string, this must be at
      '         least "ODBC;"
      '-----------------------------------------------
         Dim mydatabase As Database
         Dim myquerydef As QueryDef
         Dim ConnectString As String
      
    DoCmd.DeleteObject acQuery, SPTQueryName
     
         
         Set mydatabase = DBEngine.Workspaces(0).Databases(0)
         Set myquerydef = mydatabase.CreateQueryDef(SPTQueryName)
    
    
    ConnectString = "xxxx"
 
         myquerydef.Connect = ConnectString
         myquerydef.SQL = SQLString
         myquerydef.Close
      End Function

It works for me at least. (I'm a list box junkie...so I'm playing with creating pass-through queries based on user selections to populate the listboxes)

 

[Julian]

Thanks for you help Scooterbug but.....

This is all very strange.

I tried your suggestion and initially it worked (I had been using a reference to CurrentDb rather than DBEngine.Workspaces(0).Databases(0).

However, after running other code (using ADODB connections as I wanted to test the username & password before altering the querydefs) the problem returned. In fact at one point, I noticed that by reference to DBEngine.Workspaces(0).Databases(0) data returned was from one ODBC data set, and referencing CurrentDb returned the other data.

Even more strangely, I have even uninstalled the ODBC driver, the passthrough query still returned data.

Worse still, I created a new database and imported the relevant objects.
Tried the code and data was returned despite the Driver being deleted. Only once the database had been compacted did Access recognise a driver error.

Is data being stored within the application, is ADODB interfering (don't think so with prelim tests removing references to ADODB) or is my Access 2007 corrupted?

 

[Julian]

Using ADODB I can see details of different databases by changing the connection strings.

Using Passthrough queries or linked tables I can see details of the different databases by changing their connection strings with SQL Server as the ODBC Server.

Using Passthrough queries or linked tables I cannot see different information with the Sage ODBC (the information always comes from the first table).

It seems that the connection string info is being linked with the driver and cached when using Passthrough or Linked Table from this particular ODBC connection through Access. Only by closing & compacting can new data be seen.

Does anyone know details of how Access/DAO caches such data?