Problem when calling Access for data

[Water guy]

I'm rather new to Access, but I'll try to explain my problem clearly.
I am using an EPA water quality modeling program known as WASP.
The WASP program can be set up to automatically import data from an Access
table.
In the WASP interface the user can then enter a query to limit the actually
imported data, thus speeding up processing time.
I have done this exact operation on many occasions and it seems to work great.
However, I have a very large database that I am seeking data from. In order
to aid in the process of finding the correct data and importing it into WASP,
I set up a make-table query to first limit the data set. (100,000 records to
45,000 records) Then I attempt to set the WASP program to automatically
import and query the new table. When I do this the WASP program locks up. I
am wondering if when I used a make-table query, the table produced had a
setting that limits the querying of that data. An suggestions?
I am also trying to find a problem on the side of the WASP model too.
Thanks,

Will

 

[Roger Carlson]

There's a difference between Select queries and the various "Action" queries
and how they are executed in Access. I suspect you can't create and run
Action queries through WASP as the interface is probably set to use Select
queries only.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Klatuu]

Given the time to run the make table query and the database bloat it will
cause from recursive use, you would be better off to let the WASP program use
the actual production table. I think what you are doing is causing more harm
than good.

 

[Water guy]

There's a difference between Select queries and the various "Action" queries
and how they are executed in Access. I suspect you can't create and run
Action queries through WASP as the interface is probably set to use Select
queries only.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




Let me clarify the problem a little more.

The WASP program import data interface allows the user to specify a database
file, then select a table, and then write a SQL query to "drill down" the
data to the more applicable records. My problem is occurring when I attempt
to write in the SQL Query on a very large table. The model always seems to
lock up when calling data from a large table. On smaller tables 50-100
records, no problem.
So I made a make-table query in Access to produce a smaller table. When I
set up WASP to call the new table, the WASP program locked-up again. However
I am wondering if the table produced from the query may have a setting that
limits the ability of WASP to call data from it. In the design view I
matched all obvious field settings with the smaller tables that work. It
may be a WASP problem, and I'm investigating that too. Are there any table
settings that would adversely affect the export of data from an Access table?

 

[Roger Carlson]

Sorry. I read your problem incorrectly.

The only thing I can think of is Indexes. Do you have any indexes defined
on your original, large table. An index might speed things up considerably.
Make Table queries don't create indexes, so you'd need to set an index on
that table too.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Water guy]

Thanks, that sounds plausible, I'll check that out.