Moving from single user to two users on a home network (1 Viewer)

peskywinnets

Registered User.
Local time
Today, 21:02
Joined
Feb 4, 2014
Messages
576
I currently use a Access DB as a single user to help me run my small online home business.

My wife helps me in the business now & then & carries out certain tasks using access ...this leads to contention for the seat at the PC where Access is running!

I've read up a little & think the best way would be to have two PCs & split the database (front end & back end)...am I correct?

if so, if I then change the front end on one PC ...what do I do to ensure the other PC's front end is updated too? (sorry if this is a basic question - will it just be a matter of copying a 'front' end (whatever that might be?!) from one PC to the other?

My Access database is currently running on a PC with a single drive...occasionally when I remember I make a copy, but it's far from ideal should my hard drive fail...I was therefore thinking about putting the back end on a NAS (with two mirrored disks for redundancy) but any speed degradation would be a downer....is there a better approach? (i.e. for two users ...I'm not bothered if one PC/User has a slower service & one has a fast service if that helps decide which way to go)

Many thanks in anticipation.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:02
Joined
Sep 21, 2011
Messages
14,047
Well as it is only two of you, you could share your drive to your wife.? No need for a NAS.
Again as it is only two of you, if you amend the FE, just make sure she has a copy as well.

You would have to split it, and from what you have described, i'd leave the BE on your PC, and share your drive over the network and let her access the BE as a network drive.?

HTH
 

peskywinnets

Registered User.
Local time
Today, 21:02
Joined
Feb 4, 2014
Messages
576
Yes, I'd considered a shared drive ...but I'm still worried about "what if my PC's single drive fails & takes my access database with it" (hence considering moving the BE to a NAS) ....so, if I go with the (faster) 'shared drive' on the main PC approach, what's the best way to ensure that access is backed up (it's usually running & logged in all the time) ...what are others on here doing for backing up access if it's running all the time?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:02
Joined
Feb 28, 2001
Messages
27,001
Even with a high-usage customer of the U.S. Navy we had set-aside times during which we knew that we had maintenance windows. We did backups and upgrades during these times, which were predictable and chosen for minimum disruption to business flow.

Heck, since it is your wife, you can probably persuade her to let you start a backup and then take her out for dinner.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:02
Joined
Sep 21, 2011
Messages
14,047
Well I'd have expected you to have a backup process in place if you rely on it for your business.?:confused:

I myself have NAS boxes. I have two Linksys DNS323. The first I had, I put in two 500GB drives mirrored and then I got another and that has 2TB drives again mirrored.

I use AllwaySync at home, but in work I found FreeFileSync which is donationware and very very good in my opinion. I would run that in Task Scheduler to back up the server drive to another PC as that PC was the only one backed up to the cloud.

You could simply use a portable 2.5" USB drive? That could also be shared and then you could backup your wife's computer to that as well?

Yes, I'd considered a shared drive ...but I'm still worried about "what if my PC's single drive fails & takes my access database with it" (hence considering moving the BE to a NAS) ....so, if I go with the (faster) 'shared drive' on the main PC approach, what's the best way to ensure that access is backed up (it's usually running & logged in all the time) ...what are others on here doing for backing up access if it's running all the time?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:02
Joined
Feb 28, 2001
Messages
27,001
OK, here is the REAL answer to the question: What is the best way to backup your data?

Answer: Any method that you can (and DO) continue to use.

Whether it is a matter of a mirrored drive, a tape storage unit, a high-capacity USB, copy to a DVD, or copy to a cloud account doesn't matter. What matters is that you USE it regularly.

Now, your business needs might direct you towards a particular solution and that's OK. For instance, if you think you need weekly backups, do it. Just be prepared for media costs and media storage costs. If you have less than 600 Mbytes to store, a write-once read-many (WORM) CD is the solution and costs pennies for one platter. A thin-line jewel case with a paper label and a CD inside is near ideal. For larger storage, a DVD can hold up to (roughly) 4.3 GBytes. After that you are looking at specialized solutions.

There is also the "leapfrog" method, in which you make a daily backup, but you only keep some number of days of data. With the Navy, our servers had daily backups but we recycled the tapes. Once per week we retained one of the backups separately and did not recycle it. So that meant that for the last 7 days we had a daily backup, then if what we wanted was older, we had weekly backups. Now, the Navy was known to take a bit of overkill, so they made a second set of backup tapes and shipped those off-site once a month.

Turned out to be a good decision because during the aftermath of Hurricane Katrina, the Navy Enterprise Data Center in New Orleans was able to recover all data (with only a slight delay for shipping) once our servers could be brought back online. Unfortunately, that wasn't an instantaneous recovery, but it was a full recovery.
 

Solo712

Registered User.
Local time
Today, 17:02
Joined
Oct 19, 2012
Messages
828
I currently use a Access DB as a single user to help me run my small online home business.

My wife helps me in the business now & then & carries out certain tasks using access ...this leads to contention for the seat at the PC where Access is running!

I've read up a little & think the best way would be to have two PCs & split the database (front end & back end)...am I correct?

if so, if I then change the front end on one PC ...what do I do to ensure the other PC's front end is updated too? (sorry if this is a basic question - will it just be a matter of copying a 'front' end (whatever that might be?!) from one PC to the other?

My Access database is currently running on a PC with a single drive...occasionally when I remember I make a copy, but it's far from ideal should my hard drive fail...I was therefore thinking about putting the back end on a NAS (with two mirrored disks for redundancy) but any speed degradation would be a downer....is there a better approach? (i.e. for two users ...I'm not bothered if one PC/User has a slower service & one has a fast service if that helps decide which way to go)

Many thanks in anticipation.

Hi,
@split database - for two users on a home network, I would not bother splitting the db. Unless you want to customize the front end for your wife, the benefits of splitting are small (at a gigabit local network speed).

@backups - mirroring disks is expensive and adds another point of failure. Hourly automatic backup to an external disk should be just fine and would be my preference.

Best,
Jiri

The following executes from a form's timer:
Code:
Private Function AutoBackup()
   '
   Dim tt As Long
   Dim BUTime As Date, srcepath As String, destpath As String, i As Long
   Dim OTime As String, NTime As String, BEname As String
   
   On Error GoTo Err_Backup
   OTime = Nz(DLookup("NextBackup", "Parameters", "ID = 1")
   ' convert date/time string
   BUTime = CDate(OTime)
   ' Are we there yet ?
   If BUTime > Now Then Exit Function
   '
  
   srcepath = "C:\...\Myfile.accdb"

   'the destination would be an external disk 
   destpath = "U:\..\Builds\" & Format(Now, "YYYY-MM-DD HHMMSS") & "_Myfile.accdb"
  
   FileCopy srcepath, destpath
   '
   'time interval in minutes to do backup
   tt = 60
   'set date/time for next backup
   '
   OTime = AddMinutes(OTime, tt)
   CurrentDb.Execute "UPDATE Parameters SET NextBackup = '" & OTime & "' WHERE ID = 1"
At_the_door:
   Exit Function
   
Err_Backup:
   MsgBox Err.Number & "-" & Err.Description
   Resume At_the_door
End Function
'-----------
Public Function AddMinutes(ByVal sTime As String, mm As Long) As String
    Dim dt As Date

    dt = CDate(sTime)
    If dt < Now Then dt = Now
    dt = DateAdd("n", mm, dt)
    
    AddMinutes = Format(dt, "dd/mm/yyyy h:nnam/pm")
    
End Function
'--------------
Public Sub FileCopy(Srce As String, Dest As String)
  Dim fs As Object
 
  Set fs = CreateObject("Scripting.FileSystemObject")
  fs.CopyFile Srce, Dest
  Set fs = Nothing
End Sub
 

AccessBlaster

Registered User.
Local time
Today, 14:02
Joined
May 22, 2010
Messages
5,827
Setup a google account, and get 15GB of free space. Since this a small business, I would copy the backend to my google drive everyday by dropping it on the drive icon. Done and free. Probably beats what your doing now.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:02
Joined
Feb 28, 2001
Messages
27,001
for two users on a home network, I would not bother splitting the db.

With the caveat that the FIRST TIME you encounter a usage conflict, you know you should have split the FE from the BE. If the two users most commonly use the same forms, then you are looking at a VERY LIKELY case of destructive interference just waiting to happen, particularly if both users are in at the same time adding records to the same table.

If their work DID NOT overlap, such as one side does accounts payable and the other side does accounts receivable (or something like that) then maybe it is no biggie after all. So if deciding to not split, just be careful and be rational about the possibility of work "collisions" when both are trying to do the same thing.
 

Solo712

Registered User.
Local time
Today, 17:02
Joined
Oct 19, 2012
Messages
828
If the two users most commonly use the same forms, then you are looking at a VERY LIKELY case of destructive interference just waiting to happen, particularly if both users are in at the same time adding records to the same table.
This is what Putin calls "polnyi chush"! First of all, splitting the database does not protect against collisions. Both users would be accessing the same back-end! Duh! Second, there is absolutely no "destructive interference" danger stemming from two or more people accessing the same same table. It will wholly suffice to set the form's recordlocks property to avoid any potential collisions or simultaneous updates.

https://docs.microsoft.com/en-us/office/vba/api/access.form.recordlocks

Best,
Jiri
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:02
Joined
Feb 28, 2001
Messages
27,001
Both users would be accessing the same back-end! Duh

But in the case you are advocating, both users would be accessing the same FRONT end, DUH!

In your hyperlink, you show a way that would indeed protect the back end from sharing issues by invoking Optimistic Locks. But those locks ain't the locks I'm talking about, and the ones that concern me ain't the ones that the article is talking about either. The lock is on the FORM OBJECT ITSELF, the object for which the Form_Load event makes sense. The article you mentioned makes no reference to that object. The article is all about the form's .Recordsource, which is a table, query, or explicit SQL statement.

In the back end, with judicious use of either Optimistic Locks or No Locks, you have a very SMALL window - milliseconds or less - during which something is locked open for update (which USUALLY for Access means temporarily locked DURING update). But when the SAME FRONT END file is opened and both users of it open the same form, that form is open as a long-term object. The same form source object is open for both, in this case literally. And if both are adding records, trouble ensues.

When you split the FE and give both parties individual FE copies, there is ZERO.POINT.ZERO chance of a front-end form lock conflict because the FE isn't shared. In perhaps ten years of dealing with Access at the admin level, every time I had someone run the master copy from our shared drive (rather than making a local copy), someone else would do the same thing and I would need to recover from backup. When you share a front-end that has form objects in it, you invite corruption.

Maybe I'm coming across harshly, Jiri, and that isn't my intention. But your experience is DECIDEDLY not my experience. Sharing FE files rather than distributing copies is trouble looking for a place to happen.
 

Solo712

Registered User.
Local time
Today, 17:02
Joined
Oct 19, 2012
Messages
828
But in the case you are advocating, both users would be accessing the same FRONT end, DUH!

In your hyperlink, you show a way that would indeed protect the back end from sharing issues by invoking Optimistic Locks. But those locks ain't the locks I'm talking about, and the ones that concern me ain't the ones that the article is talking about either. The lock is on the FORM OBJECT ITSELF, the object for which the Form_Load event makes sense. The article you mentioned makes no reference to that object. The article is all about the form's .Recordsource, which is a table, query, or explicit SQL statement.

In the back end, with judicious use of either Optimistic Locks or No Locks, you have a very SMALL window - milliseconds or less - during which something is locked open for update (which USUALLY for Access means temporarily locked DURING update). But when the SAME FRONT END file is opened and both users of it open the same form, that form is open as a long-term object. The same form source object is open for both, in this case literally. And if both are adding records, trouble ensues.

When you split the FE and give both parties individual FE copies, there is ZERO.POINT.ZERO chance of a front-end form lock conflict because the FE isn't shared. In perhaps ten years of dealing with Access at the admin level, every time I had someone run the master copy from our shared drive (rather than making a local copy), someone else would do the same thing and I would need to recover from backup. When you share a front-end that has form objects in it, you invite corruption.

Maybe I'm coming across harshly, Jiri, and that isn't my intention. But your experience is DECIDEDLY not my experience. Sharing FE files rather than distributing copies is trouble looking for a place to happen.

Sorry, pal, none of this makes any sense to me. I have never heard or seen anyone writing about Front End in an unsplit database, and the notion itself is bafflegab. In shared environments where the DB is not split users access all the resources in one file on the network. Code, queries, forms, resources are moved to the requesting computer's memory where they are processed and resulting requests are sent to the the Jet engine for execution. There are no issues with shared "front end" collisions. There are no locks necessary for "form objects". Any number of users can download a form without any danger to the integrity of the database. You lock tables, not forms. Period. End of discussion.

Best,
Jiri
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:02
Joined
Feb 28, 2001
Messages
27,001
Jiri, WINDOWS does the locking. Access uses the .LDB file to keep track of locked byte ranges within a file. I am trying to avoid misunderstanding here. If my choice of language was misleading, I apologize for that. I will try to be crystal clear.

As I am using the term, a "shared front end" is potentially used by more than one user simultaneously and resides in the same physical file on the central server. This is the same in effect as running a non-split DB from a server, since that which WOULD have been the FE file is part of and included in the overall shared DB file.

By contrast, a "distributed front end" is copied from the public repository for that FE but is not run from there. The FEs are opened from their copies. Even if two users are in the database at the same time, only the BE file is actually being shared. The copies of the FE that were distributed to the various users are being used individually as private i.e. non-shared files.

A shared unsplit DB or shared FE runs into file system locks AND yes, it runs into Access locks managed through the .LDB file. EVERY data structure in an Access file is locked when in use, albeit only briefly for tables in mid-query if you set proper locking. If you look, there will be an .LDB file for BOTH components of a split DB if they have different names or if they are in different folders. The .LDB is always in the folder from which the file was opened, whether we are talking FE, BE, or unsplit DB.

It is possible for a user in the shared file to have a collision with another user sharing that file from the same place. This is one of the many mechanisms by which FE files get corrupted. The collision occurs with a component OTHER THAN the data in tables.

The problem with a shared-in-place FE file is that the locks are managed on the server where the files reside and there is where the lock collisions are detected. This is a side-effect of the Windows File Locking concept in which file locks across a network are managed by the file system of the system of file residence. That is, there is no central lock manager for all files in a domain. Each server or system manages its own files - which is called a Distributed Lock Manager.

Here is a link to the Wikipedia article on file sharing. It has a Windows portion.

https://en.wikipedia.org/wiki/File_locking

In the paragraphs for Windows locking, there is a key phrase you need to know:

The client will observe slower access because read and write operations must be sent to the server where the file is stored.

A distributed FE also has file and Access locks but because the copies were distributed to each user and they don't run from the same place, there are no lock collisions. I'll say that again: locks but no collisions. That is because the distributed FEs are separate, privately owned files local to each user's individual workstation.

You mentioned stuff that gets pulled across the net and stated that it goes into the workstation's local memory. I offer no argument on that statement but point out that it is the tip of the iceberg. You implied that it is private - but it is not always so. If it came from a shared area and was copied to the workstation, then the host disk buffer from which that shared item was copied represents a locked byte range for every other user that tries to access it. If a 2nd user TRIES to access it while the byte range is locked, the attempt will fail - and that is how file corruption occurs.
 

peskywinnets

Registered User.
Local time
Today, 21:02
Joined
Feb 4, 2014
Messages
576
To re-iterate ...I already have a NAS (installed with two HDs...mirroring one another), every few days (when I remember) I copy my Access database from my PC's hard drive over onto the NAS (I don't run my database on the NAS, because frankly it slows things up)...but the problematic bit from the last sentence is "when I remember" ...so I guess what I seek is an app that will backup my access database (running on my PC's local drive) to the NAS on a daily basis (having a few versions for fallback reasons)...I had a look at freefilesync, but not sure if that covers me.

Re splitting the FE/BE ...I'm not sufficiently DB literate to know what's best ...but I can tell you when we tried to access my database via two PCs prior we did get annoying popup warnings along the lines of "another user is accessing this data & it's locked" (or similar).

I should also declare that because it's just me (& occasionally my wife), I don't use forms much at all...I mainly use queries to get all the table data I need at any given time.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:02
Joined
Feb 28, 2001
Messages
27,001
Thanks for the feedback.

1. Regarding backup and the NAS: Look into creating a script using VB Scripting (which runs on anything since Win7 came out). Doesn't have to be much - just a file copy operation at some unlikely time. You can synthesize a date-stamped name for the backup copy of the file and can also do date magic to purge older copies - or dump them into a zip file. Run it by hand for testing, but once it works you can schedule the script using Task Scheduler. You are the owner of the system (literally, user Owner) so are by default the administrator. Therefore, you will have no privilege or permission barriers to create such a scheduled task.

2. Thank you for confirming that you were getting warnings when sharing the unsplit DB. The fact that you don't use forms might have saved you some corruption.

3. Just be careful that you and your wife discuss this situation and perhaps coordinate your efforts to avoid overlapping access. BUT if you split the DB, all the queries go to the FE file so, if you set the queries for Optimistic locking or No Locks, even your minor collisions might go away - but certainly should be reduced.
 

Solo712

Registered User.
Local time
Today, 17:02
Joined
Oct 19, 2012
Messages
828
Re splitting the FE/BE ...I'm not sufficiently DB literate to know what's best ...but I can tell you when we tried to access my database via two PCs prior we did get annoying popup warnings along the lines of "another user is accessing this data & it's locked" (or similar).

This has nothing to do with with FE/BE. Splitting the database has nothing to do with concurrency issues. You split the databases to gain performance, not to avoid collisions. If you operate the same way all the time, you will see the same locking messages even after you split the database.

Incidentally I sent you a routine to implement an automatic (dated) backup in #7. Did you try it?

Best,
Jiri
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:02
Joined
Feb 28, 2001
Messages
27,001
Jiri and I may have some disagreements but we DO agree that splitting a DB helps performance ... but I would add that it does so IF AND ONLY IF you then distribute a copy of the resultant FE to each user and run the copies locally. That way, at least the forms, reports, queries, macros, and modules are local to your workstations, which gives you much faster access to those items. If you share the FE file from a common folder, you have only done half the job because those items are only coming to you via your network. Local access = disk speeds = 10 millisecond latency for a 6,000 RPM disk (which is SLOW by modern standards). Network access = take the disk speed of the server and ADD to that the latency of sending everything through a network cable = add maybe a millisecond or three. To find out how much a network action takes, PING one machine from another and see how much the average delay comes out.

Further, you DO distribute copies of the FE to avoid FE collisions. If you also adjust queries, forms, and reports according to Jiri's referenced article on locking (which was useful, thank you Jiri), you should see far fewer locking messages from the distributed case. Maybe even none.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:02
Joined
Apr 27, 2015
Messages
6,286
Peskywinnets...I have some personal experience with this. A relative small app with a single user (me) and everything was fine until I moved the whole thing to a share drive and told anyone who wanted to use it where it was.

3 others were interested and it lasted about a week before things went south. I started getting reports of unstable conditions and other problems until it stoped working altogether. That was 3 years ago. Since splitting it, on the advice of the members of this forum, I have not had a single crash.

Coincidence? You be the judge...
 

peskywinnets

Registered User.
Local time
Today, 21:02
Joined
Feb 4, 2014
Messages
576
Incidentally I sent you a routine to implement an automatic (dated) backup in #7. Did you try it?

Best,
Jiri

thanks for the code...no, I've not tried it yet (before deploying anything, I like to understand how it is used, the impact etc. - I;'m not really a programmer...more a kludger, so sometimes whats easy for most round these parts, takes me a little while!) But I'll certainly try to understand it it - thanks for sharing
 

Mark_

Longboard on the internet
Local time
Today, 14:02
Joined
Sep 12, 2017
Messages
2,111
peskywinnets
Since this does come up from time to time, on major reason to split a database is because of how ACCESS works internally. Your "Program" is really a set of related data tables. Some hold your data (the tables you've created) and some hold your "Program" (procedures, marcos, and VBA you've written).

When all of this is in ONE file, you need to worry about not only how your OS handles file sharing but also how ACCESS deals with two or more users accessing the same record in its "Program" database. Two people in the same "Form" are really accessing the same set of related records in ACCESS. If you ever try to update a form while another users is working in that database you'll notice ACCESS doesn't like it. Likewise you can run into issues where two or more users are trying to read the same records that make up your form and have issues because of it.

When you "Split" the database, you have one file that holds your data. All of your customer information, sales, contacts, pudding recipies, what not are kept in ONE file on disk. The database that holds your code, your forms and your reports is kept in a second file.

As a developer you normally send a copy of your front end to each user. They normally run it off of their local hard drive. This reduces network traffic and avoids issues when more than one user is accessing the same file on disk. More importantly it allows the developer to work on new features and bug fixes while others are using the database.

Even as a single user, you will find having a "Master" copy of your front end (the one for current production) separate from your development copy is invaluable. Means that if you mess things up you can always go back to a known good version without issues. Also means that if you do accidentally delete vital pieces you can always get them back from your current copy.

If you go with Jeri's suggestion and have only ONE file on disk you run into issues when you are trying to work out a new form / report but can't afford to take time off from work to do so. You'll then have to work out how to import your data from your "Live" system to your "Test" system for you to make your "Test" system live.

To me, it is simply far easier to keep the two separate.

How you do his is by having one spot that keeps your data (your back end) and another for development. I normally find having your master copy of the front end in a subfolder under your data works, though this is personal preference. You'd then have a batch file that copies your master to your local PC and starts the local copy. This is what you run every time you "Start the program". It means you'll ALWAYS have the latest and greatest copy and you'll never need worry about another user using the same file.
 

Users who are viewing this thread

Top Bottom