Replies

Biggest problem Hans is whatever I develop will be used on potentially 200+ sites, so I really need to make it as bomb proof as possible
– the class from Getz/Gilbert has a lot in, and it looks like a good point to build from

Thanks to you and Charlotte for the suggestions

Nice thought Charlotte, shame not in there
– according to the MS Smart Solutions site, all source code, etc. is on the CD, but they’re quoting 4-8 weeks for delivery
– think I’ll order it, hopefully the powers-that-be won’t want anything too soon

Charlotte,
I was looking at the joins in queries (querydefs). I did have a look at the Relations collection, wondering if that held the information, but soon found out it didn’t
– looking at the Ken Getz/Mike Gilbert set of articles, the problem look more complicated than I first thought, but if I limit to simpler SQL, i.e. exclude crosstab’s, unions, etc. it should be possible
– what’d be good would be to get hold of the source code for their QueryInfo class, but it’s a pain that the MS Office Solutions seem to have decided to drop the older pages
– the class + query seem to go a long way to getting the SQL out of the MSSys… tables. I could then adjust it for the new joins and push it back into the qurydef SQL property

Thanks for the response Hans
– yeap got that from Google, just wish I could get to the actual pages so I could download their example code

Problem solved
– there was a memory leak in the Oracle 8.01.78.00 ODBC driver
– they’ve issued a new 78b driver and this works much better, well instead of using about >1Gb of memory, it now uses a Mb or two

And just to keep this topic open

One query through to Oracle I’ve got works fine, and perhaps more importantly, hardly using any memory resources
– returns about 100 records

SELECT UNIFORM_CPINFO.REFVAL, 
UNIFORM_CPINFO.ADDRESS, 
UNIFORM_CPRISKSA.PROPNO, 
UNIFORM_CPRISKSA.PRIORITY, 
tblLookupI1FoodSafety.Description, 
UNIFORM_CPRISKSA.EHINSTYPE, 
[qryMapEHSPI EHINSTYPE].MAPVALUE, 
UNIFORM_CPRISKSA.SCHEDDATE, 
UNIFORM_CPRISKSA.ACTDATE, 
DateDiff("d",[SCHEDDATE],[ACTDATE]) AS DaysTaken, 
IIf([DaysTaken]<=14,1,0) AS VisitWithinTime
FROM UNIFORM_CPINFO 
INNER JOIN (((qryI1_Premises_FoodSafety INNER JOIN ([qryMapEHSPI EHINSTYPE] 
INNER JOIN UNIFORM_CPRISKSA ON [qryMapEHSPI EHINSTYPE].CODEVALUE = UNIFORM_CPRISKSA.EHINSTYPE) ON qryI1_Premises_FoodSafety.PROPNO = UNIFORM_CPRISKSA.PROPNO) 
INNER JOIN tblLookupI1FoodSafety ON UNIFORM_CPRISKSA.PRIORITY = tblLookupI1FoodSafety.PRIORITY) 
INNER JOIN UNIFORM_CPINSPEC ON (UNIFORM_CPRISKSA.PROPNO = UNIFORM_CPINSPEC.PROPNO) 
AND (UNIFORM_CPRISKSA.CASSYS = UNIFORM_CPINSPEC.CASSYS) 
AND (UNIFORM_CPRISKSA.EHINSTYPE = UNIFORM_CPINSPEC.EHINSTYPE)) ON UNIFORM_CPINFO.PROPNO = UNIFORM_CPINSPEC.PROPNO
WHERE ((([qryMapEHSPI EHINSTYPE].MAPVALUE)="INDIC1") 
AND ((UNIFORM_CPRISKSA.SCHEDDATE) Between [forms]![frmMainMenu]![DateFrom] And CDate(Format([forms]![frmMainMenu]![DateUntil],"dd/mm/yyyy") & " 23:59:59")));

Yet when I copy the query and change for the other inspection type, (using two other tables + INDIC2 rather than INDIC1), I get 35 records, but the memory is eaten up

SELECT UNIFORM_CPINFO.REFVAL, 
UNIFORM_CPINFO.ADDRESS, 
UNIFORM_CPRISKSA.PROPNO, 
UNIFORM_CPRISKSA.PRIORITY, 
tblLookupI2WorkplaceSafety.Description, 
UNIFORM_CPRISKSA.EHINSTYPE, 
[qryMapEHSPI EHINSTYPE].MAPVALUE, 
UNIFORM_CPRISKSA.SCHEDDATE, 
UNIFORM_CPRISKSA.ACTDATE, 
DateDiff("d",[SCHEDDATE],[ACTDATE]) AS DaysTaken, 
IIf([DaysTaken]<=14,1,0) AS VisitWithinTime
FROM (((UNIFORM_CPINFO INNER JOIN (UNIFORM_CPRISKSA 
INNER JOIN UNIFORM_CPINSPEC ON (UNIFORM_CPINSPEC.EHINSTYPE = UNIFORM_CPRISKSA.EHINSTYPE) AND (UNIFORM_CPINSPEC.CASSYS = UNIFORM_CPRISKSA.CASSYS) 
AND (UNIFORM_CPRISKSA.PROPNO = UNIFORM_CPINSPEC.PROPNO)) ON UNIFORM_CPINFO.PROPNO = UNIFORM_CPINSPEC.PROPNO) 
INNER JOIN tblLookupI2WorkplaceSafety ON UNIFORM_CPRISKSA.PRIORITY = tblLookupI2WorkplaceSafety.PRIORITY) 
INNER JOIN qryI2_Premises_WorkSafety ON UNIFORM_CPRISKSA.PROPNO = qryI2_Premises_WorkSafety.PROPNO) 
INNER JOIN [qryMapEHSPI EHINSTYPE] ON UNIFORM_CPRISKSA.EHINSTYPE = [qryMapEHSPI EHINSTYPE].CODEVALUE
WHERE ((([qryMapEHSPI EHINSTYPE].MAPVALUE)="INDIC2") 
AND ((UNIFORM_CPRISKSA.SCHEDDATE) Between [forms]![frmMainMenu]![DateFrom] And CDate(Format([forms]![frmMainMenu]![DateUntil],"dd/mm/yyyy") & " 23:59:59")));

Sorry about the SQL, but the only differences, that I at least think I’ve done, are using a couple of different lookup tables. I’ve checked these and they work fine.

So why such a difference in memory usage. Now looking at the SQL, it’s different. How has the Access “SQL compiler” or whatever it’s called that converts QBE grid to SQL, changed the bracketing ?

And I really am going to switch to Oracle view (when I find the time)

Pat,
thanks for the response. I suppose the range would be 10’s through to 50,000’s
Won’t the creation temporary table mean a lot of mdb “bloat”
– our users are varied on the tech front, so asking them to compact will not be a good option. I know of code that means the database can compact itself, can’t use the Compact on Close option as that’s only in Access 2000 and XP, and we have a lot of 97 users

More and more I’m planning on going with Wendell and Charlotte’s suggestion, i.e. Oracle views

Does the table in SQLServer have a lot of indexes on it ?
– if I’m doing a lot of data insertions, I try and drop the indexes before the load, then recreate them after the insertions are all done

Wendell
yeap the function is being done in the query
Checked the Oracle tables, the two child tables only have a few fields each, and they are text or number. However the main table has a lot of fields, and at least 2 are Memo.
Had a look in Oracle and they are Varchar(2000)
More and more, I think the best way is the Oracle views, it’ll also allow me to manage the data better,
i.e. try and cope with some of the sites where the words “data” and “quality” are never seen together, unless preceded by “bad”
Do you know of anyway to use stored procedure in Access
– by that I mean rather than in VBA + ADO, in the main interface, in some way similar to the linking in Oracle views as “tables”

No Charlotte, didn’t realise that the report generated a temporary query. Thanks for that update, as the “query too complex” had always baffled me, I’d open the query, or at least what I thought was the underlying query, and it worked, then the report and ….

The memory usage happens in the query, interestingly if I remove the function call (which I know is not pretty),
TimeFlag: IIf(SplWorkingDaysV7([RECEPD],Nz([CLSDDT],[Forms]![frmMainMenu]![DateReport]),1)>[Forms]![frmMainMenu]![NoOfDays],1,0)
the query runs fine, and very little memory used
I’m going to see if I can create the WorkingDay calculation in an Oracle view, but my PL/SQL is very limited, i.e. what I’ve read in the book on my table

Sorry, I think there’s more, as the two tables involved are about 10,000 records each.
If I do the join in Access, and run the query I see over 1 Gb memory used (real + virtual) – but the query does have a local VBA function in !!
If I create the view in Oracle, which returns about 10,000 records (the join is 1:m, but the vast majority only have 1 child record), I see about 30 Mb
I’d thought originally it was a memory leak in the VBA code (it had a Database object that it wasn’t setting = Nothing), but corrected all of those, still get the massive memory usage

Is there anyway to check in more details where the memory resources are going

My suspicions are that it something convoluted between VSS and Oracle, as I was working on the databases in question at home, so didn’t have the Oracle database when I checked out the database
– Access tried to login to the original database, but I thought I’d got it to login to a local one on my laptop

Just started to look at creating views, as there’s another issue where a report chews up > 1/2 Gb of memory, but if I use a view nothing like that amount of memory. Almost as if Access was kicking off a cartesian join rather than an equi-join ?
So I’ll add these queries to the list

Many thanks for the suggestions Wendell and Charlotte

Yeap I’m thinking there is something in there that’s not right

I did try copying the “bad query” over to a new blank database, linking in a couple of the Oracle tables, and then tried it. Guess what
– CRASH
I even cut and pasted into Notepad, then back again, but still crash
But even more weird, editing the query, i.e. cut out one of the dummy select’s, save, then paste back in, and it run OK

Are there any utilities that go further in “checking” an Access mdb, I’ve tried Compact & Repair and Decompile, neither of which worked

Wendell, what has me baffled is the queries used to work
– if I remove the two dummy unions, then I get anything from 1 to 60 records back from the Oracle table
– if I add back one union or the other it works with the (All) or (Not Set) added at the top
– if I re-insert the original text it sometime crashes Access
– if I re-type the text (and correct my typing errors) it works !!

Yes, further playing with basing the report on the Oracle view, rather than doing the join in Access, shows a much reduced, BUT NOT eliminated, memory usage
The memory is still not released though, until I exit Access