VB Forum / Database Access / April 2007

I am hoping that someone has come accross this issue before and can
point me in the right direction for a suitable solution.

I have searched through google groups and tried everyting I could find
from old posts (to no avail) and also had a look through the microsoft
website to see if it can shed any light on the issue, it appears that
It cannot.

My problem is that I have 3 tables.

Tables  1 and 2 store the bulk of my data, a field in table 1 & also
in table 2 is a numerical field which references a field in table 3
for the corresponding value linked to the numbered field (There is a
many to one relationship between table 1 - 3 and table 2 - 3).

When I run a data import in to table 2, if the corresponding field  is
not found in table 3 (which is stored in an array), a procedure is
called to add this value. This procedure has been confirmed as
working.

Once this value is added to the table (table 3) another procedure re-
populates an array with the table information

The import should create about 100 records in table 3 throughout it's
cycle.

When I run the data import the first few records are sucessfully
imported and any number between 3 & 7 records are created in table 3.
But after this point the program bombs out.

If I remove the referral intergrity on the relationships between the
tables the import runs smoothly, if I populate table 3 manually and
run the import it also runs smoothly, but If i add any number of
records to table 3 during the import the import fails. This I believe
is caused by a 'lazy write'.

I have listed the code below to see if i am misssing soemthing
glaringly obvious. I am using ADO and the latest release of Jet.

I have tried adding a transactional process to resolve this without
any sucess and also added a transactional commit mode statement.

If anyone has any thoughs or pointers that may help me out it would be
greatly appreciated.

I have also repaired the access database and created a new access
database

Private Function addarrayChargeGroups(parttoadd As String) As Long

   Dim ChargeGroupsadoConnection As ADODB.Connection
   Dim ChargeGroupsadoRecordset As ADODB.Recordset, loopcount As Long

   Set ChargeGroupsadoRecordset = New ADODB.Recordset
   Set ChargeGroupsadoConnection = New ADODB.Connection

   connectstring = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & location
   ChargeGroupsadoConnection.Open connectstring

   ChargeGroupsadoConnection.Properties("Jet OLEDB:Transaction Commit
Mode") = 1
   ChargeGroupsadoConnection.Properties("Jet OLEDB:Flush Transaction
Timeout") = 1000

   ChargeGroupsadoConnection.BeginTrans

   ChargeGroupsadoRecordset.Open "Charge_Group",
ChargeGroupsadoConnection, adOpenDynamic, adLockOptimistic, adCmdTable

   ChargeGroupsadoRecordset.AddNew
       ChargeGroupsadoRecordset!chargegroup =
(Trim(parttoadd))
       addarrayChargeGroups = ChargeGroupsadoRecordset!chargegroup_ID

   ChargeGroupsadoRecordset.Update
   ChargeGroupsadoConnection.CommitTrans

   ChargeGroupsadoRecordset.Close
   Set ChargeGroupsadoRecordset = Nothing
   ChargeGroupsadoConnection.Close
End Function

Kind regards & Thanks for taking the time to read this.

Bruno