DB splitting, or lack thereof, question

[JumpinJimRivers]

Set aside network performance issues and "revision ease" and someone please tell me whether splitting a database is ABSOLUTELY necessary for a multi-user db to WORK. Will random errors occur, such as with event procedures and form/control references, if it is not??? I ask because of the obvious, and that I cannot think of any other variable to make this happen!

If you're wondering why I have not split it, it's because it ironically slowed the performance when I tried (which sort of took away the "performance" benefit of splitting the db), and being able to revise the apps w/o interfering with users has not been a swaying force yet, provided that the db simply works.

Sorry for my Scroogeness.

 

[JeremyNYC]

I'm not sure what this means:
I ask because of the obvious, and that I cannot think of any other variable to make this happen!

What's the obvious? What variable are you talking about?

Whoever told you splitting your database would speed things up needs to do some more testing, because he or she was wrong. It definitely slows things down a bit. But the payoffs are immense.

I wouldn't even consider working with an application that wasn't split front and back.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[JumpinJimRivers]

Thanks for your reply Jeremy. Sorry for not making the obvious very obvious.

I understand that there are benefits to having a split db, but what I need to know is

whether a db MUST be split in a multi-user environment in order to work WITHOUT ERRORS???

Say two users had the same Non-split db file opened, and say they even had the same form opened too, and say they even tried performing the same action at the exact same time, will the modules/form/queries still work properly?

 

[JeremyNYC]

OK, I should have read this more closely. I say there's a performance hit. There's a performance hit when you move from one mdb sitting on your hard drive to a front end on your hard drive and a BE on a file server. But having a single mdb on the file server is far slower than either of the two other options. With the forms on the file server, you have to pull the forms across the wire, and that's TONS of network traffic. By putting the forms on your hard drive you make it so you only have to pull over the data, and that's MUCH faster.

I'll let you convince yourself.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[JumpinJimRivers]

OK, but I must still not be conveying my question clearly. I understand the performance/network traffic issues at stake if the db is not split and many users are using the same db file to do everything - In fact I've decided to go ahead and split the darn thing.

BUT! I still need to know if errors will occur within a single db file's queries/forms/modules when used simultaneously by more than one user/workstation.

If so then the fact that my db is not split and is being used my more than one user may explain why users are sometimes getting these errors.

 

[JumpinJimRivers]

You know, I really should have phrased my question entirely different from the start.

Something like this:
Some users will inconsistently get random errors in modules/queries/forms in our multi-user non-split db. These errors come from very simple procedures which make them seem unexplainable. Is there any other explanation for getting these errors other than bad design or coding??? Are there any other factors at hand here, such as the db not being split and being used my more than one user at once, bad software/hardware on local PC, or networking problems?

I'm I making any sense here??

 

[JeremyNYC]

Jim,

In general you will run into more corruption and more collisions by having one mdb used by many users. But this does not mean that splitting the database will relieve all the issues you describe. You will probably have to track down each one of those and debug the heck out of your application. I know that you said the errors are inconsistent, but about 99.9% of error can be traced and dealt with. I would suggest that you start trackingt these things pretty intensely, requiring users to stop what they're doing and come get you as soon as they get an error message on the screen, so that you can do a full interview with them to laearn about all of the conditions that lead to the problem.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[SgtJarrow]

Jeremy is right....I have developed both ways (unfortunately, in my early days, I kept one big database). In the end, the situation of multiple users sharing the same .mdb file causes many problems.....it really should be split. That being said, Jeremy is also right in the error tracking part....most errors can be successfully traced to something.....try including some error logging in your db or as Jeremy suggested, have users notify you immediately. I would include some audit trailing functions to track EVERYTHING a user does, store all this in an external flat log file and review this at errors, once a day, whatever. Find the descrepencies. Also, include a global error handler for your application that will handle any error...this global error handler should include FULL error details, such as error number, description, etc (see the Err property for all the available options), as well as what function or procedure created the error, and what the user was doing at the time of the error....none of this is to difficult to implement, and you will be surprised the amount of errors you may find.... Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[JeremyNYC]

Yeah, full error logging will make this MUCH easier. I've got code on my website that I use to do this in all my databases. There are, of course, many ways to make it happen, but the code I use has kept me happy for several years.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[KenReay]

Hi

My experience of having a single database (both data and Code) or a split database with data on the server and a shared FE on the server is that it is more prone to corruption. In the case of a single shared mdb, or a shared FE, users should not be updating the forms/code etc so I see no logical reason why 'intermittent' error should be more common in this situation, unless there are genuine bugs. The obvious candidate is having (supposed) local data (ie data specific to a user, such as last accessed record etc) held in the FE, with the FE on the Client PC, each user has their own copy, so such local values are 'private', but in a shared situation the code has to be more robust and effective to ensure user A does not corrupt user B's save values. In short, go for split FE / BE everytime, with FE on Local drive and BE on server.

All the other comments about logging errors etc I agree with 100%. Always have the system record what the user actually did, otherwise you are dependant on he/she telling you what they THINK they did, which is seldom the samne thing in my experience.

Good luck. Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JumpinJimRivers]

Alrighty, I think that clarifies things for me - thank you both for your input. I'm still not convinced however that ALL these errors are rooted somewhere in the db's design/code - if I took the time to explain some of them you'd understand why (granted, that is coming out of the mouth of someone inexperienced in maintaining a db). But! I AM convinced now that I need to split the db, regardless. Anyway, you've told me what I need to know so I can proceed with this investigation.

Thanks a bunch

 

[sblanche]

Very informative conversations--I have a database that I am planning on splitting up because of intermittent problems. Tracking changes has been mentioned quite a bit. How do I go about doing this? My users do not actually log into the database. Everyone has access and they just open the database by running the mdb.

 

[SgtJarrow]

sblanche,

With this situation (no user login, either custom of Access security driven) the best you can do is track the events that happen. You will not be able to determine who performed the action.

I find it easiest to create one global event procedure logger and have all my procedures just pass the appropriate arguements for logging....see JeremyNYC's web page as listed in his signature block for a good example, or send me an email and I will forward you one... Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[allanon]

You can track the computer that is logged into the database with very little effort. Place this code into a module and then you can call it to find out which computer is using the system. As long as all computer don't have the same name then you can track who does what.

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function TheUserName() As String
Dim szUName As String
Dim lCnt, lreturn As Long
Dim varRetVal As Variant

On Error GoTo TheUserNameError
szUName = String$(255, 0)
lCnt = 255
lreturn = GetUserName(szUName, lCnt)
If (lCnt > 0) Or (lreturn <> 0) Then
TheUserName = Mid(Left(szUName, lCnt - 1), 1, 8)
Else
TheUserName = &quot;&quot;
End If
Exit Function
TheUserNameError:
Select Case Err
Case Else: varRetVal = MsgBox(&quot;TheUserName&quot;, Err, Error)
End Select
TheUserName = &quot;&quot;
End Function

Then you call it like this:

MyUser = TheUserName()