Way OT excel help

Way off topic but someone may be able to help
Need abit of excel help if we have any gurus out there

I am using vba in excel to get data from an Acccess database.
I can get it to run querrys on one table but when I join two related tables together I get an error
This is the code:

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 message says join method not suppoted.

Anybody know where I have gone wrong (statring the project is where I really went wrong ) or the correct syntax for joining two tables in a VBA querry?
 
lifeson said:
Way off topic but someone may be able to help
Need abit of excel help if we have any gurus out there

I am using vba in excel to get data from an Acccess database.
I can get it to run querrys on one table but when I join two related tables together I get an error
This is the code:

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 message says join method not suppoted.

Anybody know where I have gone wrong (statring the project is where I really went wrong ) or the correct syntax for joining two tables in a VBA querry?
No idea. ;)
 

bonj2

Guest
lifeson said:
Way off topic but someone may be able to help
Need abit of excel help if we have any gurus out there

I am using vba in excel to get data from an Acccess database.
I can get it to run querrys on one table but when I join two related tables together I get an error
This is the code:

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 message says join method not suppoted.

Anybody know where I have gone wrong (statring the project is where I really went wrong ) or the correct syntax for joining two tables in a VBA querry?
Try

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;"

spot the difference?
 

yenrod

Guest
When I left school I learnt about Excel.

It puzzled me to distraction BUT now I'm fairly conversant with it but that went by my head like a jet....

Hope you solve the prob'. !
 
It's reporting the error though and I'm guessing Lifeson didn't type that but used Excels Macros/codebuilder to do it (could well be wrong though, wouldn't be the first time).

Is it possibly to do with attempting to join two different types of field ie one is text (but only contains numbers) and the other a number or you've got the join the wrong way around and it needs to be an outer join (one to many, many to one) or it isn't a unique field in one of the tables. They're my number one table join culprits. Don't ask me about the VBA though.
 

Abitrary

New Member
Bonj has already corrected it. It's a typo in the sql being sent to the database meaning that some words have got mashed together.
 

bonj2

Guest
andygates said:
Um, Excel does support the JOIN method?
he's calling through to access:

lifeson said:
Way off topic but someone may be able to help
Need abit of excel help if we have any gurus out there

I am using vba in excel to get data from an Acccess database.
I can get it to run querrys on one table but when I join two related tables together I get an error
 
OP
lifeson

lifeson

New Member
Solved

Apparently I didnt neeed the OWNERACCESS option in the SQL
this solved it

"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 & "'));"
 
Top Bottom