External Databases in VBA

[RayMilhon]

Is it possible to query a SQL Server database in VBA without linking (showing the table in the table list of the front end) I have 4 Databases (SQL Server Backend, MS Access 2010 Front Ends) that I have been tasked with combining the 4 databases into 1. I've designed the front end but now I have to combine the existing data of the 4 into the new database. I'm doing this in the front end as the 4 tables have similar fields but are structured differently. Also there are a couple of lookup tables that are the same in some of the databases but are not in the same order.



For Example
Database1
Table bus_type
bustypeid bustypedesc
1 Commercial
2 Senior
3 Medi-cal
4 Exchange
5 Covered CA



Database2
Table type
typeid Type
1 Senior
2 Medi-Connect
3 Commercial
4 Covered CA
5 Exchange
6 Medi-Cal

I'd rather not clutter up the Access Front end with all of the tables in the existing 4 database plus the tables in the new merged database. Anybody know if this can be done?

[ranman256]

It's not cluttering. Link in the external tables. Don't waste time with code when you can solve it with a few clicks.

[pbaldy]

You can query SQL Server with a pass through query.

[ssanfu]

My $0.02.....

I would link to SQL Server because I use and am most familiar with DAO.
But you can/could connect to many databases/tables using ADO. (SQLS, Oracle, DB2, Excel, ...)