'TransferDatabase' macro to import table from a p'word protected db

[IanThomas]

Hi All,

I'm simply trying to import a table from a password protected database into another database. I've used the 'TransferDatabase' macro command, which runs and prompts the user to enter the password. I type in the password, but nothing happens! No table has been imported & it's driving me crazy

If I put an incorrect password, Access tells me that it's invalid, and so I know that it's getting through to the correct database.

When I temporarily removed the password from the 'external' database, it imported without problems.

I've tried manually using 'get external data' and it works without problems. However, I want to have a button on a form that the user can press, and (after they've entered the password) it imports automatically.

Does anybody have any suggestions? I thought it would be so simple (especially as the 'microsoft help' states that you just need to type the password in when prompted!), but I've been searching the web for an answer all morning and can't find anybody else with the same problem.

many many thanks in advance

Ian

 

[PaulBricker]

I would assume that you have a table in the Destination database that you are importing the records into. If you do, then you might try the Transfer Database method in VBA. You could put it in the click event like your macro and the syntax would be something like

DoCmd.TransferDatabase acImport,, "PathToSourceDatabase", , "SourceTable", "DestinationTable"

This will prompt for the password and do the transfer.

Paul

 

[PaulBricker]

Sorry, my reply is not complete.
Apparently the TransferDatabase Method (in Access 2007 anyway) appended a new table to the tables collection and doesn't just import data into an existing table. My mistake for not looking closer at the results of the method.

Paul

 

[IanThomas]

Thanks for your reply Paul.
I tried the VBA code:

DoCmd.TransferDatabase acImport, "Microsoft Access", "<path & name of database>", acTable, "<source table>", "<destination table>", <structure only?> False

It asks for the password, but then still doesn't work unfortunately. It's definitely 'looking' in the correct place, as, if I remove the password from the source database, it imports the table & the data without any trouble.

I think I'll have to look at other ways. Maybe if I can get the source database to export the table, rather than trying to import it, or even link the tables (although I don't want to allow the users of the database I'm creating to be able to change / update data in the source table.)

Many thanks

Ian