• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1817
  • Last Modified:

Need Help repairing ADO Database in VB6

I have seen this code on MS site for use in compacting and repairing access 2000 DB from within VB6
I tried the code but I get a the usual message "Unrecognized Database" coming up. If I use MS Access 2000 to repair it goes through without any problem. If I use this same code on a good DB it does the compact ok but a DB that has errors does not work.
  Is there something I am missing?  I thought CompactDatabase could fix DB that had errors from unclosed connections etc.


   Dim jro As jro.JetEngine
    Set jro = New jro.JetEngine
    jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\Pharm2\Pharmacy.mdb;Jet OLEDB:Database Password=Test", _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\Pharm2\PharmTemp.mdb;Jet OLEDB:Engine Type=4;Jet OLEDB:Database Password=Test"
   
Is there better or any code that can fix ADO connected Access 2000 DB from within VB6?
0
jgarth
Asked:
jgarth
  • 3
1 Solution
 
MYLimCommented:
'*******************************************************************************
'Task               :   To Compact and Repair MDB file
'Date of submission :   10-06-2003
'Developed by       :   Vinu Menon & Pavan Kumar
'Organization       :   Bay Talkitec (P) Ltd. -Hyderabad
'*******************************************************************************
'Steps to follow
'
'1  :   Referece the following DLL's
'       Microsoft DAO 3.6 Object Lib
'2  :   Add the following components
'       Microsoft Common Dialog Control
'       Microsoft Windows Common Control 6.0
'
'3  :   Place the Following Controls on the form
'       Textbox         : Name = txtFilePath
'       CommonDialog    : Name = CommonDialog
'       ProgressBar     : Name = ProgressBar
'       CommandButton   : Name = cmdCompactRepair : Name = cmdExit
'       Timer           : Name = Timer1 Set the initial Enable value = FALSE
'
'
'
'FOR COMMENTS and SUGGESTIONS, Plz. mail me on vinu_aug10@hotmail.com
'
'
'E N J O Y

Private Sub cmdBrowse_Click()
CommonDialog.DialogTitle = " Compact and Repair database" 'Set the Common Dialog Title
CommonDialog.Filter = "Microsoft Access Database (*.MDB) | *.MDB" ' Display only MDB files
CommonDialog.ShowOpen 'Show Open Dialog
CommonDialog.CancelError = True 'Cancel all errors
CommonDialog.DefaultExt = "*.MDB" ' Set the default extension
txtFilePath = CommonDialog.FileName ' Put the selected filename in the textbox
End Sub

Private Sub cmdCompactRepair_Click()
Dim dbE As New DAO.DBEngine ' Declare a new DBEngine variable
Dim x$ ' To capture the DIR return string


x = Dir(App.Path & "\repairedDB.mdb") 'see if the TempPath already exists

If x <> "" Then Kill App.Path & "\repairedDB.mdb" 'Check if the Temp file already exists

Timer1.Enabled = True 'Enable the Timer

dbE.CompactDatabase txtFilePath, App.Path & "\RepairedDB" ' Compact and repair the DB

Kill txtFilePath 'Kill the original DB

'Rename the Repaired DB with the Original DB Name
Name App.Path & "\repairedDB.mdb" As txtFilePath

End Sub

Private Sub cmdExit_Click()
End 'Close the application
End Sub

Private Sub Form_Load()
ProgressBar.Min = 0 ' Set the Min value
ProgressBar.Max = 100 ' Set the Max value
If txtFilePath = "" Then cmdCompactRepair.Enabled = False
End Sub

Private Sub Timer1_Timer()
'Check the progressbar value counter
If ProgressBar.Value < 100 Then
    ProgressBar.Value = ProgressBar.Value + 10
Else
    MsgBox " DataBase successfully Compacted", vbInformation + vbSystemModal, "Compact & Repair Database Demo"
    ProgressBar.Value = 0 'Reset the min value
    Timer1.Enabled = False 'Disable the Timer
End If
End Sub

Private Sub txtFilePath_Change()
If txtFilePath = "" Then
    cmdCompactRepair.Enabled = False
Else
    cmdCompactRepair.Enabled = True
End If
End Sub

Private Sub txtFilePath_LostFocus()
If txtFilePath = "" Then
    cmdCompactRepair.Enabled = False
Else
    cmdCompactRepair.Enabled = True
End If

End Sub
0
 
MYLimCommented:
CORRUPT ACCESS DATABASE FAQ
IMPORTANT!
 First things first! BACKUP THE DATABASE!
