Excel Excel VBA error 1004: General ODBC Error

R

rhysgowat

Joined
Jul 23, 2010
Messages
1
Reaction score
0
Hi, I'm trying to run this code, which gets information from an access database and then displays it in excel. This runs fine if I set the red text "WHERE IndMotor.KV =" a number, say 4. But I really need to have the query select one of the values from the DISTINCT (Blue) query. I thought I had gotten my variables right, but it seems to not work. If you could help me at all, that would be appreciated.

Range("BU4").Select
With ActiveSheet.QueryTables.Add(Connection:=temp$, Destination:=Range("BU4"))
.CommandText = "SELECT Distinct IndMotor.KV" _
& Chr(13) & "" & Chr(10) & "FROM IndMotor IndMotor" _
& Chr(13) & "" & Chr(10) & "ORDER BY IndMotor.KV DESC;"
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Dim Mtr1 As Variant
Dim Mtr2 As Variant
Dim Mtr3 As Variant
Dim Mtr4 As Variant
Dim Mtr5 As Variant

Mtr1 = Worksheets("Data_IndMtr").Cells(73, 5)
Mtr2 = Worksheets("Data_IndMtr").Cells(73, 6)
Mtr3 = Worksheets("Data_IndMtr").Cells(73, 7)
Mtr4 = Worksheets("Data_IndMtr").Cells(73, 8)
Mtr5 = Worksheets("Data_IndMtr").Cells(73, 9)


Range("K4").Select
With ActiveSheet.QueryTables.Add(Connection:=temp$, Destination:=Range("K4"))
.CommandText = "SELECT TOP 5 IndMotor.ID, IndMotor.KV, IndMotor.HP" _
& Chr(13) & "" & Chr(10) & "FROM IndMotor IndMotor" _
& Chr(13) & "" & Chr(10) & "WHERE (((IndMotor.KV)='Mtr1'))" _
& Chr(13) & "" & Chr(10) & "ORDER BY IndMotor.HP DESC;"
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
 
Hi,


I see two apparent issues in the red marked code...
a)In the WHERE clause three(3) parentheses open, whereas you are closing two(2)....
b) secondly the apostrophe immediately before 'Mtr1 has to have a closing one 'Mtr1'

You can help me by getting the actual sql which results during the execution of these lines of VBA code. If I see that sql-text line It will be easy for me to help you out.




Range("K4").Select
With ActiveSheet.QueryTables.Add(Connection:=temp$, Destination:=Range("K4"))
.CommandText = "SELECT TOP 5 IndMotor.ID, IndMotor.KV, IndMotor.HP" _
& Chr(13) & "" & Chr(10) & "FROM IndMotor IndMotor" _
& Chr(13) & "" & Chr(10) & "WHERE (((IndMotor.KV)='Mtr1))" _
& Chr(13) & "" & Chr(10) & "ORDER BY IndMotor.HP DESC;"
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
 
Back
Top