Loop Command

[Osiris]

Hello all,

I am having a small problem with a little program I am writing for work. Basically it is suppose to take the information in one table and write it to another. Here is the code that I have for this..

Public Function Import()

Set cnn = CurrentProject.Connection
Set tbl = New ADODB.Recordset
tbl.Open "Imports", cnn, adOpenDynamic, adLockOptimistic
Set Meterhist = New ADODB.Recordset
Meterhist.Open "dbo_MTRHIST", cnn, adOpenDynamic, adLockOptimistic

intRow = tbl("Key")
'intEnd = (DMax("Key", "Imports"))

'Do Until intRow > intEnd
Do Until tbl.EOF
With tbl
tbl.Find "Key = '" & intRow & "'"
If .EOF Then
MsgBox "There is no information to Import.", vbInformation, ""
Else:
With Meterhist
Meterhist.AddNew
Meterhist("METERNUM") = "TEST"
Meterhist("EQNUM") = tbl("Vehicle")
Meterhist("EQDATE") = Date
Meterhist("EQTIME") = Time()
Meterhist("MTRVALUE") = tbl("ODOMETER")
Meterhist("EVENT") = "Import"
Meterhist.Update
intRow = (intRow + 1)
End With
End If
End With
Loop
'DoCmd.OpenQuery "Update_Meter_tbl", acViewNormal
End Function

Now here is the problem.. this code works perfectly fine when you add a watch to break on when the value of intRow changes if you continue after it breaks each time, it also works fine if you step through the the individual lines.. However if you try to run the code, with no watchs, and your not stepping through it, it will import the first line perfectly, then when it comes to the .Update of Meterhist for the second line it errors with "ODBC -- call failed". I have no idea why it is doing this... If anyone could help me I would be VERY greatful..

Thank you

-Osiris

 

[Travis]

It may be a problem with the "With" statement.

Write the code out longhand (Remove the "With" statements and place the designators back in the code) Then try running the code. If running it without the "With" statements works then add one of them back in and try running it again. If it works after adding one "With" then add the other. Basically drill down.

 

[Osiris]

Thanks for the response

Good idea, unfortunatly it didn't make any difference...

I removed the "With" statements and added the designators back in, ran the code, and it still errors with "ODBC -- call failed" after it imports the first row..

So I stepped through the code, and again it worked exactly how it should.. imported all the rows.

Then I set a watch to break on intRow changing, and again if you continue the code after it breaks it works correctly, importing all the rows..

I am completely baffled by this... any further help from anyone is Greatly appriciated.

-Osiris

 

[Osiris]

Thank you for your help

Ok, let me do a little more explaining.

1. Option Explicit is actually set, I just didn't post the top portion of my code, where it defines certain variables, and contains the Option Explicit and Option Compare Database.

2. I have modified the loop to now check on .EOF (good suggestion, thank you.)

2a. I have added a little bit of error checking to the .Find. It will now generate a Msg box if it does not find the key, and not continue to try and add a record.

2b. I actually have to add both a date and a time field. The table that I am writing to contains both fields as primary keys.

Ok, now about your alternative. I wish that would have worked.. heh it would have made this much simpler... but since 3 of the fields I am writing to are primary keys, and they can contain duplicate values, Access won't let a query append to the table. It comes up with a key violation error..

Unfortunately I cannot change those fields from being primary keys. So it appears as if I'm stuck having to use Code to import my data..

After I make the changes you suggested to my code, it is still coming up with "ODBC -- call failed" after it imports the first record.. If anyone wishes me to repost my changed code I will, but this post is already rather long...

Back to the drawing board with me... as always any help is greatly appriciated.

-Osiris

 

[Travis]

Have you tried adding a "DoEvents" after the .Update line of your code?

 

[Osiris]

Yeah I have, still same result.

It will import one row, then error out with "ODBC -- call failed"

I thank you both for trying to help me out with this , even though I have yet to be successful.

-Osiris

 

[Osiris]

True, and I actually never thought of it that way..

If that is the case though, then why does it import all rows correctly if I step through the code?

-Osiris

 

[Osiris]

I'm not quite sure what you are saying? I am only trying to Import the rows once. I have ditched trying to import them with the query because it gives me the key violation error. So now I am only trying to import them via the code loop. Once I run the code loop it will import one row, then come up with the "ODBC -- call failed" error. So I go into the table delete the row that it imported, try to fix the code, and re-run it.

If I have the code break when intRow changes(each time), then resume running the code after it breaks(each time) it will import all of the rows correctly.

Also if I have the code break on the .Update each time it comes to that part, then continue running the code each time, it will import them all correctly.

Only if I try to run the code loop without breaking and resuming it does it come up with the "ODBC -- call failed" error. That is why I am so confused...

I apologize if I am not explaining the problem I am having very well...

I appriciate your trying to help me though.

P.S. I have modified the code in my original message to reflect how it now looks.

-Osiris

 

[Osiris]

Never mind. I figured out what my problem was.

I'm kinda stupid.

Basically it was importing all the records too fast, It would have made the "Time" field have the same value for certain records, which in my testing was the only primary key that would have been different between the records. So it appeared to Access that I was trying to add duplicate records, which it won't allow in this database.

Thank you both for your help I really appriciate it.

-Osiris