Executing queries in Access from Excel

Hi,

I am making a tool that has as a front end an Excel workbook and as a back end an Access DB. I am storing and processing the data at the DB. All the data processing and storing is triggered by buttons in the workbook, therefore I have a set of macros that runs queries in the DB. All the queries that I run are defined at the access data base.

Here the problem: I run the queries in the Excel macros by their names, and this sometimes works and sometimes not. Bellow a piece of my code.

--------------------------------------------------------------------------------------------------------------------------
Public Function EjecutarSQL(c As String) As Boolean
  Dim cn As ADODB.Connection
  Dim Path as String
  Dim StrError As String
  
  EjecutarSQL = True
  On Error GoTo ADO_ERROR
  
  If Not AbrirConexBD(cn) Then
    EjecutarSQL = False
    Exit Function
  End If
  
  cn.Execute c
    
  If Not CerrarConexBD(cn) Then
    EjecutarSQL = False
    Exit Function
  Else
    Exit Function
  End If

ADO_ERROR:

  If Err <> 0 Then
    StrError = "Error durante la ejecutión de la consulta " + c + ". Mensage técnico: " +   Err.Description
    MsgBox StrError
    Err.Clear
    EjecutarSQL = False
    MsgBox "La consulta no fue realizada correctamente."
    Exit Function
  End If
End Function
--------------------------------------------------------------------------------------------------------------------------
Public Function AbrirConexBD(cn As ADODB.Connection) As Boolean
  'Función para abrir la conexión a la BD
  Dim Path As String
  Dim StrError As String
  
  AbrirConexBD = True
  On Error GoTo ADO_ERROR
   
  Set cn = New ADODB.Connection
  Path = ActiveWorkbook.Path + "\"
  cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + _
                        "bdcc.accdb;Persist Security Info=False"
  cn.Open

ADO_ERROR:

  If Err <> 0 Then
    StrError = "Error al abrir la conexión a la BD. Mensage técnico: " + Err.Description
    MsgBox StrError
    Err.Clear
    AbrirConexBD = False
    Exit Function
  End If
  
End Function
--------------------------------------------------------------------------------------------------------------------------
Public Function CerrarConexBD(cn As ADODB.Connection) As Boolean
  'Función para cerrar la conexión a la BD
  Dim StrError As String
  
  CerrarConexBD = True
  On Error GoTo ADO_ERROR
  
  If cn.State <> adStateClosed Then
    cn.Close
  End If
  If Not cn Is Nothing Then Set cn = Nothing

ADO_ERROR:

  If Err <> 0 Then
    StrError = "Error al cerrar la conexión a la BD. Mensage técnico: " + Err.Description
    MsgBox StrError
    Err.Clear
    CerrarConexBD = False
    Exit Function
  End If
End Function

--------------------------------------------------------------------------------------------------------------------------

In the function EjecutarSQL "c" is for example an Insert query defined in the DB and named C1, thus when I want to execute this query I write EjecutarSQL("C1") in my code, and not EjecutarSQL("Insert (x1, x2, x3) into ..."). I want to keep running the queries in this way because this gives me flexibility to modify my tool without modifying the code.

The error I sometimes get is in the line “cn.Execute c”, and the message is invalid SQL syntax.

Any idea or suggestion is welcomed. Thanks in advance.

Felipe99.