Hi,
In order to update existing records, you need to sue a Update query instead
of an Append query
The generic SQL syntax for an update query is
Update tblonlinereg set yourfield = <your new value> where <some condition>
But with Update query, you cannot update at once a table (all or several
records) with values from several records from another table (for example to
update existing records from tblonlinereg with values from text file at
once)
Instead, you have to use VBA code to loop through all existing records, and
for each of them run the Update query
dim db as DAO.Database
dim rs DAO.Recordset
set db = Curentdb
set rs = db.openrecordset("select * from <your_temp_import_table> where
TempID in (select tempID from tblonlinereg)")
if rs.recordcount >0 then
do while not rs.eof
db.execute "Update tblonlinereg set yourfield1 = <the new value>,
yourfield2 = <the new value>, etc where tempID = " & rs!tempID)
rs.MoveNext
loop
endif
Hope this helps.
If you have any other questions, you can contact me back
Regards,
Bogdan
"JMCN" <pi******@yahoo.fr> wrote in message
news:27**************************@posting.google.c om...
hi
i have a general question regarding append queries in access 97. each
week i need to update my table(tblonlinereg) with new or modified
records. firstly, i import the text file into my database and then i
create a basic append query that appends the new records to the
table(tblonlinereg). this works great if the [tempID] field is
greater than the last record appended ( where: tempID > 198 ) but how
would i update/append the modifications for [datepaid] or [address]
for records that are less than 198. say if there is a change of
[address] for tempID 118? i have looked at the other discussions
strings on google and ms support site but i still cannot find how to
append new and updated records to my table(tblonlinereg).
any thoughts?
thanks in advance -jung