Excel VBA ADO SQL JOIN

Question

I'm using Excel & VBA below is the code which generates SQL statement:

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

And here is SQL generated from the above code:

Debug Print:

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

When I run the code I am getting the following error:

Can someone help point out what I'm doing wrong?

Thanks

Post the code and error message as text instead of posting it as image. — Pரதீப், Oct 02 '16 at 13:54
BTW - I am querying an Excel spreadsheet from another Excel sheet just in case I didn't make it clear. Anyways, I am very sure that all those tables exist as I have other codes that are retrieving data from these tables and are working fine. Surely if a table didn't exist then it wouldn't throw 'Syntax error'? — Zahanghir, Oct 02 '16 at 15:04
Do be aware you are using a deprecated SQL syntax, specifically implicit joins instead of explicit joins (the ANSI '92 standard). Consider replacing `WHERE` clauses with `INNER JOIN` for readability and clarity. See: http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins. — Parfait, Oct 02 '16 at 17:15

score 0 · Accepted Answer · answered Oct 02 '16 at 15:38

0

SOLVED -

I was using an SQL keyword as an alias in my SQL, e.g:

[refdata_models$] mod,

I realized 'mod' is a keyword. And this was creating the problem. I changed the alias to something else and it is working fine now.

answered Oct 02 '16 at 15:38

Zahanghir

547
4
9
22

Excel VBA ADO SQL JOIN

1 Answers1