Access and VisualSourceSafe (XP SP1)

Topic

New Reply

If anyone can explain what’s going on here, I’d be really grateful as it’s driving me mad
I’ve got a number of Access databases in VSS, the databases have ODBC linked Oracle tables in.
I use some UNION queries to provide data for combo boxes, e.g. using the SQL

SELECT UNIFORM_CNCODE.CODEVALUE, UNIFORM_CNCODE.CODETEXT
FROM UNIFORM_CNCODE
WHERE UNIFORM_CNCODE.LISTNAME="ADRECTYPE"
UNION SELECT "^" AS AllChoice , "(Not Set)" AS Bogus 
FROM UNIFORM_CNCODE
UNION SELECT "*" AS AllChoice , "(All)" AS Bogus 
FROM UNIFORM_CNCODE
ORDER BY UNIFORM_CNCODE.CODEVALUE;

which has worked in most mdb’s, but I’ve now found a couple of mdb’s where opening the query crashes Access – that’s a full crash, send report to Microsoft, and then die
What even more weird is if I reverse the two dummey select’s, the query works
I’ve even created a blank database, linked the Oracle UNIFORM_CNCODE table and copied the SQL text into a new query. Open it and CRASH
– can something be in the text, I even copied it out to Notepad, and then back in

Reply | Quote

Replies

I would suspect something in the SQL syntax that’s sending Jet over the hill. Have you tried running the query with first one of the Union statements and then the other Union statement? We use that sort of trick regularly to add additional choices to a combo box. How many records is Oracle returning from the first SELECT statement?

Reply | Quote

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 !!

Reply | Quote

This begins to sound like you may have some corruption in your Access database. Have you tried copying your SQL string into a new query, saving it as a saved query and then trying to run it? Also the same thing with a new database. BTW, I don’t think it has anything to do with VSS.

Reply | Quote

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

Reply | Quote

<>

That tells me that the issue isn’t corruption – more probably an issue with the ODBC driver trying to run the entire query within Jet because it thinks the UNION selects are local tables, or something akin to that. Oracle drivers tend not to be as stable as SQL Server and some of the other ODBC drivers – can you create something similar to your query in Oracle, and then either use a pass-through, or connect to it as a view?

Reply | Quote

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

Reply | Quote

< 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 ?>>

Nope – that’s just the Jet engine getting the entire table so it can do the join to a local table. Or, the driver decided for some reason that the query had to be run by Jet rather than the DB engine, and pulled the entire table. That’s why views and stored procedures are so much faster – they run on the server.

Reply | Quote

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

Reply | Quote

Are you getting that when you run the query or when you run a report? You do realize that reports generate temporary queries behind the scenes, right? That’s why sometimes a query will run on its own but you’ll get a “query too complex” error when you try to run a report based on it. If you have a VBA function in the query, that function may be executing for every record at least once (possibly more) depending on how the function is used. That alone can generate an awful lot of memory usage.

Reply | Quote

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

Reply | Quote

Hi Steve,
I think your memory usage may give some clues as to why Jet gives up the ghost in some cases – 1Gb of memory may be literally running out of memory, and Jet has some internal structures that reach maximums of about 1Gb. Since you are using a local VBA function, that means that everything is going on in Jet – is that function being done in the View? I don’t know of any way to get more detailed information about Access memory usage, but you should be able to predict maximum memory usage based on the table designs, and assuming that you are pulling in both tables, then compare that to pulling in the view, and see what that looks like. If either or both of the tables contain fields that Access thinks are memo or blob types, pulling in 10,000 records could really blow up memory requirements.

Reply | Quote

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”

Reply | Quote

Hi Steve,
Doing the function in the query makes it much more likely that all 10,000 records are being pulled into Access – if the function has a criteria against it, it’s almost certain that the whole table is being pulled in. So a view may well make things much better, and a stored procedure would likely be even better.
The usual way to call a stored procedure is to use a PassThrough query – however if you need to pass parameters to the stored procedure, then the QueryDef will need to be manipulated in VBA in order to create the correct SQL String to pass through to Oracle.

Reply | Quote

Have you thought about running the query without the function but building up a temporary in Access. Then apply the function against the Access table.
How many records are we talking about retrieving from the Oracle tables.
Pat

Reply | Quote

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

Reply | Quote

Like other objects, queries can become corrupted. When they do, the simplest thing to do is build a new query.

Reply | Quote