Transfer Data - 2 Secured Databases

[Guest]

I have two databases secured by different workgroup files, and I am trying to
figure out a way to import and/or append some data from database A to a table
in database B. I need to be able to do this on a recurring bases, triggered
by a macro or form control.

I''ve read prior threads about unsecring the files and resecuring them with
the same workgourp file, but the conversations suggested that you had to
"Join" that new work group, and that this would affect all other databases.
We use a number of databases that are unsecured, so I don't think this
solution will fit.

Among many other things I've tried coding for DoCmd.TransferDatabase, but it
does not seem to accept a workgroup file path or username/password.

Any suggestions would be greatly appreciated.

(Posted to security Group as well).

 

[Graham Mandeno]

First, I recommend against using the Workgroup Administrator to "Join" any
workgroup file other than the default SYSTEM.MDW.

A far more flexible way to open databases with different workgroups is by
specifying the workgroup file in a shortcut. The target of the shortcut
should look something like this:

<path to msaccess.exe> <path to database> /wrkgrp:<path to mdw>

For example:

"C:\Program Files\Microsoft Office\OFFICE11\msaccess.exe"
"D:\My Databases\Order entry.mde"
/wrkgrp:"D:\My Databases\OE workgroup.mdw"

However, this is not going to solve your problem because you need to use two
different workgroup files within the same instance of Access. The way to do
this is to create another private DBEngine using the PrivDBEngine object:

Dim dbe As PrivDBEngine

Dim ws As Workspace

Dim db As Database

Set dbe = New PrivDBEngine

dbe.SystemDB = "D:\My Databases\OE Workgroup.mdw"

dbe.DefaultUser = "SecureUser"

dbe.DefaultPassword = "secretsausage"

Set ws = dbe.Workspaces(0)

Set db = ws.OpenDatabase("D:\My Databases\OE Backend.mdb ")



Now you can open a recordset on a table in your second secure database and
manipulate it as though it were in the local one.
--
Good Luck!



Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Guest]

Great, thanks Graham. That looks like exactly what I was searching for. I'll
give it a try.

First, I recommend against using the Workgroup Administrator to "Join" any
workgroup file other than the default SYSTEM.MDW.

A far more flexible way to open databases with different workgroups is by
specifying the workgroup file in a shortcut. The target of the shortcut
should look something like this:

<path to msaccess.exe> <path to database> /wrkgrp:<path to mdw>

For example:

"C:\Program Files\Microsoft Office\OFFICE11\msaccess.exe"
"D:\My Databases\Order entry.mde"
/wrkgrp:"D:\My Databases\OE workgroup.mdw"

However, this is not going to solve your problem because you need to use two
different workgroup files within the same instance of Access. The way to do
this is to create another private DBEngine using the PrivDBEngine object:

Dim dbe As PrivDBEngine

Dim ws As Workspace

Dim db As Database

Set dbe = New PrivDBEngine

dbe.SystemDB = "D:\My Databases\OE Workgroup.mdw"

dbe.DefaultUser = "SecureUser"

dbe.DefaultPassword = "secretsausage"

Set ws = dbe.Workspaces(0)

Set db = ws.OpenDatabase("D:\My Databases\OE Backend.mdb ")



Now you can open a recordset on a table in your second secure database and
manipulate it as though it were in the local one.
--
Good Luck!



Graham Mandeno [Access MVP]
Auckland, New Zealand

I have two databases secured by different workgroup files, and I am trying
to
figure out a way to import and/or append some data from database A to a
table
in database B. I need to be able to do this on a recurring bases,
triggered
by a macro or form control.

I''ve read prior threads about unsecring the files and resecuring them
with
the same workgourp file, but the conversations suggested that you had to
"Join" that new work group, and that this would affect all other
databases.
We use a number of databases that are unsecured, so I don't think this
solution will fit.

Among many other things I've tried coding for DoCmd.TransferDatabase, but
it
does not seem to accept a workgroup file path or username/password.

Any suggestions would be greatly appreciated.

(Posted to security Group as well).