Updating Access 2002 db from VB6 using ADO connection/recordsets

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
        !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
    End With 'rsSchemeHeader
'Closes everything and exits
Who is Participating?

Improve company productivity with a Business Account.Sign Up

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.

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

henrymarriottAuthor Commented:
Apologies for the typo's - the code is new adodb.... not vice versa!
what line does the debugger stop on?

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?

henrymarriottAuthor Commented:
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
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

henrymarriottAuthor Commented:
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!
I will post points to rockiroads...

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


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.