Crashing database

[Paul Chernoff]

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]

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]

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]

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]

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]

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]

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]

Yikes! 1999 ??????

What VERSION of Access are we talking about here?

Please tell me it has been upgraded since then.

 

[isladogs]

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!

 

[The_Doc_Man]

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]

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?

 

[isladogs]

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]

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]

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]

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]

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]

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.

 

[GinaWhipp]

Hmm, upon reading I see this database was upgraded. You might be experiencing issues as the result of *bad* References. Have a look here...

https://www.access-diva.com/d5.html

 

[Paul Chernoff]

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.

 

[Paul Chernoff]

So I just swapped the phone with another and see if this phone has a problem connecting itself to the PB. I have other phones I can swap in once I program them.