Access Front end with Sql back end

[chriscardwell06]

We currently have an access database that's used by 10-15 people. It controls all of our engineering jobs, quoting jobs, as well as the metrics we report for these functions. The next step is to add our manufacturing schedule into it. Within the past year we've started getting issues with the database needing to be compacted and repaired on a regular basis. From everything I read it seems to be from the increase in users. Before adding the new section we are looking to better alternatives for the entire database. With all my research I've been looking toward making it a Sql back end and keeping the access front end. My main question that I can't seem to find answers to is will this help with the increase in users. Is the corruption of data due to the amount of users on the back end of the database or the front end. Any advice would be greatly appreciated.

 

[theDBguy]

Hi. Please correct me if I'm wrong but it sounds like you were not using a split configuration with your Access database. Is this correct? Meaning, you all share the same front end (or possibly no back end at all) from probably a shared network folder? If so, that would definitely cause some problems for you.

 

[chriscardwell06]

I did split the database. It wasn't originally split years ago. All the tables reside on the back end on the network. There's currently a set of tables for engineering and a set for quoting. Then I created the GUI front end that resides on each users computer for the to access the data.

 

[theDBguy]

I did split the database. It wasn't originally split years ago. All the tables reside on the back end on the network. There's currently a set of tables for engineering and a set for quoting. Then I created the GUI front end that resides on each users computer for the to access the data.

Okay, that is the recommended method. However, depending on what's happening in the FE, it might be necessary to C&R it every once in a while. When you said you had to C&R the db, were you referring to the BE file then?

 

[chriscardwell06]

Yes on the BE. It would corrupt and lock everyone out. I'd get everyone out and then compact it. That's why I was looking to make the back end SQL and hoping this would take care of those errors for me.

 

[theDBguy]

Yes on the BE. It would corrupt and lock everyone out. I'd get everyone out and then compact it. That's why I was looking to make the back end SQL and hoping this would take care of those errors for me.

When you have to C&R the BE file, what was its file size?

 

[chriscardwell06]

At this moment it's 24.3 mb. The front end is 42.62 mb but I don't see that being an issue. I can check the size the next time we have to do it. It can be random. Sometimes works for a couple of weeks and sometimes every single day it messes up.

 

[isladogs]

Are any of your users connecting to the network using a wireless connection e.g. Using a laptop or tablet?
When the BE gets corrupted, what actions trigger it?
What is the nature of the corruption? Database completely unusable? individual tables or tables have corrupted records e.g. with 'chinese' characters? Something else?

When corruption does occur, how do you recover it?

Whatever the answers to the above, a SQL Server BE will be more stable and will cope better with large numbers of users without a performance drop. However an Access BE should cope with the number of users you have.

 

[The_Doc_Man]

The need to C&R a database less than 50 Mbytes usually means something else is going on. In a split database with every user having a separate front end file and having one common back end file, you shouldn't need to do that much repair work.

Do you do a LOT of table churning? I.e. insertions, updates, and/or deletions in large quantities by each user? An increase in users isn't necessarily a direct cause of needing frequent C&R actions. But a DB that churns a lot will do it every time.

 

[chriscardwell06]

I would say at times yes there could be a lot of churning. When a user enters all the items under a sales order it's almost like a bom. They then will enter all the data for it and click through different buttons to timestamp where a job is. This is looked at by multiple people to see the process of a job as well as to get monthly metrics.

That I'm aware of no one is on wifi here. Everyone is hardwired.

When it goes down everyone is kicked out or can't get into it. Normally one user still is in it and has no clue it's down. They can continue to work. The error is an unrecognizable database I believe. (I'll see if we can recreate it to be exact) Then I have to get everyone out, open the actual BE table itself, and then it has to compact it. We have found that certain users use the save button in the upper left, instead of the ones on the forms, and this will make it crash. It's not everyone though so go figure.

 

[The_Doc_Man]

The discussion about saving means you have a couple of loopholes in the way you secured the front end forms. Usually, you want to trap attempts to update the current record using the BeforeUpdate event (which can be canceled so as to prevent updating).

What I did was if you clicked my save button, I set a flag and triggered the update. But you somehow managed to trigger a navigation (which I eventually blocked off later), you would update by navigation except that you wouldn't set my internal flag when you did that. I disallowed the update and popped up a snarky message about using the SAVE button to cleanly save what you were doing.

 

[chriscardwell06]

So we did some testing this morning. I had a user crash it. Once they did we got the following message for the user and anyone trying to get in at that point.

"unrecognized database format"

From there when I open the table to C&R I get the error

"Microsoft Access has detected that this database is in an inconsistent state."

The user was clicking through the buttons to put a job in check or in qc. These buttons only use the onclick funtion. He said he could not remember if it did it while clicking the buttons or when he hit save (which he used the one in the upper left toolbar and not one that is a custom button). I added those buttons to the database around 6 years ago and they haven't changed since. We've only been having these issues for about the past 6 months.

I attempted to attach screenshots but haven't figured out how to do that here yet.