Before doing anything, make at least two backups of the corrupted database.

 

 How do you know if a database is corrupt?

 "Unrecognized database format" (When your database was operating under your current version of Access before)

 "Enter database password" (when none was set)

 "The Microsoft Jet Database Engine cannot open the file"

 "Visual Basic for Applications Project in This Database Is Corrupt"

 "Unexpected Error 35012"

 An "Invalid Page Fault..." error (not necessarily a corrupt database issue)

 "You do not have the necessary permissions to open this object. Please contact your system administrator."

 [database.mdb] isn't an Index in this table. Look in the index collection of the TableDef object to determine the valid index names

 The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time -- 3197

 'Microsoft Access has encountered a problem and needs to close'

 The database 'databasename.mdb' needs to be repaired or isn't a Microsoft Access database file. -- 2239

 Operation failed - too many indexes - reduce the number and try again

 Failure to open/failure to show error

 "Microsoft jet database engine could not find object MSysDB.  Make sure the object exists and that you spell its name correctly and the path name correctly." -- 3011

 "The Microsoft Jet database engine could not find the object MSysCompactError. Make sure the object exists  and that you spell its name correctly and the path name correctly"

 "The Microsoft Jet database engine could not find the object 'databases'. Make sure the object exists and that you spell its name and path name correctly."

 "The Microsoft Jet database engine could not find the object 'filename'. Make sure the object exists and that you spell its name and the path name correctly. " 

 "Invalid field data type" -- 3259

 "Record(s) can't be read, no read permissions on 'database.mdb'"  -- 3112

 "Could not find field 'xxx'" -- 1017

 "Invalid Bookmark" -- 3159

 This database is in an unrecognized format. The database may have been created with a later version of Microsoft Access than the one you are using. Upgrade your version of Microsoft Access to the current one, then open this database. -- 3343

 "You cannot carry out this action at the present time" -- 2486

 "Record is deleted"

 "The database has been placed in a state by user 'xxx' on machine 'mmm' that prevents it from being opened or locked" -- 3704

 "Needs to be repaired" and "Not a valid password"  (after repairing) -- 2239

 "The database has been placed in an unexpected state." or "This database is in an unexpected state; Microsoft Access can't open it."
or
"This database is in an unexpected state; Microsoft Access can't open it..  The database has been converted from a prior version of Microsoft Access by using the DOA compact database method instead of the convert database command on the tools menu. This has left the database in an unconverted state."
or
"This database is in an unexpected state; Microsoft Access can't open it. This database has been converted from a prior version of Microsoft Access by using the DAO CompactDatabase method instead of the Convert Database command on the Tools menu (Database Utilities submenu).  This has left the database in a partially converted state. If you have a copy of the database in its original format, use the Convert Database command on the Tools menu (Database Utilities submenu) to convert it.  IF the original database is no longer available, create a new database and import your tables and queries to preserve your data.  Your other database objects can't be recovered."
-- 2573

 "Disk Error -- Reserved error (-1601)" --3000

 "Table 'TempMSysAccessObjects' already exists."

 "Record(s) cannot be read; no read permission on 'MSysAccessObjects'" or Record(s) cannot be read; no read permission on 'MSysACEs'. -- 3112

 "The Microsoft Jet database engine cannot find the input table or query 'MSysAccessObjects'. Make sure it exists and that its name is spelled correctly." -- 3078

 "Could not use <name>; file already in use. (Error 3045) The specified file is currently being used by another user or session. Wait for the other user or session to finish working with the file, and then try the operation again." -- 3045

 "Operation invalid without current index" -- 3019

 "'database.mdb' isn't an index in this table. Look in the Indexes collection of the TableDef object to determine the valid index names." -- 3015

 "disk or network error" -- 3043

 "Unspecified Error"  

 "The instruction at "0x11111111" referenced memory at "0x22222222". The memory could not be "written"

 The Visual Basic for Applications project in the database is corrupt.

 Error accessing file. Network connection may have been lost. or "This action will reset the current code in break mode."-- 304548

 "Microsoft Access has encountered a problem and needs to close. We are sorry for the inconvenience."

 "AOIndex is not an index in this table".
 

 Tools  
  JetComp
Jet compact utility.
  JetUtils
The Jetutils.exe file contains the following files:
   Jetlock.doc           Understanding Microsoft Jet Locking White Paper
   Readme.txt            Readme file with Installation information
   LDBView.exe           LDBView utility
   Dblck.exe             Self-extracting executable that contains the setup files for DBLock
   Msldbusr.exe          Self-extracting executable that contains Msldbusr.dll and associated files.
 
  Jet UserRoster
Check who is logged into a database.
  Remove Access Security
Remove Security Settings from an access database.
   

 Click here for recovery services

 


Typical Causes of MDB File Corruption
There are three main causes of corruption in Access/Jet mdb files.
Database is Suspect/Corrupted Due to Interrupted Write Operation
You should always quit Access properly by clicking Exit or Close on the File menu. If a database is open and writing data when Access is abnormally shut down, the Jet database engine may mark the file as suspect/corrupted. This can happen if the computer is manually turned off without first shutting down Windows or if power is lost. Other situations may not shut down Access but may still interfere with Jet's writing of data to the disk while the database is open. This can happen, for instance when networks experience data collisions or disk drives malfunction. If any of these interruptions occur, then Jet may mark the database as potentially corrupted.

