We help IT Professionals succeed at work.

Adding .txt file to a table

rdevriend
rdevriend asked
on
101 Views
Last Modified: 2012-06-08
In my MS-Access, need to add a lot of data from .txt files into MS-Access tables. Reading the .txt files is done quickly, but appending the data takes ages.

I use following systax :
For i =1 to xxxx
   insert into ...
next

Is itn't possible to accumulate all the data into a kind of internal tables and add in in one INERT INTO statement to the Access table ?

rgds,
rene

Comment
Watch Question

Commented:
To help speed things up, you could use BeginTrans and CommitTrans.  This will give you a bit of a speed increase, though perhaps not as much as you're hoping for.

The BeginTrans tells Access to wait until it gets a CommitTrans before it commits the rows.  The CommitTrans, then, saves all of the queued up records at one time.

Unfortunately, it's time consuming to import large amounts of data into Access - it's just not a very fast platform...

Chris

Commented:
If it's a fixed width or delimited text file, use

DoCmd.TransferText

To import all the data into a table then use querys to move it into other tables.

Author

Commented:
Sorry for late reply.

BeginTrans and CommitTrand does not work. I am getting all kinds of errors.

I like the uses of DoCmd.TransferText. With this, I want to use the scheme.ini file as import specification.
However, when I create such a file during exporting, I cannot find it anyware on my computer. Where does MS-Access store this file.

rgds,
rene

Commented:
According to the Access help file (under Import or Link a delimited fixed-width text file, in the Answer Wizard):

Alternatively, you can use a Schema.ini file in a Visual Basic program to provide even more control over data in the text file, such as specifying special currency formats or handling floating point data types. A schema.ini file is a text file containing entries that override default text driver settings in the Windows Registry. You store a schema.ini in the same folder as the imported or exported text file, and it must always be named schema.ini.

Chris

Author

Commented:
The file is not on my computer, not as schema.ini, not with name I specified myself.

Any idea ?
rene
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
I am no longer working on this project, but anyway, you tried to help me. Here are the points

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.