SQL Query in Excel VBA

Question

SQL Query in Excel VBA

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Good morning!

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

Now to the problem. I had a spreadsheet in Excel, where I registered the benefit cards received at the company. So I decided to create a form to facilitate the inclusion of the information. Also I took this worksheet and migrated the data to a database created in Access. I've already been able to create the Form:

NowwhatIwantistocreateaquery(SELECT*FROM)andanUPDATE(ifanydataistypedwrongly).

InthemeantimeIpackedthecreationofthequery.

HerearethecodesIused.

HereIcreatetheconnectiontotheDatabase:

PublicenderecoDBAsStringPublicSQLAsStringPublicrsAsADODB.RecordsetPublicmConnAsADODB.ConnectionPublicSubSU_Conectadb()OnErrorGoToerroenderecoDB=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 enter the form information in the worksheet:

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 this 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 when clicking the query button this error is displayed:

I've tried to do the query in many ways and I've always gotten this error.

Basically what I want to do is:

Enter the BP or CPF and click on Query data return on the form.

If someone can give me a light on how to run the query I thank.

ps: I tried the direct access query and it worked.

query excel vba ms-access

asked by anonymous 27.03.2017 / 17:10

2 answers

Toggle Class with Scrolling in Firefox Error when using SQL with javascript

score 0 · Answer 1

Because the DoCmd.RunSQL command is not used to make SELECT it should be used to 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)
 ...

score 0 · Answer 2

Reginaldo,

I tried this way but still had some mistakes.

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

I leave the code below 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 field of the form.

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

Thank you for your support.