I am working with Access 2000 and Excel 2000 and am trying to set up an access table linked to an excel worksheet.

My problem is that access dosen't seem to get the field formats correctly.

In the excel worksheet we are using the following formats for data:

General
Number
Text
Date

When I link to the worksheet I end up with the following formats in the table:

Number
Number
Number
Date

When I try to correct the errors it says that access can not save the changes in a linked table.

How can I create a text field in this situation?

Thanks

Access doesn't use the Excel defined formats to determine a column format. It uses the actual values stored in the first n rows. I'm not sure how many n is but probably around 30. So if the first 30 rows are long integer, Access assumes that that should be the data type. If subsequent rows are single or double precision, data will be lost. Don't be too down on Access because of this behaviour. Spreadsheets are quite different from tables even though they look alike. Access knows that despite what a column's datatype is officially set to, the column can contain data of ANY type so Access trys its best to be able to import all data by determining the datatype based on a column's contents.

There is no way to fix this unless you can edit the spreadsheets so that the first data row contains values that will cause the desired datatype to be assigned. The alternative is to append the data to an existing table rather than linking to the spreadsheet.