CODEWITHSUNDEEP
ms-access
Gina
Gina

Reputation: 77

System resource exceeded when appending a query Access 2016

I currently work as a data conversion specialist and we are trying to move from Access 97 to Access 2016. We have been successful so far, but now I am having an issue with a query that has over 500,000 entries in it. I am trying to append this query to a FoxPro Table and I have been getting this error "System resource exceeded". I am linking to an existing table through an ODBC driver and an Advantage Streamline SQL Data Source I created.I am aware that Access does have a 2GB limit. The query I am trying to append is not complex, but it is lengthy. The database and the table is on our server that we hold all of our data. Some Solutions that I have tried are:

  1. Changing the OLE/DDE timeout amount to the max (999999999) seconds: At first this seemed to work as the query continued to run, but then I got another error stating that I was having a criteria mismatch so then 2: I checked for null and wrapping my SELECT fields with a NZ() function for each field.

After this, I got the System resource exceeded error again.

The good thing is that the old version of Access (97) works well and gets the job done, but gets it done SLOWLY. It takes around 5-8 hours to run these large queries.

Is there another way to combat this error?

Here is a snippet of my SQL statement if it helps resolve this issue:

SELECT IIf(IsNull([GADATE]),Nz([GADATE]),DatePart("yyyy",[GADATE])) AS 
FISYRToUse
FROM AGLDET;

There are around 36 columns similar to this one that I am trying to append to this table with over 500,000 rows.

Again, thanks in advance. If there is any way to improve my question then please let me know

Upvotes: 0

Views: 2314

Answers (2)

Gustav
Gustav

Reputation: 55921

Try with:

SELECT Nz(Year([GADATE])) AS FISYRToUse
FROM AGLDET;

or:

SELECT IIf([GADATE] Is Null, "", Year([GADATE])) AS FISYRToUse
FROM AGLDET;

For test:

SELECT Year([GADATE]) AS FISYRToUse
FROM AGLDET
WHERE [GADATE] Is Null;

SELECT Year([GADATE]) AS FISYRToUse
FROM AGLDET
WHERE [GADATE] Is Not Null;

Upvotes: 0

Albert D. Kallal
Albert D. Kallal

Reputation: 49169

I would send that data + query out to a temp external accDB. And then try exporting that to the FoxPro database.

This would eliminate the iff(), nz() expressions etc.

Also, you should be getting about 100,000 rows per second here. Why this is taking so long does not make sense??

While the data and tables are on a server, you are also running the export and programs on that server also, right? If you using a client work station attached to the server, then you pulling data down the network pipe and back up - that will be slow.

100k rows per second should be about the "ball park" rate of data transfer you get here.

Access 97 is old, and thus would use less memory, less CPU and run faster then newer versions. For the last 20 years, every new version of software takes more disk space, more memory, more CPU and does run slower - so this information would not be new to you.

Also, if you can, try a MSI version of Access in place of a CTR version. The reason for this is that CTR versions of office/Access are app-v (virtualized applications), and they tend to not release memory and resources, and worse they use much more memory and resources.

Another idea would be to export the data as FoxPro/dbase format, but to a external file, and then use FoxPro or the other application to import + append.

So, there are several things going on here, and this VERY slow process suggests that some BIG details are being left out here. If you working or shuffeling the data over a network, then that would certainly explain the slow issue.

However, Access 97 as noted should and would be the fastest here by a considerable margin. Like every software system I used, the next version is slower, takes more memory, more CPU and more resources. So the next version of Word, or Excel or Access or "what ever" always runs slower and takes more resources.

So you not going to get a speed up here by using later versions. And of course after Access 97, then office supported uni-code (a really big hit in performance).

So using a newer version always going to be slower - can't think of any exception in the last 25 years.

I would thus export the data to a accDB file, and then your NEXT export will not have those conditions (edit: I mean expressions that tax the memory system). This may well fix the memory leak issue. However, as noted, if you can, using a non CTR version of Access. (and 2016 versions are hard to get/find).

Upvotes: 0

Related Questions