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