Multiple Repairs Daily (what a pain!) 3

[jwjw]

I am running a frontend/backend db over a NT network. I have 8 users, all running Win2K and Access2K. I do my developing on a copy of the frontend on my C drive and each night copy this over the one on the network. The problem that arrises daily is first thing in the morning, lets say 5 users logs in to the db. After a short time someone else tries to log on and they get the error that the backend is corrupt and they can't get in. Thus all must get out, the db has to be repaired and then the same scenario happens once again.

Any suggestions? I repair, compact and compile many times daily.

Thanks,

Jack Walsh
jack_walsh@urmc.rochester.edu

 

[SgtJarrow]

Are you saying you compact the backend throughout the day with the users using it??? There would be your corruption point. If a user tries to hit your db as you are compacting it, there is potential for damage to the db....don't do compact that often....once a day should be sufficient....you can even set a bat job on a task scheduler to open the db for you over the night or something....you don't even need to be present. Then, each day when you come in....you are packed and ready to go. 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

 

[jwjw]

I make them all exit the db, then I repair and compact it. They all find that quite annoying. It very rarly happens to me that I can't get in.

Jack

 

[SgtJarrow]

Okay...since that ain't your problem (most likely), the next thing to look at is temporary tables and global variable declarations....Are you using temporary tables which are created and deleted for each user or process? perhaps using MakeTable Queries and the like? These will tend to inflate you database quickly and can easily cause corruption. If this is the case, you will want to consider using each users front end to store their own temp tables...you would also want to set the compact on close option for each of the front ends if you are putting the temp tables here. Or you can create a separate database when you need the temp table, use it, then delete the database, all from within the code....If these don't sound like they can help, I'll try to think of something else. 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

 

[jwjw]

Yes I do use some temp tables and global variables. But to clarify my overall db - both backend and frontend are on the network and each user has a desktop shortcut to the front end. Because we need to repair so many times daily, I just email the users and tell them that the db has been repaired and they click on their shortcut and they're back in.

Should I set up a third db for these tables?

Jack

 

[SgtJarrow]

Nope.....I will bet you a six-pack that the trouble lies in the use of one front end and shortcuts to the front end....

Your setup should be one back and and a separate front end on each user's PC.....The amount of network traffic you are creating with your current setup is probably creating the errors....read/writes to the database are tremendous is a situation as you have it....

Just try the above change and I think you will be highly surprised at the results. 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]

Along the same lines as what mstrmage1768 has said;

what we have done here is that our shortcut actually points to a batch job the copies the front end to the users pc and then opens their front end. Takes a second or two to run but it ensure the user always has a current version of the front end on their pc. We DO NOT allow the users to open thier local versions of the mdb.

 

[KUZZ]

I had my DB crash several time because I was using it in a 3 user environment, with the entire DB on the network. After splitting it (always make a backup before doing so) and increasing the environment to about 7 users, the problem has dissapeared. If somebody crashes their FRONT END, the BE data is safe. I am Commpacting/repairing my BE about once a week.

Allanon - could you please let me se your BAT file code for copying the front end from networkt ot local descktop. Good luck,
Kuzz

"Time spent debating the impossible subtracts from the time during which you
can try to accomplish it."

 

[allanon]

I did not write this but here it is. It works really well to.

@ECHO OFF
copy \\wcbap15\wsimg\distrib\CmaUpdate\mbwcbsysxp.MDE C:\WCBAPPS\CMA\mbwcbsys.MDE
c:
cd \temp
START /MAX "C:\program files\OFFICE97\OFFICE\MSACCESS.EXE" "C:\WCBAPPS\CMA\mbwcbsys.MDE"

This copies the front end to the user machine and then opens it. You would of course have to modify yours to point to your own access executable (we have multiple versions on each machine).

Hope this helps.

 

[markphsd]

Hey, I can't recall the thread, but there is a FAQ on here-maybe this forum, maybe Access Modules VBA- on how to ensure that the users have the correct version. I used to use a batch file, but this FAQ method is cooler. It uses a temp table that holds a version number, every time you open the db it checks a master db on the server to see if the versions match up. If they don't match, it copies over the db then re-opens the db, if they do match the db just opens normaly. This removes that unecessary delay that the batch file does.


If you can't find it let me know.

 

[annsolomon]

Jack, Robert has given you some excellent advice. Leaving your front end on the server is asking for trouble.

allanon and markphsd,

I have 40+ users on a number of databases with front ends on their PCs. Very time consuming to go around and update their front ends when I make changes. Thanks very much for sharing your ideas and showing me a better way!

Ann

 

[jwjw]

I just want to thank all of you for your great ideas. I made a batch file that downloads the frontend to the users C: drive and runs it from there and I haven't had one repair and refresh. Thanks again.

Jack

 

[nat1967]

HI markphsd,

I think this is the FAQ you are refering to just in case anyone else was looking for it.

faq705-2010

HTH Have A Great Day!!!,

Nathan
Senior Test Lead

 

[Terpsfan]

I run a VB .exe as a short cut on the user's machine that automatically updates their front end with the latest version, which is kept on the network. It then opens the database maximized.

Const Network As String = "E:\AccessDB\Foreclosure.mde"
Const Client As String = "D:\AccessDB\Foreclosure1.mde"
Dim fso As FileSystemObject

Private Sub Form_Load()

'Instantiate file scripting object
Set fso = New FileSystemObject

'Copy file to client if no file exists on client
If fso.FileExists(Client) = False Then
fso.CopyFile Network, Client
MsgBox Client & " new file copied to client folder."


'Copy file latest update to client if file exists
'and date last modified on network is greater than client
ElseIf fso.GetFile(Network).DateLastModified > _
fso.GetFile(Client).DateLastAccessed Then
fso.CopyFile Network, Client, True
MsgBox Client & " new update copied to client folder."
End If

'Dereference file scripting object
Set fso = Nothing

'Open access front end with maximized focus
Shell "D:\Program Files\MicrosoftOffice\Office\MSAccess.exe D:\AccessDB\Foreclosure1.mde", vbMaximizedFocus

'End program
End

End Sub