June 25, 2006 at 7:06 pm
Hi,
I need to connect to up to 4 MS Access Databases and extract info from them, but I thought I would like to Repair and Compact them first.
Is there a recommended way to do this programtically?
I was hoping to do this within DTS.
June 26, 2006 at 9:47 am
You could use something like this in an ActiveX Script Task. The problem is that it creates a new database that would be the compacted database, leaving your original untouched and the compact database will error if the target file already exists. There are 3 steps you could take:
1. Determine if target exists and delete it
2. Compact the database which creates the new database
3. Copy the new database over your old database
4. Import the data
1 & 2 & 3)
Dim jetEngine
'Create a Microsoft Jet and Replication Object
Set jetEngine = CreateObject("JRO.JetEngine")
Dim source
Dim target
Dim sourceFile
Dim targetFile
sourceFile = path and file to source
targetFile=path and file to target
source = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
target = source
source = source & sourceFile
target = target & targetFile
'create a file system object
Set fso = CreateObject("scripting.filesystemobject")
if fso.fileexists(targetFile) then
fso.deletefile(targetFile)
end if
jetEngine.CompactDatabase source, target
set jetEngine = nothing
I'm a little leary about replacing the original file with the newly compacted file without first verifying that the new file works. I would create a backup of the original before replacing it.
Dim backup as string
backup = path to backup file & filename
fso.copyfile source, backup
fso.copyfile target, source
set fso = Nothing
There also exists the chance for error if any of the access files are open.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply