Working with protected Databases Access through Excel

[Dennis]

I have 2 protected databases in Access, and I'm manipulating the information
through Codigos VBA from Excel. But my problem resides when I use the
instruction SELECT INTO... IN '[Target Database]' because the database target
is protected even, and I don't know how to include the password.

I made the following:

Dim cnnX As New ADODB.Connection
Set cnnX = New Connection
dbName = ("C:\Data\DataBase1.mdb")
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Mode = adModeWrite
.Properties("Jet OLEDBatabase Password") = "abc"
.Open dbName
End With

'Create the recordset
Dim rs As ADODB.Recordset
Set rs = New Recordset

'Determines what records to show
Dim strSQL As String
strSQL = "SELECT myTable.* INTO myNewTable IN 'C:\Data\DataBase2.mdb'
FROM myTable "

'Retreive the records
rs.CursorLocation = adUseClient
rs.Open strSQL, cnn

'close connection
cnnX.Close
Set cnnX = Nothing
Set rs = Nothing


Please, help me soon!

 

[George Nicholson]

AFAIK, .Properties("Jet OLEDBatabase Password") = "abc" is the correct
usage for a simple database password (it's what I use).

BUT!

Is the fact that you Dim & Set an object called cnnX and then have a "With
cnn" statement a typo or the problem? (They are different objects)

Do you have "Option Explicit" at the top of your module? If not, it would
call typos like that to your attention when you try to compile or run.

HTH,

 

[Dennis]

Yes George, there is a error in declaration of variables, but my trouble is
the way to use SQL instruction (SELECT INTO... IN...), when the database
external target is protected.

for example:

strSQL = "SELECT myTable.* INTO myNewTable IN 'C:\Data\DataBase2.mdb'
FROM myTable "

AFAIK, .Properties("Jet OLEDBatabase Password") = "abc" is the correct
usage for a simple database password (it's what I use).

BUT!

Is the fact that you Dim & Set an object called cnnX and then have a "With
cnn" statement a typo or the problem? (They are different objects)

Do you have "Option Explicit" at the top of your module? If not, it would
call typos like that to your attention when you try to compile or run.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I have 2 protected databases in Access, and I'm manipulating the
information
through Codigos VBA from Excel. But my problem resides when I use the
instruction SELECT INTO... IN '[Target Database]' because the database
target
is protected even, and I don't know how to include the password.

I made the following:

Dim cnnX As New ADODB.Connection
Set cnnX = New Connection
dbName = ("C:\Data\DataBase1.mdb")
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Mode = adModeWrite
.Properties("Jet OLEDBatabase Password") = "abc"
.Open dbName
End With

'Create the recordset
Dim rs As ADODB.Recordset
Set rs = New Recordset

'Determines what records to show
Dim strSQL As String
strSQL = "SELECT myTable.* INTO myNewTable IN 'C:\Data\DataBase2.mdb'
FROM myTable "

'Retreive the records
rs.CursorLocation = adUseClient
rs.Open strSQL, cnn

'close connection
cnnX.Close
Set cnnX = Nothing
Set rs = Nothing


Please, help me soon!