Access Corruption Problems - Experts Only

hi all,

i am building applications using VB6 with Access 2K database and ADO, well the access is fine and cheap but it is making me a headache when used by multiusers.

My database code is simple., like the following

'db as connection

VB Code:

db.begintranse
db.execute ("delete * from invoices where code = 13")
db.committranse

now the questions:

1 - is this code problematic when executed by many users on the same dbase file thru a LAN

2 - How could i check to see if anyone is writing to the same table in the same time.

3 - is there a problem in concurrent readings from the same table., = many users are performing select sattements on the same table in the same time.??

4 - Can anyone advise me how to protect my dbase from multiuser corruption , as i am facing now a lot of (Unrecognized database Format) with the access.

5 - i want to make a backup from my access database, should i copy it by using (FILECOPY) or should i make a replica

6 - what will happen if i tried to backup the database while someone is doing anything in.

7 - how could i check that i am the exclusive user on this database

8 - how could i make a replica from an access database with VB6 Code ?


Well i know this is lenghty and hard but i am open to any ideas and willing to share this problems and solutions with other people along with my self


THX in Advance

BST RGDS

hi, maybe i can help...

in opening the recordset, the adOpenDynamic and adLockOptimistic will help you. most especially your database is being accessed by many users at the same time.

sample :

recordset.open "select * from table", connection, adOpenDynamic, adLockOptimistic

the adOpenDynamic will always synchronize the recordset you are calling with the database. while the adLockOptimistic will lock records only when you call the update command.

well, i dont know how to apply this locks in my coding style

i uses a connection directly to issue writing sql commands to a database, i dont use recordsets in update or insert.


I handle all the data storage within my interface then on the click of the save button i flush all within sql statements executed through ADO conenction directly

example

VB Code:

db.exeute ("insert into clients (name,address) values ('Mona','California')

so is your tip applicable on this syntax or not

thx for your reply

i see, by the way what nature of app are you developing? because i have always used recordset in all my programs for the purpose of filtering data according to users' choice of records. before i answer if the syntax i showed is applicable to you may i know the app you're developing?

Hi

I am developing a similiar application. I use the locking mechanism during the updates. I go with Mr. vhinehds. Then, i also heard that making use of the Users available in Ms Access for any kind of accessibility to the database will also be safe. I am still in Development and fulfledged testing of multi-user is not yet started.

Waiting for the stage.


GS

majed

If you are looking for a multiuser database, why don't you think about MySQL. I guess it's free aswell.

MySQL HomePage

i am developing a software for tourism companies , operations management ( where there is a lot of data ) in and out

i use this mechansim to make my access time on database very shortt.


by the way why do all of you ignore my other questions from 1 to 9 ????

1. - Yes. Simply because Access has many issues when used by
more than 7-10 concurrent connections, not users. This is a know
limitation of all Access versions (including 2003) noted by M$.

2. Need to use recordsets so you can set the recordlocking. Ex.
adLockOptimistic along with transactions.

3. Should be no problems selecting the same records as other
users are selecting.

4. Split your database into a back-end which will contain ONLY
tables (Data). Then have your front-end to contain all the
queries, forms, reports, macros, etc. (Tools > Database Utilities >
Database Splitter). This will help to increase the number of
connections, but will still be prone to the issues when you
connections pass the "safe" limit. Trial and error will tell you what
that limit is. Also if the front-end can be made read-only, leaving
the back-end writtable, can help.

5. Dont know too much about replica, but for a file copy, if you
have exclusive access to the db, then ok, else you may miss data
that is in the process of being updated by users (on-line backup).

6. See #5.

7. If you open the db through the UI, open Access first, then
browse to your db and instead of clicking Open, click the
dropdown arrow and select Open Exclusive. If you are connecting
using code, set your connectionstring accordingly using the
EXCLUSIVE parameter. Ex - .ConnectionString
="Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=D:\Development\My.mdb;Mode=Exclusive;...". If the
connection fails then it is opened exclusive by another user
already.

8. See #5.

HTH

thx RobDog888


the idea of opening connection exclusively to know if anyone is inside is quite great

thx for all your time and for the answers


BST RGDS

Hi,

I know this is old thread but I wanted to post it here in case someone else came across these common issues.

Q.2.Visit the below URL to check who is logged in to the database and working on the same

https://docs.microsoft.com/en-us/off...on-to-database

Q.3. Visit the below discussion URL for this query:

https://docs.microsoft.com/en-us/ans...n-a-table.html

Q.4. Yes, there are some workaround and third-party tools that can help to protect the database from multiuser corruption:

You should try to keep things away which can impact the corruption of the Access Database.

1. Don’t Leave Your Database Unnecessarily Opened.
2. Avoid Using MAC And Windows On The Same Network
3. Be Careful While Using Database On A Wireless Networks
4. Regularly Utilize Compact and Repair Applications
5. Split MDB or ACCDB Files
6. Utilize Decent Network Cards
7. Keep Updated With Latest Drivers
8. Change the Suspected Network Element
9. Defragment the Network Hard Drives
10. Terminate the Connection When Not in Use

And for getting, again and again, error “Unrecognized Database Format”, Please go through the following solution to solve this error:

There is a known issue with a Windows Update that is associated with this problem. This error refers to database corruption, And the reasons for the database corruption are:

1. System Virus
2. Sudden shutdown of system
3. Abruptly cancellation of MS Access file
4. Software Malfunction
5. Improper Data Sychronization

Workarounds you can follow to solve this error:


Workaround 1: Go back to the Previous version of MS Office



Steps to follow:

1. Open the database, and in case it is shared, the user needs to affirm that it is unopened.
2. Click on the ‘Tools’ menu. Point to the best database utilities.
3. Click ‘Convert Database’ and then press ‘To Access version File Format.’
4. Now, type the file name in the ‘File Name box’ of the ‘Convert Database.’ Click ‘Save.’

Workaround 2: Repair the database from “Compact and Repair” Method i.e inbuilt method of MS office to repair database.







Steps to follow:

1. First close the database
2. And then Backup database
3. Click on the ‘Tools’ menu and point on the database utilities. Click the ‘Compact and Repair Database.’
4. Specify file name. Click the ‘Compact’ option from the database so that the user may compact in the dialog box.
5. In the dialog box which is named, ‘Compact Database,’ specify the compacted Access file name as well as the destination. Click ‘Save.’

I hope these workarounds could help you.


Q.5. To Backup the access database, follow the below process mentioned:


https://support.microsoft.com/en-us/...a-ee81f8d6356c

Q.6. Please visit the following thread where every possible reason is mentioned about backing up access database while database is in use.

https://social.msdn.microsoft.com/Fo...jmanageability





Q.8. To make the replica from an access database with VB6 Code, visit the below thread:


https://www.vbforums.com/showthread....ation-and-Sync


Best Regards,
Aron