Loss of number/date formatting when linking MS Excel files to MS Access

Question

Hi and thanks in advance for the help.

I have an Access 2007 database that has tables created by live links to several Microsoft Excel 2010 spreadsheets.

I have several Access macros that run queries against these linked tables, and I find that the formatting of the output is in very strange formatting if I run the macros, without first having those linked Excel spreadsheet files open.

To put it another way, if I open the linked spreadsheets and run the Access macros, all the data formats correctly, but if I have the linked Excel spreadsheets closed when I run my Access macros, the formatting goes all funny - particularly with date fields.

Can anyone offer any advice on how I can resolve this problem?

Many thanks

Kim

Answer 1

I usually create a straight select query for each linked Excel table.

In this you can force any formatting or conversion you may need as well as using alias to obtain more friendly field names.

Then use this query and not the linked table for the further processing.