INSERT INTO excel interface with two password protected databases

[jrdnoland]

Okay, I know I've seen how that is done before but I can't find it. I did do this test and it worked fine for me (as an example).

Dim strSQL As String
Dim db As DAO.Database
Dim strCurDb As String
 
strCurDb = CurrentProject.FullName
 
Set db = OpenDatabase("C:\Temp\PWDProtectedDb.mdb", False, False, "MS Access; pwd=1234")
 
strSQL = "SELECT Employees2.LastName, Employees2.FirstName INTO NewTable " & _
                    "FROM " & strCurDb & ".Employees2;"

db.Execute strSQL, dbFailOnError
db.Close
 
Set db = Nothing

Thanks Bob; I'm trying to follow what you're doing.
I see the OpenDatabase is supplying the password to the receiving database. But I'm not getting where the second database is getting it's password supplied?

Sorry, I've been struggling with this on and off for days.

I did get jal's code to work but when I tried to adapt it to my more complex sql string, it didn't work.

 

[boblarson]

I was assuming you were IN the second database, but does that mean you are trying to do this between two databases from a completely different THIRD one?

 

[jrdnoland]

I was assuming you were IN the second database, but does that mean you are trying to do this between two databases from a completely different THIRD one?

Bob - I'm doing this from an excel interface using ado and sql, so yes I'm outside both databases.

 

[boblarson]

Bob - I'm doing this from an excel interface using ado and sql, so yes I'm outside both databases.

Then it would be:

Dim strSQL As String
Dim db As DAO.Database
Dim db2 As DAO.Database
Dim strCurDb As String
 

 
Set db = OpenDatabase("C:\Temp\PWDProtectedDb.mdb", False, False, "MS Access; pwd=1234")
 
Set db2 = OpenDatabase("C:\Temp\MySecondPWDProtectedDb.mdb", False, False, "MS Access; pwd=9876")

strCurDb = db2.Name 

strSQL = "SELECT Employees2.LastName, Employees2.FirstName INTO NewTable " & _
                    "FROM " & strCurDb & ".Employees2;"

db.Execute strSQL, dbFailOnError
db.Close
db2.Close
 
Set db = Nothing
Set db2 = Nothing

 

[jrdnoland]

Then it would be:

Dim strSQL As String
Dim db As DAO.Database
Dim db2 As DAO.Database
Dim strCurDb As String
 

 
Set db = OpenDatabase("C:\Temp\PWDProtectedDb.mdb", False, False, "MS Access; pwd=1234")
 
Set db2 = OpenDatabase("C:\Temp\MySecondPWDProtectedDb.mdb", False, False, "MS Access; pwd=9876")

strCurDb = db2.Name 

strSQL = "SELECT Employees2.LastName, Employees2.FirstName INTO NewTable " & _
                    "FROM " & strCurDb & ".Employees2;"

db.Execute strSQL, dbFailOnError
db.Close
db2.Close
 
Set db = Nothing
Set db2 = Nothing

Hi Bob - I did try the two connection string approach, but had the same problems. I'll give your approach a try.

I'm guessing the the Select syntax would be something like:

SELECT Table1.Field1, Table1,Field2 INTO Table2 FROM Table1;

 

[boblarson]

Hi Bob - I did try the two connection string approach, but had the same problems. I'll give your approach a try.

I'm guessing the the Select syntax would be something like:

SELECT Table1.Field1, Table1,Field2 INTO Table2 FROM Table1;

And you would be wrong. You have to look at my code where I concatenate in the name of the database along with the table name:

"FROM " & strCurDb & ".Employees2;"

Where we set strCurDb = to the path and name of the database file we are getting the data FROM.

strCurDb = db2.Name

 

[jrdnoland]

And you would be wrong. You have to look at my code where I concatenate in the name of the database along with the table name:

"FROM " & strCurDb & ".Employees2;"

Where we set strCurDb = to the path and name of the database file we are getting the data FROM.

strCurDb = db2.Name

Sorry to be so dense, but this is all starting to run together. I need to be sure I understand this enough to attempt it.

SELECT TableName.FieldName1, TableName.FieldName2 (this is in DB2)

INTO SomeTableName (this is in DB1)

FROM DB2 TableName (the same table as in the SELECT)

 

[boblarson]

Actually I have it turned around a bit because of the change in code. Db2 is actually where the data is coming FROM and db is where it is GOING.

