Solved

Updating Access 2002 db from VB6 using ADO connection/recordsets

Posted on 2004-04-03
11
1,433 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
Comment Utility
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
Comment Utility
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
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
 

Author Comment

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

Expert Comment

by:Arthur_Wood
Comment Utility
what line does the debugger stop on?

AW
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 39

Expert Comment

by:stevbe
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I will post points to rockiroads...

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

AW

0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now