Modifying linked tables in Access

**Talat** · 01-05-2010, 09:31 AM

Hi,

I have a linked (excel) table which I need to modify the data type of one of its fields. Can this be done? If so, how do I do it? Trying to change it in design mode says it can't be done.

I am using Access 2003.

Many thanks and Happy New year!

**bigroo1958** · 01-11-2010, 12:25 PM

Hi Talat,

My understanding is when you link excel tables in Access you cannot modify the table structure. You will need to format your columns in excel to the format you wish and use the Link Manager to refresh the link.

This is my understanding....others please chime in if I missed something!!

Thanks, I hope this helps.

James

**Kafrin** · 03-01-2010, 08:21 AM

This is correct, you can't modify the data type in a linked table.

If you're having diffculties making Access pick the right data type, here are some suggestions.

First, delete the link from Access. Then in Excel make sure the first row (if you have headers then the first row below the headers) contains the right sort of data in each column and save. In Access now link to the Excel file. Hopefully now it's picked the right data types. Don't forget to put your Excel file back as it should be.

If this hasn't worked, try setting up a new Excel file with just one row (+ your headings if you have any) with data in each column. Link this into Access and see if it's got the data types right. If it has, go to Linked Table Manager and relink the table to the Excel file with your data in it.

Alternatively, if your Excel file can be a csv file instead, you have more options. To set this up, open you Exel file, go to Save As and change the file type to *.csv. This will still open in Excel in the same way, but some formatting may be lost. Then in Access link to this csv file. When you go through the linking routine, you'll have the option to choose the data types manually.