Variable SQL server in pass-thru connect string

[mlwallin]

I have a large number of SQL pass-thru queries that look at a particular SQL
server and database. For an extensive upgrade, I'm setting up a test
environment on a development server. Rather than going in to change each
connect string, then going back in to change it back again later, is there a
way I can have the server option in the connect string point to a variable,
so I can just "flip the switch" from one server to another?

Here's my routine that formats the pass through:

Public Sub PassThroughFixup(ByVal strQdfName As String, strSQL As String,
Optional varConnect As Variant, Optional fRetRecords As Boolean = True)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strConnect As String

Set db = CurrentDb
Set qdf = db.QueryDefs(strQdfName)
If IsMissing(varConnect) Then
strConnect = qdf.Connect
Else
strConnect = CStr(varConnect)
End If

qdf.Connect = strConnect
qdf.ReturnsRecords = fRetRecords
qdf.SQL = strSQL

End Sub


and here's an example of how I call it:

Dim stDocName As String
Dim stSQL As String

stSQL = "OneOrAllConsults " & [txtPatientID] & ",'" & [cboEncounterID] &
"'"
stDocName = "qsptOneOrAllConsults"
Call PassThroughFixup(stDocName, stSQL)

stDocName = "rptOneOrAllConsults"
DoCmd.OutputTo acOutputReport, stDocName, acFormatSNP, , True

 

[Van T. Dinh]

Try something like:

1. Declare a constant gMSSQL_ODBC for your conntection String to the test
database. For example, for my test database:

Public Const gMSSQL_ODBC = "ODBC;DRIVER=SQL Server;Network=DBMSSOCN;" & _
"Address=localhost,1433;SERVER=localhost;" & _
"DATABASE=ABOM;APP=ABOM_ODBC;" & _
"Trusted_Connection=Yes;Regional=Yes;"

2. Create a Sub with code like:

Set db = DBEngine(0)(0)

For Each qdf In db.QueryDefs
If (qdf.Type = dbQSQLPassThrough) Or (qdf.Type = dbQSPTBulk) Then
qdf.Connect = gMSSQL_ODBC
End If
Next

run this code and all oyur Pass-Through Queries will have the Connection
String changed to the test database.

3. When you want to upload into production, simply modify the gMSSQL_ODBC
to point to the production database and run the code again.

Actually, I have both in strings in my database but one is commented out and
I keep switching from test to production and back ...

 

[mlwallin]

That was the clue I needed. Thanks!!

Try something like:

1. Declare a constant gMSSQL_ODBC for your conntection String to the test
database. For example, for my test database:

Public Const gMSSQL_ODBC = "ODBC;DRIVER=SQL Server;Network=DBMSSOCN;" & _
"Address=localhost,1433;SERVER=localhost;" & _
"DATABASE=ABOM;APP=ABOM_ODBC;" & _
"Trusted_Connection=Yes;Regional=Yes;"

2. Create a Sub with code like:

Set db = DBEngine(0)(0)

For Each qdf In db.QueryDefs
If (qdf.Type = dbQSQLPassThrough) Or (qdf.Type = dbQSPTBulk) Then
qdf.Connect = gMSSQL_ODBC
End If
Next

run this code and all oyur Pass-Through Queries will have the Connection
String changed to the test database.

3. When you want to upload into production, simply modify the gMSSQL_ODBC
to point to the production database and run the code again.

Actually, I have both in strings in my database but one is commented out and
I keep switching from test to production and back ...

--
HTH
Van T. Dinh
MVP (Access)

I have a large number of SQL pass-thru queries that look at a particular
SQL
server and database. For an extensive upgrade, I'm setting up a test
environment on a development server. Rather than going in to change each
connect string, then going back in to change it back again later, is there
a
way I can have the server option in the connect string point to a
variable,
so I can just "flip the switch" from one server to another?

Here's my routine that formats the pass through:

Public Sub PassThroughFixup(ByVal strQdfName As String, strSQL As String,
Optional varConnect As Variant, Optional fRetRecords As Boolean = True)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strConnect As String

Set db = CurrentDb
Set qdf = db.QueryDefs(strQdfName)
If IsMissing(varConnect) Then
strConnect = qdf.Connect
Else
strConnect = CStr(varConnect)
End If

qdf.Connect = strConnect
qdf.ReturnsRecords = fRetRecords
qdf.SQL = strSQL

End Sub


and here's an example of how I call it:

Dim stDocName As String
Dim stSQL As String

stSQL = "OneOrAllConsults " & [txtPatientID] & ",'" & [cboEncounterID]
&
"'"
stDocName = "qsptOneOrAllConsults"
Call PassThroughFixup(stDocName, stSQL)

stDocName = "rptOneOrAllConsults"
DoCmd.OutputTo acOutputReport, stDocName, acFormatSNP, , True

 

[Van T. Dinh]

You're welcome ... Glad to help ...