Can someone show me how to bring together Multiple SQL Server DB in 1 Access Program?

[anoble1]

Hi,

I am working with 4 SQL Server seperate databases. They are all having some issues. I would like to bring them all into 1 MS Access db so I can query them.

The problem I am having is where when I try to bring them in some of the tables, the table names are the same, so they will not copy in.

The only thing I can think of is put eash SQL Server in a separate DB and group all the tables in a Specific Query, and transfer the query over to the central MS DB that will house the 4 SQL Servers? But, how do I do that? Possible?
How would that query update automatically like linked tables?

Thanks guys!
-Andy

[Rabbit]

Why not just give them different names?

[anoble1]

Good idea, I was going to to that, but someone had made a lot of querys in the SQL Server and it would mess them up if the names changed slightly.

[Rabbit]

I meant change the name of the link/table in Access, not the source name

[anoble1]

I'm trying to think of what you mean when you say that.... I 4 different kinds of querys that link to each separate db. Below is showing 1 SQL db linking to Access. Maybe you can see whats going on. I'm not sure if I follow you about changing the link/table in access. They are all linked to different places of course.

Here is my code that I use to link:

'Old Atlanta Billing System
Public Function PingOldAtlantaBillingSystemServer()
    Dim connectString As String
    connectString = "ODBC;Driver={SQL Server};Server=GAATL01WSVINS;Database=MPWholesale;Trusted_Connection=Yes"
    Set db = DBEngine.OpenDatabase("", False, True, connectString)
End Function

'Old Billing Atlanta Server
Public Sub LinkOldAtlantaBillingSystemServer(table As String)

    cn = "odbc;driver={SQL Server};server=GAATL01WSVINS;database=MPWHOLESALE;Trusted_Connection=Yes"
    
    Set db = CurrentDb
    Set td = db.CreateTableDef()
    td.Name = table
    td.SourceTableName = "dbo." & table
    td.Connect = cn
    
    Set tds = db.TableDefs
    tds.Append td

End Sub

'GAATL01WSVINS
Public Function LinkAllGAATL01WSVINSTables()
    Dim sqlStatement As String
    Dim records As Recordset
    
    'Note: qryTablesOnServer records Every table and count of rows
    sqlStatement = "SELECT qryTablesOnGAATL01WSVINSServer.* FROM qryTablesOnGAATL01WSVINSServer"
    Set records = CurrentDb.OpenRecordset(sqlStatement, dbOpenDynaset, dbSeeChanges, dbOptimistic)
    While Not records.EOF
        LinkOldAtlantaBillingSystemServer records("TableName")
        records.MoveNext
    Wend
    records.Close
End Function

[Rabbit]

Your td.Name = table, change that to td.Name = table & "_" & serverName or something similar. It doesn't have to be server name, it could be an incremental number. Just give it a different name. You don't even need the underscore, I just put it in for posterity.