Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Updating Access 2002 db from VB6 using ADO connection/recordsets

Posted on 2004-04-03
11
Medium Priority
?
1,444 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 10747240
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 1500 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

722 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