MS Access database with multiple users via LAN and WAN

I have created a Access database. The purpose of the database is to enter data in a few locations and read data in many locations via LAN and WAN. Sometimes these tasks may be done simultaneously. How can I do this?

First, if it needs to be shared via a WAN, you may be facing a particularly high hurdle to success. That's because relational database applications built using MS Access don't perform well over a WAN. So, for starters, that may be a limiting factor and a compelling reason to consider other alternatives. 

On the other hand, deploying relational database applications using Access over a LAN is a fairly common practice.

1) Split the accdb into two files, usually called "Front End" and "Back End". The Back End, or BE, contains ONLY the data, i.e. the tables. The Front End, or FE, contains the interface objects, i.e. forms, reports, queries and code (VBA and macros). 

2) Place the BE accdb in a shared folder on your LAN. Each user of this relational database application must have permissions to files in that folder, including the ability to read, write, and delete. This is due to the nature of the file-locking protocol in Access which creates (and deletes) the lccdb file.

3) Link your MASTER copy of the FE accdb to the tables in that shared BE.

4) Give each user their own, personal, non-shareable copy of the FE. That means it goes on their own computer or laptop for their exclusive use. Being connected to the BE means they DO share the data.

If you need to deploy via WAN, you might consider Remote Desktop or other options.

With Joy Wend Your Way

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

If its WAN then you need either RDP to allow the users to connect remotely ..or switch Database Engine (BE) to a more network friendly one (MSSQL,MySQL,Oracle,PostGre....)

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

The minute you talk about WANs, Access is normally no longer a contender.  These are things that are normally determined at the project specification phase, prior to development.  Once WAN becomes a requirement, then you need to turn towards proper Web technologies: PHP, .net, ... and alternate databases: MySQL, SQL Server, PostgreSQL, ...

That all said, you may like to review

https://www.devhut.net/2016/09/24/access-back-end-location-wan-online-server-onedrive-dropbox/

Perhaps one solution would be to migrate your backend to SQL Azure.

--
Daniel Pineault
Microsoft MVP 2010-2020

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Thanks for your suggestion.  I migrated the back end  into sql azure and work everything except the picture attachment. I have tried both filedialog property and the field data type as attachment.

Is there any way to migrate the access db with picture attachment to sql server?

Is it possible to store the picture file on the sql server and link with the front end database?

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

For this you can take a look at my article on Experts-Exchange :

https://www.experts-exchange.com/articles/33716/Defeating-the-device-independent-bitmap-dib-format.html

You will be able to save the Image as BLOB and retrieve it easily

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

The minute you talk about WANs, Access is normally no longer a contender.  These are things that are normally determined at the project specification phase, prior to development.  Once WAN becomes a requirement, then you need to turn towards proper Web technologies: PHP, .net, ... and alternate databases: MySQL, SQL Server, PostgreSQL, ...

That all said, you may like to review

https://www.devhut.net/2016/09/24/access-back-end-location-wan-online-server-onedrive-dropbox/

Perhaps one solution would be to migrate your backend to SQL Azure.

Hello,

I currently have an access DB with between 20 and 50 active users at a time. When everyone is working on the LAN, it works reasonably well. However, now that everyone is remote due to Covid, using it over the WAN is not an option. We are limping along with remote desktops for certain key users, but it's not a viable option to assign a remote computer to every user.

So I would like to explore other options. Ideally, I would like to keep the Access front-end because re-creating it would be very costly. Is it feasible to switch to another back-end solution like SQL Server that can work on a WAN, and still retain the Access front-end?  Or does the Access FE still not work over a WAN, no matter what the BE is?

Any advice you could provide would be greatly appreciated!

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

You could migrate the be to SQL Azure and relink the table of the front-end.  As long as users have internet connection they could then use the Access front-end.

That said, it is quite possible performance will take a hit and you might need to redesign certain aspects of your database to try and reduce the data going back and forth.

--
Daniel Pineault
Microsoft MVP 2010-2020

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated September 27, 2020 Views 826 Applies to: