Append Query Fails

S

Sash

I'm trying to run the following append query. The table Carroll contains 238
records. Each time I run the query it's appending a different number of
records and seems to be very random. 183, 210, 192..... I'm running it from
a button as follows:

Dim stQuery4 as String
stQuery4 = "CarrollAppend"
DoCmd.OpenQuery stQuery4
DoCmd.Close acQuery, stQuery4

And here's the SQL....I'm really pulling my hair out on this and of course
my client is calling me every 5 minutes. Thanks for any help!!!

INSERT INTO CarrollMain ( ACCTBAL, LASTPMTDATE, CS66_PTLPD, ORIG_BALANCE,
ADMDATE, DISCHDATE, PTTYPE, FINCLASS, ATTPHYS, ADMDIAG, PRIN_DIAG, PTNUM,
PTNAME, PTADDR1, PTADDR2, PTCITY, PTSTATE, PTZIP, PTSSN, PTMARITAL, PTSEX,
PTDOB, PTPHONE, MRN, GNAME, GADDR1, GADDR2, GCITY, GSTATE, GZIP, GSSN, GDOB,
GPHONE, GEMP, GEMPADDR1, GEMPCITY, GEMPSTATE, GEMPZIP, GEMPPHONE, PTEMP,
PTEMPADDR1, PTEMPADDR2, PTEMPCITY, PTEMPSTATE, PTEMPZIP, PTEMPEXT, REL_ADDR1,
REL_ADDR2, REL_CITY, REL_STATE, REL_ZIP, REL_PHONE, RELATION, REL_EMP_PHONE,
PT_LNAME, PT_FNAME, G_FNAME, G_LNAME, REL_FNAME, REL_LNAME, G_MI, PT_MI,
REL_MI )
SELECT Carroll.ACCTBALANCE, Carroll.LASTPAYDATE, Carroll.PTLASTPAYDATE,
Carroll.TOTALCHARGES, Carroll.ADMDATE, Carroll.DISCHDATE, Carroll.PTTYPE,
Carroll.FINCLASS, Carroll.ATTENDPHYS, Carroll.ADMDIAG, Carroll.PRINDIAG,
Carroll.PTNUM, Carroll.PTNAME, Carroll.PTADDR1, Carroll.PTADDR2,
Carroll.PTCITY, Carroll.PTSTATE, Carroll.PTZIP, Carroll.PTSSN,
Carroll.PTMARITAL, Carroll.PTSEX, Carroll.PTDOB, Carroll.PTHPHONE,
Carroll.MRN, Carroll.GNAME, Carroll.GADDR1, Carroll.GADDR2, Carroll.GCITY,
Carroll.GSTATE, Carroll.GZIP, Carroll.GSSN, Carroll.GDOB, Carroll.GHPHONE,
Carroll.GEMP, [GEMPADDR1] & " " & [GEMPADDR2] AS EXPR1, Carroll.GEMPCITY,
Carroll.GEMPSTATE, Carroll.GEMPZIP, [GEMPPHONE] & " " & [GEMPEXT] AS EXPR3,
Carroll.PTEMP, Carroll.PTEMPADDR1, Carroll.PTEMPADDR2, Carroll.PTEMPCITY,
Carroll.PTEMPSTATE, Carroll.PTEMPZIP, Carroll.PTEMPEXT, Carroll.RELADDR1,
Carroll.RELADDR2, Carroll.RELCITY, Carroll.RELSTATE, Carroll.RELZIP,
Carroll.RELPHONE, Carroll.RELDESCRIP, Carroll.RELWPHONE, Carroll.PTLNAME,
Carroll.PTFNAME, Carroll.GFNAME, Carroll.GLNAME, Carroll.RFNAM,
Carroll.RLNAM, Carroll.GMI, Carroll.PTMI, Carroll.RMI
FROM Carroll;
 
J

John Spencer

I see nothing in the SQl of the query that would cause the problem.

I would use slightly different code.

Dim db as DAO.Database
Set db = Currentdb()
db.execute "CarrollAppend", dbFailOnError
MsgBox db.RecordsAffected & " records appended to CarrollMain"

If you manually run the query does it tell you it is about to append X number
of records? Do you get any messages about not being able to append due to key
violations, etc?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I'm trying to run the following append query. The table Carroll contains 238
records. Each time I run the query it's appending a different number of
records and seems to be very random. 183, 210, 192..... I'm running it from
a button as follows:

Dim stQuery4 as String
stQuery4 = "CarrollAppend"
DoCmd.OpenQuery stQuery4
DoCmd.Close acQuery, stQuery4

And here's the SQL....I'm really pulling my hair out on this and of course
my client is calling me every 5 minutes. Thanks for any help!!!

INSERT INTO CarrollMain ( ACCTBAL, LASTPMTDATE, CS66_PTLPD, ORIG_BALANCE,
ADMDATE, DISCHDATE, PTTYPE, FINCLASS, ATTPHYS, ADMDIAG, PRIN_DIAG, PTNUM,
PTNAME, PTADDR1, PTADDR2, PTCITY, PTSTATE, PTZIP, PTSSN, PTMARITAL, PTSEX,
PTDOB, PTPHONE, MRN, GNAME, GADDR1, GADDR2, GCITY, GSTATE, GZIP, GSSN, GDOB,
GPHONE, GEMP, GEMPADDR1, GEMPCITY, GEMPSTATE, GEMPZIP, GEMPPHONE, PTEMP,
PTEMPADDR1, PTEMPADDR2, PTEMPCITY, PTEMPSTATE, PTEMPZIP, PTEMPEXT, REL_ADDR1,
REL_ADDR2, REL_CITY, REL_STATE, REL_ZIP, REL_PHONE, RELATION, REL_EMP_PHONE,
PT_LNAME, PT_FNAME, G_FNAME, G_LNAME, REL_FNAME, REL_LNAME, G_MI, PT_MI,
REL_MI )
SELECT Carroll.ACCTBALANCE, Carroll.LASTPAYDATE, Carroll.PTLASTPAYDATE,
Carroll.TOTALCHARGES, Carroll.ADMDATE, Carroll.DISCHDATE, Carroll.PTTYPE,
Carroll.FINCLASS, Carroll.ATTENDPHYS, Carroll.ADMDIAG, Carroll.PRINDIAG,
Carroll.PTNUM, Carroll.PTNAME, Carroll.PTADDR1, Carroll.PTADDR2,
Carroll.PTCITY, Carroll.PTSTATE, Carroll.PTZIP, Carroll.PTSSN,
Carroll.PTMARITAL, Carroll.PTSEX, Carroll.PTDOB, Carroll.PTHPHONE,
Carroll.MRN, Carroll.GNAME, Carroll.GADDR1, Carroll.GADDR2, Carroll.GCITY,
Carroll.GSTATE, Carroll.GZIP, Carroll.GSSN, Carroll.GDOB, Carroll.GHPHONE,
Carroll.GEMP, [GEMPADDR1] & " " & [GEMPADDR2] AS EXPR1, Carroll.GEMPCITY,
Carroll.GEMPSTATE, Carroll.GEMPZIP, [GEMPPHONE] & " " & [GEMPEXT] AS EXPR3,
Carroll.PTEMP, Carroll.PTEMPADDR1, Carroll.PTEMPADDR2, Carroll.PTEMPCITY,
Carroll.PTEMPSTATE, Carroll.PTEMPZIP, Carroll.PTEMPEXT, Carroll.RELADDR1,
Carroll.RELADDR2, Carroll.RELCITY, Carroll.RELSTATE, Carroll.RELZIP,
Carroll.RELPHONE, Carroll.RELDESCRIP, Carroll.RELWPHONE, Carroll.PTLNAME,
Carroll.PTFNAME, Carroll.GFNAME, Carroll.GLNAME, Carroll.RFNAM,
Carroll.RLNAM, Carroll.GMI, Carroll.PTMI, Carroll.RMI
FROM Carroll;
Click to expand...
 
S

Sash

That's what is so frustrating. When I run it manually it appends all the
records. I tried

CurrentDb.Execute stQuery4, dbFailOnError

