Connecting to multiple database (SQL Xpress)

[ezfriend]

Here is a scenario that I like to get some opinions from the forum.

We have an Access application (FE) that access some databases from SQL Server Express. All of the databases (tables, views, etc...) are identical with the exception of the data in the tables for different databases.

[Yes, normally, we should use one database to handle all of the cases, but it is not an option]

The Access application is linked to a database to pull information and display on reports. Each time we want to pull data from a different database, we have to manually connect to the database using DSN and then link to the tables. With identical tables and so on, Access starting generating tables with a suffix (table, table1, table2, etc) for the new databases. This is definitely the wrong way to do it.

Question:
We have queries that we want to have it run across multiple databases. Since the tables are identical in all databases, is there an easy way to change the link tables' data source?

I was thinking about using a drop-down box to select a database and then click on a command button to refresh the linked tables' data source (5 of them) using the selected database from the drop-down box.

Any suggestion is greatly appreciated.

Thanks.

EZfriend.

 

[LPurvis]

In other words you're wanting table relinking code?
It's pretty common here and there.
e.g. to create a single ODBC table linked to SQL Server...

Sub CreateLinkedODBCTable(strTableName As String, strServer As String, strDBname As String, _
                            Optional blnIntegratedSecurity As Boolean, _
                            Optional strUserName As String, Optional strPassword As String, _
                            Optional strAlias As String)
'Creates a SQL Server ODBC linked table based on provided connection properties
'Usage example: CreateLinkedODBCTable "tblTableName", "ServerName", "Northwind", True
On Error GoTo errHere
    Dim db As Database
    Dim tdf As TableDef
    Dim prp As DAO.Property
    Dim strConnect As String
    
    strConnect = "ODBC;Driver={SQL Server};Server="
    strConnect = strConnect & strServer & ";Database=" & strDBname
    
    If blnIntegratedSecurity Then
        strConnect = strConnect & ";Trusted_Connection=Yes"
    Else
        If Len(strUserName) > 0 Then
            strConnect = strConnect & ";UID=" & strUserName
        End If
        If Len(strPassword) > 0 Then
            strConnect = strConnect & ";PWD=" & strPassword
        End If
    End If
    
    Set db = CurrentDb
    Set tdf = db.CreateTableDef()
    With tdf
        .Name = IIf(Len(strAlias) > 0, strAlias, strTableName)
        .Connect = strConnect
        .SourceTableName = strTableName
    End With
    db.TableDefs.Append tdf
    
exitHere:
    Set tdf = Nothing
    Set db = Nothing
    
    Exit Sub
    
errHere:
    MsgBox "Error " & Err & vbCrLf & Err.Description
    Resume exitHere
End Sub

I'm a fan of maintaining a local tabl of linked table names (and optionally source).
Iterating through that as part of the linking procedure is then simple.
(Deleting the old linked table before recreating with the above of course).

Cheers.

 

[ezfriend]

Thank you. This is what I need.

 

[LPurvis]

Cool.
(Plus some more text to take me over 10 characters :-s)