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! |
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! |
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! |
All times are GMT +1. The time now is 05:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com