Excel中VBA ADO SQL JOIN

问题描述：

我用Excel &以下VBA是生成SQL语句代码：Excel中VBA ADO SQL JOIN

Function getVehicleById(p_vehicleId As Long) As clsVehicle 

Dim dbConfig As clsConfig_Db 

Dim queryString As String 
Dim selectClause As String 
Dim fromClause As String 
Dim whereClause As String 

Dim conDb As New ADODB.Connection 
Dim rs As New ADODB.Recordset 

Dim vehicle As New clsVehicle 

Set dbConfig = Factory.getDbConfig 

selectClause = " SELECT * " 

fromClause = " FROM " & _ 
    " [" & dbConfig.VEHICLES_TABLE_NAME & "$] veh " & _ 
    ",[" & dbConfig.CONTACTS_TABLE_NAME & "$] con " & _ 
    ",[" & dbConfig.TRANSMISSION_TYPES_TABLE_NAME & "$] tt " & _ 
    ",[" & dbConfig.FUEL_TYPES_TABLE_NAME & "$] ft " & _ 
    ",[" & dbConfig.COLOURS_TABLE_NAME & "$] col " & _ 
    ",[" & dbConfig.MAKES_TABLE_NAME & "$] mke " & _ 
    ",[" & dbConfig.MODELS_TABLE_NAME & "$] mod " & _ 
    ",[" & dbConfig.ENGINE_SIZES_TABLE_NAME & "$] es " 

whereClause = " WHERE " & _ 
    " veh." & dbConfig.VEH_CON_ID_COLUMN_NAME & " = " & " con." & dbConfig.CON_ID_COLUMN_NAME & _ 
    " AND veh." & dbConfig.VEH_TT_ID_COLUMN_NAME & " = " & " tt." & dbConfig.TT_ID_COLUMN_NAME & _ 
    " AND veh." & dbConfig.VEH_FT_ID_COLUMN_NAME & " = " & " ft." & dbConfig.FT_ID_COLUMN_NAME & _ 
    " AND veh." & dbConfig.VEH_COL_ID_COLUMN_NAME & " = " & " col." & dbConfig.COL_ID_COLUMN_NAME & _ 
    " AND veh." & dbConfig.VEH_ES_ID_COLUMN_NAME & " = " & " es." & dbConfig.ES_ID_COLUMN_NAME & _ 
    " AND veh." & dbConfig.VEH_MOD_ID_COLUMN_NAME & " = " & " mod." & dbConfig.MOD_ID_COLUMN_NAME & _ 
    " AND mod." & dbConfig.MOD_MKE_ID_COLUMN_NAME & " = " & " mke." & dbConfig.MKE_ID_COLUMN_NAME & _ 
    " AND veh." & dbConfig.VEH_ID_COLUMN_NAME & " = " & p_vehicleId 

queryString = selectClause & fromClause & whereClause 

Debug.Print queryString 

conDb.Open dbConfig.DSN_NAME 
rs.Open queryString, conDb 

If Not IsNull(rs.Fields(dbConfig.VEH_ID_COLUMN_NAME).value) Then 
    vehicle.id = CLng(rs.Fields(dbConfig.VEH_ID_COLUMN_NAME).value) 
End If 

If Not IsNull(rs.Fields(dbConfig.VEH_VIN_COLUMN_NAME).value) Then 
    vehicle.vin = CStr(rs.Fields(dbConfig.VEH_VIN_COLUMN_NAME).value) 
End If 

If Not IsNull(rs.Fields(dbConfig.VEH_VRM_COLUMN_NAME).value) Then 
    vehicle.vrm = CStr(rs.Fields(dbConfig.VEH_VRM_COLUMN_NAME).value) 
End If 

If Not IsNull(rs.Fields(dbConfig.MKE_ID_COLUMN_NAME).value) Then 
    vehicle.makeId = CLng(rs.Fields(dbConfig.MKE_ID_COLUMN_NAME).value) 
End If 

If Not IsNull(rs.Fields(dbConfig.MKE_MAKE_COLUMN_NAME).value) Then 
    vehicle.make = CStr(rs.Fields(dbConfig.MKE_MAKE_COLUMN_NAME).value) 
End If 

If Not IsNull(rs.Fields(dbConfig.MOD_ID_COLUMN_NAME).value) Then 
    vehicle.modelId = CLng(rs.Fields(dbConfig.MOD_ID_COLUMN_NAME).value) 
End If 

If Not IsNull(rs.Fields(dbConfig.MOD_MODEL_COLUMN_NAME).value) Then 
    vehicle.model = CStr(rs.Fields(dbConfig.MOD_MODEL_COLUMN_NAME).value) 
End If 

If Not IsNull(rs.Fields(dbConfig.ES_ID_COLUMN_NAME).value) Then 
    vehicle.engineSizeId = CLng(rs.Fields(dbConfig.ES_ID_COLUMN_NAME).value) 
End If 

If Not IsNull(rs.Fields(dbConfig.ES_ENGINE_SIZE_COLUMN_NAME).value) Then 
    vehicle.engineSize = CStr(rs.Fields(dbConfig.ES_ENGINE_SIZE_COLUMN_NAME).value) 
End If 

If Not IsNull(rs.Fields(dbConfig.FT_ID_COLUMN_NAME).value) Then 
    vehicle.fuelTypeId = CLng(rs.Fields(dbConfig.FT_ID_COLUMN_NAME).value) 
End If 

If Not IsNull(rs.Fields(dbConfig.FT_FUEL_TYPE_COLUMN_NAME).value) Then 
    vehicle.fuelType = CStr(rs.Fields(dbConfig.FT_FUEL_TYPE_COLUMN_NAME).value) 
End If 

If Not IsNull(rs.Fields(dbConfig.TT_ID_COLUMN_NAME).value) Then 
    vehicle.transmissionId = CLng(rs.Fields(dbConfig.TT_ID_COLUMN_NAME).value) 
End If 

If Not IsNull(rs.Fields(dbConfig.TT_TRANSMISSION_TYPE_COLUMN_NAME).value) Then 
    vehicle.transmission = CStr(rs.Fields(dbConfig.TT_TRANSMISSION_TYPE_COLUMN_NAME).value) 
End If 

If Not IsNull(rs.Fields(dbConfig.COL_ID_COLUMN_NAME).value) Then 
    vehicle.colourId = CLng(rs.Fields(dbConfig.COL_ID_COLUMN_NAME).value) 
End If 

If Not IsNull(rs.Fields(dbConfig.COL_COLOUR_COLUMN_NAME).value) Then 
    vehicle.colour = CStr(rs.Fields(dbConfig.COL_COLOUR_COLUMN_NAME).value) 
End If 

If Not IsNull(rs.Fields(dbConfig.CON_ID_COLUMN_NAME).value) Then 
    vehicle.contactId = CLng(rs.Fields(dbConfig.CON_ID_COLUMN_NAME).value) 
End If 

rs.Close 
conDb.Close 
Set rs = Nothing 
Set conDb = Nothing 

Set getVehicleById = vehicle 
Set vehicle = Nothing 

End Function

这里是从上面的代码生成的SQL：

调试打印：

SELECT * 
FROM [vehicles$] veh, 
     [contacts$] con, 
     [refdata_transmission_types$] tt, 
     [refdata_fuel_types$] ft, 
     [refdata_colours$] col, 
     [refdata_makes$] mke, 
     [refdata_models$] mod, 
     [refdata_engine_sizes$] es 
WHERE veh.veh_con_id = con.con_id 
     AND veh.veh_tt_id = tt.tt_id 
     AND veh.veh_ft_id = ft.ft_id 
     AND veh.veh_col_id = col.col_id 
     AND veh.veh_es_id = es.es_id 
     AND veh.veh_mod_id = mod.mod_id 
     AND mod.mod_mke_id = mke.mke_id 
     AND veh.veh_id = 1

当我运行代码我a m到处以下错误：

有人能帮助指出什么我做错了吗？

感谢

将代码和错误消息发布为文本，而不是将其作为图像发布。 –

根据要求发布信息。 – Zahanghir

顺便说一句 - 我正在查询另一张Excel表格中的Excel电子表格，以防万一我没有说清楚。无论如何，我非常肯定，所有这些表存在，因为我有其他代码从这些表中检索数据，并正常工作。当然，如果一个表不存在，那么它不会抛出'语法错误'？ – Zahanghir

答

解决 -

我使用一个SQL关键字作为我的SQL别名，e.g：

[refdata_models$] mod,

我意识到， '国防部' 是一个关键字。这就造成了这个问题。我将别名更改为别的，现在工作正常。

Excel中VBA ADO SQL JOIN

相关推荐