How to open two tables in single connection [one in currentdb, other in my new connection]

[pedie]

[/FONT]
[FONT=Courier New]Dim conn As ADODB.Connection
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\some folder\somefile.accdb;Persist Security Info=False;"[/FONT]
[FONT=Courier New]

Hi, using the above connection string, how can i open two tables and transfer data from on table to another

dim rst1 as dao.recordset
dim rst2 as adodb.recordset
etc....


Thanks in advance.

 

[xenou]

just like that: create two recordsets, then do what you need to do with them.

 

[boblarson]

Why are you mixing DAO and ADO? If the data is in Access, just use DAO.

 

[dk]

Depending on exactly what and how you're transferring data it would normally be quicker to use SQL statements rather than looping through a recordset. For example:

Dim conn As ADODB.Connection
Dim sSQL As String

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\some folder\somefile.accdb;Persist Security Info=False;"

sSQL = "INSERT INTO TABLE2 (field1, field2, field3) SELECT Field1, Field2, Field3 FROM TABLE1"

conn.Execute sSQL

DK

 

[Norie]

Pedie

How about using DoCmd.TransferDatabase?

Dim CurDb As DAO.Database
Dim strSQL As String
 
    Set CurDb = CurrentDb
    
    DoCmd.TransferDatabase acLink, "Microsoft Access", CurrentProject.Path & "/" & "Database1.accdb", acTable, "Table1", "TempTable", False
    
    strSQL = "INSERT INTO Table2 SELECT * FROM TempTable"

    CurDb.Execute strSQL

    DoCmd.DeleteObject acTable, "TempTable"

Probably not very efficient and/or liable to cause problems but it worked for me to transfer data from a table in a different database.

 

[pedie]

Depending on exactly what and how you're transferring data it would normally be quicker to use SQL statements rather than looping through a recordset. For example:

Dim conn As ADODB.Connection
Dim sSQL As String
 
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\some folder\somefile.accdb;Persist Security Info=False;"
 
sSQL = "INSERT INTO TABLE2 (field1, field2, field3) SELECT Field1, Field2, Field3 FROM TABLE1"
 
conn.Execute sSQL

DK

Hi everyone.....
Thanks for helping me out here again...
I tried this way and it errors in connection line
"Runtime error 91" Object variable or with block variable not set"
Please advice. My database in not password protected.

Sub TEST1()
Dim conn As ADODB.Connection
Dim sSQL As String
conn.Open "Provider =Microsoft.ACE.OLEDB.12.0; Data Source =C:\myFolder\myAccess2007file.accdb; Persist Security Info =False;"
sSQL = "INSERT INTO BUDGET (Name, Email, ContactNo) SELECT Name, Email, ContactNo FROM BUDGET"
conn.Execute sSQL
End Sub

 

[pedie]

I want to insert into this database table from different database table is that possible [that was my actual question]
I have temp table called "BUDGETxyz" in this database and BUDGET in different db. It says table "BUDGETxyz" NOT found....

Thanks again.

[/FONT]
[FONT=Courier New]Sub zcbvxcvb()
Dim conn As New ADODB.Connection
Dim sSQL As String
Dim strcon As String[/FONT]
[FONT=Courier New]Set conn = New ADODB.Connection
strcon = "Provider =Microsoft.ACE.OLEDB.12.0; Data Source =E:\Access\Database2.accdb; Persist Security Info =False;"
conn.Open strcon
sSQL = "INSERT INTO BUDGETxyz (MNG, BUDGET, QTR) SELECT MNG, BUDGET, QTR FROM BUDGET"
conn.Execute sSQL
End Sub

 

[xenou]

--

 

[xenou]

A connection is to one database, so your "connection" only knows about the tables in the database it is connected to.

You could use the "IN" clause in a select query to push data to a remote database, as I have shown you how to do before:
http://www.mrexcel.com/forum/showthread.php?t=571587

(or you can just link the table - which is what most people do)

ξ

 

[Norie]

Pedie

Why don't you just link the table?

That worked fine for me using the code I posted.