By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

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

anoble1
100+
P: 223
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
May 10 '12 #1

✓ answered by 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.

Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,357
Why not just give them different names?
May 10 '12 #2

anoble1
100+
P: 223
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.
May 10 '12 #3

Rabbit
Expert Mod 10K+
P: 12,357
I meant change the name of the link/table in Access, not the source name
May 10 '12 #4

anoble1
100+
P: 223
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:
Expand|Select|Wrap|Line Numbers
  1. 'Old Atlanta Billing System
  2. Public Function PingOldAtlantaBillingSystemServer()
  3.     Dim connectString As String
  4.     connectString = "ODBC;Driver={SQL Server};Server=GAATL01WSVINS;Database=MPWholesale;Trusted_Connection=Yes"
  5.     Set db = DBEngine.OpenDatabase("", False, True, connectString)
  6. End Function
  7.  
  8. 'Old Billing Atlanta Server
  9. Public Sub LinkOldAtlantaBillingSystemServer(table As String)
  10.  
  11.     cn = "odbc;driver={SQL Server};server=GAATL01WSVINS;database=MPWHOLESALE;Trusted_Connection=Yes"
  12.  
  13.     Set db = CurrentDb
  14.     Set td = db.CreateTableDef()
  15.     td.Name = table
  16.     td.SourceTableName = "dbo." & table
  17.     td.Connect = cn
  18.  
  19.     Set tds = db.TableDefs
  20.     tds.Append td
  21.  
  22. End Sub
  23.  
  24. 'GAATL01WSVINS
  25. Public Function LinkAllGAATL01WSVINSTables()
  26.     Dim sqlStatement As String
  27.     Dim records As Recordset
  28.  
  29.     'Note: qryTablesOnServer records Every table and count of rows
  30.     sqlStatement = "SELECT qryTablesOnGAATL01WSVINSServer.* FROM qryTablesOnGAATL01WSVINSServer"
  31.     Set records = CurrentDb.OpenRecordset(sqlStatement, dbOpenDynaset, dbSeeChanges, dbOptimistic)
  32.     While Not records.EOF
  33.         LinkOldAtlantaBillingSystemServer records("TableName")
  34.         records.MoveNext
  35.     Wend
  36.     records.Close
  37. End Function
  38.  
May 10 '12 #5

Rabbit
Expert Mod 10K+
P: 12,357
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.
May 10 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.