Working with protected Databases Access through Excel

D

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 OLEDB:Database 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!
 
G

George Nicholson

AFAIK, .Properties("Jet OLEDB:Database 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,
 
D

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 "


George Nicholson said:
AFAIK, .Properties("Jet OLEDB:Database 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.


Dennis said:
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 OLEDB:Database 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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top