Pass Thru Query Connection

[Rickety107]

A have an access database that has linked tables to a SQL Server 2005
database. A pass thru query was created for one of the report to use. In
the properties of the query, the connection string has been entered. The
problem is that prior to releasing to production, we link the database to a
test database for testing by relinking the tables to point to the test
database. Everything is now pointed to the test environment except the
report that uses the pass thru query.

Is there a way to programmatically set the connection of the database to be
what ever connection the linked tables are using?


Here is the code. The following is in the report_open event

Dim sSQL As String
Dim qdf_SP As QueryDef


sSQL = "SELECT OB.[Sales Order Number], OB.EndDate, E.Office, E.OfficeName,
OB.TTLCost AS TTLVendor," & vbCrLf & _
"(OB.TTLCost - COALESCE(OB.TTLPaid,0)) AS TTLVendorDue,OB.[Supplier Name],"
& vbCrLf & _
"OB.SolomonSupplierID,OB.Description,OB.[Line Number],OB.Account,OB.[Account
Description]" & vbCrLf & _
"FROM EmployeeOffice As E INNER JOIN fn_GetAPAgingPerOutstBal('" & EndDate &
"') AS OB ON E.OfficeID = OB.fldCity " & vbCrLf & _
"WHERE (E.OfficeName = '" & OfficeName & "')" & vbCrLf & _
"AND (OB.TTLCost - COALESCE(OB.TTLPaid,0)) <> 0"

Set qdf_SP = CurrentDb.QueryDefs("qryAPAgingPerOutstBal")
qdf_SP.ReturnsRecords = True
qdf_SP.sql = sSQL
qdf_SP.ODBCTimeout = 180
qdf_SP.close


In the properties of the report, the recordsource is set to
qryAPAgingPerOutstBal

 

[Gary Walter]

Hi R..,

Maybe I misunderstood, but sounds like you
wanted something like?

Dim strConn As String
Dim qdf As DAO.QueryDef

'Get Connection string from some correctly linked table
strConn = CurrentDb.TableDefs("sometable").Connect

' Refresh Connect String for all Pass-Through Queries
For Each qdf In CurrentDb.QueryDefs
If Len(qdf.Connect) > 0 Then
qdf.Connect = strConnect
End If
Next

good luck,

gary