Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Speed up linked tables in Access

Posted on 2009-07-09
4
Medium Priority
?
792 Views
Last Modified: 2012-05-07
I have 7 linked tables that take data from CSV files. The trouble is each file could potentially contain 100,000 records (or maybe more) and it takes Access a few minutes per file to append the data to a table. With 7 tables this can take 15-20 minutes. Normally this wouldnt be a problem, however the appends need to be done once a week and I can see this potentially being a major issue.

Does anyone have any suggestions on how to increase the performance when taking data from a linked table connected to a CSV file and appending it to a regular table in Access? Is there a better way to import CSV files??

Your help would be much appreciated.

Many Thanks,
James
0
Comment
Question by:nikez2k4
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 24812176
Your going to want to read in the CSV using code into a table.  Then work with it.  Right now, to work with it as an linked table, that's more or less what Access is doing, but there is a lot of overhead associated with that.
There are two approaches to reading a file in code:
1. Import into a table with TransferText, then read the table and parse the records.
2. Open the file directly with the open statement, then use Input # to read the file.

 I would suggest #2, as TransferText has some length limits.  The sample snippet below reads a file, parses each record into a couple of fields, then saves the record.  What you would need to do would be very similar.

JimD.

          ' Import the file
280       pb.SetMessage "Importing Flat File"
 
          ' Get import file into a table.
          ' Note: Was originally a Text transfer.  Had to rewrite because
          ' the record length got too long.
290       CurrentDb().Execute "Delete * From tbl_tmpEDIImportPO", dbFailOnError
300       OpenADORecordset rsImport, "select * from tbl_tmpEDIImportPO WHERE 1 = 0"
310       intFileNum = FreeFile
320       Open strFileName For Input As #intFileNum
330       With rsImport
340           Do While Not EOF(intFileNum)
350               Line Input #intFileNum, strLine
360               .AddNew
370               ![Tag] = left(strLine, 3)
380               ![EDITradingPartner] = Trim(Mid$(strLine, 4, 20))
390               ![PONumber] = Trim(Mid$(strLine, 24, 22))
400               ![InputLine] = Mid$(strLine, 46)
410               .Update
420           Loop
430       End With
440       rsImport.Close
450       Close #intFileNum
 
Open in New WindowSelect All

Open in new window

0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 24812475
If you use the tables directly in your append queries, it will be slow.

You could try to create one query per table, a simple "select all fields" query. Make the table read-only by marking the query as Snapshot under properties. This will force the data to be cached by Access.

Now, use these queries as sources in your update queries.

/gustav
0
 
LVL 1

Author Comment

by:nikez2k4
ID: 24821650
Thanks for your help guys. I am relatively knew to VBA so I wasn't aware of these commands. Just out of curiosity I tried the TransferText function and was blown away with the speed of it. Creating a linked table and using an append query to import the data took around 5 minutes to do 150,000 records, but the TransferText function did it in 12 seconds!!!

Jim, I know you mentioned that TransferText has length limits, could you explain this in more detail? Is it a length limit on the actual file, per row, per field or something else?

Is there any advantage using something similar to the code you posted rather than the TrasnferText function?

Thanks again for your input,
James
0
 
LVL 58
ID: 24822482
James,
  The most fundamental problem is that because it is working directly with a table, you have table restrictions in place, ie. that you can't work with more then 255 fields in a import.  Your also forced to deal with a single table.  If your CSV file needs to be parsed into multiple tables, you need code to do that.  Last, it's difficult to control the import process and you have no opportunity to scrub the data (review and modify) if need be.  You can use a schema.ini file to control the import, but I find it simpler just to use code and you have total control over the process that way.
JimD.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question