Compacting a protected MS Access database using VB Script

[KiaKia]

Hi,
I use a VB script to routinely compact the tables of my MS Access database. It was working well when I had no protection on that database.
My problem started since I protected my database using a workgroup. The script runs automatically every morning at2:00AM ( I am not at my desk for sure!) and it asks for my password. Is there any way to pass my username and password to the CompactDatabase function in my script, or any other solution?

here is my script:

' ***************** BEGIN CODE HERE ' *****************
'
Dim objScript
Dim objAccess
Dim strPathToMDB
Dim strMsg

' ///////////// NOTE: User must edit variables in this section /////
'
' The following line of code is the only variable that need be edited
' You must provide a path to the Access MDB which will be compacted
'
strPathToMDB = "C:\MyTables.mdb
'
' ////////////////////////////////////////////////////////////////

' Set a name and path for a temporary mdb file
strTempDB = "C:\Comp0001.mdb"

' Creat an object For Access 2003, use Application.11
Set objAccess = CreateObject("Access.Application.11")

' Perform the DB Compact into the temp mdb file
' (If there is a problem, then the original mdb is preserved)
objAccess.DbEngine.CompactDatabase strPathToMDB ,strTempDB

If Err.Number > 0 Then
' There was an error. Inform the user and halt execution
strMsg = "The following error was encountered while compacting database:"
strMsg = strMsg & vbCrLf & vbCrLf & Err.Description
Else
' Create File System Object to handle file manipulations
Set objScript= CreateObject("Scripting.FileSystemObject")

' Back up the original file as Filename.mdbz. In case of undetermined
' error, it can be recovered by simply removing the terminating "z".
objScript.CopyFile strPathToMDB , strPathToMDB & "z", True

' Copy the compacted mdb by into the original file name
objScript.CopyFile strTempDB, strPathToMDB, True

' We are finished with TempDB. Kill it.
objScript.DeleteFile strTempDB
End If

' Always remember to clean up after yourself
Set objAccess = Nothing
Set objScript = Nothing
'
' ****************** END CODE HERE ' ******************

 

[cmartin75]

Use the srcLocale parameter of the compactdatabase method.

Something like:

objAccess.DbEngine.CompactDatabase strPathToMDB ,strTempDB,,,";pwd=password"

Don't forget the ; before the pwd.

Microsoft reports that the intellisense of this method is different than documentation

http://support.microsoft.com/kb/192153/en-us

 

[KiaKia]

Thanks cmartin75,

I tried the srclocale parameters, but the password you can pass using this parameter is the Database password. While my problem is passing the password ( or both Username and Password) for the workgroup which is managing access to my DB.
Please let me know if you have any comments.

Kia

 

[dilettante]

See ACC2000: Jet Compact Utility Available in Download Center.

JetComp can compact and repair when Access fails to, and it can be run from a command line specifying an alternate workgroup database as well as a DB PW and a User/PW. You could run it from a script or even a BAT/CMD file instead.

JetComp can even compact and repair workgroup databases (MDW files) themselves.

To get its command-line parameters help run JetComp with the /? switch.

 

[KiaKia]

Thank you very much dilettante,
It can solve my problem bu I am not able to find the right syntax to run JetComp from my VB Script. Can you provide me with an example or any link that I can learn how to run an exe file from VB Script?

 

[PHV]

http://msdn.microsoft.com/en-us/library/d5fk67ky(VS.85).aspx

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KiaKia]

Thanks PHV the link was very help full,
I found some examples and finally I can run the JetComp with all switches from Command Prompt Window.
c:\jetcomp -src: "Q:\Tbl.mdb" -dest: "Q:\TblBKP.mdb" -y "Q:\sys.mdw" -u "myname" -p "mypass"

I can also run JetComp from my VB Script as follows:

Set WshShell = CreateObject("WScript.Shell")
Set oExec = WshShell.Exec("c:\jetcomp")

but I am still not able to run jetcomp with the switches from my VB Scriop. I tried the followings, non of them are working.

Set oExec = WshShell.Exec("c:\jetcomp -src: " & strPathToMDB & " -dest: " & strPathTpBKP & " -y " & strPathToMDW & " -u " & strUserN & " -p " & strPassW)

Set oExec = WshShell.Exec("c:\jetcomp -src:" & strPathToMDB & " -dest:" & strPathTpBKP & " -y" & strPathToMDW & " -u" & strUserN & " -p" & strPassW)

Set oExec = WshShell.Exec("c:\jetcomp -src: 'Q:\Tbl.mdb' -dest: 'Q:\TblBKP.mdb' -y 'Q:\sys.mdw' -u 'myname' -p 'mypass'")

Set oExec = WshShell.Exec("c:\jetcomp" -src: "Q:\Tbl.mdb" -dest: "Q:\TblBKP.mdb" -y "Q:\sys.mdw" -u "myname" -p "mypass")

Set oExec = WshShell.Exec(c:\jetcomp -src: "Q:\Tbl.mdb" -dest: "Q:\TblBKP.mdb" -y "Q:\sys.mdw" -u "myname" -p "mypass")

Please let me know what is the correct syntax, if you can.

Thanks,
Kia

 

[dilettante]

I don't think JetComp will be able to parse a command line that uses apostrophes (') in place of quotation marks (").

To include a quotation mark within a string literal in VBScript you escape it by doubling it, so:

[tt]Set oExec = WshShell.Exec("c:\jetcomp -src: ""Q:\Tbl.mdb"" -dest: ""Q:\TblBKP.mdb"" -y ""Q:\sys.mdw"" -u myname -p mypass")[/tt]

The quotes should not be necessary around values without embedded special characters (typically spaces). So while I left them in for the file path names above I took them out for the user and pw values. For the file names used here the quotes are not needed there either, but do no harm and are probably better included for generality. You could easily quote the user/pw values the same way.

One can also use:

[tt]Set oExec = WshShell.Exec("c:\jetcomp -src: """ & strPathToMDB & """ -dest: """ & strPathTpBKP & """ -y """ & strPathToMDW & """ -u " & strUserN & " -p " & strPassW)[/tt]

Or even:

[tt]Set oExec = WshShell.Exec("c:\jetcomp -src: """ & strPathToMDB & """ -dest: """ & strPathTpBKP & """ -y """ & strPathToMDW & """ -u """ & strUserN & """ -p """ & strPassW & """")[/tt]