Simple Quotes in Access

Asked

Viewed 254 times

1

I have an Excel macro that reads the cell’s contents and saves it in an Access type database, when I try to insert a string that has simple quotes in the middle of the string and the following error: FALTA DE OPERADOR NA CONSULTA, string example:

João Sant'Ana

Follow my VBA code for entering data 

Private Sub btn_carregaJIRA_Click()

MsgBox ("JIRA")

Dim diretorio As String, fileName As String, sheet As Worksheet, total As Integer
Dim fd As Office.FileDialog

'Variaveis do BD Access
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

'Parametrização do Banco de Dados
Set cn = New ADODB.Connection
strDB = ThisWorkbook.Path & "\AlocacaoBD.accdb"
cn.ConnectionString = _
          "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & strDB & ";"
'Abrindo conexão com o banco de dados
cn.Open

'Iniciando o FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Configurando o FileDialog
With fd
    .AllowMultiSelect = False
    .Title = "Selecione o Arquivo do JIRA"

    If .Show = True Then
        fileName = Dir(.SelectedItems(1))
    End If
End With
   'Desabilitando o ScreenUpdating
    Application.ScreenUpdating = False

  'Abrindo o Arquivo
   Workbooks.Open (fileName)
    'Selecionando o arquivo
   Workbooks(fileName).Activate

    linhaJira = 2

    While Cells(linhaJira, 1) <> "Total"

    nomeTask = Cells(linhaJira, 1).Value
    programa = Cells(linhaJira, 2).Value
    epic = Cells(linhaJira, 3).Value
    atividadeTipo = Cells(linhaJira, 4).Value
    nomeColab = Cells(linhaJira, 5).Value
    mes = Month(Now()) - 1
    horaAloc = Cells(linhaJira, 6).Value
    jiraTec = fileName

    StrSql = "INSERT INTO Jira (jira_nomeTask,jira_Program,jira_Epic,jira_Tipo,jira_NomeColab, jira_Mes, jira_HoraAloc, jira_tec)" & _
        " Values ("" & nomeTask & "" ,'" & programa & "','" & epic & "','" & atividadeTipo & "', '" & nomeColab & "', '" & mes & "', '" & horaAloc & "', '" & jiraTec & "' )"

    Set rs = cn.Execute(StrSql)

    linhaJira = linhaJira + 1
    Wend
    Workbooks(fileName).Close
   MsgBox ("Sucesso! Foi Cadastrado: " & linhaJira)
sql vba excel-vba ms-access

1 answer

1


To solve this case you need to duplicate the quotes so that the text is accepted by SQL.

Create this function:

Private Function TrataAspasSimples(ByVal strTexto As String)
    If InStr(strTexto, "'") > 0 Then
        strTexto = Replace(strTexto, "'", "''")
    End If

    TrataAspasSimples = strTexto
End Function

Then where you need to validate single quotes evoke the function:

programa = TrataAspasSimples(CStr(Cells(linhaJira, 2).Value))
epic = TrataAspasSimples(CStr(Cells(linhaJira, 3).Value))
atividadeTipo = TrataAspasSimples(CStr(Cells(linhaJira, 4).Value))
nomeColab = TrataAspasSimples(CStr(Cells(linhaJira, 5).Value))
horaAloc = TrataAspasSimples(CStr(Cells(linhaJira, 6).Value))
jiraTec = TrataAspasSimples(fileName)

The field mes did not put because it is a numeric field integer, has no quotation marks.

Browser other questions tagged sql vba excel-vba ms-access

You are not signed in. Login or sign up in order to post.