Design Question - Possibility of Corruption 1

[shaunk]

Access 2003.

Given the potential problems associated with Access databases and corruption, I am considering using a totally disconnected model when coding a new database.
The application will have a front-end that is merely for selection of patient records and connects to an enterprise SQL database via linked tables in browse-only mode. Given this, is there any possibility of corruption of the Access d/b when using linked tables as a datasource, to browse the SQL tables.
I believe the potential for corruption comes when Access is trying to update records in its own database over a slow network, when record locking and contention issues arise.

If this is the case, I can still use a connected model on the front-end selection, and a disconnected model when updating the Access non-enterprise data?
Thanks



The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[JoeAtWork]

When you move the back end to SQL server, you don't have to worry about your tables being corrupted any more.

The front end, however, is still an Access file, and thus still susceptible to corruption. I recommend you go with the common deployment method of giving your users a batch file that copies the front-end to their local drives each time they start the application. That way at the beginning of the day they have a pristine copy of the front-end.

I've been upgrading a number of Access databases to SQL Server over the past couple months. I have found that ODBC linked tables are often quite slow when used by certain Access queries.

I've found that for certain forms, listboxes, and reports where the data is not bound directly to the table, I have replaced the Access query with a stored procedure on SQL Server. I then fill a recordset with data from the stored procedure, and bind the form/listbox/report to the recordset. It usually improves the speed by magnitudes (literally sometimes minutes to seconds).

If you have the choice between making an ADP (Access Data Project) or a simple ODBC-linked MDB file, I recommend the ADP for efficiency. The wizard will attempt to covert your queries to stored procedures and views, which are almost always more efficient. A couple caveats about ADP's:

1. You usually have to manually tweak a few forms or reports after you run the wizard
2. You can't have local tables - all tables have to be on SQL Server

 

[shaunk]

OK. Thanks for your response. I have just got back to this after being sidelined for a while.

I have taken your advise and moved all queries etc to stored procs on the SQL backend. There are now no tables or queries remaining on the Access front-end.

One final question remains in the light of peformance and security issues, is there any point to splitting the database. Will it avoid having the forms component corrupted?

I still plan to implement User Security. Thanks.


The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[JoeAtWork]

>>> is there any point to splitting the database.

I don't understand, if you have moved your tables to SQL Server, then you are already "split" (the backend is SQL Server, the front-end is the Access file with forms, report, modules, etc.).

The reason to split the database is that 9 times out of 10 corruption occurs in a code module. By splitting you protect your data from being brought down by the code modules. Also, split databases are much easier to deploy (you just give the user the new version of the front-end).

 

[shaunk]

That's right, the d/b is already split, so the data is protected.
But as you've previously posted, the front-end is still prone to corruption. I notice it grows every time I login to the d/b. What is being added to the database I don't know, probably temporary files? And since I've moved the backend to SQL, the front-end has become corrupted severla times. So I am trying to avoid this.
In any case, I'll take your advise and setup a script to copy a fresh front-end each time the user logs on.

The risk with keeping an open mind is having your brains fall out.
Shaunk