I'm trying to populate an access 97 database with data from OpenInsight using the Query objects and ODBC. Everything works fine for the first 1000 inserts os so but then the inserts fail.

My code runs something like this (simplified) :-

hXO=XOInstance(setup connection etc)

Begin Loop

  Construct SQL String

  hQry=QtyInstance(hXO)

  QryMethod(hQry, QRY_EXECUTE$, SQLString)

  XOMethod(hXO, ,XO_COMMITTRAN$)

  QryMethod(hQry, QRY_DESTROY$)

End Loop

XOMethod(hXO,XO_DESTROY$)

I have tried destroying and re-creating the Connection handle within the loop (say every 100 inserts or so). This improves things to the point where I can do 4 or 5 thousand inserts before they begin failing.

Can anyone help ?

Richard,

I've noticed that MSAccess can be sort of limiting when it comes to ODBC connectivity. A customer in the past had a data limitation while warehousing data to MSAccess. The solution to get all of the data to commit, (this was while creating and populating the tables) was to increase the max. buffer size in the ODBC System DSN configuration options.

Perhaps a combination of boosting the max. buffer size and destroying and recreating the connction handle will work for you.

-Stephen Revelation

View this thread on the forum...