Need help linking table to MS Access from SQL Server.

[anoble1]

Hi,

I am getting an error message when I try to use the Link Table Manager. When I click on it, it says: "There are no Linked Tables in the Current Database".
What the heck, I know. That's why I want to add them. Anyway, this is Access 2002. This is a blank database. I'm just trying to pull the tables from the server, and place them into my database. I am used to using Access 2003, and 2007. I don't think they did it like this. Won't even let me add the Data Source, but I did through Administrator Tools.

[NeoPa]

What you want is :
File | Get External Data | Link Tables.

I expect it will all become pretty straightforward from there :-)

PS. Linked Table Manager is for managing existing Linked Tables ;-)

[anoble1]

I thought that was for linking to another database. Ex. Access DB? I have a lot of tables on my SQL Server that requires a login of course to view. When I click on Get External Data, I can't seem to find the tables on the SQL Server. Be patient with me, haven't added "New tables to a New database" HA! Thanks

[NeoPa]

For SQL Server you need to ensure first, that the correct drivers are applied to your machine. Once done, this is managed via the ODBC applet in Control Panel. When linking new tables, one option is certainly to link to other Access databases, but various other options are also available natively, and one such available option is to use ODBC. This is what you need to connect to SQL Server. Once you select this, you will be taken to a further selection similar to the ODBC applet which lists the ODBC data sources available on your machine. One of these will be SQL Server if the driver's been properly installed. I suggest you use a machine level connection rather than a user level one, and ensure that any machines that require use of the database are configured in a similar way, as otherwise you will have portability issues.

I hope this gets you further. Good luck :-)