Repair Access 2000 database in VB6 code? 1

[custsoft]

Now and then my Access 2000 file is corrupted and I have to run the repair utility in Access 2000. Is there a way to do that in VB6 code? I did see a posting that said to use repairdatabase(databasename) but what is that? What error code do you check for when you get the unrecognized database format? 3343? Thanks for any help. Joe

 

[ufobaby]

hi,

firstly if u using DAO with Access 2000 then u need to take DAO 3.6 refrence form Project->Refrences. Pls check this first.

then if actually the database is courrupted then
use the DBEngine.RepairDatabse(.....)

will have to search for a method in ADO am not aware.

Hope this helps.. else write back


Niraj

 

[Schroeder]

This sub will compact the database. I don't know for sure if this is exactly the same as running Compact and Repair Database... from within Access. You'll need to reference Microsoft Jet and Replication Objects 2.6 Library (MSJRO.DLL).

Sub CompactDBase()
Dim oJetEngine As JRO.JetEngine
Dim strSourceConn As String, strDestConn As String
Set oJetEngine = New JRO.JetEngine
strSourceConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DbPathAndFileName"
strDestConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=TempDbPathAndFileName;Jet OLEDB:Engine Type=5;"
On Error Resume Next
Kill "TempDbPathAndFileName"
Err.Clear
On Error GoTo Exit_CompactDBase
oJetEngine.CompactDatabase strSourceConn, strDestConn
FileCopy "TempDbPathAndFileName", "DbPathAndFileName"
Exit_CompactDBase:
Set oJetEngine = Nothing
Err.Clear
Exit Sub
End Sub

As you may or may not know, when compacting/repairing, Access actually needs to create a separate Db. This is the reason for the TempDbPathAndFileName and the FileCopy command.

 

[Schroeder]

ufobaby, can you elaborate on the RepairDatabase method? I can find no reference to it and it would be handy to be able to use.

 

[ufobaby]

hi,

Use DBEngine.RepairDatabase(DataBaseName as String)
This will not compact the database.

for compacting the database use
DBEngine.CompatDatabase(SourceDatabase,DestinationDatabase)
This logic is excatlly same as the one you given it creates a db in order to compact. you will need an error handler also to check if the databse is open while compacting.

For the above u will need to include Refrence to DAO library, the version depends on what DB are u using

- Access 98 -> DAO 2.51/3.0 compatibility will do
- Access 2000 -> DAO 3.6

Hope this is what u need... else write back


Niraj

 

[Schroeder]

ufobaby, thanks.
I found RepairDatabase on the MSDN site. It says it's "no longer available" and that you should use CompactDatabase instead. Not true?
I'm not sure why I've been using the JRO technique instead of just using DAO's methods. Seems like there was a good reason but I don't remember what it was. Anyway, here's MSDN's page on the CompactDatabase method.

http://msdn.microsoft.com/library/d...ry/en-us/dao360/html/damthcompactdatabase.asp

 

[ufobaby]

hi, thanks for the link.

here's one more which says that the Repair Functionality is incorporated in the Compact function itself

http://msdn.microsoft.com/library/d...y/en-us/dndao/html/daotoadoupdate_topic11.asp

But am still not sure on how to use this with ADO, coz ADO does not have any such method.

Do we need to include the Jet Reference or something to use the above functions. Can u give some dope on this please ???

In the mean while i hope custsoft's problem is solved -


Niraj

 

[CajunCenturion]

When Microsoft began the development of ADO, they first identified all of the functional elements of DAO. Then, for efficiency purposes (using smaller object), separated this functionality into three basic groups.

ADO - The Standard Database Access Objects
ADOX - ADO Extensions Object
JRO - The Jet Replication and Security Objects.

So, the functionality is all there. Specifically, the CompactDatabase Method is part of the JRO Object.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CCLINT]

ummm......

 

[Schroeder]

ufobaby, that function I posted earlier does require that you reference the JRO library.
CCLINT, huh? did you just eat something tasty?

 

[custsoft]

Just for clarification, I use ADO to access the Access2000 database when I open the program. If that database is corrupt, I get the 'unrecognized database format' which I can't find any documentation on. I want to trap that error and then repair the database, like you can do using Access2000. So I guess the jist of your comments is that the compactdatabase will repair the file. Any idea on what error number the unrecognized database format raises? Thanks for all the help. Joe.

 

[CajunCenturion]

Perhaps CCLINT was going to point out the error in my post - I misnamed the three object models used in the ADO environment. The proper names are as follows:

ADO - Microsoft ActiveX Data Objects
ADOX - ADO Extensions for DDL and Security
JRO - The Jet and Replication Objects.


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CCLINT]

No, not that part...

 

[CajunCenturion]

Well then it could be,
Then, for efficiency purposes (using smaller object), separated this functionality into three basic groups.
But I don't think so, because the purpose of splitting the DAO functionality out into the three models was so that applications wouldn't have to incur the overhead of loading the low usage methods and properties into memeory.

So That leaves:
When Microsoft began the development of ADO, they first identified all of the functional elements of DAO.
Am I giving MS too much credit?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Schroeder]

Perhaps it's that the second sentence is, technically, a fragment.
What does the correct guess win?

 

