I wanted to know if it's possible to join a MS Access table with a SQLtable (in access) and whether the best method to do this is vba? (Unforunately I don't have the capability to link the databases)
Thanks in advance
Add a comment
|
2 Answers
Yes it is possible! You have to create a linked table ,see this link for more info: sql linked table
3 Comments
Krishn
Hi, I don't have the capability to do this as it requires an admin password and I won't be given the privileges to do so
SnakeFoot
you don't need admin privileges to do this. try to create a user ODBC connection to the sql server where you have the db
Matt Gibson
Indeed. You can do it either way, in fact -- SQL Server can query Access tables -- but linking to the SQL Server table from Access is the way it's done most usually. You shouldn't need admin privileges -- if you can see the SQL Server table in some client tool yourself right now, you can probably configure Access to see it, too.
While the answer is yes, the next word is but, because it can be painfully slow, indeed if you will have multiple joins - because Access has to pull the entire table(s) from SQL Server to perform the join(s).
So be prepared to spend some time tweaking with subqueries and - as the last option - even to upload your Access table to a temp table in SQL Server so this can handle the join (or vice versa).
