adodb.command

[OhioSteve]

I have an Access db on several users' laptops. This Access db can upload records to an sql server db. To perform the upload, I used an adodb.connection and an adodb.command.

When the command.execute method fires, sql server ALWAYS gets the data and does the insert. However, SOMETIMES the vba function stops running right on the command.execute line. Eventually, the command.execute times out and reports an error- even though the insert actually worked.

Here are a couple of fixes that I am considering:

1. I use the same command object repeatedly. I just change the parameters and call .execute again. Perhaps I should instantiate a new command EVERY single time. This would be ineficient but I am moving a tiny amount of data, so efficiency is not a key concern.

2. Perhaps I should make the command run asynchronously. Here is the syntax I am considering:
myCommand.Execute , , adAsyncExecute

Are those good ideas? I look forward to your feedback.

 

[OhioSteve]

Or maybe I need to do this instead:

With myCommand
.ActiveConnection.BeginTrans
.Execute
.ActiveConnection.CommitTrans
End With

Would that force it to finish?

 

[Melagan]

...and reports an error

...and the error is?

~Melagan
______
"It's never too late to become what you might have been.

 

[JerryKlmns]

Steve,

BeginTrans-CommitTrans-RollBackTrans apply to connection objects and not on commands

Maybe you should check the ExecuteComplete Event. But it wont work if you execute the command adAsyncExecute

 

[OhioSteve]

Meghan, the error is "timeout expired". It is weird because the call to the stored procedue DOES happen, and SQL Server inserts the data. Yet on the Access side the call times out.