Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: Stop Access Verifying Code

    Is there any way to stop access from verifying a query before it passes it threw to my SQL server? Access dosent recognize the syntax beacaue it is SQL specific. Any Ideas?


    Set Multi_Graph_First_Pass = Cur_DB.CreateQueryDef("First_Pass", "create table #Account (Account varchar(50)," & _
    " Week varchar(15)," & _
    " FFE decimal)" & _
    " insert into #Account select account , Week as [week] ,sum(ffe) as FFE " & _
    " From DM_GLOBAL_ACCOUNTS_AGGR_TBL" & _
    " Where account is not null" & _
    " and week between " & From_week & to_week & _
    " and account in (" & Right(strItems, Len(strItems) - 2) & ")" & _
    Direction & String_Var & _
    " Group by account, Week " & _
    " Order by Week asc, Account asc")
    Multi_Graph_First_Pass.Connect = "ODBC;Description=DataWarehouse;DRIVER=SQL Server;SERVER=scnc044db;UID=globalsales;PWD=global ;Network=DBMSSOCN;"
    Multi_Graph_First_Pass.ReturnsRecords = False

  2. #2
    Join Date
    Apr 2002
    Posts
    139
    Never used it myself, but I think what you need is a Pass Trough Query.
    In Query Design view, choose the menu Query / SQL-Specific / Pass Through. See Access Help for details on connection etc.

    hth

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by marion
    Never used it myself, but I think what you need is a Pass Trough Query.
    In Query Design view, choose the menu Query / SQL-Specific / Pass Through. See Access Help for details on connection etc.

    hth
    ya i am using a pass threw, the thing is it verifiies the code before it realizes its a pass threw......thus my problem

    Jim

  4. #4
    Join Date
    Apr 2002
    Posts
    139
    Interesting, you've got me puzzled too. Although I've never worked with Pass trough queries, I've got the following thoughts:

    Could it be that your SQL statement contains any parts that are specific to Access SQL, and that are not present in SQL-92 or whatever your SQL-server is expecting? For example. I understand that VBA functions are not supported in SQL-92.

    So, would things like "Cur_DB.CreateQueryDef" or "Right(strItems, Len(strItems)" be available in SQL-server as well, or are these statement unique for Access?

    I can imagine that if Access discovers Access-specific SQL dialect in your statement, it assumes that this is not a pass trough query, and so it starts interpreting it?

    hth

  5. #5
    Join Date
    Oct 2002
    Location
    UK
    Posts
    21
    Very Simple really JDionne!

    Just make sure the ODBC .Connect property is set before the .SQL - if Access _knows_ it's going to be a passthrough it won't check for syntax.

    Conch.

Posting Permissions

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