Access Code Versus Dbase Code

I have an old dBase program that I'm trying to rewrite in Access.  The problem is, it seems to run faster in dBase than it does in Access.  This is the code from Dbase versus the Code from Access.  Am I using the most effecient way in Access to do this?

Problem:  Read lines from a text file and insert the data into a table.

Dbase Code:

Append Blank
Replace Name with SubStr(Line->Line, 1,10)
Replace Age with val(SubStr(Line->Line, 11,3)

'This dBase code Adds a blank record.  The Replace line takes a section of the current line we read, and places that section into the appropriate field in the table.

Access Code:

Line Input #1, Line
SQL = "Insert into TABLE (Name, Age) values ('" & Mid$(Line, 1, 10) & "'," & Mid$(Line, 11,3) & ")"
Db.execute SQL

Is this the best way to do this in Access?  Or is there a quicker way to insert data into the table.

Thanks for any help.

If a more efficient way can be provided, I will increase the value of this question.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

can you create an import spec for your table? If you go to File | Get External Data | Import, then find your file and click the Advanced button.

That will let you specify your fixed-width fields; then Save As an import spec. Now, in future, you could run code or a macro to do the import using the spec.

Would that be quicker?
Brewdog has a good point, I have found import specs run from a macro to be quick, of course it always depends on the amount of information you are importing.

TISSAuthor Commented:
I don't think an Import Spec will work, because the text file is HUGE, and is an EDI File.  This means I check the first 7 characters of each line to determine which table I place the data in.  To use the Import Spec, the Text file would have to be the same format for each line would it not?
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Have you tried usig a batch file to spilt the edi into seperate text files, than you would be free to use the import specs. I had to do such a file, and would be more than happy to share it with you.
TISSAuthor Commented:
Sure,  I'd be interested in seeing that Batch file.
I run this Qbasic file from a batch file. It looks at the nineth character in the record, and than puts that record into the appropriate file. You should be able to use something like this.

INPUT "enter file name , example 0722.txt"; file$
let sFile$ = file$
OPEN "New1.txt" FOR OUTPUT AS #2
OPEN "New2.txt" FOR OUTPUT AS #3

IF MID$(x$, 10, 1) = "9" THEN
PRINT #2, x$
PRINT #3, x$


CLOSE #1, #2, #3

Your code would be much faster like so:

Access Code:
  Dim db As Database
  Dim rs As Recordset
  Set db = CurrentDB
  Set rs = db.OpenRecordset("TABLE")

  Line Input #1, Line

  rs!Name = Mid$(Line, 1, 10)
  rs!Age = Mid$(Line, 11,3)

It takes some time for Access to crank up the mechanism to run the SQL, and it has to do that for each record. I'm presuming that the code from Line In... to rs.UpDate would be in a loop of sorts.
TISSAuthor Commented:
Well, it was Brewdog who suggested using Import Specs, Berg1375 who gave me the idea to separate the file to make Import Specs work, and BrianWren's code is also twice as fast as my original code.  I think I'll go with the Import Specs, as they are still faster, but 50 points for all of you.   I'll open 3 separate questions for each of you.  Just post as an answer.  

Thanks a bunch!
glad we could teamwork this one for you. What you might want to do is go to the Community Support area and have EE answer this question for you; then it will be saved in the archives for future users. And they'll give you this amount of points so you can post separate questions for us without losing your shirt.
As a side note, any program written in a DOS language like dBase will always appear to run faster than a windows-based program.

No doubt you will also find that after you complete your Access version, the application will be hundreds or thousands of times bigger than the original dBase program.

Welcome to life in computers!
Just my twopence:

BrianWren's suggestion on using the recordset is not necessarily faster than using the db.execute variant. Of course, since TISS' original version uses a query for each line this will slow down things considerably and for these cases a query is not the best solution.

Adding records to a recordset will never be as fast as using queries or an import spec for that matter. It is better though because it adds the possibility of performing tests on the data before puting it into the table which when done properly can help you avoid those missing records that comes from bad data in the source (and isn't the source always bad?) If you add transactions against the workspace then it can perform nearly as fast as a query, but not quite.

I am answering this question so it can be saved as a PAQ. Since TISS has already awarded points for the experts involved, I have refunded his points for this question.

Community Support @ Experts Exchange

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.