Is access reliable?

[ahmed_optom]

Hi all,

I have been building an access database for our healthcare company for the past few months.

I have learnt so much. However, we have had several corrupt database issues, and Im starting to think that access just isnt up to it.

I am using a split database, with a batch file to load fresh front end files for about 20 users.

Several times a day, a front end will just become corrupt, and I ask them to restart, then it works. Today all the front ends just stopped working, run time error on start up. Nothing was done in the past few days to the front end, its been deployed for over a week. I try to recover etc, now the database is corrupted, the auto numbers are all reset and the data was being over written.

Is this poor design? I noticed the computer that has the most trouble is connected via wifi and regularly drops out. Im starting to think that this is the problem, but I would hope that access would not be so fragile as to lose the database every time a computer crashes or a wifi signal drops.

What are your thoughts? Im in two minds as to completely redevelop the access database, or ditch it and get a software package,

 

[DevTycoon]

Have u tried recompile /compact repair yet? Yes access is reliable if it is used the way it was intended. If you code is error prone no software will be fun to work with.

 

[NauticalGent]

What are the specifics regarding the FE batch file? Also, does every user have their own copy of the FE on their computer?

 

[ahmed_optom]

Have u tried recompile /compact repair yet? Yes access is reliable if it is used the way it was intended. If you code is error prone no software will be fun to work with.

Yes, I did that, the front end now opens, I can see the repair bar at the bottom, and then it says "the last time you opened it it caused a serious error" etc.

It also says "id is not an index in this table".

I appreciate it maybe my code, however, I dont understand how the front fail, and why it works on some machines and not others. They are all identical hardware.

 

[DevTycoon]

Decompile is a separate operation . Did you do that for sure?


Sent from my iPhone using Tapatalk

 

[ahmed_optom]

What are the specifics regarding the FE batch file? Also, does every user have their own copy of the FE on their computer?

Yes, so the batch file runs on start up, pulls a copy of the FE from a network folder, then places it on their desktop and runs it.

On most computers this works fine. Some seem to not copy properly.

Then there is the backend issues...A computer crashed whilst making a new entry, this seemed to create a duplicate id on the backend, and nothing seemed to be able to fix it. I immedately copied the backend, and then tried to fix it, by compact and repair, manually removing conflicting data.

Now if i cycle through records, its going through them at random rather than in order, and the auto number are over writing existing data.

Like I have said, I appreciate it could be my coding etc, however when I use a website with a msql backend, if i close the browser, or my pc crashes, it shouldnt crash the entire website, but I feel access in a split form is too fragile.

 

[ahmed_optom]

Decompile is a separate operation . Did you do that for sure?


Sent from my iPhone using Tapatalk

Yes, i decomplied the front end, it created a copy which was more corrupted.

It just left me with the mysycompact error table with 200+ entries, some are of the form

You tried to assign the Null value to a variable that is not a Variant data type.

Some are about permissions.

I have a working copy of the front end, its just when I try to connect it to a backup of the backend, the autonumbers are all reset and the data gets over written.

 

[DevTycoon]

Take the indexs off of the table with duplicates. Manually remove records that are duplicating or write a sql statement. Add your index back on. Did you try that on the backend?

The question I have now is how did the program let you bypass the table index?

 

[DevTycoon]

What does your table relink routine look like. Have you used dsn-less connections?

 

[ahmed_optom]

Take the indexs off of the table with duplicates. Manually remove records that are duplicating or write a sql statement. Add your index back on. Did you try that on the backend?

The question I have now is how did the program let you bypass the table index?



Sent from my iPhone using Tapatalk

Yes I did that, and the autonumbers are all reset and the data is not organised. So for example, if i have a form with patient details, and i cycle with the navigator to the next patient, and the next patient etc, it will start on patient 4, then go to 2456, then patient 67 etc. It should be 1 then 2 , 3 and 4 etc.


If I started again and made a new database and hosted on mysql server, would the chances of such massive database corruptions be lowered? I know this is impossible to answer, but I just dont know if this is my coding or was I asking to much of access?

 

[ahmed_optom]

What does your table relink routine look like. Have you used dsn-less connections?

I relinked several times, firstly when I tried to restore the data from a back up, and now on my home pc, I have the fe and be next to each other on my desktop. I relinked tables, they work.

I was unaware of dsn-less connections, and I am googling now. Is this a potential way to restore database integrity or prevent corruption in the future?

 

[DevTycoon]

I would wager that if you google this type of problem very low percentage of global access users have this problem so I am thinking you need to make new tables, extract the data from old tables and reload data.

Then start working on trapping the error.

You are going to run into headaches if you try to scale with my sql Instead of something like sql express.


problem = opportunity

 

[CJ_London]

doesn't sound like a corruption issue, just the records not displayed in the order you expect. Try sorting on the ID number.

Note that records in any db (Access/Sql Server/MySQL etc) are stored randomly. You have to provide an order such as ID or date of entry.

Perhaps someone has accessed the backend table directly and changed the default order for the table

