sql INSERT INTO record location

  • Thread starter backToASP via AccessMonster.com
  • Start date
B

backToASP via AccessMonster.com

I'm running a looping process that builds a table of data. The data must be
in a very specific order and I am building it that way - looping through and
running sql INSERT INTO statements in a certain order. I am getting all of
my data - all correct data in correct fields - but the rows are out of order
sometimes when it runs. I have run the same set of data mutliple times and
it was correct once, then broke in 2 diff places the next 2 times. I thought
INTSERT INTO always put the new record at the end of the table of data. Is
this correct?

Here is the code - as mentioned it works - just the resulting table has rows
out of order sometimes.

'loop through invoice headers
strSQLHeader = "SELECT [DOCNUM] FROM [_qryQBInvoiceHeaderData]"
Set rsHeader = New ADODB.Recordset
rsHeader.Open strSQLHeader, CurrentProject.Connection, adOpenStatic,
adLockOptimistic
If rsHeader.RecordCount > 0 Then 'records to export
'first delete previous records from table - leave first 3 lines for
definition
strDelRecords = "DELETE tblQuickBooksTransData.*, tblQuickBooksTransData.
TRNSORSPL FROM tblQuickBooksTransData " & _
"WHERE tblQuickBooksTransData.TRNSORSPL NOT IN ('!TRNS', '!SPL', '!
ENDTRNS')"
CurrentProject.Connection.Execute strDelRecords

rsHeader.MoveFirst
While Not rsHeader.EOF
curInv = rsHeader.Fields(0)
strHeaderInsert = "INSERT INTO tblQuickBooksTransData " & _
"( TRNSORSPL, TRNSTYPE, INVOICEDATE, ACCOUNT, CONAME, AMOUNT, DOCNUM,
DELID, CLEAR, TOPRINT, ADDR1, ADDR2, ADDR3, DUEDATE, TERMS, PAID, SHIPDATE,
INVTITLE ) " & _
"SELECT [_qryQBInvoiceHeaderData].TRANSLINE, [_qryQBInvoiceHeaderData]
.TRNSTYPE, [_qryQBInvoiceHeaderData].invoiceDATE, [_qryQBInvoiceHeaderData].
ACCNT, [_qryQBInvoiceHeaderData].coNAME, [_qryQBInvoiceHeaderData].AMOUNT, "
& _
"[_qryQBInvoiceHeaderData].DOCNUM, [_qryQBInvoiceHeaderData].
delIDMEMO, [_qryQBInvoiceHeaderData].CLEAR, [_qryQBInvoiceHeaderData].TOPRINT,
" & _
"[_qryQBInvoiceHeaderData].ADDR1, [_qryQBInvoiceHeaderData].ADDR2,
[_qryQBInvoiceHeaderData].ADDR3, [_qryQBInvoiceHeaderData].DUEDATE, " & _
"[_qryQBInvoiceHeaderData].TERMS, [_qryQBInvoiceHeaderData].PAID,
[_qryQBInvoiceHeaderData].SHIPDATE, [_qryQBInvoiceHeaderData].INVTITLE " & _
"FROM _qryQBInvoiceHeaderData " & _
"WHERE [_qryQBInvoiceHeaderData].DOCNUM=" & curInv & ";"
'insert the invoice header TRNS line
CurrentProject.Connection.Execute strHeaderInsert

strDetailsInsert = "INSERT INTO tblQuickBooksTransData " & _
"( TRNSORSPL, TRNSTYPE, INVOICEDATE, ACCOUNT, CONAME, AMOUNT, DOCNUM,
DELID, CLEAR ) " & _
"SELECT [_qryQBInvoiceDetails].TRANSLINE, [_qryQBInvoiceDetails].
TRNSTYPE, " & _
"[_qryQBInvoiceDetails].invoiceDATE, [_qryQBInvoiceDetails].ACCNT, "
& _
"[_qryQBInvoiceDetails].coNAME , [_qryQBInvoiceDetails].AMOUNT, " & _
"[_qryQBInvoiceDetails].DOCNUM, [_qryQBInvoiceDetails].delIDMEMO, " &
_
"[_qryQBInvoiceDetails].Clear FROM _qryQBInvoiceDetails " & _
"WHERE [_qryQBInvoiceDetails].DOCNUM=" & curInv & ";"
'insert detail SPL lines
CurrentProject.Connection.Execute strDetailsInsert

strEndTrans = "INSERT INTO tblQuickBooksTransData (TRNSORSPL) " & _
"VALUES ('ENDTRNS');"
'add ONE line to end the trans
CurrentProject.Connection.Execute strEndTrans

'now move to next invoice
rsHeader.MoveNext
Wend

'**** END LOOPING ****

Any suggestions greatly appreciated.
 
C

Carl Rapson

