Connecting Access to Excel

[Guest]

Hi everyone,

I have the below code that I am using, it has no problem connecting to the
table in Access, however when it goes to write the data to the table, I am
getting a Syntax error that says, Syntax error in INSERT INTO statement.
anyone got any ideas of how to fix this?

Public Sub InsertIntoAccess()

Dim cnAccess As ADODB.Connection
Dim sPath As String
Dim sConnect As String
Dim sSQL As String

' Get the database path (same as this workbook).
sPath = ThisWorkbook.Path
If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"

' Create the connection string.
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & "Master_Logs.mdb;"

' Build the SQL query.

sSQL = "INSERT INTO User (PIP_ID, F_NAM, L_NAME, Initials)" & _
"VALUES ('PIP46', 'JEFF', 'Johnson', 'JAJ')"

' Use the Connection object to execute the SQL statement.
Set cnAccess = New ADODB.Connection
cnAccess.ConnectionString = sConnect
cnAccess.Open
cnAccess.Execute sSQL
'cnAccess.Execute sSQL, adCmdText + adExecuteNoRecords
cnAccess.Close
Set cnAccess = Nothing

End Sub

 

[Douglas J Steele]

You don't have a space between the closing parenthesis and the keyword
VALUES

 

[Guest]

I am having a blonde moment, I'm sorry...what does that mean??? There is a
space between VALUES & the parenthesis.

 

[Guest]

Actually,

I figured it out...thanks!!

I am having a blonde moment, I'm sorry...what does that mean??? There is a
space between VALUES & the parenthesis.