R
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
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