473,836 Members | 2,303 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

append issues: clear table code?

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
Jan 6 '06 #1
5 5385
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...
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

Jan 7 '06 #2
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
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...

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
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.c om
http://www.accessmonster.com/Uwe/For...ccess/200601/1
Jan 9 '06 #5
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...
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3084
by: PMB | last post by:
Thank you in advance for any and all assistance. I'm trying to use a make table query to pull the last transactionID, so I can use an append query to reset the transactionID to the next sequential number. My problem is, the make table query is taking all the TransactionID's and putting them in the new table. Is there a way to take the last transactionID only and put it in the new table? So this way, when I clear the old Transactions,...
13
4234
by: Lee | last post by:
Hello All, First of all I would like to say thank you for all of the help I have received here. I have been teaching myself Access for about 4 years now and I've always been able to find a solution here - until now. This one is driving me crazy. I am making my first attempt at creating a runtime application. I am using Access 2003 Developer Extensions. Initially I developed the database without planning on creating a runtime app...
5
2256
by: solar | last post by:
I have copied a function that appends from table orders2 into table orders1 the row that has the value SubOrder = True in the table orders2. This function finds the highest ordered in the table orders2. But I want to find the highest ordered in the table orders2 instead. In short, I want to append the order from the table orders2 into the table orders1 and this order to get the next highest ordered.Somehow I canot manage it.Can you help...
6
5797
by: deejayquai | last post by:
Hi I'm attempting to append multiple values into a new record, using multiple criteria from a listbox. I've got the basics for the code below but I get an "Error 3085 Undefined Function" for the Dlookup part of it. Dim db As Database Dim rec As DAO.Recordset
6
1573
by: Christian Maier | last post by:
Hello! I have huge unperformant "where like" querys to a mysql DB. So there are some performance issues. To resolve my performance problem I thougt to split the query into threads. I hold 10 connections to my database and each connection queries in its own thread as the following: conn1: select fname from root where p_key between 1 and 20000 fname like '%somestring%'; conn2: select fname from root where p_key between 20001 and 40000...
3
3393
by: Darin | last post by:
This is something that on the surface seems like it should be simple, but I can't think of a way to do this. I have a table that is a list of "jobs", which users create and use. It has a single autonumber key field. Within a job, there can be multiple orders. Logically, there'd be an orders table that is a child of the job table. This orders table has a long integer field that matches it to the master job autonumber field. ...
0
1704
by: jon | last post by:
Hi there, I'm brand new to Access and may be trying to do too much too soon, but I wanted to get some expert advice on how the best way to go about what I am trying to accomplish would be. I am trying to modify the Microsoft Template at the following address (http://office.microsoft.com/en-us/templates/TC012186931033.aspx?CategoryID=CT101426031033) to work as an issues tracker that imports and updates the issues from a SQL 2005...
3
8092
by: wvmbark | last post by:
First time poster... I just found this forum and it appears there's plenty of people here that could make short work of problem that's been driving me absolutely bonkers for months. Every day we incur numerous service problems “Events”. Each morning we have a global conference call where events which occurred within the previous 24 hours are discussed. Prior to the call, an analyst has to review these events and provide a report, ‘The Morning...
2
1886
by: Cara Murphy | last post by:
Hi, I am looking to use a button on an issues database that will allow me to archive issues that are closed, once all of the information has been updated. In this sense, I want the issue to be available for final input after it has been "closed", and use a command button to "delete" it (append the record to a table called ArchiveIssues and remove it from the table called Issues). Can you please advise what my options are to facilitate the...
0
9812
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9657
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10823
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10532
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10243
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6975
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5642
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4443
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4003
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.