vba sql insert error

[mlcalves]

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

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

 

[BarryL]

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]

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]

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]

Maybe:

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

 

[mlcalves]

Maybe:

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

query = "INSERT INTO RegistosDb ([Data], [User], [NomeFicheiro]) VALUES (Now(), 'teste', 'ficheiro1')"

but doesn't accept:

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

I don't understand why, because i have this variable:

user = Application.UserName
ficheiro = ThisWorkbook.Name

Another thing, i had to comment the line above to work:

'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]

What error do you get with the new INSERT statement?

 

[mlcalves]

What error do you get with the new INSERT statement?

Forget, sorry.

is working:

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]

hi, just to say, that i change the code to this:

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]

in the code above, i had:

  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