Cannot use DoCmd.TransferDatabase with system.mdw secured SourceDB

[Michael P. Schieferer]

Hello there,

following problem: I have two Access Databases. One secured with an
additional system.mdw (NOT the system-wide but another) wich actually
holds the Data and another unsecured one.

Now I want to create Tablelinks in the unsecured DB. I use the
DoCmd.TransferDatabase command to realize this. But I'm having realy big
trouble getting the Tablelinks. I cant get it work to create the
Tablelinks as long the secured DB is secured with the system.mdw.

I tried it in VBA Code with "Microsoft Access", "Jet 3.x" and "ODBC" as
Datasource but no success.

And no, using the system.mdw as my System-wide system.mdw is no
practicable solution for this case.

I'm searching for a long time but could't find any solutions...

Many thanks for your help
Michael P. Schieferer

 

[TC]

You can do it with ADO and Jet 4 using the following method:

http://support.microsoft.com/?­id=240222

If you're using DAO and Jet 3, I'd try something like this:

(untested)

dim dbe as privdbengine, ws as workspace, db as database
set dbe = new dbengine
with dbe
.systemdb = "full path to securing mdw file"
.defaultuser = "a suitable username"
.defaultpassword = "that user's password"
set ws = .workspaces(0)
set db = ws.opendatabase ("full path to secured mdb file")
' do stuff here!
end with

At the indicated point, "do stuff here", you have (1) a worspace ws
which is using the proper secured workgroup file, and (2) a database
variable db which represents your secured database, opened for the
specified user, in the secured workspace. Then you can probably use
methods of the /current/ workspace and database, and /that/ workspace
and database, to establish the links.

I don't have Access here to check, so I can't do all the code for you.
But that should give you a start. I do not guarantee that it is
do-able, as I have never done it myself.

HTH,
TC

 

[TC]

TC wrote:

(snip)

oops:

set dbe = new dbengine

should be:
set dbe = new privdbengine

TC

 

[Michael P. Schieferer]

Hello TC,

thanks for your answer but I cannot test it here. Will have a look on
monday but it sounds logical!

Regards
Michael P. Schieferer

 

[Michael P. Schieferer]

Hello TC,

thanks for your answer. I tried the DAO solution but still not working
proper.

dim dbe as privdbengine, ws as workspace, db as database
set dbe = new dbengine
with dbe
.systemdb = "full path to securing mdw file"
.defaultuser = "a suitable username"
.defaultpassword = "that user's password"
set ws = .workspaces(0)
set db = ws.opendatabase ("full path to secured mdb file")

doCmd.TransferDatabase acLink, "Jet
3.x",db,acTable,"tblLink","tblLink", False

end with

I also tried "Microsoft Access" instead of "Jet 3.x" as DatabaseType but
the same error, "Wrong Argument for Datatype" (having the german Version
of Access so I'm guessing the english Errormessage).

Seems like the object "db" isn't a valid DatabaseName in the
TransferDatabase Method.

I was looking also looking for the ADO soultion you posted a link but it
was broken. Not important for me, just saying.

Regards
MIchael P. Schieferer

 

[Douglas J. Steele]

Since you've declared dbe as PrivDBEngine, I believe you need to refer to
that object type in your instantiation:

set dbe = New PrivDBEngine

 

[TC]

FREAKING GOOGLE ..... :-(((( It just lost all this, so I'll type it
in //AGAIN// :-(((

You can't use DoCmd in the way you are trying.

Normally, you have a workspace object ( dbengine(0) ), and a database
object ( dbengine(0)(0) ). Those refer to the current database, open
against the default workgroup file.

My code showed you how to get, /in addition/, a workspace object ( ws
), and a database object ( db ), which represent the secured database
open against the correct, securing workgroup file.

I suggested that by using suitable methods, and those 4 objects, you
may be able to establish the linkages.

But DoCmd is not one of those methods. Docmd is a method of the
Application object.

If I could help you by writing the code & providing it here - I would.
But for various reasons, I can't. (For one, I don't have Access
anywhere near where I post to the net, & I'm pressed for time with
other work right now.)

Start by learning how to establish a table linkage /without/ using
docmd. Then post your code back here, & I may be able to adapt it for
you.

Cheers,
TC

 

[TC]

Yes indeed, thanks for the catch.

TC