Access97 - Linking to SQL Server

[SHardy]

Someone who has now left the company had setup an Access97 database that I have become responsible for. The database includes linked tables from an SQL Server source.

I have had no dealings to date with SQL Server, but now I am being told that the application that is the data source may be moving to a different server.

This will mean redefining the links to the SQL Server tables.

What is the best way for me to examine the existing links, and to be able to set them up again from scratch. I need to learn how to do this, so that it is a simple process once the data moves.

Thanks

 

[RiverGuy]

I would assume they are linked through ODBC. You would need to change the DSN files that they use. I don't know if Access 97 has this, but try right clicking a linked table and open up "linked table manager". This will tell you what the DSN files are called, then you can find the files and modify their parameters.

If there is no linked table manager, look for something similar.

To set one up from scratch, you have to create an ODBC data source, or use another format the Access recognizes.

Again, I'm not sure if 97 is laid out the same way, but go to file, get external data, link tables, choose an ODBC type, and choose "New". This should bring you to where you need to be to make a new data source.

 

[SHardy]

Would I be correct in thinking that the DSN files are only required for the actual linking of the tables, and once that link is made, there is no need for the DSN files?

Like I said, someone else created this DB, including linking the tables, and it now sits on a server. I have no DSN files setup on my PC, and neither do any of the other users. Presumably they would be on the PC of the person that created it?

Had a look at creating a new DSN file, but got a bit confused. I think I'll need some major hand holding.

 

[RiverGuy]

No, you need the DSN the whole time you use the table. The DSN could be stored on another PC or file server and set up to use that.

A DSN is basically like a bridge to a data source. Different companies will put out a provider for their product, each having some of the same and some different options of setting them up.


How many linked tables are there btw? Lots of them? What is the Access used for? Just reports? Are there only linked tables or local tables as well?

If there are lots of tables and only linked tables, and they only come from one SQL database, and you happen to have Access 2000 or 2002, I would consider moving it to an Access Data Project. That's basically a front end to SQL Server but with forms and reports, etc. And you don't have to use an ODBC connection..

 

[SHardy]

No, Access97 only I'm afraid. There are 15 linked tables, as well as a number of local tables. The database is mainly used for reporting purposes.

Tried setting up a new DSN using NT authentication. Seemed to work for me, but when I asked another user to open the tables, they could not get access.

 

[RiverGuy]

NT authentication means that SQL is validating them via their Windows Networking password. You would either need to make their Windows users to have logins for SQL Server, or make a specific logon for SQL Server and have the DSN use that user name and password.

 

[Geraldo]

I need to link Visual FoxPro Tables to Access maybe trough
ODBC may anyone help me.

Geraldo

 

[SHardy]

I have been told that the Windows users do have logins for SQL server, as it is required for the application whose database I am trying to link to.

 

[RiverGuy]

Well if their network login also has a login for SQL Server. The users either need to have the same DSNs on their machines, or you need to try using file DSNs to store on a network that others can access.

Geraldo--Have you an ODBC datasource made for your FoxPro source yet?