I'm using the dbengine.compactdatabase function in my program to compact an MDB database. If the client has office 2000 with access installed, everything works fine but if he only has access runtime 2000 installed, I get an automation error. Is there a normal reason for this? If I try to compress via the odbc manager on hte client machine, it works ok so how could I programatically do the same thing? Anyone ever played with the ODBC object and API? Any comment will be very apprciated.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Compacting MDB using access runtime 1
- Thread starter sebpas
- Start date
-
1
- #2
Sebpas,
Use ADO and also set a reference to MS Jet and Replication Objects. Below is a code snippet that I use and works fine.
Public Enum AccessEngine
ACC97 = 4
ACC2000 = 5
End Enum
Public Function CompactAccessDB(Optional ByVal iEngine As AccessEngine = ACC2000) As Boolean
Dim JRO As JRO.JetEngine
Set JRO = New JRO.JetEngine
Dim sSourceDB As String, cTemp As String
Dim sDestDB As String, cMaster As String, cBackup As String
cMaster = cIM & "\MyDatabase.Mdb"
cBackup = cIM & "\MyDatabase.Bkp"
' ----- Delete Target File
cTemp = cIM & "\MyTemp.Mdb"
Call KillTargetFile(cTemp)
' ----- Make A Backup Of The Current MDB
CopyFile cMaster, cBackup, False
' ----- Compact Database
On Local Error GoTo NoCompact
sSourceDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cMaster
sDestDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cTemp
JRO.CompactDatabase sSourceDB, sDestDB & ";Jet OLEDB:Engine Type=" & iEngine
' ----- Copy compacted Temp MDB Back To Master
CopyFile cTemp, cMaster, False
' ----- Delete Temp MDB
Call KillTargetFile(cTemp)
Hope this helps.
Regards, Nick
Use ADO and also set a reference to MS Jet and Replication Objects. Below is a code snippet that I use and works fine.
Public Enum AccessEngine
ACC97 = 4
ACC2000 = 5
End Enum
Public Function CompactAccessDB(Optional ByVal iEngine As AccessEngine = ACC2000) As Boolean
Dim JRO As JRO.JetEngine
Set JRO = New JRO.JetEngine
Dim sSourceDB As String, cTemp As String
Dim sDestDB As String, cMaster As String, cBackup As String
cMaster = cIM & "\MyDatabase.Mdb"
cBackup = cIM & "\MyDatabase.Bkp"
' ----- Delete Target File
cTemp = cIM & "\MyTemp.Mdb"
Call KillTargetFile(cTemp)
' ----- Make A Backup Of The Current MDB
CopyFile cMaster, cBackup, False
' ----- Compact Database
On Local Error GoTo NoCompact
sSourceDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cMaster
sDestDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cTemp
JRO.CompactDatabase sSourceDB, sDestDB & ";Jet OLEDB:Engine Type=" & iEngine
' ----- Copy compacted Temp MDB Back To Master
CopyFile cTemp, cMaster, False
' ----- Delete Temp MDB
Call KillTargetFile(cTemp)
Hope this helps.
Regards, Nick
- Thread starter
- #3
Sebpas,
MDAC 2.5 at least must be installed, I also have SP3 of Visual Studio, although I dont't think this should cause a problem. If you want to mail me the code you have created I'll be more than happy to try it for you.
Regards, Nick
nick@isd.u-net.com
MDAC 2.5 at least must be installed, I also have SP3 of Visual Studio, although I dont't think this should cause a problem. If you want to mail me the code you have created I'll be more than happy to try it for you.
Regards, Nick
nick@isd.u-net.com
- Thread starter
- #5
Thanks NickISD but I found the problem. There is a password on my MDB and I took the syntax from somewhere else on the web but it was wrong, causing me the ISAM error. I now have put the right syntax and everything works fine now. Thanks for your help guys, it's very appreciated.
- Status
