Unanswered: From Excel, copy DB table between db files
I need to update a table in my main db (.mdb) file that is password protected. I have the updated table in a stand-alone .mdb source file that is not password. (The source file's table contains new data and some new fields as well.)
I want users to be able to run an update routine from the Excel front-end, which is our standard practice. I have the following code that runs fine if the main db file is not pw protected and I can only find ADO code to export *from* a pw-protected file not *into* a pw-protected file.
Notice I can/do connect to the PW-protected destination file before running CopyObject but that doesn't seem to help. And I'd like to do this with ADO (not DAO).
Can someone show me how to run this code using a destination db file that has a password??? (Thanks in advance.)
Dim acc As Object
Dim acc2 As Object
Dim db As Object
Dim db2 As Object
Dim gMDBName as string
'(1) CONNECT TO UNPROTECTED *SOURCE* DB FILE
Set acc = CreateObject("Access.Application")
acc.visible = False
strNewTableFilePathName = "C:\MyDocs\NewDataTypesTable.mdb"
Set db = acc.DBEngine.OpenDatabase(strNewTableFilePathName, False, False)
Set db = Nothing
'(2) CONNECT TO PW-PROTECTED *DESTINATION* DB FILE
gMDBName = "C:\MyDocs\MainDB.mdb"
Set acc2 = CreateObject("Access.Application")
acc2.visible = False
Set db2 = acc2.DBEngine.OpenDatabase(gMDBName, False, False, ";PWD=123"
Set db2 = Nothing
'(3) EXPORT TABLE FROM UNPROTECTED FILE TO PW-PROTECTED FILE
' (name it: tblDataTypes)
'ISSUE: This code only works using a string for unprotected DB file!!!!
.DoCmd.CopyObject gMDBName, "tblDataTypes", acTable, "tblDataTypes_NEW"