append query

[JMCN]

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

[Bogdan Zamfir]

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

[JMCN]

Hi Bogdan,
Thanks for the help! I started to work with the code but I was
wondering how one would identify the change of dates? for instance,
the [updated] will either be a different date or remains the same. how
would you write "is not equal to" condition? oh finally i forgot to
indicate that i'm using access 2002 version.

thanks again!! jung

for example:

Function UpdateRegistration()
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = Curentdb
Set rst = db.openrecordset("SELECT * FROM tblConvention WHERE
[updated] in (SELECT [updated] FROM tblonlinereg)")
If rs.RecordCount > 0 Then
Do While Not rst.EOF
'update the record (including all fields) if the
tblConvention![updated] field is not equal to tblonlinereg![updated]
db.Execute "UPDATE tblonlinereg SET updated <> updated", WHERE
updated <> " & rst!updated);
rst.MoveNext
Loop
End If
End Function

*************************************************

"Bogdan Zamfir" <bz*****@despammed.com> wrote in message news:<bs************@ID-218369.news.uni-berlin.de>...

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