I need some help with the Access 2003 database syntax to connect to another Access 2003 database so I can query the tables in both databases using join.
So far I got it to work if I put in the absolute path to the mdb file.
E.g. select * from mytable in 'c:\myfolder\mydb.mdb'
But I have search everywhere and I cannot find the equivalent syntax for DSN. The external Access 2003 database is only accessible via DSN. I have tried various combinations and nothing worked.
If anyone has found the syntax before, please share it with me.
I'll be running the query in asp.net (vb) using ODBC.
Try Link tables
With Link tables you can query multiple mdb using join as if tables are in same mdb.
From current mdb, select menu bar, Insert,Table, Link table, choose tables from 'c:\myfolder\mydb.mdb'
lets say you have table mytable on 'c:\myfolder\mydb.mdb'
Your query becomes, 'select * from mytable'
Hope it helps.
I'm afraid link tables are not an option for me. The databases are part of a software solution and I can't expect the customer to modify things behind the scenes. Also they may not have Access installed on their server. I also doubt whether link tables would be available if I use asp.net and ODBC to connect to it.
If you cannot see any sense of my solutions. Well good luck.