vba sql insert error

mlcalves

New Member
Joined
Mar 10, 2021
Messages
36
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Hi,

I'm getting error in the below code. I think i have the INSERT statement wrong.

can anyone help? thank you

i have thins on e

VBA Code:
Private Sub Workbook_Open()

Dim data As Date
Dim user As String
Dim ficheiro As String

'Creating objects of Connection and Recordset
Dim conn As New Connection, rec As New Recordset
Dim DBPATH, PRVD, connString, query As String
'Declaring fully qualified name of database. Change it with your database's location and name.
DBPATH = "C:\Users\xrada76\Documents\RegistoDb.accdb"
'This is the connection provider. Remember this for your interview.
PRVD = "Microsoft.ace.OLEDB.12.0;"
'This is the connection string that you will require when opening the the connection.
connString = "Provider=" & PRVD & "Data Source=" & DBPATH
'opening the connection
conn.Open connString

data = Now
user = Application.UserName
ficheiro = ThisWorkbook.Name

'the query I want to run on the database.
query = " INSERT INTO RegistosDb (Data, User, NomeFicheiro) VALUES ('" & data & "', '" & user & "', '" & ficheiro & "') "

'running the query on the open connection. It will get all the data in the rec object.
rec.Open query, conn

'closing the connections
rec.Close
conn.Close


End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,436
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
Hello, What error do you receive?

RegistoDb is the name of the access database, is it also the name of the table you want to update?
 

mlcalves

New Member
Joined
Mar 10, 2021
Messages
36
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Hello, What error do you receive?

RegistoDb is the name of the access database, is it also the name of the table you want to update?
Hi,

the name of access database is RegistoDb.accdb

The table is RegistosDb


Run-time error '-2147217900 (80040e14)':

Automation error



thank you
 

mlcalves

New Member
Joined
Mar 10, 2021
Messages
36
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
For example, i tried this, that works:



Sub ADO_Connection()

'Creating objects of Connection and Recordset
Dim conn As New Connection, rec As New Recordset
Dim DBPATH, PRVD, connString, query As String
'Declaring fully qualified name of database. Change it with your database's location and name.
DBPATH = "C:\Users\xrada76\Documents\RegistoDb.accdb"
'This is the connection provider. Remember this for your interview.
PRVD = "Microsoft.ace.OLEDB.12.0;"
'This is the connection string that you will require when opening the the connection.
connString = "Provider=" & PRVD & "Data Source=" & DBPATH
'opening the connection
conn.Open connString
'the query I want to run on the database.
query = "SELECT * from RegistosDb;"
'running the query on the open connection. It will get all the data in the rec object.
rec.Open query, conn
'clearing the content of the cells
Cells.ClearContents
'getting data from the recordset if any and printing it in column A of excel sheet.
If (rec.RecordCount <> 0) Then
Do While Not rec.EOF
Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _
rec.Fields(1).Value
rec.MoveNext
Loop
End If
'closing the connections
rec.Close
conn.Close

End Sub



But if i change to INSERT, it gives error:



Sub ADO_Connection()

'Creating objects of Connection and Recordset
Dim conn As New Connection, rec As New Recordset
Dim DBPATH, PRVD, connString, query As String
'Declaring fully qualified name of database. Change it with your database's location and name.
DBPATH = "C:\Users\xrada76\Documents\RegistoDb.accdb"
'This is the connection provider. Remember this for your interview.
PRVD = "Microsoft.ace.OLEDB.12.0;"
'This is the connection string that you will require when opening the the connection.
connString = "Provider=" & PRVD & "Data Source=" & DBPATH
'opening the connection
conn.Open connString
'the query I want to run on the database.
query = "INSERT INTO RegistosDb (Data, User, NomeFicheiro) VALUES (Now(), 'user1', 'ficheiro1')"
'running the query on the open connection. It will get all the data in the rec object.
rec.Open query, conn

'closing the connections
rec.Close
conn.Close

End Sub




It gives error:

Run-time error '-2147217900 (80040e14)':


Syntax error in INSERT INTO STATEMENT




I don't understand why =/
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,559
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Maybe:

VBA Code:
query = "INSERT INTO RegistosDb ([Data], [User], [NomeFicheiro]) VALUES ('" & data & "', '" & user & "', '" & ficheiro & "')"
 

