Unanswered: How To: Create an mde via vbs/Compact and Repair via vbs
Here's some code I thought I'd post as I've read a few threads on this. This piece of vbs code can be created in notepad and saved as a vbs. What it does is it will take an existing mde file and create a new one with the user's login name. It then executes the new mde file with the user's login name. I call this technique "Spawning" and it has made our life a lot easier. Attached is also a RemoteShareInfo.mdb file which will let you select a server (i.e. SQL Server) and it will find all the user's in the mde files. This makes it very easy to find user's in a specific mde database (i.e. the mde file will show TimeSheetJohnS.mde as being open) - but you may need SQL Server client installed on your machine for it to work. I also attached a sample vbs script of the code below in the zip file. I hope you find this useful.
Set WshNetwork = CreateObject("WScript.Network")
GetUser = WshNetwork.UserName
Set WshNetwork = Nothing
LUName = "\\SQLServer\Databases\Databases\TimeSheet2003\Hou rs2003.mde"
'Call Shell(LUName, 1)
'LUName = GetLocation(11, False)
oldname = LUName
newName = Replace(LUName, ".mde", "") & GetUser & ".mde"
retval = 0
Set objFSO = CreateObject("Scripting.FileSystemObject")
'DoCmd.OpenForm "ProgressBar", acNormal, , , acFormReadOnly, acWindowNormal
'wscript.echo oldname, newname
retval = objFSO.CopyFile(oldname, newName, True)
'DoCmd.Close acForm, "ProgressBar"
Set objFSO = Nothing
Set objShell = CreateObject("Wscript.Shell")
'objShell.Run "MSAccess.exe " & newName & " /compact", 1, True
objShell.Run "MSAccess.exe " & newName, 1
Set objShell = Nothing
'Access.SetOption "Auto Compact", True
'Call Shell("MSAccess.exe " & newName & " /compact", 1)
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)