Import Table convert text to numeric

[brendanolear]

I receive an external server feed Table direct to access, the Value field from this feed is in text format. I therefore wish to copy this feed into a new table within DB and convert the value field to numeric. The new table however is overwritten with the text format on each refresh. What is the simplest way to create a new table that replicates the feed table but auto updates the value field from text to numeric? Thanks

 

[JonXL]

Maybe a query on the lines of this?

INSERT INTO 
   [newTable] 
   ([PK], [numberField])
   SELECT 
       [PK], cDbl([textField])
   FROM
       [serverTable] 
   WHERE
       [serverTable].[PK] NOT IN 
           (
               SELECT [PK] FROM [newTable]
           )

Test on a copy of your DB.

 

[ranman256]

you can use the existing table with an extra numeric field.
then run update query to add the str field to the num field.