ODBC in SQL error

[MrYemin09]

Hi,
Please help me!
I writed SQL query in VBA excel with use ODBC connection and Database Access. My database table have 4 tables ,if i writed query use only one table i have result ,but 2 tables joined and use writed query doesn't work it. Please explain me! I showed code following and attached example tables .doc file(ComputerShop).

[vba]Sub query1()
Sheets.Add
ActiveSheet.Name = n & "-SQL1"
sSQL = "SELECT Product.ID_Prod, Product.Model, Product.Country,Laptop.Model,Laptop.Types, Laptop.Price" _
& "FROM Product, Laptop " _
& "WHERE Product.Model=Laptop.Model AND price< 500" _

ActiveCell.Range("C1") = sSQL
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=ComputerShop;DBQ=H:\VBA\SQL\ComputerShop"), _
Array("\ComputerShop.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Range("A4"))
.CommandText = Array(sSQL)
.Name = "SQL From ComputerShop"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

[/vba]

Very Thanks!

[bonyclyd]

Hi

I noticed that your sql statement needs a blank between 'Laptop.Price' and 'FROM'. Plz add a blank between them.

HTH
SJOO

[MrYemin09]

Hi,
I tested it , but no result .

Very Thanks bonyclyd!

[geekgirlau]

Your join is incorrect

[VBA]
sSQL = "SELECT Product.ID_Prod, Product.Model, Product.Country, Laptop.Model, Laptop.Types, " & _
"Laptop.Price " & _
"FROM Product INNER JOIN Laptop ON Product.Model = Laptop.Model " & _
"WHERE price< 500"

[/VBA]

I'm just guessing here as I don't know the structure of your tables. One way to do it is to create the query in Access and test that it's giving you the correct results. When it's done, go into SQL view and copy the SQL string so you can paste it into your code.

[MrYemin09]

Thanks geekgirlau!
I try test your's code ,but not result show in syntax error.
(.Refresh BackgroundQuery:=False)

[geekgirlau]

This is different to the code you've posted above - please post your code with the changes you've made.