SQL Table connection

[Tmole99]

I have an access database in two parts, data and program (reports, form etc)

Normally I link them using linked table manager and all is ok.

I now want to upgrade the data portion to SQL because of size, I have sucessfully upsized the database to SQL 2000 but how do I now link the damn tables in SQL to my programs etc in the other Access database

I have just realised that Linked Table Manager does not include the ODBC

Help please, does this mean that I have to re-write all my front end ?

 

[luceze]

You will have to create a DSN to connect with the SQL database. Go to Insert/table. Select link table. Change "Files of Type" to ODBC. The select datasource dialog will open. Switch the tab to Machine Data Source. Click New. Select System Data Source. Click Next. Select SQL Server as your driver. Click Next. Click Finish. The SQL Server DSN wizard should appear. Enter a name for your DSN. Select the server name where your SQL database is stored. Enter a description if you like. Click next. The next part is up to your DB admin as to how to log in to the database. Ours is set up to SQL ID. That means you will need a username and password to the SQL server on which the SQL DB resides. Click next. Select your database. Click next. Click Finish. You can now test the connection or close this window. You will now be back to the Select Data Source dialog. Select the DSN that you just created and you will be presented with a list of tables in your SQL database. Choose the desired tables and click Ok. The SQL tables will now be linked. Change the table names to the names of your original tables and everything should work fine.

HTH,
Eric

 

[Tmole99]

Thats great, it worked

But Access seems to rename everything to doa_whatever

Do I just have to rename these ?

 

[Tmole99]

Sorry Luceze

Just re-read your post

 

[luceze]

Glad to help.

 

[Tmole99]

Great I know have a database that contains all the tables named correctly. I know they work because you can just click on them and theres the data

However, when I try and use linked table manager it says that Jet cannot find the table named blah blah blah

I know my paths are correct, please help me

 

[luceze]

Not sure. Does this happen for all your linked tables? Or only certain ones?

You could try this:

Once all your tables are renamed go to the linked table manager. Check the box that says Always prompt for new location. Re-enter the path for the tables.

HTH,
Eric

 

[Tmole99]

Thanks mate

Didn't work but now I have the method I will try again from the beginning

Thanks again