?
Solved

Access Query from Linked Tables

Posted on 2009-04-24
12
Medium Priority
?
424 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:tahirih
  • 6
  • 5
12 Comments
 
LVL 75
ID: 24229663
"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
 
LVL 6

Expert Comment

by:Paul Cook-Giles
ID: 24229718
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
 

Author Comment

by:tahirih
ID: 24229767
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Expert Comment

by:Paul Cook-Giles
ID: 24229789
Huh.  Perhaps a different approach would be more productive.  What are you trying to accomplish?
0
 

Author Comment

by:tahirih
ID: 24229803
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
 
LVL 6

Expert Comment

by:Paul Cook-Giles
ID: 24229828
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
 

Author Comment

by:tahirih
ID: 24229844
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
 
LVL 6

Accepted Solution

by:
Paul Cook-Giles earned 2000 total points
ID: 24229918
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
 

Author Comment

by:tahirih
ID: 24229936
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
 
LVL 6

Expert Comment

by:Paul Cook-Giles
ID: 24229961
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
 

Author Comment

by:tahirih
ID: 24229996
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
 

Author Closing Comment

by:tahirih
ID: 31574419
thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question