In TransferDatabase method where is the PWD placed

[Dalain]

Hello, I am currently working on a database (Message Creator), which as far as the user is concern; you can Save and Load your work like a word Document. What really happens is that I use the Common Dialog feature so that the used can select a Path and FileName. I then use the PathAndFileName to create a database in the Directory selected by the User. (Using my own default extension)

SAVE File Code

PathAndFileName = “A:\ Enviro_12Jan03.csx”

DoCmd.Hourglass True
Set dbsNew = wrkDefault.CreateDatabase(FileName, dbLangGeneral)

DoCmd.TransferDatabase acExport, "Microsoft Access", PathAndFileName, acTable, "InputEI_Frn", "InputEI_Frn"
(There are 6 Tables transferred like above.)

DoCmd.Hourglass False
DoCmd.Beep
MessageBox = MsgBox("Operation Complete", vbInformation, "Save File&quot


LOAD File Code
To Load a saved, I first link the Tables to the Database and place an “Lk” in front of the Table Name

(PathAndFileName = “A:\ Enviro_12Jan03.csx”)

DoCmd.TransferDatabase acLink, "Microsoft Access", PathAndFileName , acTable, "InputEI_Frn;", "LkInputEI_Frn"
(There are 6 Tables that get Linked like above.)

If the Link is Successful, I delete the Current information in the Local Tables

DoCmd.RunSQL ("DELETE *FROM InputEI_Frn;&quot

Then I transfer the Info from the External Table to the Local Table

DoCmd.RunSQL ("INSERT INTO InputEI_Frn SELECT LkInputEI_Frn.* FROM LkInputEI_Frn;&quot

Then I delete all the Linked Tables

For Each obj In dbs.AllTables
If Mid(obj.Name, 1, 2) = "Lk”
DoCmd.DeleteObject acTable, obj.Name
End If
Next obj


The problem is the data in the Created Database can be sensitive (Personnel Info) and therefore should only be access through the master database (Message Creator) in order to do this I encrypted it and added a password.

In the Save FileCode I entered

Set dbsNew = wrkDefault.CreateDatabase(FileName, dbLangGeneral & ";pwd=1234", dbEncrypt)

This works great, But when I try to Load the File, big Problem!

If I use the Code
DoCmd.TransferDatabase acExport, "Microsoft Access", PathAndFileName, acTable, "InputEI_Frn", "InputEI_Frn"
The user will be asked for the password for each table being transfered and since the Password is hidden in the code, they will never be able to load the file.

So where/how in the statement, do I put the ";pwd=1234;”

Any Help is appreciated
Thanks
Pierre

 

[BillPower]

Try:

PathAndFileName = ";DATABASE= A:\ Enviro_12Jan03.csx;Uid=;pwd=YourPassword"

 

[Dalain]

Thanks billpower
But, I still get is an error "Can't find the database file ';DATABASE= A:\ Enviro_12Jan03.csx;Uid=;pwd=1234"

I have allready tried these
;DATABASE= A:\ Enviro_12Jan03.csx;pwd=1234
;DATABASE= A:\ Enviro_12Jan03.csx;pwd=1234;
;pwd=1234;DATABASE=A:\ Enviro_12Jan03.csx

Pierre

 

[BillPower]

I've just noticed the .csx extension, it's got to be .mdb or .mdb and also is the DB on your floppy drive

 

[Dalain]

Tried that too, still have the same error.

My main database is an mde file.(or it will be)

When you referance an access file, the extention does not mater, what does is that the file is an accual Access database file. As you can see in my original post, there when there is no password everything works great!

I currently use something similer in another database and the file is Password protected with the Extention changed to .GPD, but here I use OpenDatabase to access the table and it works with a password.

So I tried using OpenDatabase statement on my current problem database


Set dbs = OpenDatabase("A:\Enviro_12Jan03.csx", False, False, ";DATABASE=A:\ Enviro_12Jan03.csx;pwd=1234&quot

Set LkInputEI_Frn = dbs.OpenRecordset("SELECT * FROM InputEI_Frn&quot

At this point I was able to access the info in the recordset.

This code below does not work as access is looking for a Table/Query in the statement not the currently open recordset.

DoCmd.RunSQL ("INSERT INTO InputEI_Frn SELECT LkInputEI_Frn.* FROM " & LkInputEI_Frn & ";&quot

If i convert my code to use a recordset how do I referance it in my SQL statement. Where LkInputEI_Frn is the recordset vice a linked table.

I know that I can step through the Recordset and add the records one by one to the InputEI_Frn table. But I have 6 tables to Update. Also, it would take more time to run the function.

Using the DoCmd.TransferDatabase is easier, except of course for the PWD problem.



Pierre
p.s.
I did try as you suggested with the proper extentions, same error.

 

[BillPower]

I think we've got there in the end:

Dim PathAndFileName As String, strDB As String
PathAndFileName = "A:\Enviro_12Jan03.csx"
Set dbsPassword = OpenDatabase(PathAndFileName, False, False, "; pwd=1234&quot
DoCmd.TransferDatabase acExport, "Microsoft Access", PathAndFileName, acTable, "InputEI_Frn", "InputEI_Frn"
dbsPassword.Close

 

[Dalain]

Sorry, this still asks me for the password in order to create the linked table.

 

[BillPower]

If you want email your db's to me at billpower@cwcom.net
I reproduced DB, tables and password here and works perfectly. Can't really suggest anything else.

 

[Dalain]

Well billpower you were right about placing the OpenDatabase statement first.

The reason it did not work for me at first,was that the TransferDatabase statement had acExport I needed aclink.

Once I noticed that, I started to play around with it again and all of a sudden it worked.

Thanks for your help

pierre