I'm currently putting a small database together where for performance reasons I wish to import an entire table from a seperate file so I can query it locally. So far I have:
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;" & _
sQRY = "SELECT * FROM tblEmp"
rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
'code to create new local table from rs???
Could anyone tell me where I go from here? Any help would be much appreciated.
Thanks Matthew- good Point! But what if I wanted to do a little more than simply transfer a table across- i.e apply some criteria? I guess my question is can you run a make-table query on data in an external database and the resulting table appear in the current project? Or would I just have to create the new table externally and import it using TransferDatabase?
Going back to my example code though- I would still like to know if it's possible to transfer the contents of my recordset into a local table. Can this be done with one line of SQL or would I have to loop through the recordset and append one row at a time maybe?