Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Location
    Manchester, England
    Posts
    23

    Unanswered: ADO Table Import

    Hi,

    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:

    Sub ImportTable()
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cnn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;" & _
    "DATA SOURCE=H:\SPM\PHC\PHCTables.mdb"
    sQRY = "SELECT * FROM tblEmp"
    rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly

    'code to create new local table from rs???

    cnn.Close
    End Sub

    Could anyone tell me where I go from here? Any help would be much appreciated.

    Regards,

    JBee.

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    If your source is another Microsoft Access Database, why not simply use the TransferDatabase option?
    Code:
    DoCmd.TransferDatabase acImport, "Microsoft Access", _
        "H:\SPM\PHC\PHCTables.mdb", acTable, "tblEmp", _
        "tblEmp"

  3. #3
    Join Date
    Aug 2004
    Location
    Manchester, England
    Posts
    23
    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?

    Many Thanks,

    JB

  4. #4
    Join Date
    Feb 2004
    Posts
    137
    For a make-table query from an external source into a new local table, you can try:
    Code:
    SELECT tblEmp.* INTO tblEmp
    FROM tblEmp IN '' [MS Access;DATABASE=H:\SPM\PHC\PHCTables.mdb;];

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •