Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-18-2018, 10:42 AM   #1
Paul Chernoff
Newly Registered User
 
Join Date: Aug 2018
Location: Washington DC
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Paul Chernoff is on a distinguished road
Crashing database

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).

Paul Chernoff is offline   Reply With Quote
Old 10-18-2018, 12:02 PM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,139
Thanks: 0
Thanked 683 Times in 668 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Crashing database

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?
Ranman256 is offline   Reply With Quote
Old 10-18-2018, 12:12 PM   #3
Paul Chernoff
Newly Registered User
 
Join Date: Aug 2018
Location: Washington DC
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Paul Chernoff is on a distinguished road
Re: Crashing database

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.

Paul Chernoff is offline   Reply With Quote
Old 10-18-2018, 12:42 PM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,472
Thanks: 62
Thanked 1,175 Times in 1,075 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Crashing database

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...d.php?t=301939
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 10-18-2018, 12:51 PM   #5
Paul Chernoff
Newly Registered User
 
Join Date: Aug 2018
Location: Washington DC
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Paul Chernoff is on a distinguished road
Re: Crashing database

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.
Paul Chernoff is offline   Reply With Quote
Old 10-18-2018, 12:58 PM   #6
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,609
Thanks: 309
Thanked 404 Times in 389 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Crashing database

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.
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is online now   Reply With Quote
Old 10-18-2018, 01:00 PM   #7
Paul Chernoff
Newly Registered User
 
Join Date: Aug 2018
Location: Washington DC
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Paul Chernoff is on a distinguished road
Re: Crashing database

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.

Paul Chernoff is offline   Reply With Quote
Old 10-18-2018, 02:50 PM   #8
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,472
Thanks: 62
Thanked 1,175 Times in 1,075 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Crashing database

Yikes! 1999 ??????

What VERSION of Access are we talking about here?

Please tell me it has been upgraded since then.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 10-18-2018, 02:57 PM   #9
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,987
Thanks: 92
Thanked 1,715 Times in 1,592 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Crashing database

Quote:
Originally Posted by The_Doc_Man View Post
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!

Attached Images
File Type: jpg Capture.jpg (66.2 KB, 77 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 10-18-2018, 07:11 PM   #10
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,472
Thanks: 62
Thanked 1,175 Times in 1,075 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Crashing database

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 10-18-2018, 07:34 PM   #11
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,222
Thanks: 20
Thanked 865 Times in 850 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: Crashing database

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?
__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
Old 10-18-2018, 07:46 PM   #12
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,433
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Crashing database

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-19-2018, 03:13 AM   #13
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,987
Thanks: 92
Thanked 1,715 Times in 1,592 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Crashing database

Quote:
Originally Posted by The_Doc_Man View Post
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 10-23-2018, 06:55 AM   #14
Paul Chernoff
Newly Registered User
 
Join Date: Aug 2018
Location: Washington DC
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Paul Chernoff is on a distinguished road
Re: Crashing database

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 is offline   Reply With Quote
Old 10-23-2018, 06:57 AM   #15
Paul Chernoff
Newly Registered User
 
Join Date: Aug 2018
Location: Washington DC
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Paul Chernoff is on a distinguished road
Re: Crashing database

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 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Crashing! Darrenc General 11 09-27-2012 01:52 AM
Database Keeps Crashing gemma-the-husky General 7 01-02-2012 06:29 PM
Would like some Help with crashing database brittaink General 5 11-01-2007 03:41 AM
Crashing Database Mez Queries 12 08-12-2005 07:39 AM
Crashing a database vivian General 3 11-27-2003 06:13 AM




All times are GMT -8. The time now is 01:42 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World