Import Record count
- Thread starter starter
- Start date
I deleted the records from mssql.
I removed "On error resume next".
When I select the file for import, I get this error:
Run-time error '3061':
Too few parameters. Expected 1.
Issue appears with:
Code:
'attempt to append import to MSSQL table
Dim lngRecordsAffected As Long
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryClearOldTransactions", acViewNormal
'DoCmd.OpenQuery "qryAppendCCTransactions", acViewNormal
With CurrentDb.QueryDefs("qryAppendCCTransactions")
[COLOR="Red"].Execute[/COLOR]
lngRecordsAffected = .RecordsAffected
End With
DoCmd.SetWarnings True
MsgBox DCount("*", "tCCTransactions") & " records found " & vbCrLf & lngRecordsAffected & " records were imported"
- Local time
- Yesterday, 21:36
- Joined
- Feb 28, 2001
- Messages
- 30,373
Don't know if this will work for this case, but instead of doing a DoCmd.whatever for the append, do a CurrentDB.Execute of the same append query. If that query works in "Execute" context, you can do a test of CurrentDB.RecordsAffected as the next instruction to determine how many records were ACTUALLY appended. Again, don't do math to find the answer. Just ask Access to tell you.
- Local time
- Yesterday, 21:36
- Joined
- Feb 28, 2001
- Messages
- 30,373
OK, my error of omission, .Execute works for QueryDef objects too. The only thing I can add to arnelgp's comment is to make the .Execute have parameter dbFailOnError as a parameter, just to give it one.
If arnel is right (and he VERY often is) then the query failed. That parameter is benign in this context, since it says "fail if something goes wrong" and it was already failing anyway so... just gilding the lily.
If this is a debugging issue then you don't want to turn off warnings around this code. you WANT it to bomb so that you can get a spot notification and can examine things at the point of the error. If you are doing this on a production copy of your code then you have other issues to consider, but if this is a private development copy then you should be OK. Also, using dbFailOnError is actually nicer because it is more likely to clean up after itself and not leave a half-updated table sitting around after a failure.
If arnel is right (and he VERY often is) then the query failed. That parameter is benign in this context, since it says "fail if something goes wrong" and it was already failing anyway so... just gilding the lily.
If this is a debugging issue then you don't want to turn off warnings around this code. you WANT it to bomb so that you can get a spot notification and can examine things at the point of the error. If you are doing this on a production copy of your code then you have other issues to consider, but if this is a private development copy then you should be OK. Also, using dbFailOnError is actually nicer because it is more likely to clean up after itself and not leave a half-updated table sitting around after a failure.
