VBA Code to Link SQL Server Tables (1 Viewer)

I think this is a common issue for all those people who are upgrading from access to SQL server (as database), so i'm asking where i can find some resource to accomplish the following "basic" task.

The following command is used to attach an Access Table contained in an .mdb file:
DoCmd.TransferDatabase acLink, "Microsoft Access", "c:\myapp\mydb.mdb", acTable, "target tablename", "local tablename"

How do you attach a table contained in an SQL server 2005 database?
Say that the database is on a server called MYSQLSERV, which contains a database called MYAPPDATABASE, that contains the table "MYTABLE"...

I tried the following, but i didn't succeed:
I created an "ODBC source" called MYODBC, linked to the correct server and to the correct database (I tested and I was able to link and browse the data in a table), then I tried this command:
DoCmd.TransferDatabase acLink, "ODBC Databases", "MYODBC", acTable, "target tablename", "local tablename"
I got the following error:
Runtime error 2507: The ODBC Databases type isn't an instyalled database type or doesn't support the operation you choose.
What am I missing?

goto:

http://www.access-programmers.co.uk/forums/showthread.php?t=155752

Following your link and browsing for help in the sites suggested in the discussion, I found the answer to my problem, thank you.

Just to add a small contribution (and make things clear, where normally you would get crazy guessing what is the right command)
i will write here the correct sintax to attach a table from a MS SQL database using VBA code.
The following code refers to (and has been succesfully tested for) Access 2003 towards MS SQL 2005:

docmd.TransferDatabase acLink,"ODBC Database",
"ODBC;Driver={SQL Server};Server=MYSQLSERVER;Database=MYSQLDB;
Uid=USER;Pwd=PASSWORD",acTable,"MYSQLTABLE","MYACCESSTABLE"

where
MYSQLSERVER is the name of the server (or PC) where the MS SQL database resides
MYSQLDB is the name of the database (inside MYSQLSERVER) to connect
MYSQLTABLE is the name of the table (inside MYSQLDB) to connect
MYACCESSTABLE is the name that MYSQLTABLE will get as linked table inside Access
USER and PASSWORD are the MS SQL defined user and password

If you want to use NT authentication, the command changes as follows:

docmd.TransferDatabase acLink,"ODBC Database",
"ODBC;Driver={SQL Server};Server=MYSQLSERVER;Database=MYSQLDB;
Trusted_Connection=Yes",acTable,"MYSQLTABLE","MYACCESSTABLE"

I needed this complete command, because in my application i have to choose where to connect to a .mdb file
or to an MS SQL database, and just changing the [table.connect] string is not enough: I delete the linked tables
and then re-create them (attached) with the correct connection parameters.

PS: The example above, also frees you from defining ODBC connections, before attaching table (DSN less).