Excel VBA ADO SQL S'INSCRIRE

J'utilise Excel & VBA ci-dessous est le code qui génère une instruction SQL:

Function getVehicleById(p_vehicleId As Long) As clsVehicle Dim dbConfig As clsConfig_Db Dim querySsortingng As Ssortingng Dim selectClause As Ssortingng Dim fromClause As Ssortingng Dim whereClause As Ssortingng 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 querySsortingng = selectClause & fromClause & whereClause Debug.Print querySsortingng conDb.Open dbConfig.DSN_NAME rs.Open querySsortingng, 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 

Et voici SQL généré à partir du code ci-dessus:

Déboguer Imprimer:

• Remplissez la forme de l'user avec datatables du classur fermé, selon 2 critères
• Excel VBA Type ODBC Mismatch dans l'expression
• Erreur Mise à jour des loggings via ADODB Excel VBA dataset
• Excel VBA pour interroger la database MS Access sur Sharepoint avec ADODB – "Pas un nom de file valide"
• Mettre à jour Excel avec ADODB avec Workbook Open

 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 

Lorsque je lance le code, je reçois l'erreur suivante:

Quelqu'un peut-il indiquer ce que je fais mal?

Merci

• ADODB Paste Très lent
• Importation de données depuis Access vers Excel en utilisant SQL SELECT WHERE <DATE>
• Excel VBA – gamme de search et jeux d'loggings SQL ADODB connectés pour correspondre l'écriture Set de résultats dans la colonne
• Q: problème Excel VBA ADO RecordSet Cell Version
• VBA obtient des lignes de sortie DBMS depuis la connection ADODB

 [refdata_models$] mod,