backToASP via AccessMonster.com said:
I'm running a looping process that builds a table of data. The data must
be
in a very specific order and I am building it that way - looping through
and
running sql INSERT INTO statements in a certain order. I am getting all
of
my data - all correct data in correct fields - but the rows are out of
order
sometimes when it runs. I have run the same set of data mutliple times
and
it was correct once, then broke in 2 diff places the next 2 times. I
thought
INTSERT INTO always put the new record at the end of the table of data.
Is
this correct?

Here is the code - as mentioned it works - just the resulting table has
rows
out of order sometimes.

'loop through invoice headers
strSQLHeader = "SELECT [DOCNUM] FROM [_qryQBInvoiceHeaderData]"
Set rsHeader = New ADODB.Recordset
rsHeader.Open strSQLHeader, CurrentProject.Connection, adOpenStatic,
adLockOptimistic
If rsHeader.RecordCount > 0 Then 'records to export
'first delete previous records from table - leave first 3 lines for
definition
strDelRecords = "DELETE tblQuickBooksTransData.*,
tblQuickBooksTransData.
TRNSORSPL FROM tblQuickBooksTransData " & _
"WHERE tblQuickBooksTransData.TRNSORSPL NOT IN ('!TRNS', '!SPL', '!
ENDTRNS')"
CurrentProject.Connection.Execute strDelRecords

rsHeader.MoveFirst
While Not rsHeader.EOF
curInv = rsHeader.Fields(0)
strHeaderInsert = "INSERT INTO tblQuickBooksTransData " & _
"( TRNSORSPL, TRNSTYPE, INVOICEDATE, ACCOUNT, CONAME, AMOUNT,
DOCNUM,
DELID, CLEAR, TOPRINT, ADDR1, ADDR2, ADDR3, DUEDATE, TERMS, PAID,
SHIPDATE,
INVTITLE ) " & _
"SELECT [_qryQBInvoiceHeaderData].TRANSLINE,
[_qryQBInvoiceHeaderData]
TRNSTYPE, [_qryQBInvoiceHeaderData].invoiceDATE,
[_qryQBInvoiceHeaderData].
ACCNT, [_qryQBInvoiceHeaderData].coNAME, [_qryQBInvoiceHeaderData].AMOUNT,
"
& _
"[_qryQBInvoiceHeaderData].DOCNUM, [_qryQBInvoiceHeaderData].
delIDMEMO, [_qryQBInvoiceHeaderData].CLEAR,
[_qryQBInvoiceHeaderData].TOPRINT,
" & _
"[_qryQBInvoiceHeaderData].ADDR1, [_qryQBInvoiceHeaderData].ADDR2,
[_qryQBInvoiceHeaderData].ADDR3, [_qryQBInvoiceHeaderData].DUEDATE, " & _
"[_qryQBInvoiceHeaderData].TERMS, [_qryQBInvoiceHeaderData].PAID,
[_qryQBInvoiceHeaderData].SHIPDATE, [_qryQBInvoiceHeaderData].INVTITLE " &
_
"FROM _qryQBInvoiceHeaderData " & _
"WHERE [_qryQBInvoiceHeaderData].DOCNUM=" & curInv & ";"
'insert the invoice header TRNS line
CurrentProject.Connection.Execute strHeaderInsert

strDetailsInsert = "INSERT INTO tblQuickBooksTransData " & _
"( TRNSORSPL, TRNSTYPE, INVOICEDATE, ACCOUNT, CONAME, AMOUNT,
DOCNUM,
DELID, CLEAR ) " & _
"SELECT [_qryQBInvoiceDetails].TRANSLINE, [_qryQBInvoiceDetails].
TRNSTYPE, " & _
"[_qryQBInvoiceDetails].invoiceDATE, [_qryQBInvoiceDetails].ACCNT,
"
& _
"[_qryQBInvoiceDetails].coNAME , [_qryQBInvoiceDetails].AMOUNT, " &
_
"[_qryQBInvoiceDetails].DOCNUM, [_qryQBInvoiceDetails].delIDMEMO, "
&
_
"[_qryQBInvoiceDetails].Clear FROM _qryQBInvoiceDetails " & _
"WHERE [_qryQBInvoiceDetails].DOCNUM=" & curInv & ";"
'insert detail SPL lines
CurrentProject.Connection.Execute strDetailsInsert

strEndTrans = "INSERT INTO tblQuickBooksTransData (TRNSORSPL) " & _
"VALUES ('ENDTRNS');"
'add ONE line to end the trans
CurrentProject.Connection.Execute strEndTrans

'now move to next invoice
rsHeader.MoveNext
Wend

'**** END LOOPING ****

Any suggestions greatly appreciated.
Click to expand...

Table data is not stored in any particular order. You can retrieve the data
in a particular order by defining indices. My suggestion is to include a
sequential value in your INSERT INTO statement (1,2,3,...) for each record
(your table will need a field defined for that value also), and then
retrieve the records sorted by that value.

Carl Rapson
 
Top