SQl 'insert into' in VBA not writing to tables

[Barntek]

I am attempting to use VBA in access 2003 to run a SQL 'insert into' command. The SQL command is part of a loop such that the code updates with a variable string on each pass. The code runs and works, however when running over several passes the SQL does not update the required tables on all passes. However the code does update on some of the passes, if i run the VBA code again then a different set of passes are updated to the table by the SQL. The SQL takes about 2 minutes to run. The VBA is show below. How can i make the code write to the tables on each pass?

Private Sub Holding_Click()
On Error GoTo err

Dim i As String
Dim j As String
Dim a As String
Dim CON As ADODB.Connection
Dim rstWEEK As New ADODB.Recordset


Set CON = CurrentProject.Connection
CON.CommandTimeout = 0

rstWEEK.Open "week", CON, adOpenKeyset, adLockOptimistic

i = InputBox("Start week")
j = InputBox("End week")

rstWEEK.MoveFirst
Do Until rstWEEK!week = i
rstWEEK.MoveNext
Loop

DoCmd.RunSQL "delete tbl_summary"

exitloop2:

rstWEEK.MovePrevious
a = rstWEEK!week
rstWEEK.MoveNext
i = rstWEEK!week
rstWEEK.MoveNext
SysCmd acSysCmdSetStatus, i

DoCmd.RunSQL "insert into tbl_summary SELECT 'holding' as measure, '" & i & "' as data_week, week_num.year_week as year_week, year_number, week_number, SUM([tblOrderBookDetail~wk" & i & "].ACTUAL_SALE_VALUE - [tblOrderBookDetail~wk" & i & "].VAT_PAYABLE) AS value " _
& "FROM [tblOrderBookDetail~wk" & i & "] LEFT OUTER JOIN week_num ON [tblOrderBookDetail~wk" & i & "].Delivery_Date = week_num.date_number LEFT OUTER JOIN [tblOrderBookDetail~Wk" & a & "] ON [tblOrderBookDetail~wk" & i & "].SALE_ITEM = [tblOrderBookDetail~Wk" & a & "].SALE_ITEM AND [tblorderbookdetail~wk" & i & "].Reference_No = [tblOrderBookDetail~Wk" & a & "].Reference_No " _
& "WHERE ([tblOrderBookDetail~wk" & i & "].Reference_No IN (SELECT reference_no FROM [tblorderbookdetail~wk" & i & "] WHERE sale_item LIKE 'zzg%')) AND ([tblOrderBookDetail~Wk" & a & "].Reference_No IS NULL) AND ([tblOrderBookDetail~Wk" & a & "].SALE_ITEM IS NULL) " _
& "GROUP BY week_num.year_week, week_num.year_number, week_num.week_number;"

If i = j Then
GoTo exitloop1
Else
GoTo exitloop2
End If

exitloop1:

SysCmd acSysCmdRemoveMeter
rstWEEK.Close
CON.Close

MsgBox "Done"

err_exit:
Exit Sub

err:
MsgBox err.Description
GoTo err_exit
End Sub


Thanks
Barney

 

[xlbo]

I'm guessing that the code keeps on running WHILST the SQL is executing. You need the code to pause whilst the SQL executes.

Via ODBC, I would set the "BackgroundQuery" property to false - not sure what the equivalent is in ADO though...

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Barntek]

Thanks for this, i am trying to find the equivalent in ADO but can't so far, can anyone else help?

Thanks
Barney