Access crashing while trying to check large tables

[DanWallace]

Hi there,

So the first part of my function selects all records in table nbif951 that have no matching record in npap001 using the field C_PLANTYPE. Now I used the query wizard to create this select statement, but it works fine when I test it on a smaller table. It's just when I try to run it on the full-size table (which contains a few million records) it just hangs and never makes it past the line that populates the recordset no matter how long I let it run. Any thoughts? Is there a limit on the size of what you can put into a recordset?

strSQL1 = "SELECT nbif951.C_CUSTOMER, nbif951.C_ACCOUNT, nbif951.C_PLANTYPE FROM nbif951 LEFT JOIN npap001 ON nbif951.[C_PLANTYPE] = npap001.[C_PLANTYPE] WHERE ((npap001.C_PLANTYPE) Is Null);"
   Set db1 = CurrentDb
   Set rsTable = db1.OpenRecordset(strSQL1)

 

[DanWallace]

Hmmm weirdest thing is I can SELECT * FROM nbif951 just fine. That should bring back even more records than the other conditional select would. hmmmmmm.

 

[Steve R.]

You may want to take a closer at

WHERE ((npap001.C_PLANTYPE) Is Null)

write a VBA test code that verifies that the data in npap001.C_PLANTYPE is correct. I have had cases where the data was an empty string and NOT null. You may want to change the () around npap001.C_PLANTYPE to []. The [] are needed if your data contains spaces in the string data.

[deleted incorrect syntax suggestion, my appologies]

See if you can incrementally add complexity to the SQL statement.

 

[Jemmo]

How big is your database bloating to?
If you go over 2 Gb in size it wll crash.

 

[DanWallace]

You may want to take a closer at

WHERE ((npap001.C_PLANTYPE) Is Null)

write a VBA test code that verifies that the data in npap001.C_PLANTYPE is correct. I have had cases where the data was an empty string and NOT null. You may want to change the () around npap001.C_PLANTYPE to []. The [] are needed if your data contains spaces in the string data.

See if you can incrementally add complexity to the SQL statement.

The SQL statement I cut/pasted from the query design wizard into my code. Also, I checked all that data for npap001.C_PLANTYPE and there are no empty strings. All rows have a value for C_PLANTYPE in this particular database, so it should be returning no records at all, which is what I want.

This is only one out of about 30 different checks I have in this function. The others all work fine. The only difference with this one is the massive size of the table.

It might be worth mentioning the table is not local, it is linked from a server.

 

[DanWallace]

How big is your database bloating to?
If you go over 2 Gb in size it wll crash.

The database is under the limit. It's still less than a gig.

 

[jal]

Did you try pasting the query into SQL View, running it from there?

 

[DanWallace]

Did you try pasting the query into SQL View, running it from there?

When I did that it hung for a long time but eventually it came back with results. But in code it never seems to reach my recordset. I've left it for 45 minutes in an abysmal waste of time lol. Still nothin.

 

[jal]

When I did that it hung for a long time but eventually it came back with results. But in code it never seems to reach my recordset. I've left it for 45 minutes in an abysmal waste of time lol. Still nothin.

Maybe the recordset is what is tying up the affair. In that case, change it to a MakeTable query:

DoCmd.RunSql "SELECT....INTO TempTable FROM ...."

and then, in the next line of code, open the recordset on the temp table (the results).


rs.Open "TempTable"....


Just a wild guess - couldn't hurt to try it.

 

[DanWallace]

Ahh, it seems to have had to do with the way I was dim'ing my recordsets. It's working now. Thanks guys.