Access is pretty stable but as mentioned by others, only if used as intended. If your users are connecting via wireless network for example, I would not recommend Access as a backend, use SQL server or MySQL which can handle interruptions to the back end during transactions better - although you will still need code to recover the situation if a transaction is interrupted.

 

[DevTycoon]

I was unaware of dsn-less connections, and I am googling now. Is this a potential way to restore database integrity or prevent corruption in the future?

DSN less connections are a way to use native language to standardize you method of automatically establishing connections to databases like MySQL ,MySQL, spreadsheets,access databases,etc.

Also if you are using test data share the files, it is easier to help that way.







problem = opportunity

 

[ahmed_optom]

doesn't sound like a corruption issue, just the records not displayed in the order you expect. Try sorting on the ID number.

Note that records in any db (Access/Sql Server/MySQL etc) are stored randomly. You have to provide an order such as ID or date of entry.

Perhaps someone has accessed the backend table directly and changed the default order for the table

Access is pretty stable but as mentioned by others, only if used as intended. If your users are connecting via wireless network for example, I would not recommend Access as a backend, use SQL server or MySQL which can handle interruptions to the back end during transactions better - although you will still need code to recover the situation if a transaction is interrupted.

There are also duplicate primary IDs.

eg there maybe two patient id of 4. I dont know how this could have happened.

 

[DevTycoon]

Upload your files if you need more help


problem = opportunity

 

[Pat Hartman]

If you have duplicate autonumbers, the table is definitely corrupted. Try exporting the table to a .csv file and then opening it in Excel to get rid of the duplicates. ALWAYS open .csv files in Excel by first opening Excel and then using the Data tab to pick the .csv file. This gives you a dialog similar to what you get with Access and will allow you to define data types. This will prevent long numeric strings from being converted to scientific notation and other numeric codes such as zip from loosing their leading zeros.

Once the bad records are deleted or given a new ID if that is what you want. Go back to the BE and delete the existing bad table along with all relationships. Compact and repair. Open it again and link to the fixed .csv file. Copy the schema from the linked .csv. If you properly defined dates and unmeric items when you linked, the new schema will be correct but you will have to modify the length of text fields if you want them to be shorter than 255 bytes. Change the PK to be autonumber and make sure that the autonumber is the PK. You can make additional unique indexes to enforce business rules as necessary but the Autonumber must be the PK Then you can create an append query to copy the linked data back into the permanent table. Recreate all relationships. Compact and repair again. Hopefully, you won't have any orphan records. If you do, you'll need to fix that problem either by deleting the orphan records or by adding/fixing the parent records.

What causes an autonumber to corrupt? I've seen two issues.
1. A form that is bound to a table that has a subform bound to the same table. The master/child linking can become confused. You probably won't ever need to do this on purpose but check to see if you have done it accidentally by using a RecordSource for the subform that joins to the main form table.
2. Having an autonumber that is not defined as the primary key. This may be why your records seem to be out of order when you open the table in DS view. Opening a table in DS view NORMALLY will show the rows in PK order because when a db is compacted, Access rewrites all the tables back to PK sequence. The rows can get out of sequence if they are updated and become too large to fit back into their original slot. Other RDBMS use their own method for where rows are stored so it is more obvious with them that ds view doesn't actually return rows in PK order but Access always adds new records at the end of the work space in PK order so you only see anomalies that are caused during updating.

All the bad press that Access gets is pretty much directed at Jet/ACE rather than Access itself. So, if you have an unreliable network, too many concurrent users, a database approving the 2G maximum, you should seriously consider converting the BE to SQL Server. Depending on how well you implemented good client/server practices, you may have little to nothing to modify in the FE or you may have a lot to modify. But don't confuse Access with Jet/ACE when it comes to data stability.

 

[ahmed_optom]

Pat,

I pretty much did what you have suggested ( in a round about way) and now recovered the database to a functional state with most of the data intact. Thanks,

Pat, I am trying to understand the first issue you raised about autonumbers. I do have forms with subforms that have the same table as recordsource. For example, I have a form that has a subform that simply shows the previous record set for that particular patient. eg patient id 2, in the main main you are adding todays visit data, in subform it will be previous visit information.

Is that the kind of form set up which you are referring to?

 

[Pat Hartman]

No. The problem would arise if you are trying to add a record in the main form and also in the subform.

If you use a continuous subform with a Top 1 predicate and order descending by date/ID, you can do this with a single subform. One record will show - the previous visit. and you can enter the new visit information on the empty new line row.

 

[DevTycoon]

Pat,
I do have forms with subforms that have the same table as recordsource. For example, I have a form that has a subform that simply shows the previous record set for that particular patient. eg patient id 2, in the main main you are adding todays visit data, in subform it will be previous visit information.

Maybe your question is solved already.

I wanted to test it so I made a test database with no macros.

The database forms mimic your scenario I think. There is a client form that tracks power of attorney history, client activity history, and shows the most recent client activity detail.

Some code would need to be added to refresh the record sources if any dates were modified causing recent date to change but other than that it seems to work like your requirements indicate.

The real kicker with the test db is that the Person table holds, power of attorneys, clients, and employees so the subforms on the client sheet connect to the person table many times with no issues.