Append records by incrementing a field value from vb6

[the_net_2.0]

All,

I am working in VB6 and I'm running an update query with an "IN" subquery. The update query is updating one field in records that have ID's that are in the temp table. so the query looks like:

UPDATE TABLE SET TABLE.FIELD = TABLE.FIELD - 128 WHERE 
 
(((TABLE.FIELD >= 128 AND TABLE.Ref) In 
 
(SELECT TABLE-temp.ID FROM TABLE-temp)));

After this, I need to append this same record information to a log table. The INSERT INTO statement will be using a combination of variable values and actual field names from the table. It will look like this:

INSERT INTO LOGTABLE (1, 2, 3, 4) 
 
SELECT "value", "value", TABLE.FIELD1, TABLE.FIELD2
 
FROM TABLE

The log table has an ID field in it and each record has to be incremented by one. I don't have the authority to change it to an autonumber, so I'm looking for a way to run a query (or 2?) that will append the records from my 'TABLE' but at the same time incrementing the ID field by one for each of the records appended.

I'm assuming that this can't be done in a batch append process, correct? This is vb6 and I'm trying to think of a method here that will avoid me having to loop recordsets and perform and "addnew" on the Log Table, one record at a time from my temp table.

Thanks for any help guys. Appreciate it.

 

[Guus2005]

so the problem is that the log table does not have a unique identifier? and you want to add it manually?

the way you are suggesting it, using addnew, would be the easiest one. when you need to update 10000 records it might get a little slow.

using an insert query that checks for the highest autonumber and adds one (dmax(...,...)+1) is also slow.

if you save a timestamp, the name of the table and the id's of the records that are changed to the logfile isn't that enough? it is easier to do and a lot fasdtyer.

HTH

 

[the_net_2.0]

if you save a timestamp, the name of the table and the id's of the records that are changed to the logfile isn't that enough? it is easier to do and a lot fasdtyer.

HTH

No, it isn't. We follow rules set forth by the FAA. And this table log is related to one of them. So it still stands that I have to loop the change recs and add them one by one, unless there is a query that can do this all in one shot.

 

[Guus2005]

add this function:

Public Function NewID(strfield As String, strtable As String, vntDummy) As Long

    Static lngX As Long

    If lngX = 0 Then lngX = DMax(strfield, strtable)
    
    lngX = lngX + 1
    NewID = lngX

End Function

run query:

INSERT INTO LOGTABLE (1, 2, 3, 4) 
SELECT newid("id","logtable",[COLOR="Red"][table].[id][/COLOR]), "value", TABLE.FIELD1, TABLE.FIELD2
FROM TABLE

you need to add the red part eventhough the function doesn't do anything with it because the function runs only once if the arguments stay the same. you need to run it every record so you change the arguments.

HTH

 

[the_net_2.0]

you need to run it every record so you change the arguments.

HTH

that's what I'm saying, sir. If your query has to be run once per record, then I might as well stay with ".addnew". It doesn't make any sense loop records and run a query to append when you can just write data like I'm doing now.

 

[Guus2005]

that's what I'm saying, sir. If your query has to be run once per record, then I might as well stay with ".addnew". It doesn't make any sense loop records and run a query to append when you can just write data like I'm doing now.

you are missing the point. i was clarifying how the function works and how and why it needs to be triggered from within the running insert query.

i explained it to you so you can create a similar solution yourself one day. i am always hoping that people learn from the solutions i come up with. i know i do!

just save the function in a module, adjust the insert statement so it fits your database and run the query.

this is why i Share & Enjoy!