Help Access Append Query

[antoniorossano]

Hy I'm Antonio from Italy. I need help to create a query (SQL) in MS Access
2003 that appends data in two identical tables named "tblUsers" stored, the
first (A) on local database logon.mdb and the second in another local
database (B) stored in "E:\utenti\logon.mdb", but i need to append only the
new records of database A that doesn't exist in database B. Someone cab help
me with the exact expression and grammar to do this?
many thanks everybody !! ;-)

 

[Jeff Boyce]

If you have two copies (in two databases) of the same information, and if
someone changes (just) one of those records in one of the databases, which
one has the correct value?

What about the idea of storing it once, then "linking" to that one table
from any other Access databases that need to "use" the data?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[antoniorossano]

If you have two copies (in two databases) of the same information, and if
someone changes (just) one of those records in one of the databases, which
one has the correct value?

not exactly: one is general and has all fields (20).
the other is for particular use and there are only (12) fields, but has two
more different fields.
I need also that only changes on the first are replicate to the second and
not back, so the link cannot help me; all that i need is the correct sql
query !!!
Thank you very much for the answer!!
Antonio

 

[Jeff Boyce]

So, once the "copy" is made, you don't care if some of the common fields'
values are different? I'd still worry about which db holds the correct
values (but I worry...).

There's nothing about a link that prevents one db from seeing the other db's
table(s) ... that's what they're for.

It sounds like you're determined to do it the way you've decided. Hopefully
someone else here in the newsgroup can help -- I haven't done what you're
describing, for the reasons noted.

Good luck!


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[antoniorossano]

the two databases names is the same: logon.mdb
the two tables names are the same: tblUsers.
this is the query i wrote, but it doesn0t work: what is bad?
The query, without the clause WHERE, works fine. but with that clause makes
an error "incorrect database name" (strange). But without that clause it
still append all records and not only tha are new in he master.

INSERT INTO tblUsers ( UID, PWD, COMPARTIMENTO, UFFDOGANE, COGNOME, NOME,
INDIRIZZO, CAP, COMUNE, PROVINCIA, TELEFONO, CELLULARE, TELEFAX, EMAIL, WWW,
ID, ADMIN, ENABLED, YEARENABLEDDATE, MONTHENABLEDDATE, DAYENABLEDDATE,
COD_Questionario ) IN 'E:\Documenti Antonio\Siti Web\CoNaSpDo\FAD\FAD\
riservato001\_private\logon.mdb'
SELECT tblUsers.UID, tblUsers.PWD, tblUsers.COMPARTIMENTO, tblUsers.UFFDOGANE,
tblUsers.COGNOME, tblUsers.NOME, tblUsers.INDIRIZZO, tblUsers.CAP, tblUsers.
COMUNE, tblUsers.PROVINCIA, tblUsers.TELEFONO, tblUsers.CELLULARE, tblUsers.
TELEFAX, tblUsers.EMAIL, tblUsers.WWW, tblUsers.ID, tblUsers.ADMIN, tblUsers.
ENABLED, tblUsers.YEARENABLEDDATE, tblUsers.MONTHENABLEDDATE, tblUsers.
DAYENABLEDDATE, tblUsers.COD_Questionario
FROM tblUsers
WHERE tblUsers.UID<>[E:\Documenti Antonio\Siti Web\CoNaSpDo\FAD\FAD\
riservato001\_private\logon.mdb].[tblUsers.UID];

 

[Piet Linden]

the two databases names is the same: logon.mdb
the two tables names are the same: tblUsers.
this is the query i wrote, but it doesn0t work: what is bad?
The query, without the clause WHERE, works fine. but with that clause makes
an error "incorrect database name" (strange). But without that clause it
still append all records and not only tha are new in he master.

INSERT INTO tblUsers ( UID, PWD, COMPARTIMENTO, UFFDOGANE, COGNOME, NOME,
INDIRIZZO, CAP, COMUNE, PROVINCIA, TELEFONO, CELLULARE, TELEFAX, EMAIL, WWW,
ID, ADMIN, ENABLED, YEARENABLEDDATE, MONTHENABLEDDATE, DAYENABLEDDATE,
COD_Questionario ) IN 'E:\Documenti Antonio\Siti Web\CoNaSpDo\FAD\FAD\
riservato001\_private\logon.mdb'
SELECT tblUsers.UID, tblUsers.PWD, tblUsers.COMPARTIMENTO, tblUsers.UFFDOGANE,
tblUsers.COGNOME, tblUsers.NOME, tblUsers.INDIRIZZO, tblUsers.CAP, tblUsers.
COMUNE, tblUsers.PROVINCIA, tblUsers.TELEFONO, tblUsers.CELLULARE, tblUsers.
TELEFAX, tblUsers.EMAIL, tblUsers.WWW, tblUsers.ID, tblUsers.ADMIN, tblUsers.
ENABLED, tblUsers.YEARENABLEDDATE, tblUsers.MONTHENABLEDDATE, tblUsers.
DAYENABLEDDATE, tblUsers.COD_Questionario
FROM tblUsers
WHERE tblUsers.UID<>[E:\Documenti Antonio\Siti Web\CoNaSpDo\FAD\FAD\
riservato001\_private\logon.mdb].[tblUsers.UID];

What happens if you break down and create a linked table and then
create your query? Does it work?

 

[Krzysztof Naworyta]

antoniorossano wrote:
| the two databases names is the same: logon.mdb
| the two tables names are the same: tblUsers.
| this is the query i wrote, but it doesn0t work: what is bad?
| The query, without the clause WHERE, works fine. but with that clause
| makes an error "incorrect database name" (strange). But without that
| clause it still append all records and not only tha are new in he
| master.
|
| INSERT INTO tblUsers ( UID, PWD, COMPARTIMENTO, UFFDOGANE, COGNOME,
| NOME, INDIRIZZO, CAP, COMUNE, PROVINCIA, TELEFONO, CELLULARE,
| TELEFAX, EMAIL, WWW, ID, ADMIN, ENABLED, YEARENABLEDDATE,
| MONTHENABLEDDATE, DAYENABLEDDATE, COD_Questionario ) IN 'E:\Documenti
| Antonio\Siti Web\CoNaSpDo\FAD\FAD\ riservato001\_private\logon.mdb'
| SELECT tblUsers.UID, tblUsers.PWD, tblUsers.COMPARTIMENTO,
| tblUsers.UFFDOGANE, tblUsers.COGNOME, tblUsers.NOME,
| tblUsers.INDIRIZZO, tblUsers.CAP, tblUsers. COMUNE,
| tblUsers.PROVINCIA, tblUsers.TELEFONO, tblUsers.CELLULARE, tblUsers.
| TELEFAX, tblUsers.EMAIL, tblUsers.WWW, tblUsers.ID, tblUsers.ADMIN,
| tblUsers. ENABLED, tblUsers.YEARENABLEDDATE,
| tblUsers.MONTHENABLEDDATE, tblUsers. DAYENABLEDDATE,
| tblUsers.COD_Questionario
| FROM tblUsers
| WHERE tblUsers.UID<>[E:\Documenti Antonio\Siti Web\CoNaSpDo\FAD\FAD\
| riservato001\_private\logon.mdb].[tblUsers.UID];


(...)

WHERE
Not exists (select * from [E:\Documenti Antonio\Siti
Web\CoNaSpDo\FAD\FAD\riservato001\_private\logon.mdb].[tblUsers] as x
where x.UID = tblUsers.UID)