ODBC connection between MS SQL 7.0 and Access 97

[tlaksh]

I have sql tables linked to access 97 forms. This works fine on the same machine.But when i try to open access from any other machine it says ODBC call error. What could be the possible cause to this error. Are there some basic things that i need to check. I have set up all the dsn on the machine. Does the other machine require to have SQL loaded as well?? I am new to all this so please help...

Thanks for ur input in advance.

 

[dneufarth]

What networking protocols are you using?


Dave

 

[tlaksh]

I am using TCP/IP protocol

 

[elviajero]

I just went through this myself and while I'm not an expert I will tell you what I found to accomplish this on clients within our WAN. I got the same ODBC call error.

In Control Panel ODBC data sources have to have the SQL driver loaded and configured with your database as the default database on each client you're loading the database on. I apologise if I tell you something you already know, I'm just telling you what I found out by trial and error. I hope others would feel free to correct me or add to what I've said but I can tell you it worked for me.

Under the User DSN tab make sure SQL server driver is loaded along with the others. If it's not, add it. You'll be prompted for name, description, and what SQL server you want to connect to. Use a short but descriptive name unique term for name and description; type in the name of the SQL server if DNS is enabled. You should be able to type in the IP address as well.

Hit next in the Wizard. Make sure the tab "How should SQL server verify the authenticity of the Login ID" is set to 'With SQL server authentication..." as opposed to Windows NT. Enter a valid login and password for your SQL server.

Click on the Configuration tab and make sure TCP/IP is your named protocol under network libraries as it sounds like you've already done. Click OK and go back to the Wizard; hit Next.

There will be an option to Change the Default Database. If you've gotten to this point you've authenticated to the SQL server. Your database if you've added it successfully on the SQL server end will be an option in the dropdown list for the default database. Choose it and hit next. On the next screen in the Wizard I left the defaults; hit Finish.

This should be done on each client you are trying to install the database on. Make sure if you've split it that you have both the _be file or whatever you called the splitted database file, and the .mde on the hard drive of the user.

It should work. As I say, it did for me and more experienced people may have something to add to or correct what I've said. You're basically installing a SQL server driver on each client that's trying to authenticate to it.