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.

 

[pedie]

Pedie

Why don't you just link the table?
That worked fine for me using the code I posted.

Norie, thanks alot. that worked for me too...
Once i was told here that linking and deleting the table again could take some time if the db is in Network/shared drive but that is a good learning...

And now the SQL thing also works perfect....thanks everyone. Thanks Xen, DK, Bob...everyone for always being there for me.

-------------------------------------------

Now one more question relating to this please.
When the sql command is used to transfer datas so i need to state cursor type, lock type etc?

Thanks again.

 

[xenou]

Now one more question relating to this please.
When the sql command is used to transfer datas so i need to state cursor type, lock type etc?

Thanks again.

Its a little complicated dealing with all these options. If you like, you can read up on it, but in my experience things work fine if you ignore these - just declare the recordset type and that's enough.

 

[pedie]

Its a little complicated dealing with all these options. If you like, you can read up on it, but in my experience things work fine if you ignore these - just declare the recordset type and that's enough.

Xen ,thanks again. when we use sql line like this as we havent use anyline like rst.open etc, i thought maybe we dont have to specify the cursor type, adLockBatchOptimistic...adOpenStatic etc

Thanks again.
I really 'm liking this command

[/FONT]
[FONT=Courier New]sSQL = "INSERT INTO BUDGET ( MNG, BUDGET, QTR, YEARX ) IN '" & mydbname & "'" & vbNewLine & _
"SELECT BUDGET.MNG, BUDGET.BUDGET, BUDGET.QTR, BUDGET.YEARX" & vbNewLine & _
"FROM BUDGET;"[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]conn.Execute sSQL[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]