and one time it appended 236 (the correct #) and the very next time it
appended 219. I'll try your code next.

John Spencer said:
I see nothing in the SQl of the query that would cause the problem.

I would use slightly different code.

Dim db as DAO.Database
Set db = Currentdb()
db.execute "CarrollAppend", dbFailOnError
MsgBox db.RecordsAffected & " records appended to CarrollMain"

If you manually run the query does it tell you it is about to append X number
of records? Do you get any messages about not being able to append due to key
violations, etc?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I'm trying to run the following append query. The table Carroll contains 238
records. Each time I run the query it's appending a different number of
records and seems to be very random. 183, 210, 192..... I'm running it from
a button as follows:

Dim stQuery4 as String
stQuery4 = "CarrollAppend"
DoCmd.OpenQuery stQuery4
DoCmd.Close acQuery, stQuery4

And here's the SQL....I'm really pulling my hair out on this and of course
my client is calling me every 5 minutes. Thanks for any help!!!

INSERT INTO CarrollMain ( ACCTBAL, LASTPMTDATE, CS66_PTLPD, ORIG_BALANCE,
ADMDATE, DISCHDATE, PTTYPE, FINCLASS, ATTPHYS, ADMDIAG, PRIN_DIAG, PTNUM,
PTNAME, PTADDR1, PTADDR2, PTCITY, PTSTATE, PTZIP, PTSSN, PTMARITAL, PTSEX,
PTDOB, PTPHONE, MRN, GNAME, GADDR1, GADDR2, GCITY, GSTATE, GZIP, GSSN, GDOB,
GPHONE, GEMP, GEMPADDR1, GEMPCITY, GEMPSTATE, GEMPZIP, GEMPPHONE, PTEMP,
PTEMPADDR1, PTEMPADDR2, PTEMPCITY, PTEMPSTATE, PTEMPZIP, PTEMPEXT, REL_ADDR1,
REL_ADDR2, REL_CITY, REL_STATE, REL_ZIP, REL_PHONE, RELATION, REL_EMP_PHONE,
PT_LNAME, PT_FNAME, G_FNAME, G_LNAME, REL_FNAME, REL_LNAME, G_MI, PT_MI,
REL_MI )
SELECT Carroll.ACCTBALANCE, Carroll.LASTPAYDATE, Carroll.PTLASTPAYDATE,
Carroll.TOTALCHARGES, Carroll.ADMDATE, Carroll.DISCHDATE, Carroll.PTTYPE,
Carroll.FINCLASS, Carroll.ATTENDPHYS, Carroll.ADMDIAG, Carroll.PRINDIAG,
Carroll.PTNUM, Carroll.PTNAME, Carroll.PTADDR1, Carroll.PTADDR2,
Carroll.PTCITY, Carroll.PTSTATE, Carroll.PTZIP, Carroll.PTSSN,
Carroll.PTMARITAL, Carroll.PTSEX, Carroll.PTDOB, Carroll.PTHPHONE,
Carroll.MRN, Carroll.GNAME, Carroll.GADDR1, Carroll.GADDR2, Carroll.GCITY,
Carroll.GSTATE, Carroll.GZIP, Carroll.GSSN, Carroll.GDOB, Carroll.GHPHONE,
Carroll.GEMP, [GEMPADDR1] & " " & [GEMPADDR2] AS EXPR1, Carroll.GEMPCITY,
Carroll.GEMPSTATE, Carroll.GEMPZIP, [GEMPPHONE] & " " & [GEMPEXT] AS EXPR3,
Carroll.PTEMP, Carroll.PTEMPADDR1, Carroll.PTEMPADDR2, Carroll.PTEMPCITY,
Carroll.PTEMPSTATE, Carroll.PTEMPZIP, Carroll.PTEMPEXT, Carroll.RELADDR1,
Carroll.RELADDR2, Carroll.RELCITY, Carroll.RELSTATE, Carroll.RELZIP,
Carroll.RELPHONE, Carroll.RELDESCRIP, Carroll.RELWPHONE, Carroll.PTLNAME,
Carroll.PTFNAME, Carroll.GFNAME, Carroll.GLNAME, Carroll.RFNAM,
Carroll.RLNAM, Carroll.GMI, Carroll.PTMI, Carroll.RMI
FROM Carroll;
Click to expand...
.
Click to expand...
 
Top