Results 1 to 1 of 1
  1. #1
    Join Date
    Dec 2004
    Madison, WI

    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
    Dim objFSO
    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

    Dim objShell
    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)
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts