Ok, first off: I\'m trying to pull my own query from an Access database. Now the query is already made, and i\'ve tried "SELECT * FROM query_name" but that doesn\'t work. Doesn\'t seem to want to let me pull from a query, which is fine. (Unless there is a way to do this. I\'d much rather do it this way.)
\r\n
\r\nAs I tried to work around it I found the SQL syntax from Access and plugged it in to my execute command like so:
\r\n
\r\n Set dbConn1 = CreateObject("ADODB.Connection"): dbConn1.Open "MRP", "", ""
\r\n Set dbrs = dbConn1.Execute("SELECT ServiceOrders.ServiceOrder, ServiceOrders.JobNo, Orders.OrderDate, Customers.ContactFirstName, Customers.ContactLastName, Orders.[Job Address], Orders.Street, ServiceOrders.Occupied, Customers.PhoneNumber " & _
\r\n "FROM Purchase Orders INNER JOIN Customers INNER JOIN Orders ON Customers.AccountNumber = Orders.[Account No] INNER JOIN ServiceOrders ON Orders.[Lighthouse Job No] = ServiceOrders.JobNo AND Orders.[Lighthouse Job No] = ServiceOrders.JobNo ON [Purchase Orders].JobNumber = Orders.[Lighthouse Job No] " & _
\r\n "WHERE ((([Purchase Orders].Closed)=0));")
\r\n
\r\nAnd that gives me an error on the inner From clause. I originally had parenthesis around the inner join parts, and that generated an error in the Inner Join statements. Here is the SQL directly from Access:
\r\n
\r\nSELECT ServiceOrders.ServiceOrder, ServiceOrders.JobNo, Orders.OrderDate, Customers.ContactFirstName, Customers.ContactLastName, Orders.[Job Address], Orders.Street, ServiceOrders.Occupied, Customers.PhoneNumber
\r\nFROM [Purchase Orders] INNER JOIN ((Customers INNER JOIN Orders ON Customers.AccountNumber = Orders.[Account No]) INNER JOIN ServiceOrders ON (Orders.[Lighthouse Job No] = ServiceOrders.JobNo) AND (Orders.[Lighthouse Job No] = ServiceOrders.JobNo)) ON [Purchase Orders].JobNumber = Orders.[Lighthouse Job No]
\r\nWHERE ((([Purchase Orders].Closed)=0));
\r\n
\r\nAny help would be GREATLY appreciated.
\r\n
\r\nAlso, I want to display and print reports from the results of these recordsets. What would be the best way to go about this? Recordsets could be as large as 1,000 records or more, but will usually hover around the 200-500 mark.
\r\n
\r\nThank you all for your time.