SQL Server Performance

SQL query in MS access 2003

Discussion in 'T-SQL Performance Tuning for Developers' started by vaddi, Apr 12, 2007.

  1. vaddi New Member


    I have a query in sql server 2000 , which I should make it work in MS access 2003.

    Can anybody help me out in coverting the below query.

    SELECT A.Project_ID, A.Title, A.comm1 AS Comments, A.Partner AS PM, A.Staff_Assigned AS TL, A.Contact_Name AS FL, A.MD, A.Status, A.Project_Type,
    ISNULL(B.Delivered_Date, B.Delivery_Date) AS Start_Date, ISNULL(C.Delivered_Date, C.Delivery_Date) AS End_Date FROM dbo.PROJECT A LEFT OUTER JOIN dbo.PROJDATE B ON A.Project_ID = B.Project_ID AND B.Date_Type = 'Start Date' LEFT OUTER JOIN
    dbo.PROJDATE C ON A.Project_ID = C.Project_ID AND C.Date_Type = 'End Date'

  2. Adriaan New Member

    In an Access MDB/MDE, you can use ADO, DAO or a pass-through query to pass any valid T-SQL script to SQL Server. Use a ptq for as the recordsource for comboboxes, listboxes, reports, etc. ADO and DAO are not for binding data in MDB/MDEs.

    <air code>
    ... create sql statement ...
    ... set a QueryDef object to the ptq ...
    ... add the sql string ...
    ... set the connect string for the querydef ...
    ... close the QueryDef ...
    ... open the report ...
    ... reset the SQL string and connection string on the QueryDef ...
    </air code>

    In an Access ADP/ADE, you can connect directly to SQL Server, and only T-SQL syntax can be used.
  3. vaddi New Member

    Hi thanks for the reply..

    Might sound silly , but where can I find the option for PTQ.

    I am searching for it, but could not figure it out.

  4. vaddi New Member


    Thank you very much . I have got it working.

  5. vaddi New Member


    I have got a question. I am running the report using the PTQ.

    But it always prompts me for the DSN connection..and the window just keeps on poping up , when I go the design view.

    what should I do.

  6. Adriaan New Member

    Work out the air code that I posted, put it in an unbound Access form.

    Add some unbound fields and comboboxes to the form that you can concatenate as your WHERE clause.

    Dim strSQL As String
    Dim q As DAO.QueryDef

    If Me![colA] <> vbNullString Then
    strSQL = "colA LIKE '" & Replace(Me![colA], Chr(39), Chr(39) & Chr(39)) & "'"
    End If

    If Me![colB] <> vbNullString Then
    If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
    strSQL = strSQL & "colB LIKE '" & Replace(Me![colB], Chr(39), Chr(39) & Chr(39)) & "'"
    End If

    If Len(strSQL) > 0 Then strSQL = " WHERE " & strSQL

    strSQL = "SELECT column_list FROM table " & strSQL

    Set q = CurrentDb().QueryDefs(ptq_name)
    q.SQL = strSQL
    q.ConnectString = "................."
    Set q = Nothing

    DoCmd.OpenReport report_name, vbPreview

    Set q = CurrentDb().QueryDefs(ptq_name)
    q.SQL = "()"
    q.ConnectString = vbNullstring

    [edit - don't ever forget to set the object to nothing]
    Set q = Nothing

Share This Page