Solved

Updating Access 2002 db from VB6 using ADO connection/recordsets

Posted on 2004-04-03
11
1,436 Views
Last Modified: 2012-06-27
Run-time error ‘-2147217887 (80040e21)’:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Running in VB6 Dev Env on W2k server and converting a series of small sequential files to records in a database (MS Access DB Ver 2002 SP3) – for simplicity and to try and solve the problem, I have removed all relationships and am restricting the conversion to data which goes into only one table but I still get the above error. The proc runs perfectly for one file and updates the db no problem. I close recordsets and connection and set them = nothing at the end of the proc and I close the sequential file.

If I then open a new sequential file and call the conversion proc I get the above message. Can any one please shed any light on it for me??

Code snippets shown below.
    Dim HPAConnect as ADODB.Connection
    Dim SchemeHeader as ADODB.Recordset
    Set HPAConnect = ADODB.NEW Connection
   Set SchemeHeader = ADODB.NEW Recordset
    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=G:\Program Files\Microsoft Visual Studio\HPA2005\HPA.mdb;" _
    & "Persist Security Info=False"
    HPAConnect.Open sConnect

      rsSchemeHeader.Open "SchemeHeader", HPAConnect, adOpenDynamic, adLockOptimistic, adCmdTable
      With rsSchemeHeader
        .AddNew
        !SchemeID = sThisSchemeID
        !Name = fund$ 'name
        !Description = n1$ '?
        !BeginDate = funddate$ 'BeginDate
        !LastUpdated = lastupdate$ '!LastUpdated
        !active = active '!Active
        !IntroByID = intro$ '!IntroByID *****
        !SchemeLine1 = add$(1) 'SchemeLine1 - 4 + SchemePostCode
        !SchemeLine2 = add$(2)
        !SchemeLine3 = add$(3)
        !SchemeLine4 = add$(4)
        !SchemePostCode = ""
        !PhoneNo = phone$(1)
        !FaxNo = fax$(1)
        !SchemeReviewDate = revpen$(1)
        !PSORefNo = pso$ 'SH!PSORefNo
        !ApprovedDate = pso_dat$ '!ApproveDate
        !OPBRefNo = pso_ref$ ' !OPBRefNo
        !CoTaxOff = pso_tax$ ' !CoTaxOff
        !SchemeTaxOff = pso_fun$ '!SchemeTax Office
        !ContactLine1 = contact$(1) 'Should really go into a separate table
        !ContactLine2 = contact$(2)
        !ContactPhoneNo = phone$(2)
        !ContactFaxNo = fax$(2)
        !Trustees = membership$ 'SH!Trustees
        !Notes = "" '*******
        'THESE are on Actuary Section (frm 13) but relate to scheme not a specific member
        !ActPerRevDate = act_per$ '!ActPerRevDate
        !ActNextRevDate = act_nex$ '!ActNextRevDate
        !ActuaryNotes = act_notes$ '!ActuaryNotes
        .Update
    End With 'rsSchemeHeader
'Closes everything and exits
0
Comment
Question by:henrymarriott
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 10747240
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 10747243
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10747340
these two lines:

    Set HPAConnect = ADODB.NEW Connection
   Set SchemeHeader = ADODB.NEW Recordset

should read:

    Set HPAConnect = NEW ADODB.Connection
   Set SchemeHeader = NEW ADODB.Recordset

I am surprised that the code even compiles.

AW
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:henrymarriott
ID: 10747350
Apologies for the typo's - the code is new adodb.... not vice versa!
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10747378
what line does the debugger stop on?

AW
0
 
LVL 39

Expert Comment

by:stevbe
ID: 10747662
are any of the fields "required" but the conversion file does not contain a value for it? Are you trying to write "" to fields that o not allow zero length string?

Steve
0
 

Author Comment

by:henrymarriott
ID: 10747692
I think the answer is in MS kb. Thanks Rockiroads for pointing at that. I should have found it myself I guess. The problem is in fact an earlier bug which I found thru the mS link you suggested. A truly daft one but you get that error if you set consecutive fields in the database to "" - why on earth that gives rise to that error I have no idea and I would not have found it without your ref so very many thanks
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10752270
ok, glad you found it,
who were you supposed to thank then, me (rockiroads) or Arthur wood 'cos you have accepted his answer
Not whinging about points, but just curious to know what was the proper solution

Reason being, it helps me and others to know solutions to different problems

0
 

Author Comment

by:henrymarriott
ID: 10753711
I think I must have had a bad weekend! I meant to accept your answer not AW's. Is there any way that i can get the points reallocated (Sorry Arthur but .....). In the end it turned out to be a combination of the latest Jet 4.0 engine (SP8 applied) and making sure that I had MDAC 2.5 or later. Jet 4.0 was the critical one I think. I ran a few more tests and sure enough the error was always coming up after assigning "" to 2 or 3 fields in a row - daft but true - I will appeal to get you your points!
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10754385
I will post points to rockiroads...

rockiroads---look for a question for your benefit.

AW

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question