Results 1 to 3 of 3
  1. #1
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81

    Question Importing large file freezes up DB

    Hi All,
    I'm running into an issue with one of my file imports. The particular file imported is an .xlsx file with upwards of 57,373 KB.
    The file is located on a network drive - as the database being used to import it (though different locations).



    I'm using the DoCmd.TransferSpreadsheet method to import the data into a specific table in the DB (table is in a backend DB - which is linked to the Front End DB where the Import Button is located). But due to the size of this particular file, it's seriously SLOW to the point where it's freezing up the database and really bloating the heck out of the backend. Is there a more logical way to do this rather than what I'm currently using??

    Code:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strImportTable, strFileName, True

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I have used Transferspreadsheet for large files before with no freeze.
    Try importing to your front end temp file, then run an append query to add it to the BE.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    For large Excel or CSV files, I suggest you instead link the file so its a linked table. Then import from that doing any additional processing as necessary.
    Doing so gives you more control over the process
    It will still take a long time if you are appending millions of records
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. MS-Access increasingly large file size
    By Herbie in forum Access
    Replies: 4
    Last Post: 01-14-2014, 12:08 PM
  2. Importing large excel file into multiple access files
    By Ghost in forum Import/Export Data
    Replies: 10
    Last Post: 11-05-2013, 11:19 AM
  3. Replies: 6
    Last Post: 12-03-2012, 08:08 AM
  4. Replies: 7
    Last Post: 10-31-2012, 04:58 PM
  5. Importing large txt file into Access 2010
    By Jimbo in forum Import/Export Data
    Replies: 6
    Last Post: 06-30-2011, 08:26 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums