Help! I need to know how to share an Access database

[ahill48]

I have a very small Access database that I created on my desktop. I need to share this at three separate locations. We do not have a server or a SharePoint we can use. I looked into purchasing a SharePoint, but they were way too expensive.


Is there a way to use one of our computers as a server and house the back end on that computer? If so what would be the cheapest, most secure way to access from each of the computers with the front end file? Are there other options for sharing?

I am not a coder, or indeed very experienced with Access. I created the database by googling how to do what I needed it to do and asking questions in forums like this one. It functions very well, and I've been using it to enter data on my computer; but to be really useful, we need to provide the search functions at the locations. Any help you can provide would be much appreciated.

[ranman256]

use your computer, set it to share with those other people. It becomes a 'server'.
split your database, give users rights to the backend,
give each individual their own copy of the frontend.

[ahill48]

use your computer, set it to share with those other people. It becomes a 'server'.
split your database, give users rights to the backend,
give each individual their own copy of the frontend.

Can this be done over the internet? Do we need a VPN or anything? Not sure how this would work. We do not have a company network.

[CJ_London]

but to be really useful, we need to provide the search functions at the locations

this implies only you are entering/changing data, the other locations are only viewing the data. If this is correct, at it's simplest level, just zip and email the file. Ideally the db should be split, so you only need to send the backend - and their front ends would be set up to not allow any changes to the data.

[ahill48]

this implies only you are entering/changing data, the other locations are only viewing the data. If this is correct, at it's simplest level, just zip and email the file. Ideally, the db should be split, so you only need to send the backend - and their front ends would be set up to not allow any changes to the data.

There are three computers at different locations that will be doing searches...they will, however, also need to have the ability to enter notes on individual records and change the status of those records from a drop down list. So, I guess all locations need to have the ability to change and enter data, however limited. There will be two people (one of them is me) in separate locations, who will have full data entry permissions. At this point, we haven't decided how many computers will have access, but it will be at least 4 in 3 separate locations, plus mine which is in another location out of state. I thought to manage the user permission levels with log on's that limit individual users activities.

Building the database has turned out to be the easy part...sharing, not so much. LOL, I wish I had more experience in THIS area.

[CJ_London]

Having simultaneous access of multiple users without a network and without any real budget is not really possible. There are free/low cost options such as using remote access, but only one user can do so at a time. Or use something like dropbox, but again, only one user at a time.

You could look at using Office 365, but not sure if that will meet your needs, or there are hosting services which will host your access db (basically using citrix or remote server), but comes at a cost - see this link for a number of options - I have no experience of any of them, but some provide free initial accounts for you to try

https://nortonsafe.search.ask.com/we...doi=2016-09-01

Another option is synchronisation. Each user has their own copy and once a day or some agreed frequency, copies are forwarded to you for syncing and an updated copy returned, but this requires more coding to provide the synching facility and a process to identify potential clashes - e.g. user A changes the status of a record, user B changes the status of the same record, but to a different status - when synced, which record is the correct one? Adding notes is less contentious since these will be new records, but it still needs to be managed effectively.

[ahill48]

The owner has been told he can set up a WAN/VPN and share the db that way. I believe it's possible if we split the database, but I'm not sure how it would work. What are the considerations when using VPN? Is there anything I need to know specifically?

[CJ_London]

Access is not really intended for working across a WAN due to the risks of connection interruption/failure and the resultant risk of corruption - if going this route, use SQL Server/Express for the back end.

To set up a VPN, you'll find help and advice here

https://nortonsafe.search.ask.com/we...doi=2016-09-01

You will probably need fixed IP addresses for all computers so you will need to talk to your broadband supplier.

In my experience, using access using VPN is extremely slow - perhaps 100 times slower than across a LAN, so it is extremely important to ensure your db is optimised to minimise the amount of data traffic passed across the network. Plenty of suggestions on this and other forums for what needs to be done.

Suggest you also check with your owner's advisor what they advise. This is a discussion on the subject

http://www.utteraccess.com/forum/ms-...-t1979880.html

[ssanfu]

Don't try to use Access FE/BE over a WAN! See http://www.kallal.ca/Wan/Wans.html

[CJ_London]

Thanks Steve - I was looking that link and couldn't find it!

That article, although still relevant, is now 13 years old and since then broadband has improved so think the speed comparisons could do with an update, although in some respects the comparison has got worse because LAN speeds have also improved, and in the case of this OP, it would appear his comparison would be against local drive speeds since they do not have a network.

As a simple comparison, the article quotes high speed internet as 40 times faster than dial up and 100 time slower than LAN, and in those days high speed internet was around 20mbps. Today with highspeed internet, rates of 80mbps are typical (I get 89mbps download, and 20mbps upload at the moment), so simplistically 4 x faster, so if something on the LAN takes 4 seconds it will take 25 times longer across a WAN - 100 seconds. All this of course, will depend on the broadband connections on both the OP's computers.

[ahill48]

Well, thank you, Ajax and Steve for your help. The owner has decided (over my objections) to use Google Drive. Does either of you know what happens if two people try to open the file at the same time? Are they able to open it, and if so how is it synched? If they enter data, I suppose it would just be the last user's copy gets saved. I assume someone can search while someone else is entering data with no problems, right?

I mean, I guess Google Drive is better than a WAN, but I don't know if it will work for so many users. I've been arguing that we should only have 1 data entry person if we're going this route. I changed the set up so that if it's accessed with a viewer rather than the program, they will only get search forms. To enter data you have to access it in the actual program.

[CJ_London]

Don't know about google drive and don't see why it is better than a WAN. - and I don't think you can use access, see below. Had a quick look and looks like you would need to use something called parasql which many google drivers rave about - https://www.parasql.com/

See this comment on the google drive help page here https://support.google.com/drive/ans...DDesktop&hl=en

Note: To edit Microsoft Office files with others, you'll need to convert them to Google Docs, Sheets, or Slides.

and these are limited to word, excel and powerpoint

Suggest you give some of these things a try

[ssanfu]

My point about the link is that if you are using an Access BE across a WAN, you WILL corrupt the BE. This is discussed under the heading "Why a does JET file share corrupt when the connection breaks?"

[ahill48]

I was able to upload to Google Drive, although I'm not sharing widely through my organization, it is synching and seems to be working well. I made a big point, however, of telling the owners I didn't consider this a good or long term solution.

The owners have now decided we should use their SharePoint. Accordingly, I split the database and used the little SharePoint utility in Database tools to upload the back end to the SharePoint site as lists. When I open the front end all is well, and it seems to function perfectly. However, I had one of our employees in a different location open the front end; but after she put in her credentials and opened one of the forms, we found that the three fields that are drop down lists, linked to separate tables, were not populating.

I have a customer table, with the field "Customer Status"; the drop down list has 4 options that exist on a separate table. In both the front end the employee used, and on the list existing on the SharePoint, this field is not populated. It does, however, show the options when you click on the field on the SharePoint, but doesn't show them on the employees front end file.

I sent the front end file to her via email, should I have done something differently?

[ahill48]

Oh...and thank you for being so patient with me.