How to connect to SQL Server database on the WEB

This explains how to connect Access to a SQL server database that is located on the Internet. Why? So you can have one master database(s) that everyone in the organization can get to 24-7. This can be accessed from any PC in the world that has an Internet connection and Microsoft Access. It can also be accessed through a Palm VII by creating a simple ASP page on the site as well.

Step1. Create a ODBC System DSN.
A. Open Control panel and double click the Data Sources Icon or ODBC Icon (depending on which version you have or Operating System)
B. Click the ôSystem DSNö tab at the top
C. Click the ôAddö button (upper right)
D. Scroll to the bottom of the list and pick SQL Server.
E. Click ôFinishö button
F. Give it a meaningful name (keep it short)
G. In the Server type the IP address of the Server where the SQL server database is. (Note: This may require a phone call to the ISP or Host of your site/database) Example: 123.21.123.34 Sometimes http://yoursite.com works
H. Click ôNextö button
I. Login screen is where you will have to determine the permissions (In our case itÆs the Login and Password of the WEB site). (When we edit the site in Front Page it is the same login)
J. So you would click the second Radio button ôWith SQL Server Authenticationàö
K. Click the ôClient Configurationö button
L. Pick TCP/IP connection
M. The IP address is the same as above and the ôPortö should be 1433
N. Click ôOKö to get back.
O. Key in Login and Password.
P. Click ôNextö button
Q. If you have the Password and Login and IP or site correct you will get to the next screen.
R. Click ôChange default database to:ö (Top check box )
S. You should see a list and your Database to connect to.
T. Just click Next to the end
U. Then click ôFinishö
V. Click the ôTest data Sourceö button
W. After its done testing (2-3 seconds) it should say ôTESTS COMPLETED SUCCESSFULLYö

Step2. Connect to ODBC DSN in Access
A. Open Access and click the ôTablesö tab.
B. Right click in the white space.
C. Click ôLink Tablesö
D. In the ôFiles of Typeö box (at the bottom) click on ôODBC databasesö (last item in list)
E. You will see the same ODBC screen as above.
F. Click the ôSystem DSNö or ôMachine Data Sourceö Tab at the top
G. Find your newly created DSN from above.
H. Double click it and Key in the Same Login and password as above.
I. If its correct you will see the table in that database
J. (VERY IMPORTANT) check the ôSave Passwordö check box below the ôDeselect allö button. Other wise you will have to enter the login in and password every time you open the Access database.
K. Pick as many tables as you want to connect to.
L. Each table will then present you with a list of Index fields to choose from.
M. Pick a few of them other wise the data does not get refreshed correctly. Such as ôand ID number, a Zip code, Phone number, Choose several
N. That's it you should see the new table like so ôdbo_Tablenameö with a Globe icon in front of it.
O. You can use the table any way you wish in Queries, Reports and forms.

Adding new data requires using ADO VBA code though. Sometimes it works without it. I have had best luck with creating VBA ADO connection code in a form or module.