Crashing database (1 Viewer)

Paul Chernoff

Registered User.
Local time
Today, 07:00
Joined
Aug 24, 2018
Messages
25
We have Access databases running with simple file sharing. The database files are on a Windows Server 2012R2. The clients are running Windows 10 under VMware. The Windows clients are only running Access, users have been warned not to run any other software. The data is in one file, most of the programming is in a 2nd and we have a 3rd file with additional programming for exports.

The data file is screwing up 2-3 times a day with 1-5 users at any time, usually just 2. When this happens I tell everyone to quit Access so I can open up the data file and allow it to repair itself. It is complaining about being out of sync. It repairs and the table that would hold references to any objects that have disappeared hasn't been formed. No one has noticed any damage but who knows. This last time the database shrunk from 75MB to 65MB, so that concerns me.

Any troubleshooting suggestions to make this database more stable? When we have time we will start writing an RFP and looking for someone to rewrite the database from scratch. The design is around 28 years old (was rewritten around 1999 in Access based on an Informix database (no design changes at all).
 

Ranman256

Well-known member
Local time
Today, 07:00
Joined
Apr 9, 2015
Messages
4,337
you do have the database split, FE app and BE tables?
BE tables db on the network, and each user has their own copy of the FE?
(sharing FE apps can crash)

all the above True?
 

Paul Chernoff

Registered User.
Local time
Today, 07:00
Joined
Aug 24, 2018
Messages
25
All of the tables are on the server. So that is probably the problem.

The file WPDprog has the programming work. I assume this is what you mean by the FE app.

The file WPDdata has the data and I assume that this is what you mean by the BE tables.

So I should copy WPDprog to each workstation? Sorry, but I am not familiar with the terms FE and BE.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:00
Joined
Feb 28, 2001
Messages
26,996
It comes from considering what you see as the "front" of the application and the underlying tables and relationships as being the "backside" of the application. So FE = Front End, BE = Back End.

Traditionally, as well as for tech reasons, you use something in the Database Tools ribbon to SPLIT a database into an FE file and a BE file. The result of a split is that the queries, forms, reports, macros, and code modules go into the FE while the tables and their relationships go into the BE. You may have to diddle a bit with the Link Manager (another of those Database Tool things) to make a proper distributable FE.

This forum's SEARCH function should help you find articles on database splitting. It is a popular topic.

Here's a link to WHY you do this:

https://www.access-programmers.co.uk/forums/showthread.php?t=301939
 

Paul Chernoff

Registered User.
Local time
Today, 07:00
Joined
Aug 24, 2018
Messages
25
Of course, FE and BE.

I think the programmer has taken care of this issue, though he never mentioned giving each user their own FE app. I will cover this issue with him. The previous database owner ran everything single user, he would simply make multiple copies of the database, with all but one being treated as a READ ONLY database.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:00
Joined
Sep 21, 2011
Messages
14,038
You mention in your intro post that it has been split as recommended by someone on this forum.?

The database is written so there is one database with all of the programming and one with all of the data, just as recommended by a member here.
 

Paul Chernoff

Registered User.
Local time
Today, 07:00
Joined
Aug 24, 2018
Messages
25
Someone recommended splitting the files, and the programmer had already done this back in 1999. I will talk with him about this issue and his experience with other clients.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:00
Joined
Feb 28, 2001
Messages
26,996
Yikes! 1999 ??????

What VERSION of Access are we talking about here?

Please tell me it has been upgraded since then.
 

isladogs

MVP / VIP
Local time
Today, 11:00
Joined
Jan 14, 2017
Messages
18,186
Yikes! 1999 ??????
What VERSION of Access are we talking about here?
Please tell me it has been upgraded since then.

Hey! What's wrong with ancient versions of Access?
Answer - plenty actually!
Anyway I can beat that .... though it is on a VM rather than regular use!

 

Attachments

  • Capture.jpg
    Capture.jpg
    66.2 KB · Views: 195

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:00
Joined
Feb 28, 2001
Messages
26,996
The most interesting part of any system like that is that it still runs in the face of all of the Windows patches that have come down the pike trying to secure the unsecurable.
 

GinaWhipp

AWF VIP
Local time
Today, 07:00
Joined
Jun 21, 2011
Messages
5,901
Hmm, so you say it's screwing up but that doesn't say much. What error messages are you getting? What version of Access? Is Replication involved?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:00
Joined
Feb 19, 2002
Messages
42,970
Once the FE is distributed so that each user has his own personal copy of the FE, the app will be more stable. Think about it this way. Would you install Word.exe on a server and have everyone open the Word executable? Even though the Access app is not an executable, it should be treated as one and not shared. Only the BE (data database) is shared.

There is one more potential problem. If you are using a wireless LAN, Access becomes unstable if the LAN has interruptions that cause the FE to lose connection to the BE. Access cannot recover from this. It is of course worse when every user has opened the same physical copy of the FE. When one user looses connection, ALL users are impacted. With each user having his own copy of the FE, only the user who lost connection will be impacted and have to shut down. However, if the blip happens during a sensitive event such as a data write, you are still in jeopardy of having the BE corrupted and causing a data loss. Make absolutely certain you back up several times each day so that you have convenient restore points.

If you have the wireless LAN and cannot convert it to wired, your best option is to convert the BE to SQL Server. Access still cannot recover from a "blip" but you are unlikely to have any data corruption because SQL Server is more robust at handling network problems.

And finally, The simple way to distribute the FE is to create a batch file. The batch file copies the FE from the master folder on the server to a specific local folder and then starts the database. Each user has a shortcut to run the batch file (which is located on the server for ease of maintenance). With this method, every time a user opens the app, a fresh copy of the FE is copied down to his hard drive. This eliminates any bloat issues you might have assuming that every time the programmer makes a change, he compacts the FE before putting the new version in the master folder. Write back if you want more details about the various distribution methods.
 

isladogs

MVP / VIP
Local time
Today, 11:00
Joined
Jan 14, 2017
Messages
18,186
The most interesting part of any system like that is that it still runs in the face of all of the Windows patches that have come down the pike trying to secure the unsecurable.

Geriatric versions of Access like 2.0 almost certainly wouldn't work in current versions of Windows.
My VM with that is running on Win XP.

FWIW, Access 1.0 will only run in Win 3.1 and its variants ... AFAIK
 

Paul Chernoff

Registered User.
Local time
Today, 07:00
Joined
Aug 24, 2018
Messages
25
We are running the current version of Access365. I went to each computer and made sure all Office365 updates were installed last week. When we got the database the previous owner was using Access 2003. The developer also moved to the current version of Access. I made sure we were using all new software (OS and Office) and not sticking to XP and Access 2003.

Initial results of putting the FE database on local drives helped, got the crashing down to once a day. Today we've had 3 crashes in a 4 minute period.

In terms of networking, these are our most recent Cat 6 drops. No wireless involved.
 

Paul Chernoff

Registered User.
Local time
Today, 07:00
Joined
Aug 24, 2018
Messages
25
One more question. All programming are macros. Would it make any difference to upgrade the macros to Visual Basic? Feel like I am clutching at straws here.

We are slowly ramping on the users to see if one of them might be triggering the problem. Which doesn't make sense because once we put the FE on local computers we could go 5 hours with no problems with all 4 users.
 

Paul Chernoff

Registered User.
Local time
Today, 07:00
Joined
Aug 24, 2018
Messages
25
I am suspecting that one person has a bad connection to the network. Her IP phone reset today, not a good sign. Our Cat 6 wiring first goes into a Cisco phone and then to the computer.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:00
Joined
Feb 28, 2001
Messages
26,996
Your comment about the connection going through the phone to get to the backbone is a bit strange. ANYTHING that interrupts the network connection has a serious potential of crashing and corrupting a DB file.

Your best friend here will be the Windows event logs because they can be correlated to other events that might appear in other logs. Everything time-tags based on the system clock, so if two logs have entries at the same second, they WERE effectively simultaneous (or within the same tick of the internal clock, anyway). You SHOULD see some sort of Application event log when Access crashes. It would be interesting to know if there was a network drop not more than 1 minute before the app crash. If so, that would tend to confirm that the network drop did some damage.

You get to these logs from Settings >> Control Panel >> Administrative Tools >> Event Viewer. You should have two or three different logs to choose among. Since they are all time-tagged to the second and sorted by time, it should be easy to quick-scroll them to any critical areas.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:00
Joined
Sep 21, 2011
Messages
14,038
Pretty standard practice these days I would have thought.?
That is how our network was setup. Halves the number of ports required.

Try changing the lead(s), even the phone.? Swap the phone with another user even.?

I am suspecting that one person has a bad connection to the network. Her IP phone reset today, not a good sign. Our Cat 6 wiring first goes into a Cisco phone and then to the computer.
 

Paul Chernoff

Registered User.
Local time
Today, 07:00
Joined
Aug 24, 2018
Messages
25
The phone setup is very common and we have been using it for 6 years. The phones are 1GB and act as routers. This one phone is giving me problems though, first reported to me today. It has been having problems working as a phone though the routing seems to work fine. But there can be a hiccup which messes everything connections. We use an editorial workflow product, K4, and it is very sensitive to network problems, so if the phones were causing network problems we would not be getting our main product out the door.

Thanks for the log information, they may come in handy.

Thanks for the note on bad references. I'll look into it.
 

Users who are viewing this thread

Top Bottom