Linked Table To SQL Server?

[snkscore]

I've inherited an access application that consists of a number of .accdb files.

Depending on what your doing, 1 of the 2 accdb files contains most of the UI, and the other accdb files keep the data, and those files are kept on the network so everyone access the same data.

I want to convert these central accdb files (not the UI ones) to run on SQL Server. I know that there is an conversion wizard that upsizes the database to sql server, and from sql server I know I can import access data, but I don't see anywhere in the Linked Table Manager to connect to a SQL server database.

How do I do this?

[RuralGuy]

Properly designed, 1 of the .accdb should contain *all* of the UI not just most of it. As for SQL Server, I believe you will need to use an ODBC connection for which I'm not familiar. There are SQL Server guru's here that might drop by with a proper answer for you.

[snkscore]

Properly designed, 1 of the .accdb should contain *all* of the UI not just most of it. As for SQL Server, I believe you will need to use an ODBC connection for which I'm not familiar. There are SQL Server guru's here that might drop by with a proper answer for you.

Yes all the UI is in the 1 accdb file, but I think there are a few tables that the app uses that are not in the "backend" database, even if the backend accdb file contains the vast majority of the tables.

I've read about how I can add linked tables that are on a SQL DB, but I don't see anything about repointing existing linked tables to a SQL Server.

The stuff I read says that if there is an existing table (or I'm guessing an existing linked table) named TableUsers, and you try to add a linked table called TableUsers from a different location, it will create the linked table as TableUsers1.

I'm not sure how Access works, but I'm guessing that I can't just rename the tables how I want them and everything will work. I'm guessing when you rename a table in access all the things that reference that table are updated to reference the newly named table.


The real problem here, is that when I go to the "Linked Tables Manager" (I think that's what it's called) if lets me pick the location for all the linked table, basically asking me to find the accdb file for each linked table, but there is no way to specify an odbc option at this point. How can I do this?

[RuralGuy]

Look in Help under:
Import or link to SQL Server data
I got there starting with ODBC