All fields

[George Stevenson]

How would you go about accessing all of the fields in a database record with
a single statement so that I could copy the record into another database
without needing to know what fields are defined?

I'm interested in backing up database tables from an ODBC source into a
local table without having to reference each individual field in a record.
I want to be able to reference the entire record at once.

 

[Douglas J. Steele]

SELECT * FROM MyTable will return all fields.

 

[George Stevenson]

and can I reference * to move all fields to a different table?

 

[Douglas J. Steele]

INSERT INTO Table2
SELECT * FROM Table1

assuming Table2 has the exact same columns as Table1.

If what you're looking for is a generic ability to do this, there is no
silver bullet. You'll need to ensure that the two databases are set up
consistently.

 

[Peter R. Fletcher]

If you really want to do it "without needing to know what fields are
defined", the approach that Douglas outlined wil not help you - as he
notes, it only works when the logical structures ot the source and
destination tables are identical (which implies that they are known!),
and even then you will have problems if there is an Autonumber field
in the Tables. To do literally what you are asking requires that you
programmatically create a new Table and add a field corresponding to
each field in the old table (you can do this "automatically" with a
For Each fld in tblOld .... Loop structure). Only after doing that can
you populate the new Table. There is probably an easier way of doing
what you ultimately want to do.

How would you go about accessing all of the fields in a database record with
a single statement so that I could copy the record into another database
without needing to know what fields are defined?

I'm interested in backing up database tables from an ODBC source into a
local table without having to reference each individual field in a record.
I want to be able to reference the entire record at once.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher