PDA

View Full Version : Field Name unknown in SQL with VBA excel



MrYemin09
12-23-2009, 12:27 PM
Hi,
Please check code SQL query .
If use in query : SELECT *
FROM Table1.Table2
WHERE Table1.sid=Table2.sid ,have Resulted.

If use this code : SELECT Table1.sid, Table1.name,Table2.company
FROM Table1.Table2
WHERE Table1.sid=Table2.sid ,have no Reuslted.If this code run ,show message box ( SQL syntax error '1004').


Why unknown (Field Name) in VBA excel with ODBC?

Please Help me!

Sub query1()
Sheets.Add
ActiveSheet.Name = n & "-SQL1"
sSQL = "SELECT Shop.idshop,Shop.sname,Laptop.lname,Laptop.ltype,Laptop.price" _
& "FROM Shop,Laptop " _
& "WHERE Shop.idshop=Laptop.idshop" _
& "ORDER BY Laptop.price" _

ActiveCell.Range("C1") = sSQL
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Test;DBQ=H:\myfolder\VBA\Test"), _
Array("\Test.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Range("A4"))
.CommandText = Array(sSQL)
.Name = "Query From Test"
.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

geekgirlau
12-23-2009, 05:54 PM
Your join is incorrect - I would suggest that you either build queries in Access and copy the SQL string, or do some reading on the syntax of SQL.


sSQL = "SELECT Shop.idshop,Shop.sname,Laptop.lname,Laptop.ltype,Laptop.price " & _
"FROM Shop INNER JOIN Laptop ON Shop.idshop = Laptop.idshop " & _
"ORDER BY Laptop.price"