MS Access large text file

[August]

Hello,

Anyone has the experience posting to Access table with over 700,000 to 1,000,000 records? If anyone has some tips for me on what is the fastest way of posting data into access table using Visual Basic 6 is very much appreciated.

Thanks in advance.

August

 

[KenHigg]

Have you had problems with the traditional import or link options in Access?

 

[August]

Hi Ken,

Link is not a problem but posting to access table with this huge text file is a night mare, it took more than 48 hours to post more than 700,000 records, is there a way that I can make this a little bit faster or technique to post this text file into access table faster.

Any suggestions is very much appreciated.

Thanks
August

 

[KenHigg]

Sounds like you do have a fixable situation - 700k records shouldn't be porblem with access. I'm not sure what you mean when you say 'post'? Are you linking to a single external table/text file/etc and appending them into a table in access?

 

[MStCyr]

With this many records you may well exceed the MaxLocksPerFile. You may want to reset this value from the default value of 20,000 ... maybe you should set it to around 200,000.

 

[KenHigg]

As an alternative - I would bring all the original records over the to the local drive to work with as opposed to futzing with them over the network...

 

[August]

MS Access problem

Hi Ken,

Sorry i was off yesterday from work and was not able to respond to your reply.

Yes, I am using external file (a text file) and posted them to access table using SQL command with VB6 codes. My problem is that I know there are some technique that I can use speed up my posting to an access table through SQL command BUT I can't find it. I am working on this to reduce my processing to a couple of hours, you know I am frustrated I can't find any solutions until now.

Thanks again for your suggestions, if ever you may find something please let me know.

Regards
August

 

[August]

Hey, MSTCyr, thanks for your suggestions, Where I can find that MaxLockPersfile? Thanks in advance for your help.

 

[August]

Ken, the text file that I am using within the folder together with test of the file.

 

[boblarson]

Ken, the text file that I am using within the folder together with test of the file.

Does that mean that the text file AND database is on YOUR LOCAL machine, or is it on a network drive? Ken was meaning that, if the file and database is on your local machine, it will move WAY FASTER than if it is located on a network share.

Now, also Ken had made mention of linking to the text file. This might also work faster than your SQL statement. If you link the database to the text file and have an append query created (in the Queries section) that will take those records and append from the "virtual table" you created by linking to the text file, that may also speed things up.

It also may help things if you compact the database PRIOR to importing and then afterwards as well.

--just a few suggestions