Connecting to Access database from Excel sheet - syntax error

Question

Hi I have a database with several fields and I want to insert information only into two of them. To feed this information from excel;

Sub testing()
On Error GoTo errorhandler

'
' testing Macro
'
' Keyboard Shortcut: Ctrl+d
'

    Dim cn As Object
    Dim rs As Object
    Dim strSql As String
    Dim strConnection As String
    Set cn = CreateObject("ADODB.Connection")
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database\Path\Here\Database.accdb"

    strSql = "INSERT INTO Clients (FirstName, Initial Contact) VALUES ('Joe Sixpack', 12/1/2015);"

    cn.Open strConnection
    Set rs = cn.Execute(strSql)

'    rs.Close

    cn.Close

Exit Sub
errorhandler:
    MsgBox (Err.Number & " " & Err.Description)

End Sub

I receive an error message "syntax error in INSERT INTO statement". But I look at the documentation of SQL on W3 schools and I see

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

So the syntax looks correct. I've just verified that the table names are also correct. So where is the syntax error?

Syntax error is the space in column name which needs to be wrapped in square brackets or backticks. Also, dates need to be wrapped in pound/hashtags. — Parfait
– Parfait, Commented Dec 15, 2016 at 18:01

Tim Williams · Accepted Answer · 2016-12-15 17:42:25Z

2

That's not how you represent a date in Access

INSERT INTO Clients (FirstName, [Initial Contact]) 
   VALUES ('Joe Sixpack', #12/1/2015#)

edited Dec 15, 2016 at 17:42

answered Dec 15, 2016 at 17:39

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bdpolinsky Over a year ago

Hi. Now it's a different error: it's asking me to enter in more fields, but according to the documentation I see, the way to only update a couple of fields is specify which fields I want to do; INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway'); not INSERT INTO table_name VALUES (value1,value2,value3,...);

Rdster Over a year ago

If there are fields that require a value, and don't have a default set, you will need to include them and a value.

bdpolinsky Over a year ago

how do you set it so that the fields should not require a value?

Collectives™ on Stack Overflow

Connecting to Access database from Excel sheet - syntax error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related