PDA

View Full Version : Solved: SQL JOIN error



lifeson
02-01-2008, 07:17 AM
I am getting a problem when trying to join 2 related access tables in an excel vba query
src = "SELECT TblDiscountsApplied.LeadNumber, " & _
"TblDiscountsApplied.QuoteID, " & _
"TblDiscountsApplied.ResourceType, " & _
"TblDiscountsApplied.DiscountCodeID, " & _
"TblDiscount.DiscountDescription, " & _
"TblDiscountsApplied.Value " & _
"FROM TblDiscountsApplied " & _
"INNER JOIN TblDiscount " & _
"ON TblDiscountsApplied.DiscountCodeID = TblDiscount.DiscountCodeID" & _
"WHERE (((TblDiscountsApplied.LeadNumber) = 30520827) And ((TblDiscountsApplied.quoteID) = 'A'))" & _
"WITH OWNERACCESS OPTION;"

The error says join expression not supported.
The two tables linked are TblDiscount which holds the discount code and discount description and TblDiscountsApplied which holds the discounts applied as a code.
I have joined the two tables as a query in access an it worked fine so I copied the SQL into VBA and now I get the error

Obviously I have the src wrong
Can anyone point me in the right direction please?

RichardSchollar
02-01-2008, 01:25 PM
Hi

If you're trying to execute this via an ODBC/OLEDB connection then I susepct that the "WITH OWNERACCESS OPTION" is throwing it off - I don't think there's anything amiss with the Join.

Richard

asingh
02-01-2008, 07:54 PM
Hi,

After You have constructed the SQL string "SRC" how are you exactly processing it via VBA..to execute the query..????

lifeson
02-02-2008, 02:43 AM
Removing the OWNER ACCESS seems too have done the trick

This is the full module
Private Sub GetDiscounts()
'Get quotes listed against lead number selected
Application.Cursor = xlWait

Dim DBFullName As String
Dim Cnct As String, src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Dim leadID As Long
Dim quoteID As String
Dim resourceID As String

leadID = lstLeads.Value
quoteID = lstQuotes.Value
resourceID = lstQuotes.List(lstQuotes.ListIndex, 2)
' Database information
DBFullName = "C:\Program Files\FieldSalesApplication\PremierPlusField.mdb"

' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct
' Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
' Filter
src = "SELECT TblDiscountsApplied.LeadNumber, " & _
"TblDiscountsApplied.QuoteID, " & _
"TblDiscountsApplied.ResourceType, " & _
"TblDiscountsApplied.DiscountCodeID, " & _
"TblDiscount.DiscountDescription, " & _
"TblDiscountsApplied.Value " & _
"FROM TblDiscountsApplied " & _
"INNER JOIN TblDiscount " & _
"ON TblDiscountsApplied.DiscountCodeID = TblDiscount.DiscountCodeID " & _
"WHERE (((TblDiscountsApplied.LeadNumber)=" & leadID & ") " & _
"AND ((TblDiscountsApplied.QuoteID)='" & quoteID & "') " & _
"AND ((TblDiscountsApplied.ResourceType)='" & resourceID & "'));"
.Open Source:=src, ActiveConnection:=Connection
' Write the field names
Sheets("TblDiscountApplied").Select
Cells.Clear
For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next
' Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset Recordset
Set Recordset = Nothing
End With
Connection.Close
Set Connection = Nothing
Application.Cursor = xlDefault
End Sub