DAO Error When Compacting MS Access 1

[hsugiyama]

Hi all,

I'm having a problem, repairing/compacting an MS Aceess 97 MDB from VB6 with the following code. It just doesn't work on one PC with a DAO error 3125, &quot;<Name> is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.&quot; The code works just fine on other PC's, but not on this one. The PC is Windows 2000 Pro with Access 2003 installed. Can I not use the DAO compact method if MS Access 2003 is installed?

Thank you for helping.

Regards,

Hideki


Public Sub gufCompactDB()

Dim intFileNameLen As Integer
Dim strLDB As String
Dim strCompactedFile As String

intFileNameLen = Len(gstrADO_DATASOURCE)
strLDB = Left(gstrADO_DATASOURCE, intFileNameLen - 3) & &quot;ldb&quot;
strCompactedFile = Left(gstrADO_DATASOURCE, intFileNameLen - 3) & &quot;cmp&quot;
If Len(Dir(strLDB)) = 0 Then
On Error GoTo ERROR_HANDLER
'Repairing
DBEngine.RepairDatabase gstrADO_DATASOURCE
'Compacting
DBEngine.CompactDatabase gstrADO_DATASOURCE, _
strCompactedFile
'Replacing with compacted data
Kill gstrADO_DATASOURCE
Name strCompactedFile As gstrADO_DATASOURCE
On Error GoTo 0
End If
Exit Sub
ERROR_HANDLER:
MsgBox &quot;The system cannot optimize the data file due to the following error.&quot; & vbCrLf & vbCrLf & Err.Number & &quot; &quot; & Err.Description _
, vbExclamation, gstrMsgTitle
End Sub

 

[Michael42]

hsugiyama,

I use this in VB5\Access 97 DB often:

Screen.MousePointer = vbHourglass
DBEngine.CompactDatabase App.Path & &quot;\db1.mdb&quot;, App.Path & &quot;\db_new.mdb&quot;
DoEvents
Kill App.Path & &quot;\db1.mdb&quot;
FileCopy App.Path & &quot;\db_new.mdb&quot;, App.Path & &quot;\db1.mdb&quot;
DoEvents
Kill App.Path & &quot;\db_new.mdb&quot;
Screen.MousePointer = vbDefault

MsgBox &quot;Compact Database operation completed.&quot;, vbInformation, &quot;Status&quot;