So, if we rename them, then it might be more logical to you.

Dim strSQL As String
Dim db As DAO.Database
Dim db2 As DAO.Database
Dim strCurDb As String
 
 
 
Set db = OpenDatabase("C:\Temp\PWDProtectedDb.mdb", False, False, "MS Access; pwd=1234") ' [B][COLOR=red]Data Coming FROM[/COLOR][/B]
 
Set db2 = OpenDatabase("C:\Temp\MySecondPWDProtectedDb.mdb", False, False, "MS Access; pwd=9876") ' [B][COLOR=red]Data GOING INTO[/COLOR][/B]
 
strCurDb = db.Name ' [COLOR=red][B]provides name and path of the database we are getting data FROM[/B][/COLOR]
 
strSQL = "SELECT Employees2.LastName, Employees2.FirstName INTO NewTable " & _
                    "FROM " & [COLOR=red][B]strCurDb[/B][/COLOR] & "[SIZE=4][COLOR=red].[/COLOR][/SIZE]Employees2;"
 
[B][COLOR=red]db2[/COLOR][/B].Execute strSQL, dbFailOnError ' [B][COLOR=red]We execute from the one GETTING the data[/COLOR][/B]
db.Close
db2.Close
 
Set db = Nothing
Set db2 = Nothing

Hope that helps.

 

[jrdnoland]

Actually I have it turned around a bit because of the change in code. Db2 is actually where the data is coming FROM and db is where it is GOING.

So, if we rename them, then it might be more logical to you.

Dim strSQL As String
Dim db As DAO.Database
Dim db2 As DAO.Database
Dim strCurDb As String
 
 
 
Set db = OpenDatabase("C:\Temp\PWDProtectedDb.mdb", False, False, "MS Access; pwd=1234") ' [B][COLOR=red]Data Coming FROM[/COLOR][/B]
 
Set db2 = OpenDatabase("C:\Temp\MySecondPWDProtectedDb.mdb", False, False, "MS Access; pwd=9876") ' [B][COLOR=red]Data GOING INTO[/COLOR][/B]
 
strCurDb = db.Name ' [COLOR=red][B]provides name and path of the database we are getting data FROM[/B][/COLOR]
 
strSQL = "SELECT Employees2.LastName, Employees2.FirstName INTO NewTable " & _
                    "FROM " & [COLOR=red][B]strCurDb[/B][/COLOR] & "[SIZE=4][COLOR=red].[/COLOR][/SIZE]Employees2;"
 
[B][COLOR=red]db2[/COLOR][/B].Execute strSQL, dbFailOnError ' [B][COLOR=red]We execute from the one GETTING the data[/COLOR][/B]
db.Close
db2.Close
 
Set db = Nothing
Set db2 = Nothing

Hope that helps.

I understand what you're doing and I tried it but it doesn't work for my application. I'm using an access 07 database; I get the error message that the database format is unrecognized.

 

[jal]

I understand what you're doing and I tried it but it doesn't work for my application. I'm using an access 07 database; I get the error message that the database format is unrecognized.

You probably are using DAO 3.6. There is a new version, you will have to add a reference to it (probably at Tools > References in Excel). I'll see if I can get you more info on this.

Also, I think I'll try to produce an .accdb version of my ADO code for you to see. I don't have Access 2007, but I just remembered you can create ACCDB databases using pure code.

 

[jal]

Ok, for the new DAO compatible with accdb, add a ref to Microsoft Office 12.0 Access DBengine Object library. The syntax is mostly the same as I seem to recall.

 

[jal]

By the way, the reason you dont have to add a ref in Access 2007 as that it presumes the new DAO to be the default DAO.

 

[jal]

I can't run an accdb experiment because I don't know how to create a password using code.

 

[jrdnoland]

You probably are using DAO 3.6. There is a new version, you will have to add a reference to it (probably at Tools > References in Excel). I'll see if I can get you more info on this.

Also, I think I'll try to produce an .accdb version of my ADO code for you to see. I don't have Access 2007, but I just remembered you can create ACCDB databases using pure code.

jal - I was referring to Bob's example. I added a ref to DAO 3.6 (I think) that is the highest version I have using office 07 and it wouldn't recognize the format.

I was able to get your version working but was unable to apply it using my additional parameters.

This worked:

Dim cn As New ADODB.Connection

cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=roscoe;Data Source=N:\DB TEST\DB ONE\DB ONE.accdb"
cn.Open

Dim cmd As New ADODB.Command
cmd.ActiveConnection = cn


cmd.CommandText = "INSERT INTO [AllNames] SELECT * FROM  [;Database=N:\DB TEST\DB TWO\DB TWO.accdb;pwd =roscoe].[AllNames]"

cmd.Execute
cn.Close
MsgBox "Success"

End Sub

When I tried to add some of the fields I need I couldn't get it to work.

'***

INSERT INTO   tblGeneral_InformationSecondary(P_Gen_Numb_Revisions,P_Gen_Stock_Code) IN   'D:\RMS Test DB\Raw Material Specification Primary Database.accdb' SELECT tblGeneral_Information.Gen_Numb_Revisions,tblGeneral_Information.Gen_Stock_Code   FROM tblGeneral_Information IN '[;Database=D:\RMS Test DB\Raw Material   Specification Secondary Database.accdb;pwd=jeff]' WHERE   (((tblGeneral_Information.Gen_Stock_Code)='0807-035' AND   (tblGeneral_Information.Gen_Numb_Revisions)=3 AND   (tblGeneral_Information.)=0));

'***

I also changed the password on the secondary DB to jeff.

 

[jal]

Yes, I was well aware you were referring to Bob's version. Again, you cannot use DAO 3.6 for accdb databases, as I said. Instead, use the reference I just gave you.

As for ADO (my version), I'll take a quick look at your code hoping to spot syntax errors.

 

[jal]

Ok, I see at problem in your code. You wrote:

SELECT tblGeneral_Information.Gen_Numb_Revisions,tblGeneral_Information.Gen_Stock_Code FROM tblGeneral_Information IN [COLOR=red]'[[/COLOR];Database=D:\RMS Test DB\Raw Material Specification Secondary Database.accdb;pwd=jeff[COLOR=red]][/COLOR][COLOR=red]' [/COLOR]WHERE (((tblGeneral_Information.Gen_Stock_Code)='0807-035' AND (tblGeneral_Information.Gen_Numb_Revisions)=3 AND (tblGeneral_Information.)=0));

I don't recall putting apostrophes, in my version, around the square brackets.

 

[jal]

A second problem in your code:

'***INSERT INTO tblGeneral_InformationSecondary(P_Gen_Numb_Revisions,P_Gen_Stock_Code) IN 'D:\RMS Test DB\Raw Material Specification Primary Database.accdb'



As I said before, you don't need to put two filenames in the SQL. Only put the filename of the DB that is NOT already specified in the connection string. Your code is redundant, and although it might work, it might also lead to more syntax errors. Again;
(1) Name one of the DBs in the connection string.
(2) With the other DB, name it (with its password) in square brackets, using the example I gave you.

Bob and I trying to help you, but you're not always adhering strictly to our examples and instructions. Pay attention, please.

 

[jal]

Nevermind the alias for now, I can't seem to get the alias to work. I'll keep trying. (For now, I deleted my comment about possibly using an alias).

 

[jrdnoland]

A second problem in your code:

'***INSERT INTO tblGeneral_InformationSecondary(P_Gen_Numb_Revisions,P_Gen_Stock_Code) IN 'D:\RMS Test DB\Raw Material Specification Primary Database.accdb'



As I said before, you don't need to put two filenames in the SQL. Only put the filename of the DB that is NOT already specified in the connection string. Your code is redundant, and although it might work, it might also lead to more syntax errors. Again;
(1) Name one of the DBs in the connection string.
(2) With the other DB, name it (with its password) in square brackets, using the example I gave you.

Bob and I trying to help you, but you're not always adhering strictly to our examples and instructions. Pay attention, please.

I appreciate your help and am trying to pay attention. I'm trying to adapt what you guys are giving me to make it work with the initial code I provided. Your examples are not identical to what I'm trying to do and my experience with this is not as advanced as yours.

I don't want to take up any more of your time, I will try to hire a professional to help me fix this, thanks for all your efforts!

 

[jal]

A third problem in your code:

FROM tblGeneral_Information IN [;Database=D:\RMS Test DB\Raw Material Specification Secondary Database.accdb;pwd=jeff]

That's not the code I gave you in the password example (although I may have used it in the non-password version). The password version was more like this:

FROM [;Database=D:\RMS Test DB\Raw Material Specification Secondary Database.accdb;pwd=jeff].[tblGeneral_Information]