Working with protected Databases Access through Excel

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!

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" <[email protected]> wrote in message
news:[email protected]...
>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!
>

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" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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!
> >
>
>
>