Copy Table to a Password Protected Database

[TheSearcher]

Hello All,
I'm using the DoCmd.CopyObject method to copy a table from my front end db to my back end. My back end db is password protected and it keeps prompting me for the password. Can I somehow send the password in code so that the user is not prompted?
Thanks in Advance,
TS

 

[CJ_London]

Rather than using copyobject you can use a sql make table query and embed the password in it.

In the query window right click in the top part and select properties. You can put the full path and db name in the destination db and the password goes in the Destination connection str.

To see how the latter is constructed look in your front end MsysObjects in the connect column for one of your linked tables (link to a table temporarily if necessary). It is normally constructed as PWD=mypassword;

Alternatively, again using the query builder create a make table query and when prompted for the destination you have to option of choosing another db. However I have found this doesn't always work.

Once you have made your table in the BE, it won't have a primary key or indexing but again you can use sql to create these. This link can help you with that

http://www.w3schools.com/sql/sql_create_index.asp

 

[TheSearcher]

Thanks CJ - but I got a "Could Not Find Installable ISAM" error message when running the query. Do you know what that's about?

 

[CJ_London]

Not offhand - it may be the connection string is not correct. If you search for 'connection strings' on the web you will find a number of sites which detail the design of the string for all types of data sources.

Did you try using the query builder to make the connection?

I have a function that builds the string for use in queries - here is an example for making a table called tmp in a password protected db from a table called myTable in the current db

SELECT myTable.* INTO [[COLOR=red]C:\...\SysData.accdb[/COLOR];PWD=mypassword].tmp
FROM myTable

If you modify this by replacing myTable with the name of one of your tables and the bit in red with the path and name of your BE, it should work - it may depend on other settings you may have but I doubt there is anything to worry about - your connection string search should explain anything else you may need to do

Then you can look at it in the query builder properties to see how the string is constructed.

Just done that and interestingly the entire string between the square brackets appears in the connection string and isn't split between database and connection string - if you do split it you get an error when you run it. Must be an Access thing

 

[TheSearcher]

Thanks CJ. Your query works.
The reason I wanted to use the CopyObject method was because of what I need to do afterward. Once the new table is created the user will have the option to "activate" it. Essentially, what happens during this process is that I backup the current table, delete it after backing it up, and then rename the "new" table to its regular name. The code I use is:

DoCmd.CopyObject , "tbl_Employee_Master_OLD", acTable, "tbl_Employee_Master"
DoCmd.DeleteObject acTable, "tbl_Employee_Master"
DoCmd.Rename "tbl_Employee_Master", acTable, "tbl_Employee_Master_NEW"
MsgBox "The New Employee Catalog has been activated.", vbInformation, "Success."

I'm just not sure how to accomplish this in a split db where the back end is password protected. Your query will handle the first part but not the rest.
How would you approach this?

Thanks,TS

 

[CJ_London]

Not sure why you need to do this but you can't copy tables in Access SQL.

Suggest you do the following

Use the make table query to create the copy of old
add indexes to old if required
use the SQL

DELETE * 
FROM [C:\...\SysData.accdb;PWD=mypassword].tbl_Employee_Master

to empty the db

Then use an append query in the same style

 

[CJ_London]

instead of 'delete *...

you can use the sql Drop

DROP TABLE [C:\...\SysData.accdb;PWD=mypassword].tbl_Employee_Master

then use your append as originally described but directly to the new table - followed by modifying the table to create the indexing

 

[TheSearcher]

Thanks CJ. I haven't coded in Access for a while and, I must say that, your replies have been very helpful.
Thanks a lot.

 

[CJ_London]

Happy to help!