Vba to Change Pass Through Query Connection

Coby · July 12th, 2007, 07:30 PM

I have some queries which are saved queries, yet sometimes I want to change the ODBC Connection String without having to manually open each of the queries in design mode to change the string. I have been trying to execute this through a Vba module. Below is an example what I have so far, but am struggling with the call to the object and changing its ODBC Connection property.

---------------------------------------------
Option Compare Database

Sub DbPointer()

Dim dbs As Database
Dim qrySNJobs As QueryDef

Set dbs = CurrentDb
Set qryPnetJobs = dbs.CreateQueryDef

qryPnetJobs.Connect????:( = ODBC;DSN=PalmSprings-PlantNetDb;DATABASE=PlantNet;Trusted_Connection=Ye s

End Sub

Any ideas on where I am going wrong would be appreciated tremendously.

Coby.

ajvol01 · April 25th, 2011, 09:12 AM

Original Code:

Code:

Option Compare Database

Sub DbPointer()

Dim dbs As Database
Dim qrySNJobs As QueryDef

Set dbs = CurrentDb
Set qryPnetJobs = dbs.CreateQueryDef


qryPnetJobs.Connect????:( = ODBC;DSN=PalmSprings-PlantNetDb;DATABASE=PlantNet;Trusted_Connection=Ye s

End Sub

Updated code

The QueryDef connect property is expecting a string value that must be a string variable or text surrounded by quotes

Code:

Option Compare Database

Sub DbPointer()

Dim dbs As Database
Dim qrySNJobs As QueryDef

Set dbs = CurrentDb
Set qryPnetJobs = dbs.CreateQueryDef


qryPnetJobs.Connect = "ODBC;DSN=PalmSprings-PlantNetDb;DATABASE=PlantNet;Trusted_Connection=Yes"

End Sub