SQL Server Performance Forum – Threads Archive

SQL query in MS access 2003

Hello, 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’
Thanks

---

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.

---

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

---

Hi, Thank you very much . I have got it working. Thanks

---

Hi 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. thanks

---

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

---

]]>