Will frontend & backend solve problem? 2

[sblanche]

I am having a problem with my database that I hope splitting it up to a front end on each pc and back end on the server may help to solve. I have instructed my users on how to exit properly. Unfortunately, when they get locked (our network is not the most reliable) they ctrl/alt/del and the .ldb file is left open. When the next person tries to open the DB they get an error message something like "the database was exited incorrectly or is corrupt, do you want to repair". If someone's frontend on their pc gets locked, could it still affect everyone else? Would splitting up the DB help eliminate this problem?
Thanks for your help-- SB

 

[jmcclain]

Splitting the database up may help out, as a problem with one user's front-end should not affect the other users.

Actually I think you will find that if only data resides on the back-end, a lot of your network problems may go away as the form and report objects are not being passed across the network (and subject to network issues).

I would recommend proceeding with splitting up the db!

Enjoy!

 

[vbajock]

I'd check the code to make sure objects are getting closed properly. Corrupt databases are often caused by crummy code as much as by network problems.

 

[sblanche]

jmcclain-
Thanks for the quick response. Guess I'm going to have to bite the bullet and split it up. I have been looking around tek-tips and found info on how to make sure users are using the most up-to-date copy of frontend. It's a bit over my head but I'll get it figured out eventually. Thanks again. LOVE THIS SITE.

 

[JeremyNYC]

I've got a couple of pages on my website about how to deploy databases. I use a batch file to do this, and it makes it easy to roll out new versions to the users--I never have to touch the individual PCs, and the front end only gets passed across the network if the user's version is out-of-date.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[sblanche]

Thanks Jeremy for responding. I have had a printout of your batch instructions sitting on my desk for quite a while. Unfortunatly I don't understand all that it says which is why I have not split my db yet. Now that I am having so much trouble with our network, I'm going to have to split it, so I will probably have to bother you with questions. Thanks again--SB

 

[vbajock]

What kind of network problems are you having? Splitting the database isn't really a cure for hardware problems.

 

[sblanche]

Most of the problems are because we periodically loose connection to the drive where my DB resides. Unfortunately I have no control over the hardware. Network support is in charge of that and they say they are working on it. On rare occasions, an individual user will lock up and ctrl/alt/del to get out and won't tell us until I threaten everyone with 40 lashes with a wet noodle. Then it's too late to see what's wrong. The ldb is left open and the DB gets crankey and want's to be repaired.

Thanks,--SB

 

[vbajock]

I never used mapped drives in any of my code or links. Use the absolute path, like \\myserver\datadir\mydata.mdb.

If your users are locking up on access you should split your mdbs. On the network, the BE should contain only the tables the users share, nothing else. Your local FE should have the all other objects, and any tables that are only used locally like temp tables or buffers. The local mdb should be set to compact on close. After you split the local mdbs set the paths as specified above and you will solve 90% of your problems.

Keep a copy of the current version of the FE on a public drive and set up a batch file or script to copy it to a standardized shared folder on the users drive. That way they can either copy a new version themselves or you can do it from your workstation or via log in script.

 

[sblanche]

jbajock--You suggest the local FE mdb should be set to compact on close--Currently, since my DB is located on a server with about 8 people using it at the same time, I do not have it set to compact on close (I do that at lunch when I make everyone get out of the DB). Does this mean that Jane's local mdb can compact when she closes the DB even though Joe has his local mdb open, and still working. Both mdb's are using the same BE on the server. Thanks--SB

 

[vbajock]

Yes you can compact the locals at anytime, anything going on in the local FE will not affect any one elses FE.

 

[sblanche]

vbajock-Thanks for the response back. Previously you said to check code to see if objects are getting closed properly. The database has been in use about 1 1/2 years. It used to be only about once a month when we experienced problems. I can't seem to pin point any problems in the form code. I am going to split so hopefully that will help. If you see any thing, please let me know. They can't use the X to close. In order to close a form, the user has either an EXIT button or CANCEL button on the form.
The EXIT BUTTON--
Private Sub Command178_Click()
On Error GoTo Err_Command178_Click
blnClose = True
DoCmd.Close acForm, "FRMCounMainTabEdit", acSaveNo
Exit_Command178_Click:
Exit Sub
Err_Command178_Click:
MsgBox Err.Description
Resume Exit_Command178_Click
End Sub

The Cancel button has:
Private Sub CancelButton_Click()
On Error GoTo Err_CancelButton_Click
blnClose = True
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close
Exit_CancelButton_Click:
Exit Sub
Err_CancelButton_Click:
MsgBox "Access will not allow you to Cancel unless there is actually information to Cancel. Please enter 1 character somewhere on the main tab and then select Cancel."
Resume Exit_CancelButton_Click
End Sub

The actual form has "on close"

Private Sub Form_Close()
DoCmd.SetWarnings True
End Sub

 

[vbajock]

None of the code you posted declares any objects that require a close statement.

If they are using the Cancel button and the form won't close, are they also triggering the error message shown in Err_CancelButton_Click ?

 

[sblanche]

The form closes fine when they use the close button.

When I do split the db and now that the batch file works to copy latest FE(thanks to JeremyNYC), I have some questions about how everyone else works from here. FYI- My users do not have to log into the DB so no security or way of telling who/if has DB open.

1- When I need to make changes to table structure, I assume I need to make sure no one has their FE end open and working. Only thing I can think of is telling everyone they have to be out of DB at certain time and then move the BE to another location to work.

2-If I move BE and someone has the FE open, will I corrupt their FE or cause damage to the BE?

3-When I make changes to FE--should I keep a copy of the FE with the BE on the Network and make changes there (since users will be using the BE this would concern me) or should I move the FE and a copy of the BE to my local pc, make changes there and then copy the FE up to the network for downloading to local pc.

(I will always make changes on copies and always have backups. I learned the hard way on that one.)

Thanks for everyonels help--SLB

 

[vbajock]

1- When I need to make changes to table structure, I assume I need to make sure no one has their FE end open and working. Only thing I can think of is telling everyone they have to be out of DB at certain time and then move the BE to another location to work.
- The only time you cannot make changes to a table is when a user has a form open whose recordsource is based on that table.

2-If I move BE and someone has the FE open, will I corrupt their FE or cause damage to the BE?
-By move, if you mean delete from the current location after moving it, yes this could cause problems, if you mean copy the database while someone has it open, I doubt you would cause any problems

3-When I make changes to FE--should I keep a copy of the FE with the BE on the Network and make changes there (since users will be using the BE this would concern me) or should I move the FE and a copy of the BE to my local pc, make changes there and then copy the FE up to the network for downloading to local pc.

- You want to keep a design copy of the FE and distribute to all PCs that use at one time.