ISTech Support Forum - APPEND QUERY inserts multiple lines into DB

Welcome, Guest. Please Login.

	04/19/24 at 09:11:59 News: \| IS Tech Support \| Evo~ERP \| Evo Wiki \| Forum \|

ISTech Support Forum › Crystal Reports, ODBC & Access › Access General Issues

‹ Previous topic | Next topic ›

Pages: 1

APPEND QUERY inserts multiple lines into DB (Read 4740 times)

Full Member

I love DBA

Posts: 216
Gender: male

APPEND QUERY inserts multiple lines into DB
05/04/11 at 06:10:02

I am trying to insert an item into an existing S/O using an append query. (this is done because we are giving a free item to customers provided they meet certain qty condistions - which I establish in a seperate query)
The append query has only the following entries:

Field1: EXPR1:"12345" > Append to BKAR_INVL_INVM (12345 represents the S/O # that the item is added to).
Field2: EXPR2:"S650" > Append to BKAR_INVL_PCODE (S650 represents the product ID)
Field3: EXPR3:"1" > Append to BKAR_INVL_PQTY

The problem is that when running the query, it inserts 40+ line items instead of only one.
Where do I go wrong Huh

Helmut Bode
General Pencil Co.
DBA Classic 2004.1 w/IS Tech update 7/25/13
EVO-ERP
Pervasive 11
Win2008 Server
Terminal Server
12 Users

IP Logged

Kelloggs

Active Member

Do crazy people know
they are crazy?

Posts: 785
Gender: male

Re: APPEND QUERY inserts multiple lines into DB
Reply #1 - 05/04/11 at 07:40:56

There is another way to do that
Public Sub my_insert()
Dim var_sql as string
var_sql = "INSERT INTO MY_TABLE (FIELD_1, FIELD_2) VALUES (1,2)"
conn_dba(var_sql)
End Sub

Public Function conn_dba(var_sql As String)
On Error GoTo Err_conn_dba

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command

  Set cn = New ADODB.Connection
  Set cmd = New ADODB.Command

  cn.ConnectionString = "DSN=DBA"
  cn.Open

  Set cmd.ActiveConnection = cn
  cmd.CommandText = var_sql
  cmd.Execute

  cn.Close
  Set cmd = Nothing
  Set cn = Nothing

Exit_conn_dba:
   Exit Function
Err_conn_dba:
   MsgBox Err.Description
   Resume Exit_conn_dba
End Function

By the way these are the fields that you should include (I think):

BKAR_INVL_INVNM,
BKAR_INVL_CNTR,
BKAR_INVL_ESD,
BKAR_INVL_PCODE,
BKAR_INVL_PDESC,
BKAR_INVL_PQTY,
BKAR_INVL_PPRCE,
BKAR_INVL_PEXT,
BKAR_INVL_TXBLE,
BKAR_INVL_LOC,
BKAR_INVL_UM_LN_1,
BKAR_INVL_UM_LN_2,
BKAR_INVL_COOP,
BKAR_INVL_OOQTY,
BKAR_INVL_SCCOG

Huh

Kelloggs

« Last Edit: 05/04/11 at 12:56:33 by Kelloggs »

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)

IP Logged

Kelloggs

Active Member

Do crazy people know
they are crazy?

Posts: 785
Gender: male

Re: APPEND QUERY inserts multiple lines into DB
Reply #2 - 05/04/11 at 08:08:29

If you want to do it the traditional way: Append Query.
Create a "Temp" table with exactly the same fields.

This would be the SQL for you append query:

INSERT INTO BKARINVL ( BKAR_INVL_INVNM, BKAR_INVL_CNTR, BKAR_INVL_ESD, BKAR_INVL_PCODE, BKAR_INVL_PDESC, BKAR_INVL_PQTY, BKAR_INVL_PPRCE, BKAR_INVL_PEXT, BKAR_INVL_TXBLE, BKAR_INVL_LOC, BKAR_INVL_UM_LN_1, BKAR_INVL_UM_LN_2, BKAR_INVL_COOP, BKAR_INVL_OOQTY, BKAR_INVL_SCCOG )
SELECT BKAR_INVL_INVNM, BKAR_INVL_CNTR, BKAR_INVL_ESD, BKAR_INVL_PCODE, BKAR_INVL_PDESC, BKAR_INVL_PQTY, BKAR_INVL_PPRCE, BKAR_INVL_PEXT, BKAR_INVL_TXBLE, BKAR_INVL_LOC, BKAR_INVL_UM_LN_1, BKAR_INVL_UM_LN_2, BKAR_INVL_COOP, BKAR_INVL_OOQTY, BKAR_INVL_SCCOG
FROM Temp;

