MS Access Linking to Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

dfriend846

Thread Starter
Joined
Aug 11, 2005
Messages
214
I am linking an Excel table to Access and one of the columns contains both numbers (e.g. 93500) and text data (e.g. P934a30). See images. My problem is that the link is set up successfully but Access considers the problem column to be numeric and displays $Num! for the text values. I cannot change the field type in Access. I have changed the Excel column format to Text but that does not help. I have seen this before so I suspect it is a common issue that I just do not know the answer for. My reason for doing a link is that the Excel tables will be updated periodically and this seems the easiest way. Can anyone offer any suggestions?
Dennis
 

Attachments

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,484
when you link the excel file - you have an option in the wizard to set the field type for that column - if memory serves me correctly - not on a PC with access currently
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Dennis, you need to "Cheat" here, delete the link to the current Excel Sheet.
In the Problem Coulmn(s) enter a "'" in front of the the number in the first rows).
This makes the Number entry store as "text" and you will get a "warning" in the cell if you have them turned on.
Save the Workbook.
Now link to the Workbook and Access will set it up as a Text Field.
If anybody deletes the "'" or overwrites it with a number you will get the original error come back.
Personally at Access startup I would Import the Excel data in to a temporary table, Update a proper Access table with a query and then delete the temp table.
The only problem is the access table won't reflect changes to the Excel sheet until you close and re-open it.
 
Joined
Oct 20, 2004
Messages
7,837
An easier answer - in the Excel file, highlight the column and go to Format Cells for the Local ERP #. Go to Custom and type in & as the format. Link it to Access - it works fine.
 
Joined
May 7, 2005
Messages
74
i am pretty much a noob in access but cant you go into design view and change the column to text?
 

dfriend846

Thread Starter
Joined
Aug 11, 2005
Messages
214
Hi OBP, I have tried your second solution and it works but the first seems not to: If you note (green image) the first row of the import is a text value. Should I still need to put a ' before it?
dennis
 

dfriend846

Thread Starter
Joined
Aug 11, 2005
Messages
214
Unfortunately not for a linked Excel worksheet. You can change the format on an imported table but not a linked one.
 

dfriend846

Thread Starter
Joined
Aug 11, 2005
Messages
214
Interesting, I used an & in the custom format and it replaced all the number values with &. I will check further after work to see if they come out as the proper answer in the Access table.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top