Using Access Database with my VB6 project... DB corrupts

[mark01]

I have an access database that I use with my vb6 application. I have about 50 user's accessing this data at one time. About once or twice a month, the database will corrupt, and I will have to repair it. Sometimes the repair wont work, and I have a useless database.

Is this most likely a problem with my code, or do I need to use a different database system? (MSDE?)

Thanks

 

[Swi]

You should be compacting and repairing the database every night. Also, you may want to look into MSDE as 50 users is a lot for Access to handle at once. Are you seeing any performance issues such as speed issues?

Swi

 

[mark01]

I'm not seeing any speed issues. This application only runs at user login & logout.. It closes the db connection while running, then re-opens it at logout. I'll bet that at most, there are 10 concurrent connections to this db.

 

[Swi]

Just try writing some code to Compact and Repair the database in the evening then. It sounds to me like it is not done frequently enough.

Swi

 

[shakespeare5677]

Swi, I think mark01 is right, we had a similar issue, contacted MS and figured out that it was a problem on their end (I don't remember the specifics). The solution was to compact and repair the db every day or so.
-Max

 

[Swi]

Here is a link to get you starting with programmatically compacting and repairing your DB:

http://www.freevbcode.com/ShowCode.Asp?ID=2879

Swi

 

[shakespeare5677]

Sorry folks, got your names confused.

 

[manojvb]

Sub CompactDB()
On Error GoTo CompactDB_Err
Const conFilePath = "C:\Windows\My Database\"

' Compact the database to a temp file.
DBEngine.CompactDatabase "C:\Windows\My Database\manojvb.mdb", "C:\Windows\My Database\manojvb1.mdb"

' Delete the previous backup file if it exists.
If Dir(conFilePath & "manojvb.bak") <> "" Then
Kill conFilePath & "manojvb.bak"
End If

' Rename the current database as backup and rename the temp file to ' the original file name.
Name conFilePath & "manojvb.mdb" As conFilePath & "manojvb.bak"
Name conFilePath & "manojvb1.mdb" As conFilePath & "manojvb.mdb"
MsgBox "Compacting is complete"

Exit_CompactDB:
Exit Sub

CompactDB_Err:
MsgBox Err.Description
Resume Exit_CompactDB
End Sub