data type problems w/ imported data

[jojomore]

Hello,
I have a csv file that contains new records that I need to update an existing table in an Access 2000 database that I have inherited. I want to automate the process, these are employee records and the csv file is updated weekly. I want to import the data then run some queries to update an existing table.

However, the data types do not match. I cannot change the datatypes in the existing table because all of the coding of the DB's forms were developed in VBA using text fields. The csv file outputs fields as they should be, ie date/time, number, etc. I am not a VBA programmer and get lost very quickly.

So far I have set up a macro to 1) delete last week's update table, 2)import the csv file creating a new table and 3)alter the column types. I still need to create some queries that compare the imported data to existing, add new records and update others.

My question is, THERE HAS TO BE AN EASIER WAY! It took me 3 hours to figure out how to get the SQL ALTER statements to work! (I can only update one column at a time using the macro RunSQL command) Can anyone offer any suggestions? Tell me what I am doing wrong??

 

[rac2]

Access provides some functions that convert and format data. Typically these are used in SELECT statements to change the way data is displayed when it is retrieved from a table. It might be easier to develop a procedure using these functions to transform the data.

First import the data from the CSV file into a temporary table. Then develop an Append Query to transform the data and add the new rows to the existing table.

INSERT finalTable (text_col_A, text_col_B)
SELECT Left([ProductName], 1), IIf(IsNull([Region]), [City] & " " & [PostalCode], [City] & " " & [Region] & " " & [PostalCode])
FROM tempImportTable

This would add rows to the finalTable with the initial character of the imported ProductName in text_col_A and either the city and postal code or the city, region, postal code in text_col_B. See the Build dialog in Query Design View for conversion functions.