Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004

    Unanswered: Speed up Data Import

    What is the fastest way to copy data from an external ODBC database to a table in MS Access? I need to copy data from just over 60 different tables the data is to be read only, the majority of tables contain between 1,000 and 2,000 records a few contain less than 30 and 1 contains between 40,000 and 170,000 depending on what time of the year it is. At present I am using the following code per table required with pre-created pass through queries to pull in the data, this takes about 2 minutes to do all 60 tables:
        CurrentDb.Execute "DELETE fLAStudentDay_tbl.* FROM fLAStudentDay_tbl"
        CurrentDb.Execute "INSERT INTO fLAStudentDay_tbl SELECT fLAStudentDay_ptqry.* FROM fLAStudentDay_ptqry"
    I have also just found the following code which pulls the data in to a recordset and then copies it to a table:
        Dim db As DAO.Database
        Dim cdb As Database
        Dim fld As DAO.Field
        Dim fLAStudentDay As DAO.Recordset
        Set db = OpenDatabase("", False, True, "ODBC;Dsn=INTACCESS;UID""=;PWD="";")
        Set cdb = CurrentDb
        Set fLAStudentDay = db.OpenRecordset("SELECT StuDayStuRecId, StuDayDate, StuDayCodes, StuDayAMCode, StuDayPMCode, StuDayHDAv, StuDayHDN, StuDayHDA, StuDayHDP FROM fLAStudentDay WHERE StuDayDate >= #2005-09-01#", dbOpenSnapshot)
        Set fLAStudentDay_tbl = cdb.OpenRecordset("fLAStudentDay_tbl")
        CurrentDb.Execute "DELETE fLAStudentDay_tbl.* FROM fLAStudentDay_tbl"
        Do Until fLAStudentDay.EOF
        For Each fld In fLAStudentDay.Fields
        fLAStudentDay_tbl(fld.Name) = fld.Value
        Next fld
    Is this code likely to have a significant performance boost over the pass through query version. I don't really want to physically try this code for all 60 tables unless there is a possibility that it will be faster.

    Are there any other ways to pull the tables in to MS Access that could be faster?

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    Generally speaking SQL (bulk) queries will run much faster then iterating through a recordset. Depends on your data In the past I've found you may need to do your process as 3 queries for each table (an Insert, an update and then delete redundant data).

    I suppose the 'best' resoltuioon to your prblem is to do a comparison.

    The downside of bulk queries is that your error checking is worse than a vba function. Reporting and user feedback is poor. If you don't have to do much reformatting or data manipulation then bulk queries are almost certainly hte route. If you have a really exotic data manipulation then VBA may be the route.

    good luck

Posting Permissions

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