Multiple databases (2002 SP-1)

Topic

New Reply

Here is an advanced topic for someone. I have developed an Access front-end to produce reports from an SQL Server database. I have linked to the SQL Server through an ODBC connection, which has worked well.

I now have a need to grab data from two different databases residing on the same server. It would be nice to be able to combine the data from both databases into one report, but I would settle for just being able to grab the data from both databases from within the same MDB.

I looked into TransferDatabase function, but that became too cumbersome. I know this can be accomplished as an ADP, but that means migrating [read: start from scratch] my entrire current application. Is there a way to do this from within my current MDB. Failing that, please point me to a decent resource for creating/working with Access Projects.

Thanks

Reply | Quote

Replies

Why not just link to the appropriate tables in the new database as well?
Pat

Reply | Quote

Is far as I can tell, you only have access to one ODBC connector at a time. And you can only access one database with each ODBC connector. Is there something I am missing?

Reply | Quote

Are you linking the tables thru ODBC to SQL Server? It appears that you are. Can A2002 link to multiple databases?
Is your database an .MDB or an .ADP?
If an .MDP then can’t you just run the AddIn “Database Linker” and link the tables via ODBC?
If an .ADP then you cannot link the tables anyway (AFAIK).
I don’t have any experience with Access2002 but have A97 and A2000 and in both those you can link to multiple databases.
Pat

Reply | Quote

Not true – each table can be in it’s own SQL Server database. The path is stored in the properties of the linked table, and we do it routinely. All you need to do is to create an ODBC source for each of the SQL Server databases, and then make the appropriate connection.

Reply | Quote

I stand corrected. Thank you, Pat and Wendell!! The solution was so easy, I overlooked it. Isn’t that the way it usually is!

Reply | Quote