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.

The error would imply that the SQL itself is incorrect, rather than the code necessarily. What is the SQL in question?

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Added this once only,future posts that break the Forum Rules may be closed,so READ THEM

In a similar vein to what the big R asked, is it always the same query that fails (and does this particular query ever work)? Also, what are you doing with the recordset produced? You don't seem to be assigning it anywhere.

Richard

Surely, of the two of us, you would be the big R?

The mighty big R's ...

Originally Posted by romperstomper

Surely, of the two of us, you would be the big R?

Certainly in terms of girth and height Probably not brain power, unfortunately