PDA

View Full Version : ODBC in SQL error



MrYemin09
12-22-2009, 02:59 PM
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).

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



Very Thanks!

bonyclyd
12-22-2009, 04:04 PM
Hi

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

HTH
SJOO

MrYemin09
12-23-2009, 03:38 AM
Hi,
I tested it , but no result .

Very Thanks bonyclyd!

geekgirlau
12-23-2009, 03:55 PM
Your join is incorrect


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"



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
12-23-2009, 05:15 PM
Thanks geekgirlau!
I try test your's code ,but not result show in syntax error.
(.Refresh BackgroundQuery:=False)

geekgirlau
12-23-2009, 05:58 PM
This is different to the code you've posted above - please post your code with the changes you've made.