[CCLINT]

A big fat star!

But actually it was both parts (two "umms&quot. And now three parts....and four "ummms".

Gota run....

 

[ufobaby]

hi
thanks a lot every one for this, but don't u guys think that custsoft is still not got what he wanted....

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q238401

the article does not mention any thing abt trapping the error but has some point to resolve it..... just check it out.


Niraj

 

[CCLINT]

You are absolutly right!
I should have posted an answer to this question rather than getting into, no, starting something that wouldn't have made life easier for anyone, anyways, and being a SA. It would not help anyone in the future, nor would it bring anyone's lost hair back. And it certainly wouldn't help the above issue.

Knowledge of "How or Why" something was developed often helps us understand things better, so we can better solve problems, or advance, in the future.
This particular "How and Why" of the past development will not give any insight into the mechanics of the current model nor it's implementation - only that the models would have similarities, (more so with RDO than DAO), will help previous DAO/RDO users to make easier transaction to the ADO model.
Therefore, it is practically a dead issue.

 

[CajunCenturion]

After reading the posts over, I think that I may have given the impression that the purpose behind the development of ADO was to spilt up the functionality of DAO into three modules. That is certainly not the case, nor was it my intention. What I meant to say, was that during the development of ADO model, they wanted to be sure that all of the functionality that currently existed in the DAO would be available somewhere within the new ADO model.

As a separate and distinct point, the developers understood that the single DAO model had a memory usage drawback, and they didn't want to do that again with ADO, so they took the overall functionality of ADO, and based on function and expected usage, broke it out into ADO, ADOX, and JRO.

If I confused anyone, or gave the wrong impression I apologize.

'----------------------------------------------------------

Now to the point: How to Trap the Error of the Corrupted Database.

This is an example of an error handler that I have used.


Private Sub Some_Proc()

If (gBol_UseErrorHandler) Then
On Error GoTo HandleError
End If

<The Code>

Exit Sub

HandleError:

ErrMessage &quot;Some Useful Info&quot;, &quot;ModuleName.Some_Proc&quot;
Resume Next

End Sub


Then in a module, I have the following:


Const ADO_ERROR = -2147467259

Public Sub ErrMessage(rStr_Err As String, rStr_Title As String)

Dim lRst_Error As ADODB.Error
Dim lStr_Msg As String
Dim lStr_ErrDesc As String
Dim lLng_ErrorNumb As Long

lLng_ErrorNumb = Err.Number
lStr_Msg = &quot;PROC&quot; & vbTab & &quot;: &quot; & rStr_Title & vbCrLf & vbCrLf & _
&quot;REFER&quot; & vbTab & &quot;: &quot; & rStr_Err & vbCrLf & vbCrLf & _
&quot;ERROR&quot; & vbTab & &quot;: &quot;

If (Err.Number = ADO_ERROR) Then
For Each lRst_Error In gADO_Connect.Errors
lStr_ErrDesc = lRst_Error.Description
If (Left(lStr_ErrDesc, 32) = &quot;[Microsoft][ODBC Driver Manager]&quot Then
lStr_Msg = lStr_Msg & &quot;[Microsoft][ODBC Driver Manager]&quot; & vbCrLf
lStr_ErrDesc = vbTab & &quot;: &quot; & Mid(lStr_ErrDesc, 34)
End If
lStr_Msg = lStr_Msg & lStr_ErrDesc & vbCrLf & vbCrLf & vbTab & _
&quot; (Source&quot; & vbTab & vbTab & &quot;: &quot; & lRst_Error.Source & &quot&quot; & _
vbCrLf & vbTab & _
&quot; (SQL State&quot; & vbTab & &quot;: &quot; & lRst_Error.SQLState & &quot&quot; & _
vbCrLf & vbTab & _
&quot; (NativeError&quot; & vbTab & &quot;: &quot; & lRst_Error.NativeError & &quot&quot; & vbCrLf
Next
Else
lStr_Msg = lStr_Msg & Err.Number & &quot; -- &quot; & Err.Source & vbCrLf & Err.Description
End If

Debug.Print lStr_Msg

lStr_Msg = lStr_Msg & vbCrLf & vbCrLf & &quot;ACTION&quot; & vbTab & &quot;: &quot; & _
&quot;Please Notify System Administrator&quot;
MsgBox lStr_Msg, vbExclamation + vbOKOnly, &quot;An Error Has Occurred&quot;

End Sub


As to the specifics, when you encounter the corrupted database, this should trap the error, but the standard Err.Number will probably be ADO_ERROR (-2147467259). In this code, I walk the the ADO.Error objects extracting specific information about ADO Errors. I do not know what will be there for the &quot;Corrupted Database&quot; error, but I would with a watch, inspect that ADO Error Object, probably paying the most attention to the ADOError.NativeError property value. I don't know for sure, but I suspect that you'll have a certain value in ADOError.NativeError for the Corrupted Database.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[ufobaby]

thank u guys,

this throw a lot of light on the subject. we also got quite a lot of link to refer to.

Hey custsoft, we all r waiting to know whether u got the point and the solution to your problem, else please donot hesitate to write back.

after all we are here to share knowlegde
-> seniors correct me if am wrong <-


Niraj