Remote ODBC to MS Access

[QuackerJack]

I have an Access database on a dedicated server. I need to access this database via ODBC from my machine at home. What all do I need to do to set up a DSN on my machine? I have heard that I need to create a share on the server to I can map a drive on my machine, but have not seen anything about that. I DO have full access to the remote (dedicated) server if needed.

Thanks in advance.

[Thing]

This needs to be setup on the server containing your database.

1. Open Control Panel
2. Open Administrative Tools
3. Open Data Sources (ODBC)
4. Select the System DSN tab
5. Click the Add button
6. Click the Microsoft Access Drive (*.mdb)
7. Name the datasource
8. Under Database click the Select button
9. Select your database
10. Click ok

That's it, now you can reference it by the name you gave the datasource.

[QuackerJack]

Yes, that is how I would normally do it on a local network or local machine, but the database resides on a dedicated server on the internet.

To give you an idea of what I am doing, I am trying to connect to my Access database (which is on a remote server on the internet) from UPS WorldShip so WorldShip can update the tracking number of an order that is stored in the database. In order to do so, I need to create a DSN on the local machine where WorldShip is installed, but when I get to the point of selecting the database, I only have the option to choose one that is on my machine or on a drive mapped from my machine. Therein lies the problem. My machine is running Windows XP and the server is a Windows 2000 server. Do I need to create a share on the server and if so, how do I do that. This is unfamiliar territory for me.

This needs to be setup on the server containing your database.

1. Open Control Panel
2. Open Administrative Tools
3. Open Data Sources (ODBC)
4. Select the System DSN tab
5. Click the Add button
6. Click the Microsoft Access Drive (*.mdb)
7. Name the datasource
8. Under Database click the Select button
9. Select your database
10. Click ok

That's it, now you can reference it by the name you gave the datasource.

[Thing]

Looks like you need to share out the directory where your Access DB is stored on your other server, then you should be able to select it.

[wwb_99]

I would not reccomend attempting to share drives over the net.

Only thing I can think of is setting up a VPN and then mapping the drive that way. Might be a good case for a web service here as well.

WWB

[QuackerJack]

I have found that the data center where the server is blocks the ports necessary to connect to a share. Thus, I am going to go with a 2-step process involving both Access and mySQL...dumping data back and forth between them and using mySQL for the ODBC connection.

Thanks for all your suggestions.