Tongue

Kelloggs

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)

IP Logged

Lynn_Pantic Administrator evolution (n) - gradual change to a different form Posts: 5681	Re: APPEND QUERY inserts multiple lines into DB Reply #3 - 05/04/11 at 08:19:54 Also keep in mind that updating an order this way rather than using the program the way it was designed will not update the item stock status and if the item being added to the order was not a giveaway item, the open order balance and other fields would also not be updated.
Back to top	Lynn Pantic IS Tech Support lynn@istechsupport.com IP Logged

HB Full Member I love DBA Posts: 216 Gender:	Re: APPEND QUERY inserts multiple lines into DB Reply #4 - 05/04/11 at 08:21:47 Thanks a lot for your help. I will try your 2nd suggestion with the temp file
Back to top	Helmut Bode General Pencil Co. DBA Classic 2004.1 w/IS Tech update 7/25/13 EVO-ERP Pervasive 11 Win2008 Server Terminal Server 12 Users IP Logged

Kelloggs

Active Member

Do crazy people know
they are crazy?

Posts: 785
Gender: male

Re: APPEND QUERY inserts multiple lines into DB
Reply #5 - 05/04/11 at 08:59:54

Quote from Lynn_Pantic on 05/04/11 at 08:19:54:

Also keep in mind that updating an order this way rather than using the program the way it was designed will not update the item stock status and if the item being added to the order was not a giveaway item, the open order balance and other fields would also not be updated.

yes, but if you "resaved" the order before been release and later invoice. It should work fine right?

embarrassed

Kelloggs

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)

IP Logged

HB Full Member I love DBA Posts: 216 Gender:	Re: APPEND QUERY inserts multiple lines into DB Reply #6 - 05/04/11 at 10:44:52 tried the temp file solution and then append to BKARINVL - still getting 40+ line item additions with the same item number.
Back to top	Helmut Bode General Pencil Co. DBA Classic 2004.1 w/IS Tech update 7/25/13 EVO-ERP Pervasive 11 Win2008 Server Terminal Server 12 Users IP Logged

Kelloggs Active Member Do crazy people know they are crazy? Posts: 785 Gender:	Re: APPEND QUERY inserts multiple lines into DB Reply #7 - 05/04/11 at 12:19:07 I did test it twice. It works OK. But before going any further. I hope you are testing all this on your play company right?? Do not try it on your live data You must be doing something different or you table needs some serious reindexing (BACKUP, BACKUP, BACKUP) did you try the first method? Kelloggs
Back to top	Evo ERP - 35 Users Dumped MS Access like a hot potato (VB.Net rules!!!) IP Logged

HB Full Member I love DBA Posts: 216 Gender:	Re: APPEND QUERY inserts multiple lines into DB Reply #8 - 05/05/11 at 06:28:03 I am still trying to make it work (on a test company ). I am also considering alternatives because of the indications Lynn brought up.
Back to top	Helmut Bode General Pencil Co. DBA Classic 2004.1 w/IS Tech update 7/25/13 EVO-ERP Pervasive 11 Win2008 Server Terminal Server 12 Users IP Logged

Kelloggs Active Member Do crazy people know they are crazy? Posts: 785 Gender:	Re: APPEND QUERY inserts multiple lines into DB Reply #9 - 05/05/11 at 07:55:34 It should work. Let us know if we can help. Kelloggs PD: dont forget that for text and date fields you need to use : ....,'Text','05/02/2011',......
Back to top	Evo ERP - 35 Users Dumped MS Access like a hot potato (VB.Net rules!!!) IP Logged

Pages: 1

ISTech Support Forum › Crystal Reports, ODBC & Access › Access General Issues

‹ Previous topic | Next topic ›