Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Functions in Queries (A2K SR1)

    Hello.

    I've not done a great deal of A2K development, but I have done quite a bit under A97. I've got an A2K database converted from A97 and a number of the queries are running really slowly now. I've pinpointed the problem to be a change in the way A2K handles functions called from queries. Under A97, if you called a function from a query and didn't pass it any field values from the query it was called once only, under A2K it seems to call the function once per record.

    I've looked through Help and also MSDN and the only concrete thing I've found was a knowledge base article titled "ACC2000: Number of Times a Custom Function Runs in a Query" (Q210554). Unfortunately this confirms what I was expecting (that the function should only be called once per query execution) not what seems to be happening (once per record). I've tried the example in the article in case something really odd was happening in my real application, but it exhibits the same behaviour.

    To save anyone looking, this is the query and the function from the MSDN article. It uses the Northwind sample DB.

    SELECT Employees.LastName, ShouldIncrement() AS RecordNumber FROM Employees;

    Global RecordNum

    Function ShouldIncrement()
    RecordNum = RecordNum + 1
    ShouldIncrement = RecordNum
    End Function

    The example shows the query returning 1 in the RecordNumber field for each record where in fact it returns a different value for each record. Is this a bug? A new feature? Is there some setting I can turn off? Any other simple workaround?

    Thanks in advance,

    Simon.

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

    Re: Functions in Queries (A2K SR1)

    All I can say is that it gives me the same results in Access 2000 SR1 as well, in spite of what the <A target="_blank" HREF=http://support.microsoft.com/default.aspx?scid=kb;en-us;Q210554&id=210554&SD=MSKB>MSKB article</A> says. Both Example A and Example B are running once for each record.

    There are a couple of things besides that which can slow down execution in Access 2000, though. One is running against an Access 97 back end, which is horribly slow. So if you have a split database, make sure the back end got converted as well.

    Another is subdatasheets, which seem like a good idea but perform miserably and slow down the entire database, especially if you combine them with lookup fields in your tables or combobox lookups on your forms. Subdatasheets have to be turned off one table at a time for each database, but there is code in the help files to do this.

    Finally, be sure to turn off Name Autocorrect for each database, both logging and tracking, from the Tools-->Options-->General tab. The latter won't change the way your queries are behaving, but it will prevent some other problems from biting you when you least expect them.
    Charlotte

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Functions in Queries (A2K SR1)

    Charlotte,
    I just ran that function in a query in a test database (I'd never really thought about this before so I was curious) and the shouldincrement function returned 1 for every record. I'm running A2K (9.0.3821 SR1) on Win2k (SP2 as best I recall).
    There's nothing like consistency. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Functions in Queries (A2K SR1)

    Rory, I think we may be onto something here. I just ran it on my laptop (same Access and Win2K as you) and it behaved as the article indicated it would. However, on my machine at work, where I only have SP1 on Win2K, I saw the behavior that Simon described. That suggests it's a Win2k problem.
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Functions in Queries (A2K SR1)

    Oooh, that's interesting. On my main development machine I'm running A2K (9.0.3821 SR1) and Win 2K (SP1). On my other (standalone) PC I'm running A2K with no service packs installed and Win 2K (SP2). The query runs as it should on my other machine. I wanted to upgrade my other PC to A2K SR1 (just to prove to myself that it isn't a difference in the query results in different Access versions), but I can't seem to download the right set of files (I can't use the standard online installation on my other PC as it has no internet connection).

    Most irritatingly, I can't upgrade my main PC to W2K SP2. I've tried and I always get problems - particularly with the Search for Files and Folders functionality.

    Most PCs that will run my Access application are using NT 4 Workstation, so hopefully the problem won't occur there either. Thanks for your help.

    Simon

  6. #6
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functions in Queries (A2K SR1)

    Charlotte - looks like it was resolved <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=2000&Number=101043&page=0&v iew=collapsed&sb=5&o=&fpart=&vc=1#Post101043>here</A>.

    (My first post in Access!)

Posting Permissions

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