When Jet begins a write operation, it sets a flag, and resets the flag when the operation is completed. If a write operation is interrupted, the flag remains set. When you try to open that database again, Jet determines that the flag is set and reports that the database is corrupted. In most cases, the data in the database is not actually corrupted, but the set flag alerts Jet that corruption may have occurred. In cases such as this, compacting and/or repairing the database can typically restore the database. Fortunately, there are ways to determine which user and workstation was responsible for marking the file as suspect. See the "How to Determine What Users/Workstations Are Causing the File to be Marked Suspect" section later in this article.
Faulty Networking Hardware
In this case, the file corruption does not involve the Jet database engine; rather the file is literally corrupted by some outside cause. The cause can be one or more links in the hardware chain between the computer that the database resides on and the computer that has the database open. This list includes, but is not limited to, network interface cards, network cabling, routers, and hubs.

Hardware-based corruption is typically indicated by .mdb files that cannot be restored through the use of compacting, repairing, or Jetcomp. Hardware corruption will typically recur until the responsible hardware is repaired or replaced.
Opening and Saving the MDB File in Another Program
There is no way to recover a .mdb file that was opened and then saved in a program other than Access. For example, Microsoft Word allows you to open an Access database and then save it (although it serves no good purpose to do so because if you open an MDB file in another program, all you can see are extended characters). Saving the file in this manner will cause the .mdb file to prompt you for a database password when you try to open it in Access even though the file may have never been password-protected in Access. The password prompt occurs in such cases because the first byte range that Access checks when it opens a file is where the database password would be. If that byte contains corrupted data, Access treats the file as being password protected. Even if there were a way to get around the password prompt in this case, the database would still be unrecoverable because the binary structure is scrambled and therefore unreadable to Access. Recovering a backup copy of the file is the only solution in this case. For additional information about this issue, click the article number below to view the article in the Microsoft Knowledge Base:
223043 ACC: Database Password Appears Even Though It Was Never Set

How to Determine What Users/Workstations Are Causing the File to Be Marked Suspect
You can determine which workstation and user caused Jet to mark a .mdb file as suspect with the LDBView utility, which is available by downloading Jetutils.exe from the following Microsoft Web site:
http://download.microsoft.com/download/access97/utility1/1/WIN98/EN-US/JETUTILS.EXE

For additional information about the Microsoft Jet Utilities, click the article number below to view the article in the Microsoft Knowledge Base:
176670 ACC: Microsoft Jet Utilities Available in Download Center

Ldbview let you observe which users are currently logged into the database, as well as any users that may have left the file in a suspect state.
Steps That You Can Take to Help Prevent Corruption
Avoid losing power during database writes. Losing power can cause the database to be left in a suspect state.
Avoid dropping network connections.
Avoid abnormal termination of Microsoft Jet connections such as power loss, manual shutdown, having Task Manager shutdown the application, and so on.
When programming, close all Data Access Object (DAO) and ActiveX Data Objects (ADO) objects that you have open. Examples include Recordset, QueryDef, TableDef, and Database objects.

Fatal system errors almost always cause abnormal termination. If your database is prone to fatal errors, you should resolve the errors before the database becomes too damaged to open or recover.For additional information about the Microsoft Jet Utilities, click the article number below to view the article in the Microsoft Knowledge Base:
148424 ACC: Troubleshooting Fatal System Errors in Microsoft Access 95 and Microsoft Access 97

Compact the database often.

IMPORTANT: Do not run the Repair Database command in Access 97 unless Microsoft Jet prompts you to do so.
Do not run IPX on Windows NT Server where Jet databases are located across the network and the client is Microsoft Windows 95 with IPX/SPX. Instead, run TCP-IP on the Windows NT Server and a dual protocol stack of IPX and TCP-IP on the Windows 95 client. Windows NT to Windows NT with IPX/SPX will not cause the problem, nor will Novell to any client.
Avoid a large number of open and close operations in a loop (40,000 successive open/close operations to over 1,000,000.
Accessrecovery, MDB,access,password,crack,msaccess,recovery,office,access2,access2002,access2000,
access97,access95,2,2000,2002,97,95,break,restore,security,hack,lost,forgotten,download,free,software,
development,security,probelms,help with,protection,problems,software,tools,access, repair,access, mdb,
datenbank, database, reparatur, reparieren, defekt, kaputt
0
 
MYLimCommented:
You can use a batch file to compact and repair access databases. Create a file like compact.bat and put the following code in it.:

<MS Access Path>\MSACCESS.EXE /Compact <Database to Compact>

For example:

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" /Compact "C:\AccessDatabases\TestDB.mdb"
exit

You can then call this using the NT Scheduler or call the batch file using Shell from a VB program.
0
 
jgarthAuthor Commented:
Thanks Mylin that was a very detailed answer, the problem that your code pointed out to me was that I had no reference to DAO 3.6 I never thought it was needed since I used ADO
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now