Need code to compact a secured database

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

Hi everyone,

I have the following code to compact my database. When I
ran my code, I got an error message because my database
was secured. My user name is TIM and password is 123.

Could anyone tell me how to use VBA to logon as TIM and
compact the database?

Public Sub test()

DBEngine.CompactDatabase "c:\tempdb\db2.mdb", "c:\tempdb\db
2_test.mdb"

End Sub

Thanks a lot.

Tim.
 
DBEngine.CompactDatabase "c:\tempdb\db2.mdb", "c:\tempdb\db
2_test.mdb"
Click to expand...

Use the DOS shortcut: I haven't checked the parameters but it should
probably be doable a bit like this (by the way, put the whole command on
one line). Then again, if it's secured, you don't really want the password
hardwired in anywhere, do you?


c:\Office9\MSAccess.Exe
c:\tempdb\db2.mdb
/compact c:\tempdb\db2_test.mdb
/uid=Tim
/pwd=123

HTH


Tim F
 
Hi Tim F,

My database is user-level security database. I want to
create a sub in other database to compact my secured
database.

I have the code like this:


Public Sub test()

Dim wrks As Workspace

DBEngine.SystemDB = "D:\tim.MDW"

Set wrks = DBEngine.CreateWorkspace("", "tim", "123")

DBEngine.CompactDatabase "c:\tempdb\db2.mdb", "c:\tempdb\db
2_test.mdb"

End Sub


I ran the code but I got error message: invalid userID or
password. When I debuged the code, it pointed to
C:\Winnt\system32\system.mdw not pointed to "D:\tim.MDW".

Do you know why and how to fix it?

Thanks in advance.

Tim.
 
Do you know why and how to fix it?
Click to expand...

No. I have never found any need to compact a db within vba code so,
although I suspect it can be done, I have not explored the objects and
methods. My impression is that you cannot change the dbengine.systemdb
property after any data access has happened; and I would guess that a
compact should be a workspace method rather than a dbengine one: but as I
said, I have not explored this.

Checking the help files, it looks as though the command line I suggested
would work, except that I forgot the /wrkgrp "workgroup information
file.mdw" switch.

Best of luck

Tim F
 
Tim, you should be able to compact a secured database with something like:

Dim x As PrivDBEngine
Set x = New PrivDBEngine
With x
.SystemDB = "C:\blah.mdw"
.DefaultUser = "Tom"
.DefaultPassword = "secret"
then use opendatabase, compactdatabase methods etc.
End With

HTH,
TC
 
Hi TC,

I tried the code, but it gave me an error message: "You
don't have the necessary permission to use
the "D:\tim.mdw"....."

Could you tell me where I did wrong?

The following is my code:

Public Sub test()

Dim wrks As PrivDBengine

Set wrks = New PrivDBEngine

With wrks

.SystemDB = "D:\tim.MDW"
.defaultUser = "tim"
.defaultPassword = "123"


DBEngine.CompactDatabase "c:\tempdb\db2.mdb", "c:\tempdb\db
2_test.mdb"
End With

End Sub

Thanks a lot.

Tim.
 
Hi Tim

Unfortunately I do not have Access on this PC, to check.

Maybe a silly question, but: are you sure that you are replacing
"D:\tim.mdw" with the name of the workgroup file which is required for
opening your secured database? And have you replaced "tim" and "123" with
the username & password of a user who has permission to compact that
database?

Post the actual code you used.

HTH,
TC
 
Hi TC,

I tried the code again by creating a new secured
database. It worked fine to me on the new database.
Then, I modified the code to compact the existing
database, it gave me same error message again. I couldn't
figured out the problem. The different between new and
exist database are:

new db stored in timtim folder and named test.mdb in local
drive.

existing db stored in tim test timtim folder and named
test_test_test_test.mdb in LAN drive.

Is it mater the length folder and file name? Or stored on
LAN?

Thanks for your time.

Tim.
 
You need to post the actual code you used.

TC


Tim said:
Hi TC,

I tried the code again by creating a new secured
database. It worked fine to me on the new database.
Then, I modified the code to compact the existing
database, it gave me same error message again. I couldn't
figured out the problem. The different between new and
exist database are:

new db stored in timtim folder and named test.mdb in local
drive.

existing db stored in tim test timtim folder and named
test_test_test_test.mdb in LAN drive.

Is it mater the length folder and file name? Or stored on
LAN?

Thanks for your time.

Tim.
Click to expand...
 
Hi TC,

The code works fine now. I think my existing database
have some problems that why it failed when the code
compact the db. I recreated the whole database and then
it worked fine.

Thank you very much for your help.

Tim
 
Back
Top