How to create INSERT INTO of several registries using Driver ODBC Excel

I am using an Excel Sheet to collect manual data from usertrying to load these data on a Excel Binary (.xlsb) database by Driver ODBC Excel.

Basically, the vba code is looping through the lines filled by the user to create an INSERT INTO SQL statement.

However, following error is given: “[Microsoft][Driver ODBC Excel]Semicolon (;) missing at the end of SQL instruction”

Can you help me?

Following is an example of the SQL Statement:

INSERT INTO [dbfalha$](db_id,db_line,db_order,db_item,db_coil,db_defect,db_action,db_position,db_marking,db_logdate,db_logtime) Values(100001,'Fundição Contínua de Bronze',23134568,1900003780004,1,'teste 1','teste 1r',10,'com tinta','24/12/2021','00:47'),(100002,'Fundição Contínua de Bronze',23134568,1900003780004,1,'teste 2','teste 2r',20,'com tinta','24/12/2021','00:47');

Following is vba code:

'Creating loop string for data insertion on fails database
Range("S12").Select
preencher = True
sSQLString = "INSERT INTO [dbfalha$](" & Range("S11").Value & ") Values"
sSQLString2 = ""

Do While preencher = True
If ActiveCell.Value <> vbNullString Then
    sSQLString2 = sSQLString2 & "(" & max_id & "," & ActiveCell.Value & "," & db_logdate & "," & db_logtime & "),"
    max_id = max_id + 1
    ActiveCell.Offset(1, 0).Select
Else
preencher = False
End If
Loop

sSQLString = sSQLString & sSQLString2
sSQLString = Left(sSQLString, (Len(sSQLString) - 1))
sSQLString = sSQLString & ";"

Range("S24") = sSQLString

'SQL consult to insert data into database
    Conn.Open sconnect
    mrs.Open sSQLString, Conn
    Conn.Close

Related Posts

About Alexis Taylor