Access Query from Linked Tables

I am running a query in Access from Tables that linked to Excel spreadsheets. I am able to run a query once (I am creating new Access tables from these linked spreadsheets), but on the second attempt, I get the error "Numeric Field Overflow."

It appears that linked spreadsheets have a one time query use.

Please offer thoughts/suggestions.

Thanks.
tahirihAsked:
Who is Participating?
 
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
So you're running two MakeTable queries on linked Excel spreadsheets, renaming the resulting Access tables, and then running Union queries on the two Access tables?  
Do you need to run the MakeTable queries a second time to refresh the data in the resulting Access tables?
If so, why don't you run your union query on the linked Excel spreadsheets?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"It appears that linked spreadsheets have a one time query use."

Not the case.  Something else is going on ... but I'm not sure what.

mx
0
 
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
The "Numeric Field Overflow" error occurs when you try to insert a numeric value (eg:  100,000) into a field or variable that cannot accept it.  Integer fields, for instance, cannot accept numbers higher than 32,767.  I'd look at the numeric fields in the spreadsheet, then at the field types created by your MakeTable query, and see if this is the problem.  When you run the query a second time, have you deleted the tables created by the first run?  Are you changing the MakeTable to Append?

Paul,
Santa Ana
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
tahirihAuthor Commented:
I have tried numerous times. The query works like a charm the first time and not the second, regardless of whether the output table from the first run is deleted or not.

Thanks.
0
 
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
Huh.  Perhaps a different approach would be more productive.  What are you trying to accomplish?
0
 
tahirihAuthor Commented:
I am creating a series of queries, and I need to rename these linked tables to be incorporated into later queries.

I tried again. What happens is the second time I run the same query (keeping the output table from the first run intact), I get the error message, and the output table from the first run disappears.

0
 
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
Running a MakeTable query a second time will delete the table created by the first execution... but you should be getting a warning message before the table is deleted.  (If you're not, check Tools, Options, Edit/Find, and look at the Confirm box;  document deletions is probably unchecked.)

Renaming linked tables is something entirely different from running MakeTable queries.  Could you explain exactly what you need to accomplish (and list the steps you're taking to accomplish it)?  If I can understand where you need to be, we can probably find a good way to get you there.  :)
0
 
tahirihAuthor Commented:
Ok, I can just as easily Copy/Paste, but I am trying to automate everything as much as possible.

The linked table is called "Original 1" (Excel format). I then use the MakeTable Query to create a new table A (Access format). The same process is used in creating table B from "Original 2."

I then can run various queries, one example being a Union Query that will compile information from Original 1 and Original 2.

Hope this helped.

Thanks.
0
 
tahirihAuthor Commented:
This is a weekly update, and the linked tables are updated weekly. Hence, I prefer leaving them linked. I still do not understand why Access cannot run a query twice on a linked table.

Thanks.
0
 
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
I've don't understand why your queries won't run a second time either.  Did you check the Confirm Deletion setting?  Are you running the queries as MakeTables or as Appends the second time?

But regardless of why the queries won't run, I'm still not clear on why you're importing the data into Access before you run your Union query.
0
 
tahirihAuthor Commented:
Ok, I am going to let this one go...I copied and pasted as an Access table, and was then able to run the queries multiple times.

Your assistance helped.

Thanks.
0
 
tahirihAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.