Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with protected Databases Access through Excel
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with protected Databases Access through Excel
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" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UDF is not working on a protected sheet in Excel 2003 | Excel Discussion (Misc queries) | |||
Import Access Data from 3 databases into 1 worksheet *ERROR* | Excel Discussion (Misc queries) | |||
how to access databases from excel using ms query and odbc? | Excel Discussion (Misc queries) | |||
Security for Viewing MS Access Databases From Excel Datasource | Excel Discussion (Misc queries) | |||
Open PW-Protected MS Access db using Excel VBA | Excel Programming |