Changing DSN for linked tables

[Mark Ferri]

Hi,
One of the users in our organisation uses an Access (7
database that consists of many linked tables ( linked via
ODBC). We recently upgraded the database it links to.
Is there a way to modify the DNS properties of the linked
tables or do they have to be dropped and recreated?

If I select a table and view properties it
displays "Linked Table ODBC" plus a description of the DSN
it is using. There doesn't seem to be anyway to change it.

Am I looking in the wrong place?

Any help would be gratefully received, I know next to
nothing about Access development having only ever dealt
with SQlServer.

regards

mark

 

[Douglas J. Steele]

Check out the code at http://www.mvps.org/access/tables/tbl0010.htm for how
to change ODBC linked tables.

Or take a look at
http://members.rogers.com/douglas.j.steele/DSNLessLinks.html for how to
avoid using a DSN.

 

[Mark ferri]

Thanks for your reply, I@ll have look into your
suggestions

regards

Mark

-----Original Message-----
Check out the code at

http://www.mvps.org/access/tables/tbl0010.htm for how

 

[mark ferri]

Thanks for the reply, I'll have a look into your suggestion

regards

Mark

-----Original Message-----
Check out the code at

http://www.mvps.org/access/tables/tbl0010.htm for how

 

[ndeshpcreview]

creating DSN for Access table to link to SQL server

I was faced with the same problem. Sometimes, the DSN to which your Access table is referring gets deleted.
In such a situation, your table does not open at all. The problem is it still refers to your old server, and there's no way u can go to its 'Properties' and change it.
So, the way out is to create a new dsn.

Firstly, rightclick on the table name (in your MS Access database) and then go to 'Linked Table Manager'.
Now check the checkbox adjacent to the table you are dealing with. Also check the "Always prompt for enw location" checkbox just below. Then click 'OK'.

Now a new window opens asking you to choose from existing dsn or create a new one.
Click on "New..". Select the driver you want. Incase you are connecting to an Sql server, select the SQL Server option at the bottom of the list. Then click on "Advanced..". Here the Driver name is highlighted in a small box. For ex: DRIVER={SQL Server} . Below this , type in the SQL server name like "Server = yourservername" and below that type the "Database=yourDBname" i.e., you need to know your server and database names for doing this.

Then click on "Next" and choose the path wher you want to store this dsn and give ur dsn a name. Click Save. Click "Finish". Then from the list, choose which server u want to connect to. then "next".
Then u can choose either Windows authntication or ur server authntiction. then keep clicking "next".
then "test datasource".say OK. and then ur dsn just appears in d window. so select that and say OK.

Walaah!! now ur table will open.