Connecting Access to Remote SQL Server

[nmathews]

I'm trying to make a macro in Microsoft Access 97 that would connect to a remote web server on the net and update a table in a database with the results of a query in access. I've searched the web and tried several codes, but Access just hates everything I've tried.

[IanTurner]

Why not connect the SQL Server to the AccessDB and have the SQL Server pull the data it needs?

[nmathews]

I really dont want all the information on the internet. See it's for a small club. The only thing thats being set to the web is members names and phone numbers. The database that the secertary has control of does all the attendance, membership information and ect. I just figured if their was some way to make a button that the secetary could click to update the website it would less confuesing for them. The people who we elect for secetary normally have almost no computer skills, so simple is better.

[IanTurner]

The only way I can think of to do it is to write a VB application that will ftp the AccessDB to the web - then call a DTS package on the SQL Server to pull in the correct data.

I know it can be done, but whether it is worth the effort in your case is another matter entirely.

[wackal]

you could upgrade to Access 2000. The 2000 version has a feature to let you connect to SQL Server using Access as the front-end

[nmathews]

Where do I find the feature: that lets you connect to SQL Server using Access as the front-end in 2000? I have access to a computer with 2000 on it. I just don't have Access 2000 at home where I'm working on this project.

[IanTurner]

Use the Link Table facility and then select an ODBC data source for the external file.

[wackal]

in the menu bar under Tools --> Database Utilities --> Upsizing Wizard.

I haven't used it in a very long time, but when I did I was able to link my access database to a SQL Server database that was on my web host's server.

[nmathews]

Access 97 has that function of linking a table to a webserver. However, I've tried that and I keep getting this error:

Microsoft SQL Server ODBC Driver Version 03.81.9041

Running connectivity tests...

Attempting connection
[Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection.

TESTS FAILED!

[wackal]

don't link tables, use the Upsizing Wizard. They are 2 different things.