M
michael.p.logue.42
OK. This is a really weird problem that I've never encountered
before, and can't seem to figure out what's happening. Here's the
scenario.
Two tables (one front end, one backend) with exactly the same data
fields. Frontend table is temporary, and at a certain stage this data
is appended to the backend duplicate. Pretty straight forward.
However, here's the problem.
I'm using a straightforward SQL statement in code:
Dim txtSQL
txtSQL = "INSERT INTO tblLeads SELECT tblLeadsTmp.* FROM tblLeadsTmp
DoCmd.RunSQL txtSQL
Every thing worked fine, until I noticed that one of my date fields
wasn't carrying over. Double checked everything, and found nothing
wrong with the tables. Tried re-naming the offending field in both
tables, but still no joy.
So then I created a standard Append query, and ran that from code
instead:
DoCmd.OpenQuery "qyrUpdLeads"
Same problem - the same date field was not carrying over.
However, when I run the Append query manually, the date field is
appended to the backend table as it should.
I've tried running the sql statement and saved query by listing every
field (no wildcard), and still the same problem. It'll transfer the
field correctly if I do it manually, but gets dropped when run by
code.
I've also done a Compact/Repair on both databases with no change in
the error.
Any Idea what's going on here? I'm at a complete loss.....
before, and can't seem to figure out what's happening. Here's the
scenario.
Two tables (one front end, one backend) with exactly the same data
fields. Frontend table is temporary, and at a certain stage this data
is appended to the backend duplicate. Pretty straight forward.
However, here's the problem.
I'm using a straightforward SQL statement in code:
Dim txtSQL
txtSQL = "INSERT INTO tblLeads SELECT tblLeadsTmp.* FROM tblLeadsTmp
DoCmd.RunSQL txtSQL
Every thing worked fine, until I noticed that one of my date fields
wasn't carrying over. Double checked everything, and found nothing
wrong with the tables. Tried re-naming the offending field in both
tables, but still no joy.
So then I created a standard Append query, and ran that from code
instead:
DoCmd.OpenQuery "qyrUpdLeads"
Same problem - the same date field was not carrying over.
However, when I run the Append query manually, the date field is
appended to the backend table as it should.
I've tried running the sql statement and saved query by listing every
field (no wildcard), and still the same problem. It'll transfer the
field correctly if I do it manually, but gets dropped when run by
code.
I've also done a Compact/Repair on both databases with no change in
the error.
Any Idea what's going on here? I'm at a complete loss.....