Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • Last Modified:

COMMIT TRANS WITH ADO

I'm using ADO 2.0 to connect to an ACCESS 97 database through an ODBC DSN.

I'm using the default provider: "MSDASQL"

Once connected, I used the following code:

conADO.BeginTrans
for nInd = 1 to 10
  rstADO.addnew
  ...
  rstADO.update
next
conADO.CommitTrans

I get the error "Operation invalid at this time." at conADO.begintrans.

WHY ?

Without begintrans and committrans, everything is working fine.
0
mxclouti
Asked:
mxclouti
  • 3
1 Solution
 
vmanoCommented:
try this:
move the line conADO.BeginTrans before you open the recordset.
and move the line conADO.CommitTrans  before you close the recordset.your code should look someting like this:

conADO.BeginTrans
Set rstADO = .....

for nInd = 1 to 10
  rstADO.addnew
  ...
  rstADO.update
next

rstADO.Close
conADO.CommitTrans  

let me know if this helps
vmao



0
 
vmanoCommented:
there is a correction in the line
"move the line conADO.CommitTrans before you close the recordset"

it should be
"move the line conADO.CommitTrans after you close the recordset"

sorry for the confusion.

vmano
0
 
mxcloutiAuthor Commented:
Thanks. It's working. That was so simple.

Instead of the default provider (MSDASQL) I've tried to used the provider (Microsoft.JET.OLEDB.3.51):

conADO.ConnectionTimeout = glTimeOut
conADO.Provider = "Microsoft.JET.OLEDB.3.51"
conADO.Properties("user id") = "mxclouti"
conADO.Properties("password") = "nosmoke"
conADO.Properties("data source") = gsMDB
conADO.Properties("Jet OLEDB:System database") = gsMDW
conADO.Open
   
and I get the error "-2147467259 Unspecified error" at conADO.Open

If you can answer that for me or else I'll ask this question to all experts and accept your answer.

Thanks.
0
 
vmanoCommented:
I am not sure of the error number. I never tried like what you did. here is an alternative to what you are doing.

Dim Con as String

Con = "Driver={....}; Dbq=...;DefaultDir=...;Uid=...;Pwd=...;"

conADO.ConnectionString = Con
conADO.Open
..
conADO.Close
conADO.ConnectionString = ""

let me know if this helps
vmano



0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now