News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Wednesday, April 12, 2006

How to export objects to secured database

Unfortunately, if you try to use DoCmd.CopyObject or DoCmd.TransferDatabase with secured database (secured with database password) – it will fail. The trick – is to open this secured database before export. Here a sample code:

Sub ExportToSecuredDB()
Dim strDestinationMDB As String
Dim dbsData As DAO.Database

strDestinationMDB = "C:\Passworded.mdb"

Set dbsData = DBEngine.OpenDatabase(strDestinationMDB, _
False, False, ";pwd=MyPassword")

'Export form
DoCmd.CopyObject strDestinationMDB, "Form1", acForm, "Form1"

'Export table
DoCmd.TransferDatabase acExport, _
"Microsoft Access", _
strDestinationMDB, _
acTable, _
"Table1", _
'Close secured database
Set dbsData = Nothing

End Sub

And here about the same, but for Excel:
How to import password-protected Excel spreadsheet


Anonymous Anonymous said...

what if both of the db's are password protected

10:48 PM  
Blogger Alex Dybenko said...

Hi, same code should work also

12:52 AM  
Blogger Unknown said...

when I try to do this I get 2501 transferdatabase action was cancelled???

3:24 AM  
Anonymous Anonymous said...

I had the same problem where transferdatabase did not work for the "CurrentProject" (forms, reports etc.) but worked for "CurrentData" (tables, queries etc.)

If this happens between two MS Access databases, try the same export/import from the MS Access application (File->Get External Data->Import), it might give you some insight as to why the code is not working.

In my case, the source database was protected with a password and once I removed it (in the VBA window, Tools -> (project) Properties -> Protection tab) the transferdatabse code worked for forms, reports etc.

12:22 AM  
Anonymous Anonymous said...

Hi Alex
Thank you for your help.
I want to insert some records in to a table in a secured database, and before to do that, i want to delete and empty the table first.
best regards:
jack martin

9:57 AM  
Blogger Alex Dybenko said...

open secured DB as above and then run:

dbsData.Execute "Delete * From MyTable Where ID=xxx"

1:05 PM  
Anonymous Anonymous said...

Thanks, that's great! I tried it and it worked perfectly!

4:05 PM  

Post a Comment

<< Home