Repairing a access data base

Do you know the VB6 code to repair access database

Page 1 of 1

3 Replies - 3710 Views - Last Post: 18 December 2009 - 09:53 AM Rate Topic: -----

#1 domco  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 16-November 08

Repairing a access data base

Post icon  Posted 16 November 2008 - 09:10 AM

Do you know the code to have from within VB6 take a access database and repair and compact it, the go to the next access data base you have and do the same
Is This A Good Question/Topic? 0
  • +

Replies To: Repairing a access data base

#2 Kipper11  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 21-November 08

Re: Repairing a access data base

Posted 24 November 2008 - 01:30 AM

View Postdomco, on 16 Nov, 2008 - 08:10 AM, said:

Do you know the code to have from within VB6 take a access database and repair and compact it, the go to the next access data base you have and do the same

Public Function DbUtil(strDatabaseName As String) As Boolean
	On Error GoTo Err_CompactDatabase
	Dim strPath As String
	Dim strPath1 As String
	Dim strPathSize As String
	Dim strPathSize2 As String
MyDate = Format(Time, "dd-mm-yy")
	Screen.MousePointer = vbHourglass
	'Save Paths for Database
	strPath = App.Path & "\" & strDatabaseName
	strPath1 = App.Path & "\DBBackup\" & "1" & strDatabaseName
	'Repair Database
	DBEngine.RepairDatabase strPath
	'Get Size of File Before Compacting
	strPathSize = GetFileSize(strPath)
	'Kill the file if it exists
	If Dir(strPath1) <> "" Then Kill strPath1
	'Compact Database to New Name
	DBEngine.CompactDatabase strPath, strPath1, dbLangGeneral, dbVersion30, ";pwd="
	'Get Size of File After Compacting
	If Dir(strPath) <> "" Then Kill strPath
	'Compact back to original Name
	DBEngine.CompactDatabase strPath1, strPath, dbLangGeneral, dbVersion30, ";pwd="
	'Kill the file, no need to save it
	'Get Size of File After Compacting
	strPathSize2 = GetFileSize(strPath)
	DbUtil = True
	'Display the Summary	
 Exit Function
Err_CompactDatabase:
	Select Case Err
		Case 3356
		"Unable to compact and repair " & UCase(strDatabaseName)
		"Database already open " & UCase(strDatabaseName)
		Case Else
			"Unable to compact and repair " & UCase(strDatabaseName)
				Err.Clear
		Resume Next
	End Select

Screen.MousePointer = vbNormal
End Function



Was This Post Helpful? 0
  • +
  • -

#3 suretalu  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 18-December 09

Re: Repairing a access data base

Posted 18 December 2009 - 08:57 AM

if you'd like to repair corrupted databases, I know an easier program for repair database sql 2005
Was This Post Helpful? 0
  • +
  • -

#4 OldManMike  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 15-December 09

Re: Repairing a access data base

Posted 18 December 2009 - 09:53 AM

View Postdomco, on 16 Nov, 2008 - 08:10 AM, said:

Do you know the code to have from within VB6 take a access database and repair and compact it, the go to the next access data base you have and do the same


domco,

I have had some Jet databases so messed up that they were unbelieveable. For one business in New Mexico, I had to create an entire new mirror image database (using standard Table Definition objects) and then open the corrupted database and step through each record from each table, one at a time, adding them to the new database.

There are two major problems. If there was a Self Incrementing ID column then you have to just add records up to the Max number of records in each table. Then before you write the record, you find that ID first.

The second is that if it is fully or even partially normalized, you have to work out the logic of the parent/child relationships and make sure you maintain that relationship.

In the case I mentioned, I had both situations and luckily, there were secondary field(s) that were able to substitute for the ID column that enabled me to find the correct relationships. Except in one case. I added a new, additional field to that table and set values that allowed me to write a program to regain the logical relationships.

There are two areas you need to trap for errors. First when you set a field value like " rs!AnyValue = " or rs.Fields(j).Value = ...
Always preceed it with On Error Resume Next. Then catch any errors. If err then log the ID, the field name and the error description and substitute some value you can use later to isolate the error. Date fields are the worst and Currency then Longs are next worst. I had one database that had somehow exceeded the 4 Billion size in a Long self incrementing ID column! Those you just kind of Hope that they were not important... if you get my drift, wink, wink!

The second problem area is at rs.Update

Besure to trap errors and if error, log enough info to allow you to manually fix it later on.

It isn't pretty. But if a database is so hammered that the Repair fails, or it won't open exclusively into access for repair... you can usually still get most of the data out of it. As long as it will open from code, there is hope.

This post has been edited by OldManMike: 18 December 2009 - 10:02 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1