mlcalves

New Member
Joined
Mar 10, 2021
Messages
36
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Maybe:

VBA Code:
query = "INSERT INTO RegistosDb ([Data'], [User], [NomeFicheiro]) VALUES ('" & data & "', '" & user & "', '" & ficheiro & "')"

Thank you a lot. but i continue with some problems.

the insert worked, but not with the variable:

system accepts
VBA Code:
query = "INSERT INTO RegistosDb ([Data], [User], [NomeFicheiro]) VALUES (Now(), 'teste', 'ficheiro1')"

but doesn't accept:

VBA Code:
" INSERT INTO RegistosDb ([Data], [User], [NomeFicheiro]) VALUES (Now(), '" & user & "', '" & ficheiro &"') "

I don't understand why, because i have this variable:
VBA Code:
user = Application.UserName
ficheiro = ThisWorkbook.Name

Another thing, i had to comment the line above to work:
VBA Code:
'rec.Close

Because it gaves the error: "
Run-time error '3704':

Application-defined or object-defined error
".


Can you help or anybody please?

Thank you
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,559
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

What error do you get with the new INSERT statement?
 

mlcalves

New Member
Joined
Mar 10, 2021
Messages
36
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
What error do you get with the new INSERT statement?
Forget, sorry.

is working:

VBA Code:
Private Sub Workbook_Open()

Dim data As Date
Dim user As String
Dim ficheiro As String

'Creating objects of Connection and Recordset
Dim conn As New Connection, rec As New Recordset
Dim DBPATH, PRVD, connString, query As String
'Declaring fully qualified name of database. Change it with your database's location and name.
DBPATH = "C:\Users\xrada76\Documents\RegistoDb.accdb"
'This is the connection provider. Remember this for your interview.
PRVD = "Microsoft.ace.OLEDB.12.0;"
'This is the connection string that you will require when opening the the connection.
connString = "Provider=" & PRVD & "Data Source=" & DBPATH
'opening the connection
conn.Open connString

data = Now
user = Application.UserName
ficheiro = ThisWorkbook.Name

'the query I want to run on the database.
query = " INSERT INTO RegistosDb ([Data], [User], [NomeFicheiro]) VALUES (Now(), '" & user & "', '" & ficheiro & "') "

'running the query on the open connection. It will get all the data in the rec object.
rec.Open query, conn

'closing the connections
'rec.Close
conn.Close

End Sub
 

mlcalves

New Member
Joined
Mar 10, 2021
Messages
36
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
hi, just to say, that i change the code to this:

VBA Code:
Private Sub Workbook_Open()

  Dim user       As String
  Dim ficheiro   As String
  Dim conn       As New ADODB.Connection
  Dim DBPATH     As String, _
      PRVD       As String, _
      connString As String, _
      strSQL     As String, _
      lAffected  As Long
      
' Declaring fully qualified name of database. Change it with your database's location and name.
  DBPATH = "C:\Users\myuser\Documents\RegistoDb.accdb"
' This is the connection provider. Remember this for your interview.
  PRVD = "Microsoft.ace.OLEDB.12.0;"
' This is the connection string that you will require when opening the the connection.
  connString = "Provider=" & PRVD & "Data Source=" & DBPATH

  user = Application.UserName
  ficheiro = ThisWorkbook.Name

' the query I want to run on the database.
  strSQL = "INSERT INTO RegistosDb ([Data], [User], [NomeFicheiro]) VALUES (Now(), '" & user & "', '" & ficheiro & "');"

  With conn
    .Open connString
    .Execute strSQL, lAffected
    .Close
  End With
  Debug.Print IIf(lAffected > 0, "Success", "Failed")

  Set conn = Nothing

End Sub
 

mlcalves

New Member
Joined
Mar 10, 2021
Messages
36
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
in the code above, i had:

VBA Code:
  connString = "Provider=" & PRVD & "Data Source=" & DBPATH & ";MS Access;PWD=1421"

but doesn't work. i need to have the database access protected.

can anyone help?



thank you
 

Forum statistics

Threads
1,143,840
Messages
5,721,100
Members
422,339
Latest member
SHIVATVM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top