Exporting tables to a password-protected Database through code

[R2D2]

A project I am working on involves a two-tiered backend database setup. A database called "Finance Backup.mdb" runs a bunch of queries that creates some backup tables, and then exports these tables to "Finance Views.mdb", which my front-end application links to. I have set things up so that Finance Backup runs and exports the tables every night (via a scheduled windows task).

The problem I am having is that I have set a password on "Finance Views.mdb." I got some code off of msdn that has allowed me to still use and link to the tables in Finance Views from my front-end application by using the openDatabase method and passing it the appropriate password. Here's the code for this:

Dim wrk As Workspace
Dim dbProtected As Database

Set wrk = DBEngine.Workspaces(0)
Set dbProtected = wrk.OpenDatabase("C:\Finance Views", False, False, ";PWD=thePassword")

This works wonderfully to allow front-end application access to Finance Views. However, I cannot get Finance Backup to work, because Finance Views requires a password for any table to be exported to it, and even though I've tried the above code, a window requesting the password pops up during the export process of Finance Backup.

So, is there someway to export tables to a password-protected database through code?

 

[Fornatian]

Dumb Question?
Would you have the same problem if you imported the data 'on opening' the Finance Views database rather than exported it from the source?

 

[R2D2]

Thanks for the suggestion. I'm going to try doing an import, because I think that will eliminate the problem.

 

[R2D2]

That doesn't work either

There's a major problem with importing via Finance Views: I need to be able to kick this process off by using a batch file that will open Finance Views and run the autoexec macro. This batch file needs to start Finance Views with the appropriate password. I've looked into Access Startup options, but the /pwd option doesn't seem to be working. Here's my batch file:

@echo off
start /max "c:\program files\microsoft office\office\msaccess.exe" "C:\finance views.mdb" /pwd thePassword
exit

(I've replaced the real password with "thePassword" here).

I've tried many different changes to this syntax to see if it's a syntax error, but I haven't been able to fix it. The only thing I can think of is that maybe this only works when the database is setup with user-level security rather than a single password for the database as a whole.
Anyone have any ideas for making the password switch work?

 

[R2D2]

Problem solved

I got the export to work. I got a solution from http://www.experts-exchange.com/Databases/MS_Access/Q_20402135.html.

Basically, I did it in 3 steps:

1. Change the password of the database I want to export to to "".

2. Export the tables.

3. Set the password back to what it originally was.

I used the following function to do this:

Function SetDBPassword(strDBPath As String, _
                      strOldPwd As String, _
                      strNewPwd As String)
  ' This procedure sets a new password or changes an existing
  ' password.

  Dim dbsDB      As DAO.Database
  Dim strOpenPwd As String

  ' Create connection string by using current password.
  strOpenPwd = ";pwd=" & strOldPwd

  ' Open database for exclusive access by using current password. To get
  ' exclusive access, you must set the Options argument to True.
  Set dbsDB = OpenDatabase(Name:=strDBPath, _
                           Options:=True, _
                           ReadOnly:=False, _
                           Connect:=strOpenPwd)

  ' Set or change password.
  With dbsDB
     .NewPassword strOldPwd, strNewPwd
     .Close
  End With

  Set dbsDB = Nothing
End Function