Solved

Updating Access 2002 db from VB6 using ADO connection/recordsets

Posted on 2004-04-03
11
1,437 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 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

740 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