#Num! numeric overflow query problem

[Guest]

Hi

This one's driving me mad.

I am trying to 'copy' a linked table into my database. The table is three
fields: two that are text fields and one that is a number. The first record
is dodgy and has a empty second text field and #Num! instead of a number. I
cannot change these as it's a linked table.

I can't use CopyObject because it just copies the linked table as another
linked table.

I have set up a MakeTable query in which the criteria is Is Not Null for the
blank field and >0 for the number field. This runs OK the first time
(correctly ignoring the dodgy record) but subsequent times it falls over
with a numeric overflow error. If I edit the query in any way and run it
again it runs once correctly and then errors every other time.

I got so frustrated with this that I decided a different approach. I created
a blank table and used a delete query to clear it, followed by an append
query (from the linked table, with the above criteria) to refresh it. The
delete query runs fine every time but the append query only runs once
correctly and then errors every other time!!

Anybody got any ideas of what else I could try?
Thanks!

 

[Cheese_whiz]

Hi Andy,

If you are trying to bring a table from one access database to another then
you want to 'import' it. In 2003 (and earlier, I think), it's File>Get
External Data>Import.

In 2007, it's External Data>Access

I think you would want to go to the file that has the actual table.......not
the file that has a link to the table in it.

Hope that helps,
CW

 

[Jerry Whittle]

Get someone to fix the linked table. If it's Access, there's a very good
chance that the table is corrupt.

 

[George Nicholson]

What's the file size *at the point of failure*? Any chance it "falls over"
because the 2nd pass hits Access 2 gig limit?

Maybe you need to compact between passes?

Off the top of my head, I can't think of any other reason for a query to
run OK once and fail the next time (given the same input).

 

[Guest]

Thanks for your replies! Unfortunately, none of them help me. The database I
am linking from is managed elsewhere so I can't amend the record and there
are only a couple of thousand records - so it's not a size issue. I'm
stumped!
Thanks again.

 

[J_Goddard via AccessMonster.com]

What is the datatype of the field are you importing the numeric data into?
If it is integer, then you may be trying to import numbers larger than the
maximum value for integer type, and you need to change it to type Long.

John