Dynamic Connection to SQL Server DB from Access

[dfrazell]

We have a backend SQL Server that has 6 identical database schemas, one
for each region (soon to be 8). I need to create a frontend reporting
system that will dynamically switch to the regions database when
selected. Currently our Access database has a link to each table
(100's of tables) in each regions database and each report is
duplicated in each region.

How can I create a single report that can be used by all regions that
will dynamically switch table connections based on the region selected?
We are using Access 2002 for our reports.

Thanks,
D. Frazell

 

[Brian Wilson]

We have a backend SQL Server that has 6 identical database schemas, one
for each region (soon to be 8). I need to create a frontend reporting
system that will dynamically switch to the regions database when
selected. Currently our Access database has a link to each table
(100's of tables) in each regions database and each report is
duplicated in each region.

How can I create a single report that can be used by all regions that
will dynamically switch table connections based on the region selected?
We are using Access 2002 for our reports.

Thanks,
D. Frazell

What about creating a single pass-through query "qryAllRegions". This query
is dynamically re-written before you run the report. Now you can write

If UpdateQuery("DatabaseWithRegion1") then
' Open the report based on qryAllRegions
End If



Public Function UpdateQuery(strDatabase As String) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strConnect As String
Dim strSQL As String

strConnect = "ODBC;" & _
"Driver={SQL Server};" & _
"Server=MyServer;" & _
"Database=" & strDatabase & ";" & _
"Trusted_Connection=yes;"

strSQL = "SELECT ID, FieldOne, FieldTwo FROM " & _
"MyTable WHERE ID<10;"

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("qryAllRegions")

qdf.Connect = strConnect

qdf.SQL = strSQL

UpdateQuery = True

Exit_Handler:

If Not qdf Is Nothing Then
Set qdf = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function