Transferdatabase with password 1

[Poppekop]

I have a tool in an MS Access project (adp) that imports tables from an Access database (mdb) onto an SQL server using the transferdatabase method. It is a generic tool that works on different SQL databases by resetting the connection of the project and different access databases by a textbox with the name and path.

The thing is, that I get Access databases with password, and they tell me the password, but I can't find a way to put that in the transferdatabase method. There's hundreds of tables to import so typing in the password every time isn't an option (still I get an error when I do that: "You entered an expression that has an invalid reference to the property CurrentRecord.&quot

I tried using the copyobject,, but then I get stuck with the fact that it asks for a destination database and not the source database. I tried tricking it with an application object opening a currentdatabase and run the docmd.transferdatabase from there, but then I get an error saying that the project is locked by another user (which is me, because I'm the only one using it)

Can somebody help me with this?

 

[Diesels]

Yea kicked my butt also. Essentually you must remove the password from the database before you use the transferdatabase command. Do your thing with the data in the tables, then put the password back. In my application that master database has to link through 160 databases, update records, append records for master, then go on to the next one.

Public Sub LinkTables(Pathname)
Dim dbsPassword As Database
Data_Pathname = Pathname + "TPdata.mdb"
Set dbsPassword = OpenDatabase(Data_Pathname, True, False, "; pwd=currentpassword&quot
dbsPassword.NewPassword "currentpassword", ""
dbsPassword.Close
DoCmd.TransferDatabase acLink, "Microsoft Access", Data_Pathname, acTable, "Claim", "Claims 1", N
RetCode = Transfer_and_update() '< manipulate the data
DoCmd.DeleteObject acTable, &quot;Claims 1&quot; 'delete
'set the password back.
Set dbsPassword = OpenDatabase(Data_Pathname, True, False, &quot;; pwd=currentpassword&quot
dbsPassword.NewPassword &quot;&quot;, &quot;currentpassword&quot;
dbsPassword.Close

End Sub

 

[Poppekop]

cool, that is a nice work around. Now all I have to do is make sure we have the same password on all of the databases, but that won't be a problem