Export table with data type changes

[Guest]

Rather than second guess a solution perhaps I should explain my problem. I have inherited a database that has been rather lazily set up. There are memo fields where a text field would have sufficed, 255 char text fields when 40 chars would have done.

It is not a problem really when on the desktop and the db's sitting on a server but I now also want to transfer it to a iPaq PPC. I would like to have some way I can write from the existing table to a new table which has more sensible data types (which I can then transfer to my PPC).

Can this be done so that I can periodically refresh the copy of the "master" table? (A "make table" query doesn't appear to have any control of the data types for instance).

TIA

 

[Graham Mandeno]

Hi Keith

You can set up the destination table as required and then use an append
query to add records to it from the source table. Data type conversions
will happen automatically, provided they are possible.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Rather than second guess a solution perhaps I should explain my problem. I

have inherited a database that has been rather lazily set up. There are memo
fields where a text field would have sufficed, 255 char text fields when 40
chars would have done.

It is not a problem really when on the desktop and the db's sitting on a

server but I now also want to transfer it to a iPaq PPC. I would like to
have some way I can write from the existing table to a new table which has
more sensible data types (which I can then transfer to my PPC).

Can this be done so that I can periodically refresh the copy of the

"master" table? (A "make table" query doesn't appear to have any control of
the data types for instance).

 

[Guest]

Hi Keith

You can set up the destination table as required and then use an append
query to add records to it from the source table. Data type conversions
will happen automatically, provided they are possible.

Thanks Graham

I'll give it a go. Will the Append deal with deletions and/or alterations of existing records?

Ta again
Keith (also in NZ).

 

[Guest]

Thanks Graham

I'll give it a go. Will the Append deal with deletions and/or alterations of existing records?

Answering my own question: No, but it's fairly easy just to empty the target table before running the Append query

 

[Graham Mandeno]

Hi again Keith
alterations of existing records?

Answering my own question: No, but it's fairly easy just to empty the

target table before running the Append query

Yes, that's the easiest way. If for some reason you can't do that (eg
records in a related table), then you can use an update query to do the
alterations and a delete unmatched query (WHERE not in (Select...)) to do
the deletions. In this case your append query would also need to be only
unmatched records.