Vista - vb script to compact Access database, how to add login & pw

I don't understand why this script seems to successfully compact the back-end
Access database on my computer but on the users system it can't run and
instead asks for user name and password? I would really like the script to
compact the database without any dialogs.

It is a split database with the back-end on the z:drive and the interface
on the users desktop. I only want to compact the back-end files to ensure
the data is not lost in the event of possible corruption. My first
question: is there an easy way to insert the password and user name so this
works for the user?

My other question is why does it work on my computer and not the users? Is
that because vbs knows I am the developer of the Access db and have a
developer pw while the user is not a developer and has to log in with the
password? It seems weird because it doesn't ask me for the password even
though I do not have Access open it appears to let me run the compact script.

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:\SwimClub\acsc_be.mdb"
'
' ////////////////////////////////////////////////////////////////

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

' Create Access 97 Application Object
'Set objAccess = CreateObject("Access.Application.8")

' For Access 2000, use Application.9
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
call MsgBox("compact successful")
End If

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

Janis wrote:Use the JetComp utility instead of automating Access.
http://support.microsoft.com/kb/273956


--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

I don't think this utility is working. I downloaded the compact utility. I
created a new empty database. In the utility I selected the soruce backend
database. I selected the compact to database as the new blank database. It
got an error when I ran it and no explanation as to what was wrong. This
database was a Access 2000 but it was upgraded to Access 2003. Could that be
the reason? The other question, isn't this utility for a tech support
person? I don't know if it will stay set up for the from and to sources for
a technically challenged user? I may need to stick with the script.

thanks,

"Bob Barrows" wrote:

Sorry, I cross posted a reply to a reply on my question about a VB script to
compact an Access database. The cross post didn't attach the original vbs
script. The problem is, I can't get the compact utility to work, in order to
use the utility instead of the vbs script. I don't know if it is because the
utility is for Access 2000? The database was upgraded to 2003.
In any case, if you have to open the utility and select the resource and the
place to send the compacted database to, every time you use it my user won't
be able to use it even if it is preferable.
The original problem with this script is that when the user runs it, it
stops running and asks for the user and password first, in order to compact
the database. Can you add the password and login to this script so the user
doesn't have to add it? It is a secure environment with only 1 user. Thanks,
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:\SwimClub\acsc_be.mdb"
'
' ////////////////////////////////////////////////////////////////

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

' Create Access 97 Application Object
'Set objAccess = CreateObject("Access.Application.8")

' For Access 2000, use Application.9
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
call MsgBox("compact successful")
End If

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

Janis wrote:Yes, it did.
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Hmm, you chose the wrong Access group to crosspost this to. Your question
has nothing to do with Access macros. You should have chosen one of the VBA
groups if you wanted their input.

Janis wrote:Probably. There was a link in the article I posted to this article:
http://support.microsoft.com/kb/295334/EN-US/
which does apply to Access 2003.
This utility can be run from the command line, so you can use WshShell .run
to run it. See http://msdn.microsoft.com/en-us/libr...ky(VS.85).aspx

When you have the correct version of the utility downloaded, open a command
window (enter "cmd" into the Start > Run dialog) and enter
p:\ath\to\jetcomp.exe /? to see the command line options. Experiment with
creating the proper command line to run jetcomp to compact your database,
and when you have it, use the Run method in your vbscript code to execute
it.

PS. It is always recommended to back up your database before compacting it.
You can use the FileSystemObject to copy your database file - look it up in
that link to the documentation I provided in the next to last paragraph
above. If you would like a downloadable .chm file containing the scripting
documentation, go here:
http://tinyurl.com/7rk6