Exporting a table to pass-protected DB

ekc
ekc used Ask the Experts™
on
I am trying to export one table from one database to another. The
source code below works fine when the destination database is not password
protected. But, I can't find the way to do it when it is.
Both databases are Access 2000.
     Could someone help, please?


Sub export_table()

Dim vconnection As New ADODB.Connection
Dim vCommand As ADODB.Command

Set vCommand = New ADODB.Command
vconnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\temp\from.mdb;pwd=my_pass"

Set vCommand.ActiveConnection = vconnection

vCommand.CommandText = "SELECT Table1.* INTO Table1 IN 'db=c:\temp\to.mdb' FROM Table1"

vCommand.Execute


End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The MSDN has your answer online.
I have included the link at the bottom of this comment, but here is the excerpt in particular you will want to use:

Function OpenProtectedDB(strDBPath As String, _
                         strPwd As String)
   Dim cnnDB As ADODB.Connection

   Set cnnDB = New ADODB.Connection

   ' Open database for shared (by default), read/write access, and
   ' specify database password.
   With cnnDB
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Properties("Jet OLEDB:Database Password") = strPwd
      .Mode = adModeReadWrite
      .Open strDBPath
   End With
   ' Code to work with database goes here.
   cnnDB.Close
   Set cnnDB = Nothing
End Function

It's explained in more depth here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovrworkingwithdatabasepasswordsinvbacode.asp
sorry, I just noticed you're doing this with one connection. You may need to unprotect the target database before you can insert into the database in this fashion. You should be able to use this function to set its password to an empty value, and then when you're done, to put a new password in place.
ekc

Author

Commented:
>  You should be able to use this function to set its password to an empty value, and then when you're done, to put a new password in place.

How this can be done?
Could you post the code please?

I think your function uses the password (as well as all the other parameters) to open the db, not to manipulate its parameters?
We will have to do this in a few steps:
1) unprotect the target database
2) insert into the target database
3) reprotect the target database

1) ok, lets unprotect the target database first:
(this function uses dao. I can't find an ADO equivalent, sorry.) Set strNewPwd = "" to unprotect the database. Also, We want to store strOldPwd somewhere for later use in step 3.

 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

2) Enter data into the target database. Your existing code should work fine.

3) reprotect the target database:
You want to use the first function to add a password to the database, similar to when we unprotected it. Your strOldPwd = "", and your strNewPwd = step 1 strOldPwd.

Note that we're mixing ado and dao code, so don't confuse your dao and ado connection strings. If someone has a way to do this without the dao code, I'd like to hear it as well.

if you have more questions, email me at tyler.dueck@mysolutions.biz and I'll respond over lunch.
ekc

Author

Commented:

  Thank you! It's working, although I'd like it better without this password change.

  Good luck!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial