Format Issues with Linked Tables

[Smitty]

I have a Sales Rep table I'm linking in Access from Excel. In Excel there are a few sales ID fields that are text formatted (Sales Rep #, Territory #), but when linked, Access sees the Territory # as number, not text. I don't have the same problem with the Sales Rep #, both of which are 5 digits.

This creates a type mismatch error in queries as the related data in my Access table is text.

Any idea on how I can retain the Excel formatting? I've run out of ideas, including forcing text format with Text to Columns.

Unfortunately I've tried to reformat the Access data a Number, but run out of memory on 300K records.

I could try to rebuild the Access master table but it's 2 years worth of individual weekly data files from our Data Warehouse.

Thanks,

 

[SydneyGeek]

Hi Smitty, here's 2 ideas.

1. Try creating a dummy record in row 2 of the Excel workbook, and use text where you want text formatting. Nasty, I know, but Access looks at the first data row and determines the data type from that. If you're careful with your ID or date value, you can easily exclude the item from any analysis.

2. Another option on the reformatting -- you should be able to achieve this by creating a temp table with the correct data types, and then appending the existing tables to it. Use CLng or CDbl to coerce the values during the Append.
Or maybe, create a new Number field in the resident Access table and run an Update query to populate it with the number version of your Territory #. Again, CLng should do it.

Denis

 

[Smitty]

Thanks Denis,

I'll give it a shot.