Field Data Type for Linked Tables

B

Brian

I have linked a MS Excel worksheet as a table in my Access database. I have
a field that contains numbers and letters. I have formatted the cells in
Excel as text, but when it is linked in Access its data type is number, which
causes the fields with numbers and letters to be displayed as #Num! in my
Access table and form. I can't change the data type in Access. When I
attempt to I get the message "Microsoft Office Access can't save property
changes for linked tables." Is there any way to get around this issue?
Thanks for your help!
 
K

KARL DEWEY

One way is to put a dummy record as first row so that there are letters where
there are a mixture in the column.
 
B

Brian

Karl,

I inserted a dummy record with letters only and the data type still appears
as "Number".
 
B

Brian

I had deleted the link and re-created it, but it wasn't working, so I added
multiple records with text and numbers. When I link it now it shows the data
type as Text and the dummy records appear correctly, but the records with
only numbers in that field appear as #Num!.
 
B

Brian

Here is what is in the Excel spreadsheet

10 Total10A
11 Total10A
12 Total10A
13 Total10A
14 Total10A
15 Total10A
16 Total10A
17 Total10A
18 Total10A
19 Total10A
20 Total10A
21 Total10A
22 Total10A
23 Total10A
24 Total10A
25 Total10A
26 Total10A
27 Total10A
28 Total10A
29 Total10A
30 Total10A
31 Total10A
0
0
0
0
0
0
0
0
0
0
0
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1


This is what it shows up as in Access

10 Total10A
11 Total10A
12 Total10A
13 Total10A
14 Total10A
15 Total10A
16 Total10A
17 Total10A
18 Total10A
19 Total10A
20 Total10A
21 Total10A
22 Total10A
23 Total10A
24 Total10A
25 Total10A
26 Total10A
27 Total10A
28 Total10A
29 Total10A
30 Total10A
31 Total10A
#Num!
#Num!
#Num!
#Num!
#Num!
#Num!
#Num!
#Num!
#Num!
#Num!
 
K

KARL DEWEY

I do not get the error.
One way would be to create the table, open in datasheet view, copy the Excel
data, and paste append.
 
B

Brian

Got it

Thanks Karl

KARL DEWEY said:
I do not get the error.
One way would be to create the table, open in datasheet view, copy the Excel
data, and paste append.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data Type Issue in Linked Excel Table 7
Linked Table Data Types or Field Formats 1
Linked Excel table with zip code data type error 5
Record-Locking remains open after closing 0
#Num Error Linked Excel Table 1
Type mismatch in linked tables. Please help! 4
Linked SQL Tables don't report Cascading Updates and Deletes 2
Linked Excel File won't update 1

Top