no SQL, remotely access an access database

[sib.inpursuit]

hi friends,

i know that this is for sql but i want to discuss the case when my access database containning the tables is placed on a computer on the lan network of my office and all other users from different have an access frontend(VBA). Everytime the user opens their .mdb on their computer , the code fetches the tables from that central computer's access and also modifies it when changes are made.

what will be the connection string for it using DSN from access to access?
can it be done without DSN?


regards

 

[SOS]

1. Access can't connect to Access using a DSN, nor does it need to.

2. Access is not built to use on a WAN (wide area network) - where users are not in the same building. If you need to have users outside the LAN (local area network) use it, then it is best to use Terminal Services or Citrix.

 

[sib.inpursuit]

SOS thanks for the reply
please tell me a way that my code be able to fetch data from one access database on computerA to be imported in another access database in computerB and also the changes made in the data on computerB be saved back in computerA

i might sound really stupid

 

[JGT]

I don’t know if I got your doubts, but sharing data in a Database environment like ACCESS, normally means breaking the App in two: one central database called Back End (BE), where all data used by all users is stored, and a man machine user interface, called Front End (FE), placed in each user PC, accessing the same BE. So there is no need for transferring data from one PC to another.
As SOS mentioned, when the BE is an ACCESS db, it is not wise to access the BE through WAN, because of network instability; Access is very fragile, if a data transaction is interrupted you likely can loose the whole db.
If you need to access the BE through WAN, you can use a SQL Server BE, which is not only stable against network instability, but may times quicker.
If you need to work with different BEs on different networks, or with stand alone PCs, than the option is to use replication. The build in synchronization SW keeps track of all changes and updates the data whenever you connect 2 BEs on the same network. But it is a really complicated matter, I used replicated Access dbs some time ago, and I must say it works, but it’s not an easy way out.

 

[dfenton]

With all the users on the local LAN, there is no reason to use anything other than linked tables from the front-end MDB/ACCDB (forms/reports/queries/modules/etc.) to the back-end MDB/ACCDB (tables only). Each user has a front end on his or her workstation and there is a single shared back end with data tables on a file server accessible to all over the LAN.

Thus, there's no need for Windows Terminal Server or Jet replication, since the data is accessible on the local LAN.

But if some of the users are not on the local LAN but are connected via WAN or VPN over the Internet, then the advice about replication or Terminal Server applies. Terminal Server is the superior approach, but assumes an always-on connection and users who are never disconnected from the network.