SQL query in VBA Excel

Question

good morning!

First I would like to say that I understand almost nothing of Visual Basic and that all I have learned so far is in the race on forums. So if this is too simple to resolve, forgive me.

Now to the problem. I had a spreadsheet in Excel, where I registered the received benefit cards in the company. So I decided to create a form to facilitate the inclusion of information. In addition I took this spreadsheet and migrated the data to a database created in Access. I was able to create the Form:

Now what I want is to create a query (SELECT *FROM) and an UPDATE (if any data is entered erroneously).

However I stopped at the creation of the consultation.

Below are the codes I used.

Here I create the connection to the Database:

Public enderecoDB       As String
Public SQL              As String
Public rs               As ADODB.Recordset
Public mConn            As ADODB.Connection

Public Sub SU_Conectadb()

On Error GoTo erro

enderecoDB = ThisWorkbook.Path & "\database\controlecartoes.mdb"

Set mConn = New ADODB.Connection

mConn.ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" & enderecoDB & ";Jet OLEDB:Database"
mConn.Open
Set rs = New ADODB.Recordset

Exit Sub

erro:

MsgBox "Erro! Não Foi Possível Conectar ao Banco De Dados" & vbNewLine & vbNewLine & Err.Number & Err.Description, vbCritical

Set mConn = Nothing
Exit Sub

End Sub

Public Sub Desconectabd()

On Error Resume Next

mConn.Close

Set mConn = Nothing

On Error GoTo 0

End Sub

Here I insert the form information in the spreadsheet:

Private Sub btn_incluir_Click()
SQL = ""

SQL = "INSERT INTO controle (TP_BENEFICIO, BP, CPF, NOME, DTADM, FILIAL, NRCARTAO, SOLICPOR, DTRECEBE,"

SQL = SQL & " DTENVIOBS, ENVIADORETIRADO, NMMINUTA)"

SQL = SQL & "VALUES ("

SQL = SQL & "'" & controlectform.frmsegndvia.modalidadebox.Value & "', "
SQL = SQL & "'" & controlectform.frmcolabor.boxbp.Value & "',"
SQL = SQL & "'" & controlectform.frmcolabor.cpfbox.Value & "',"
SQL = SQL & "'" & controlectform.frmcolabor.boxnome.Value & "',"
SQL = SQL & "'" & controlectform.frmcolabor.dtadmbox.Value & "',"
SQL = SQL & "'" & controlectform.frmcolabor.filialbox.Value & "',"
SQL = SQL & "'" & controlectform.frmsegndvia.nmcartaobox.Value & "',"
SQL = SQL & "'" & controlectform.frmsegndvia.soliciporbox.Value & "',"
SQL = SQL & "'" & controlectform.frmsegndvia.dtrecebebox.Value & "',"
SQL = SQL & "'" & controlectform.frmsegndvia.dtenviobsbox.Value & "',"
SQL = SQL & "'" & controlectform.frmsegndvia.enviadoretiradobox.Value & "',"
SQL = SQL & "'" & controlectform.frmsegndvia.nmminutabox.Value & "' "
SQL = SQL & ")"

mConn.Execute SQL

MsgBox "Inclusão realizada com sucesso!"

End Sub

And that was my attempt to return the data back to the form:

Private Sub btn_consulta_Click()

SQL = "SELECT [controle$].NOME FROM [controle$] WHERE [controle$].BP =" & controlectform.dadoscolaborpgconsulta.nmbpbox.Value

DoCmd.RunSQL "SQL"

controlectform.dadoscolaborpgconsulta.nomecolaboradorbox.Value = SQL

End Sub

Only that by clicking the query button is shown this error:

I have tried to make the query in several ways and always got this error.

Basically what I want to do is:

inform the BP or CPF and by clicking query the data return in the form.

If anyone can give me a light on how to perform the query I appreciate.

ps.: I tested the direct query on access and it worked.

Answer 1

Reginald,

I tried that way but still had some mistakes.

After a few more searches on the internet I found something that served me perfectly.

I’ll drop the code in case anyone else needs it:

Function SelectNome(Tabela As String, Campo As String, Criterios As String) As Variant

    Dim Resultado As Variant

    Dim sql As String
    Dim cn  As ADODB.Connection
    Dim rs  As ADODB.Recordset

    Set cn = New ADODB.Connection

    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & enderecoDB & ";Jet OLEDB:Database"

    cn.Open

    Set rs = New ADODB.Recordset

    sql = "SELECT " & Tabela & "." & Campo & " From " & Tabela & " where " & Criterios & ";"

    rs.Open sql, cn

    i = 2
    If Not rs.EOF Then
        Do While Not rs.EOF
            Resultado = rs(0)
            rs.MoveNext
        Loop
    End If

    cn.Close

    SelectNome = Resultado

    controlectform.nomecolaboradorbox.Value = Resultado

End Function

By clicking the query button it returns the name in the corresponding form field.

The fields are disabled because this screen will be, at first, only for consultation. If any data is wrong you will need to click change to enable the fields.

Thank you for your support.

Answer 2

Because the command DoCmd.RunSQL does not serve to make SELECT it should be used to make DELETE, INSERT, UPDATE or SELECT INTO

To make selects do something like this:

sSQL = "SELECT [controle$].NOME FROM [controle$] WHERE [controle$].BP =" & controlectform.dadoscolaborpgconsulta.nmbpbox.Value

Dim rs As DAO Recordset
Set rs = CurrentDB.OpenRecordset(sSQL)


 strText = rs.Fields(1)
 ...