Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access and VisualSourceSafe (XP SP1)

    If anyone can explain what's going on here, I'd be really grateful as it's driving me mad <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>
    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<pre>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;</pre>

    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 <img src=/S/frown.gif border=0 alt=frown width=15 height=15>
    What even more weird is if I reverse the two dummey select's, the query works <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    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 <img src=/S/woops.gif border=0 alt=woops width=58 height=36>
    - can something be in the text, I even copied it out to Notepad, and then back in

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access and VisualSourceSafe (XP SP1)

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

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access and VisualSourceSafe (XP SP1)

    Wendell, what has me baffled is the queries used to work <img src=/S/frown.gif border=0 alt=frown width=15 height=15>
    - 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 !!
    <img src=/S/woops.gif border=0 alt=woops width=58 height=36> <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access and VisualSourceSafe (XP SP1)

    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.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access and VisualSourceSafe (XP SP1)

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

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access and VisualSourceSafe (XP SP1)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    - 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 <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    Are there any utilities that go further in "checking" an Access mdb, I've tried Compact & Repair and Decompile, neither of which worked <img src=/S/help.gif border=0 alt=help width=23 height=15>

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access and VisualSourceSafe (XP SP1)

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

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

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access and VisualSourceSafe (XP SP1)

    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 <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access and VisualSourceSafe (XP SP1)

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

    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.
    Wendell

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access and VisualSourceSafe (XP SP1)

    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 <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

    Is there anyway to check in more details where the memory resources are going <img src=/S/question.gif border=0 alt=question width=15 height=15>

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access and VisualSourceSafe (XP SP1)

    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.
    Charlotte

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access and VisualSourceSafe (XP SP1)

    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.
    Wendell

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access and VisualSourceSafe (XP SP1)

    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 .... <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access and VisualSourceSafe (XP SP1)

    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" <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Do you know of anyway to use stored procedure in Access <img src=/S/question.gif border=0 alt=question width=15 height=15>
    - 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"

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access and VisualSourceSafe (XP SP1)

    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.
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •