Solved: SQL JOIN error

[lifeson]

I am getting a problem when trying to join 2 related access tables in an excel vba query
[vba]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;"[/vba]

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]

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]

Hi,

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

[lifeson]

Removing the OWNER ACCESS seems too have done the trick

This is the full module
[VBA]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
[/VBA]