locked
Compact Database with Password in Visual Studio RRS feed

  • Question

  • I am attempting to compact a database using a vb routine in Visual Studio 2008. See code below. When I run it in debug mode, when it executes the compactdatabase command it automatically goes to the 'Catch' section and doe snot compact the db. What am I missing in the command? (I have verified the pw is correct).

    Dim hrpwd As String = "hrpay"
    Dim SrcDB = "C:\test\Test.accdb"
    Dim DstDB = "C:\test\Test1.accdb"


    Try                  
    dbEngine.CompactDatabase(SrcDB, DstDB, ";pwd = '" & hrpwd & "'")      

    Catch
                   

    End Try

    • Moved by Cindy Meister MVP Tuesday, September 13, 2011 2:09 PM not using VSTO technology (From:Visual Studio Tools for Office)
    Tuesday, September 13, 2011 1:32 PM

Answers

  • Finally got it. I was missing a comma in the command (thanks for reminding me there are 5 arguments in the function Andrey). So here is the command that works for me (with the password hardcoded in the command and not a string field):

    dbEngine.CompactDatabase(tmpPath, dbPath, , , ";pwd=hrpay")

    • Marked as answer by J-Bal Wednesday, September 14, 2011 6:58 PM
    Wednesday, September 14, 2011 6:58 PM

All replies

  • What is the full code you are attempting to use?  I tried opening a new project and putting that in there and it gives me an error on dbEngine.CompactDatabase.
    Bob Larson, Access MVP 2008-2010, 2011
    Tuesday, September 13, 2011 6:04 PM
  • Drop the quotes around the password.  The syntax is pwd=password not pwd='password'.
    http://www.saberman.com
    Wednesday, September 14, 2011 3:31 AM
  • Tried it 3 different ways and was not able to get it to work (starting with your suggestion to take out the quotes on the password):

    Using string field hrpwd: dbEngine.CompactDatabase(tmpPath, dbPath, , ";pwd = ' & hrpwd & '")

    Password hardcoded in command with no quotes: dbEngine.CompactDatabase(tmpPath, dbPath, , ";pwd = hrpay")

    Password hardcoded in command with quotes: dbEngine.CompactDatabase(tmpPath, dbPath, , ";pwd = 'hrpay'")

    Wednesday, September 14, 2011 4:38 PM
  • The forth one you missed:

    dbEngine.CompactDatabase(tmpPath, dbPath, , ";pwd=" & hrpay)


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Wednesday, September 14, 2011 5:15 PM
  • And 1 thing about argument order. There are 5 arguments (expression.CompactDatabase(SrcName, DstName, DstLocale, Options, password)) and 3 last of them support ";pwd=" option. So, I would define the named argument explicitly like:

    dbEngine.CompactDatabase(tmpPath, dbPath, password:= ";pwd=" & hrpay)


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Wednesday, September 14, 2011 5:24 PM
  • Finally got it. I was missing a comma in the command (thanks for reminding me there are 5 arguments in the function Andrey). So here is the command that works for me (with the password hardcoded in the command and not a string field):

    dbEngine.CompactDatabase(tmpPath, dbPath, , , ";pwd=hrpay")

    • Marked as answer by J-Bal Wednesday, September 14, 2011 6:58 PM
    Wednesday, September 14, 2011 6:58 PM
  • Oh, I confused your vaariable with its value. But now we really can see that there was missed argument. So, I believe you know that all of these should work.

    dbEngine.CompactDatabase(tmpPath, dbPath, , , ";pwd=" & hrpwd)

    dbEngine.CompactDatabase(tmpPath, dbPath, password:= ";pwd=" & hrpwd)

    dbEngine.CompactDatabase(tmpPath, dbPath, , , ";pwd=hrpay")

    dbEngine.CompactDatabase(tmpPath, dbPath, password:= ";pwd=hrpay")

    However, glad to see the problem is solved.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Wednesday, September 14, 2011 7:12 PM
  • My sugestion for case OLE DB 12.0 and bank use password:

    dbe.CompactDatabase (sourceDbSpec, destinationDbSpec, null, null, "; pwd = mypassword");


    Saturday, March 24, 2018 2:45 AM