How to Create a Writeable Temp Copy of a Table, Then Add the Recs to Temp, & Write to Orig

  • Thread starter Thread starter Mike Thomas
  • Start date Start date
M

Mike Thomas

I have a situation where I need to:

1. Make a blank copy of a table
2. Add and populate the records one at a time to the copy
3. Update the original
4. Append the records from the copy to the orig

#1 Is there a way to do this with an SQL statement, bringing along the field
definitions, but not properties such as auto numbering?

2. No problem, rst.AddNew and rst.Update are fine. There is a lot of data
manipulation with each record.

3. No problem, that's just an SQL statement.

4. What is the best way to append records from one table to another where
both tables have the same structure? I find the syntax of the INSERT INTO
statement awkward when the tables have a lot of fields and those fields are
different types. Is there a mechanism , or a strategy, in Access to
simplify this.

Many thanks
Mike Thomas
 
Mike

1 Yup Access supports Data Definition Queries that can Create, Alter, and
Delete Tables. Here is an example of a Data Definition Query that creates a
new empty table.

CREATE Table MyTable (
MyID AutoIncrement CONSTRAINT MyIdConstraint PRIMARY KEY,
MyText CHAR(50),
MyNum LONG,
MyDecimal DOUBLE,
MyDate DATETIME
)

Here is the sql for a Data Definition Query that removes the table

DROP Table MyTable

2 Depending what the source for the new records is, opening a recordset and
appending them one at a time might be the only way, but if the records are
in a container that access can attach to (i.e. Sql Server, Excel, Etc.) then
I would write an Append Query. An Append Query will typically out perform
any kind Open RecordSet and loop by an order of magnitude.

3 Yup Update Query

4 There are a basically two different kinds of Append queries.

INSERT INTO MyTable (ThisField, ThatField) VALUES (ThisVar, ThatVar)

which will insert one record at a time into the Table MyTable. Another type
is

INSERT INTO MyTable (ThisColumn, ThatColumn)
SELECT SomeColumn, SomeOtherColumn
FROM YourOtherTable
WHERE YourOtherTable.SomeColumn > SomeValue

will insert all of the records from YourOtherTable into MyTable where the
condition(s) in the WHERE clause are met.

You do need to be aware of your data types but you can usually convert these
values on the fly in the query. If you have an example that is giving you
problems feel free to post it here. Be sure to include all of the relevant
information like the source and destination table definition, some test
data, and what you expect as the result. I am confident that some one here
will have a solution.

Ron W
 
Ron,

Many thanks for your help. It's caused me to take a closer look at the
query builder.

Mike Thomas
 
Back
Top