Function loop problems

[ThunderGeek]

Greetings,
I am trying to create a loop that fills a table ([orders detailkpi]) from a text file(C:\shopsite\ORDERS\1OrderDetails.txt).
The loop section isn’t working very well. I keep getting a runtime error 3421. I probably have wrote something incorrectly and can not see the forest for the trees. Any help would be most appreciated. TG

Function FillOrderInfoTA()
Set Connection = Application.CurrentProject.Connection
Set db = CurrentDb
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
Set rs = db.OpenRecordset("Order DetailsKPI")
Dim reccount As Long
Dim x As Integer

Open "C:\shopsite\ORDERS\1OrderDetails.txt" For Input As #1

Do While Not EOF(1)
'read in the first line up to the crlf
Line Input #1, record
reccount = reccount + 1
If reccount = 1 Then GoTo SkipHeader

'use split function and tab as delim.
ParsedRecord = Split(record, Chr(9))

'--- set the fields inside the loop -
rs.AddNew
rs![WebID2] = ParsedRecord(0)
rs![Order ID] = ParsedRecord(1)
rs![Product ID] = ParsedRecord(3)
rs![SALE PRICE] = ParsedRecord(4)
rs![Qty] = ParsedRecord(5)
rs.Update

'5 is the ubound for one order
If UBound(ParsedRecord) > 5 Then

'add another record for each additional order detail provided
For x = 6 To UBound(ParsedRecord) Step 4
rs.AddNew
rs![WebID2] = ParsedRecord(0)
rs![Order ID] = ParsedRecord(x + 1)
rs![Product ID] = ParsedRecord(x + 2)
rs![SALE PRICE] = ParsedRecord(x + 3)
rs![Qty] = ParsedRecord(x + 4)
rs.Update
Next x

End If
SkipHeader:
Loop
Close #1
rs.Close
Set rs = Nothing
End Function

 

[PHV]

Set db = CurrentDb
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
Set rs = db.OpenRecordset("Order DetailsKPI")
You are mixing DAO and ADODB ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ThunderGeek]

Greetings
Thanks for the fast response but its still poppping an error. It locks up at this point:
rs![Order ID] = ParsedRecord(x + 1)
Thanks again
for the respose TG

 

[ThunderGeek]

PHV
I just looked at your post again and realized that you were pointing out where my mistake was, not actually posting how to correct it. I had just copied and pasted the lines over my original lines. Sorry for not seeing the obvious. How do I change that portion of the code to un-mix the DAO and ADODB? I’m not to knowledgeable about the coding.
Also I'm using access 2003 but the database is in 2000 format.
Thanks again TG

 

[vbc22]

I just looked at your code---that's not going to be your only problem, I'm afraid. You should compile the database to let Access tell you how the code is: from the menu bar, Debug|Compile Access9db

You should have the variables and objects properly dimensioned and data typed.

I don't think this line will work; there has to be an object to reference against the contents of your textfile.

Open "C:\shopsite\ORDERS\1OrderDetails.txt" For Input As #1

I was in search of a similar solution that required importing data from a textfile into a table. I found that this thread helped:

http://www.tek-tips.com/viewthread.cfm?qid=1070834

Regards.