H
HSalim[MVP]
Hi All.
This not Exactly an Access question but I figured this was probably the post
appropriate group for this question.
Background.
I have an Excel spreadsheet that has a column of invoice numbers that need
to be validated against a Great Plains database in SQL Server. As you might
expect I am using ADO and VBA to acomplish the task.
FYI: The spreadsheet is usually between 3000 and 8000 rows so performance is
an issue.
I tried using both a Connection object with a recordset and a command object
with a connection object. the command object is a lot faster.
In a code module, at the start of the process, I instatiate a connection
object and an command object
as I process the rows, I execute the command object - pass in DocNum as
input parameter and , retrieve CoID and CustNmbr as output parameters.
As I loop through the rows, the perormance slowly deteriorates - from
something like 100 rows per second to 1 row per second, in the space of 1000
rows - pretty rapid decline, right?
So I add a little check if - if row mod 250 = 0 then destroy all objects
using the following routine:
Sub DestroyObjects()
Set pDocNum = Nothing ' Parameter
Set pCoID = Nothing ' Parameter
Set pCustNmbr = Nothing ' Parameter
Set ocmd = Nothing ' Command object
Set oConGP = Nothing 'Connection object
mbConnected = False ' just a boolean flag
End Sub
I am explicitly setting these to nothing. I have stepped through the code
to see that these steps are being executed.
Yet, when I come around to opening that connection object, I get an error:
"Function or Procedure usp_lkup_CoID has too many arguments"
I found KB #298118 PRB: Error When You Combine ADO Refresh Method with
CreateParameter
This is the exact error message, but I'm not refreshing my parameters
collection, and in fact, am closing and reopening the object.
Then I found KB # 194979 INFO: ADO Spawns Additional Connections to SQL
Server
Which seems to be the issue we have at hand. I am using a read-only
fast-forward server-side cursor so it sure seems applicable, but there is no
resolution to this...
Eventually, I had to just ignore this error and move on - I have
oConGP.Open
If Err.Number <> 0 Then
If Err.Number <> -2147217900 Then
MsgBox "Error connecting to database server: "
<snip>
That solved the problem and speed is no longer an issue - zipping through
at the same 100 rows per sec rate with some loss at renewal time.
Has anyone encountered this? What, if anything, can I do about it? Am I
making a mistake ignoring the error?
Thanks for your time.
Regards
HS
This not Exactly an Access question but I figured this was probably the post
appropriate group for this question.
Background.
I have an Excel spreadsheet that has a column of invoice numbers that need
to be validated against a Great Plains database in SQL Server. As you might
expect I am using ADO and VBA to acomplish the task.
FYI: The spreadsheet is usually between 3000 and 8000 rows so performance is
an issue.
I tried using both a Connection object with a recordset and a command object
with a connection object. the command object is a lot faster.
In a code module, at the start of the process, I instatiate a connection
object and an command object
as I process the rows, I execute the command object - pass in DocNum as
input parameter and , retrieve CoID and CustNmbr as output parameters.
As I loop through the rows, the perormance slowly deteriorates - from
something like 100 rows per second to 1 row per second, in the space of 1000
rows - pretty rapid decline, right?
So I add a little check if - if row mod 250 = 0 then destroy all objects
using the following routine:
Sub DestroyObjects()
Set pDocNum = Nothing ' Parameter
Set pCoID = Nothing ' Parameter
Set pCustNmbr = Nothing ' Parameter
Set ocmd = Nothing ' Command object
Set oConGP = Nothing 'Connection object
mbConnected = False ' just a boolean flag
End Sub
I am explicitly setting these to nothing. I have stepped through the code
to see that these steps are being executed.
Yet, when I come around to opening that connection object, I get an error:
"Function or Procedure usp_lkup_CoID has too many arguments"
I found KB #298118 PRB: Error When You Combine ADO Refresh Method with
CreateParameter
This is the exact error message, but I'm not refreshing my parameters
collection, and in fact, am closing and reopening the object.
Then I found KB # 194979 INFO: ADO Spawns Additional Connections to SQL
Server
Which seems to be the issue we have at hand. I am using a read-only
fast-forward server-side cursor so it sure seems applicable, but there is no
resolution to this...
Eventually, I had to just ignore this error and move on - I have
oConGP.Open
If Err.Number <> 0 Then
If Err.Number <> -2147217900 Then
MsgBox "Error connecting to database server: "
<snip>
That solved the problem and speed is no longer an issue - zipping through
at the same 100 rows per sec rate with some loss at renewal time.
Has anyone encountered this? What, if anything, can I do about it? Am I
making a mistake ignoring the error?
Thanks for your time.
Regards
HS