Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107

    Unanswered: vba enhanced queries on sql server

    hey all,

    workin on a project with access frontend and sql server backend. quite several of my queries use vba functions as in IIf and sum user defined function too. As far as i know those queries are not handed thru to the sql server coz it cannot deal with it. So far so good. But i'm experiencing serious connection problems using those queries. Could there be any problem arising from vba enhanced queries on sql server tables other than a sumwhat slower performance and a higher net traffic?

    comments appreciated
    best regards
    bachatero

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Iif should not be a problem but vba UDFs will be (I presume sum = some?).

    Access will pass as much of the processing to the BE as it can but when it comes to UDFs it has no option but to request the data and process it client side. You can improve performance dramatically by porting as much of the data retrieval code to SQL server in the form of stored procedures, views and functions.

    The only problems specific to what you suggest might be time out problems... What specifically are you observing?

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    that fits my idea of it. the thing is i hav a function which is called getQPar for query parameters as the standard access parameters are annoying like ..
    i don't get a time out - i attached the error message (sorry it's partially in german)

    and yes sum eq some
    Attached Thumbnails Attached Thumbnails ErrorSQL.bmp  

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bachatero
    (sorry it's partially in german)
    That's ok - but could you translate for me please?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    it seemed to be rather a problem of the machine .. changed to another workstation .. and tada .. no more problems. took me sum 5-6 hrs to get there - great.
    thanx a lot anyway.
    bachatero

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Glad you figured it out.

    As a sidenote, you would probably benefit from moving your functions and other database rules and logic to the physical database itself. There are very few VBA functions dealing with data only that cannot be replicated on SQL Server.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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