INSERT INTO excel interface with two password protected databases

[jal]

This also worked for me, note that it uses an alias (although it worked fine without it) AND a WHERE clause, which puts it closer to your version.

Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Jet OLEDB:Database Password=access;Data Source=C:\Documents and Settings\Names1.mdb"
cn.Open
Dim cmd As New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "INSERT INTO [Names] SELECT * FROM [;Database=C:\Documents and Settings\Names2.mdb; pwd =access].[Names] as N Where N.Name = 'John' "
cmd.Execute
cn.Close
MsgBox "Success"

 

[jal]

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!

If you have no prior VBA experience, then hiring a professional might be wise. But if you have a little VBA knowledge, we should be able to get you started. Rewrite the code and, if you like, post it back so we can take another look at it.

 

[jrdnoland]

If you have no prior VBA experience, then hiring a professional might be wise. But if you have a little VBA knowledge, we should be able to get you started. Rewrite the code and, if you like, post it back so we can take another look at it.

I will look over what you have provided and make another attempt. I mostly work on this at night after my day job.

I do have vba experience, but mostly with Excel. Databases are
relevatively new to me and the syntax examples I can find are not always that helpful.

Again, thanks to you and Bob for your help!

 

[boblarson]

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.

You would need a reference to Microsoft Office 12.0 Access Database Engine Library for use with 2007.

Oh, and just an FYI for you AND jal - Using DAO would actually be more efficient than using ADO because DAO is native to Access and therefore doesn't need the extra translation layer than ADO requires. But you might only notice a difference if you had a very large set of data to deal with.

 

[jal]

I will look over what you have provided and make another attempt. I mostly work on this at night after my day job.

Tell me about it. I too have never worked for an IT dept, nor held a programming job.

 

[jrdnoland]

Tell me about it. I too have never worked for an IT dept, nor held a programming job.

Thank you, your suggestions worked. I was able to get the queries to run. I got pulled off of it for a little while and have just now got it fixed.