append issues: clear table code?

Michael C via AccessMonster.com

Hello,

I have a table that I am appending 3 seperate tables into. My main problem
is that each time I append the data, it simply adds to the data already there.
That might sound ok, except that if I append the data 3 times in succession,
it copies the same data over 3x. Now I have copies in triplicate. It used
to only transfer records that weren't already there, but not anymore.

If I can't get the append to append correctly, I was thinking of something
crazy like a code that would wipe my append destination table clean each time
before I do the append command. That way, there is only one set of current
data combined from all 3 tables.

Any ideas?

Perry

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200601/1

Jan 6 '06 #1

Wayne Morgan

An Append Query will do exactly what you are mentioning unless you do
something to limit which records are added. If there is a unique identifier
field that gets carried over, you could use an "unmatched query" to find the
records in the source table that don't match records currently in the
destination table. Use the unmatched query as the source of the append
query. If there is a Date/Time field in the records, you may be able to
limit the records to only those records that are newer than the latest
date/time in the destination table.

There are other ways to restrict what gets copied, it will depend on what
your data looks like as to how you'll want to do it.

--
Wayne Morgan
MS Access MVP
"Michael C via AccessMonster.com" <u9916@uwe> wrote in message
news:59f8ec70c5769@uwe...

Hello,

I have a table that I am appending 3 seperate tables into. My main
problem
is that each time I append the data, it simply adds to the data already
there.
That might sound ok, except that if I append the data 3 times in
succession,
it copies the same data over 3x. Now I have copies in triplicate. It
used
to only transfer records that weren't already there, but not anymore.

If I can't get the append to append correctly, I was thinking of something
crazy like a code that would wipe my append destination table clean each
time
before I do the append command. That way, there is only one set of
current
data combined from all 3 tables.

Any ideas?

Perry

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200601/1

Jan 7 '06 #2

Wayne Morgan

PS.

Clearing the source table first then appending can also be a valid option.
However, this can cause a lot of bloat as you delete then re-add the
records. Do you really need this other table or could you create a Union
Query based on the 3 source tables.

--
Wayne Morgan
MS Access MVP

Jan 7 '06 #3

Allen Browne

Perry, I think your question is how to write some code to clear all the data
out of Table1, so you can execute another query statement to append records
again:

Dim db As DAO.Database
Dim strSql As String

Set db = dbEngine(0)(0)
strSql = "DELETE FROM Table1;"
db.Execute strSql, dbFailOnError

strSql = "INSERT INTO Table1 (...
db.Exeucte strSql, dbFailOnError
Set db = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Michael C via AccessMonster.com" <u9916@uwe> wrote in message
news:59f8ec70c5769@uwe...

I have a table that I am appending 3 seperate tables into. My main
problem
is that each time I append the data, it simply adds to the data already
there.
That might sound ok, except that if I append the data 3 times in
succession,
it copies the same data over 3x. Now I have copies in triplicate. It
used
to only transfer records that weren't already there, but not anymore.

If I can't get the append to append correctly, I was thinking of something
crazy like a code that would wipe my append destination table clean each
time
before I do the append command. That way, there is only one set of
current
data combined from all 3 tables.

Any ideas?

Perry

Jan 7 '06 #4

Michael C via AccessMonster.com

All of my source tables originate from the same form, same record, so I went
with your clear table code, which worked great.
I have one question as a result of this. How many lines of data will by
table be able to hold before I will need to worry?

Allen Browne wrote:

Perry, I think your question is how to write some code to clear all the data
out of Table1, so you can execute another query statement to append records
again:

Dim db As DAO.Database
Dim strSql As String

Set db = dbEngine(0)(0)
strSql = "DELETE FROM Table1;"
db.Execute strSql, dbFailOnError

strSql = "INSERT INTO Table1 (...
db.Exeucte strSql, dbFailOnError
Set db = Nothing

I have a table that I am appending 3 seperate tables into. My main
problem

[quoted text clipped - 16 lines]

Perry

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200601/1

Jan 9 '06 #5

Allen Browne

You need not worry unless you foresee millions of records, assuming a
well-designed database.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Michael C via AccessMonster.com" <u9916@uwe> wrote in message
news:5a1ab9f0e8a32@uwe...

I have one question as a result of this. How many lines of data will by
table be able to hold before I will need to worry?

Jan 9 '06 #6