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.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200601/1

[Wayne Morgan]

Re: append issues: clear table code?

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.c om" <u9916@uwe> wrote in message
news:59f8ec70c5 769@uwe...[color=blue]
> 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.c om
> http://www.accessmonster.com/Uwe/For...ccess/200601/1[/color]

[Wayne Morgan]

Re: append issues: clear table code?

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

[Allen Browne]

Re: append issues: clear table code?

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.c om" <u9916@uwe> wrote in message
news:59f8ec70c5 769@uwe...[color=blue]
>
> 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[/color]

[Michael C via AccessMonster.com]

Re: append issues: clear table code?

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:[color=blue]
>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
>[color=green]
>> I have a table that I am appending 3 seperate tables into. My main
>> problem[/color]
>[quoted text clipped - 16 lines][color=green]
>>
>> Perry[/color][/color]

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200601/1

[Allen Browne]

Re: append issues: clear table code?

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.c om" <u9916@uwe> wrote in message
news:5a1ab9f0e8 a32@uwe...[color=blue]
> 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?[/color]