TISS
asked on
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.
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.
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.
berg
berg
ASKER
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?
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.
ASKER
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.
CLS
INPUT "enter file name , example 0722.txt"; file$
let sFile$ = file$
OPEN sFile$ FOR INPUT AS #1
OPEN "New1.txt" FOR OUTPUT AS #2
OPEN "New2.txt" FOR OUTPUT AS #3
WHILE NOT (EOF(1))
LINE INPUT #1, x$
IF MID$(x$, 10, 1) = "9" THEN
PRINT #2, x$
ELSE
PRINT #3, x$
END IF
WEND
CLOSE #1, #2, #3
HTH
berg
CLS
INPUT "enter file name , example 0722.txt"; file$
let sFile$ = file$
OPEN sFile$ FOR INPUT AS #1
OPEN "New1.txt" FOR OUTPUT AS #2
OPEN "New2.txt" FOR OUTPUT AS #3
WHILE NOT (EOF(1))
LINE INPUT #1, x$
IF MID$(x$, 10, 1) = "9" THEN
PRINT #2, x$
ELSE
PRINT #3, x$
END IF
WEND
CLOSE #1, #2, #3
HTH
berg
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.AddNew
rs!Name = Mid$(Line, 1, 10)
rs!Age = Mid$(Line, 11,3)
rs.Update
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.
Access Code:
Dim db As Database
Dim rs As Recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("TABLE")
Line Input #1, Line
rs.AddNew
rs!Name = Mid$(Line, 1, 10)
rs!Age = Mid$(Line, 11,3)
rs.Update
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.
ASKER
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!
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!
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?