trying to share access database that have odbc connection

Question

i have access mdb file that i put on a server 2003 and accessible as a shared resource from office only \myoffice\accessdatabase.mdb

lately, i added odbc connection on the server 2003 to another server (windows 2008) in a sister company and tables were shared successfully

i could run the database locally from the 2003 server (using ms access locally) and get data from the remote server of the other sister company, no problems

however, now if a client in the office trying to access this database from my server 2003, it doesn't provide the data that supposed to be pulled from the other company!! isn't it supposed that the mdb file which is on the server will connect automatically through odbc and gets the data for local network and clinets? or i am missing something important here?

i can't install the odbc on the clients of the office, only the server can connect to the other company... but all clients are connected to the server 2003 ok, no issues, also this database runs ok, but without that data which is supposed to be coming from the sister company

any idea or suggest to make it work?

Answer 1

Microsoft Access was designed and intended as an individual desktop database. It sounds to me like you know that.

The database you have has outgrown its original purpose. You're going to have to do some hard work to make it suitable for this new purpose.

One thing you can do is put a web service in front of the database that exposes it to outside clients. They'll have to authenticate and authorize themselves and go through at least two firewalls to access this data source. They'll only be able to use the methods that the service exposes. The service will handle transactions, validate and bind parameters, protect against SQL injection attacks, etc.