How to get rid of the error (#Num!) in linking

[Jalal]

Need to resolve the error (#Num!) while linking tables from Excel 2003 to
Access 2003. Had matched field proprieties, still getting (#Num!) error, that
cannot modify in access linked table.

 

[Wayne-I-M]

Hi

Try this

http://support.microsoft.com/kb/208414


Wayne
Manchester, England.

 

[Jalal]

Had already tried same macro but it deleted the data contained in cells and
ranges that I had run this micro on!
Thanks

 

[Wayne-I-M]

Have a look at at answer given by Barry Gilbert

http://groups.google.com/group/micr...roup:microsoft.public.access#a8b5bcd4f77450d9

If this does not work then I'm stumped - I hardly ever import excel to
access so not too sure about any problems -

maybe someone else will answer your post

 

[Clif McIrvin]

Need to resolve the error (#Num!) while linking tables from Excel 2003
to
Access 2003. Had matched field proprieties, still getting (#Num!)
error, that
cannot modify in access linked table.

Hi Jalal

I've been fighting that #Num! error in a linked Excel 2003 to Access
2003 for some time now and have done so many different things to combat
it that I really don't know what solved it (if indeed I have solved it,
or if it's still going to bite me again.)

I'm going to assume, since you said linked table, that this is data that
is continually being revised in Excel so you intend for this to be a
permanent link.

In the other responses I read I saw a reference to "Access looks at the
first row to assign the data type"; and "Access looks at the first 8
rows to assign the data type." I believe there is someplace in Access
you can change how many rows Access looks at when linking to Excel; I'm
sorry, I can't tell you where to find it.

Try inserting one new row at the top of your data to set the format for
Access. In every column that you might possibly have mixed data types
in, enter the word "mixed" or "text" as the value of that cell so that
when Access does the link it will set that column data type to text.

After this row is completed, save the workbook, go to Access, delete
your linked table and set up the link again. Hopefully this will work
for you.

Post back and let us know if you're still having trouble.

 

[Clif McIrvin]

Jalal, I just tried what I recommended (below) on my data and I'm sorry
to report that it did not solve the problem. I really don't have the
time to experiment further right now.

What I have done in my situation is not very satisfactory, but I am
getting by. I have determined what columns I have the most critical
need for, and have manually located and edited any cells in the Excel
worksheet that were generating a #Num! error. Then, in Access, I created
a new table for the columns I need, and created a couple sets of nested
queries to move new data from the linked table to the access table. I
have a select query to identify any modified rows which is nexted into
an update query; and another select query to identify any new rows which
is nested into an append query. It's ugly, but it's getting the job
done for me for now.

I'll have to try that knowledgebase macro and resetting the Excel
formatting on the columns and see if that works for me.

 

[Ricky II]

Hi everybody ...

I'm having the same problem with one table (yes, just one) sited in an SQL
Server Database.

I can link the table, but when I try to open it, it come out an "ODBC Error"
message box. When I try with many other tables, sited on the same Database,
it was no error and it let me see the data.

Maybe there is a compatibility problem between Sql Server Database and
Access 2003??. When I use the same linked table, on an ACCESS 2000, it works
OK, without any problem.

Please, can somebody help me?.

Thanks

 

[Ricky II]

Hi everybody ...

I'm having the same problem with one table (yes, just one) sited in an SQL
Server Database.

I can link the table, but when I try to open it, it come out an "ODBC Error"
message box. When I try with many other tables, sited on the same Database,
it was no error and it let me see the data.

Maybe there is a compatibility problem between Sql Server Database and
Access 2003??. When I use the same linked table, on an ACCESS 2000, it works
OK, without any problem.

Please, can somebody help me?.

Thanks

PA: sorry if I'm repeating the question, but I have no idea of what is
happening.