Access Query from Linked Tables

"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

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

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.

Huh.  Perhaps a different approach would be more productive.  What are you trying to accomplish?

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.

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.  :)

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.

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.

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.

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.

thanks