Importing Multiple Tables from a Protected Access Database

[Terry Lawson]

All



I need to import and not link eight tables from another password protected database.

I'm trying to use for e.g:
DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\test\test_data.mdb", acTable, "tblMember", "tblImportedMember", , True 'member table

If I then follow the above with say:
DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\test\test_data.mdb", acTable, "tblCountry", "tblImportedCountry", , True 'country table

I get asked twice for the password for the protected database from which I'm importing.

Is there a mechanism for just inputting the password once?

I suspect that I don't understand fully understand the StoreLogin function or is that a red herring?

Help/assistance from you guys would be much appreciated.

Regards

Terry Lawson

[llkhoutx]

Take a look at this URL.

[Terry Lawson]

Sorry to take so long to respond, I've tried what I know but to no avail

URL gives details for Excel import, I'm not clever enough to convert that code to solve my problem

Any further suggestions?

Regards

Terry Lawson

[llkhoutx]

Heres's another URL which imports from a password protected database.

[Terry Lawson]

I'm still struggling

I've pasted the code from the link you have given me with my (mis)understandings on the same row.

Set oAcc = New Access.Application 'set a new Access application
Set db = oAcc.DBEngine.OpenDatabase(strForeignDB, False, False, ";PWD=" & strProjPW) 'set a new database named strForeignDB with password = strProjPW

oAcc.OpenCurrentDatabase strForeignDB 'open the new strForeignDB database (with password from set db above)
oAcc.DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentProject. 'import the table "t0zAA" from currentproject.fullname to "t0ZRBB" which will be in StrForeignDB
FullName, acTable, "t0ZAA", "t0ZRBB", True 'I think that currentproject.fullname is the database that this code is run in?


I think that this does the reverse of what I need, that is the code as written imports tables into a protected database, I want to import tables into the database that the code is run in from a password protected database.
I've tried to do the obvious and used oAcc.Docmd.TransferDatabase acExport etc. but still get problems.

I'm sorry if I'm a pain, but I'm very much a self taught novice.

Further help would really be appreciated.

[llkhoutx]

Without my deciphering you code, what happens when you run what you've posted?

[Terry Lawson]

I've changed the code slightly it is now:

Dim oAcc As Application
Dim db As Database
Dim strForeignDB As String
Dim strProjPW As String
Dim temp As String


strProjPW = "splendid"
strForeignDB = "C:\SFHG\sfhg_data.mdb"

Set oAcc = New Access.Application
Set db = oAcc.DBEngine.OpenDatabase(strForeignDB, False, False, ";PWD=" & strProjPW)


oAcc.OpenCurrentDatabase strForeignDB

temp = CurrentProject.FullName

oAcc.DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentProject.FullName, acTable, "tblMember", "Importmember", True

Set db = Nothing
Set oAcc = Nothing

temp returns as expected the name of the database running the code.

I'm getting error 3011 "tblMember not found", but I'm certain it's there.
I've tried expanding the table to be copied to strForeignDB & "\tblMember" and get the same error (3011).