0

From a form fields in MS Access I'm attempting to move the values to a SQL Server table.
Thanks

When I execute the VBA code I receive the following message of "Syntax error in INSERT INTO statement".

Image of Error Message

I can take the code from the Immediate display and copy it to the SQL Server New Query tab and execute it and it performs the command perfectly. What suggestions does anyone have as to what I'm performing incorrectly?

Image of VBA Code To INSERT from Access to SQL Server

Private Sub btnSixClicksAdmittedPatientsByDates_Click()
On Error GoTo Err_btnSixClicksAdmittedPatientsByDates_Click

    Dim Response As Integer
    Dim dbs As DAO.Database
    Dim rs As DAO.Recordset
    Dim sqlstr As String
    Dim objControl As Control
    
    Set dbs = CurrentDb
    
    sqlstr = "INSERT INTO [mcif].[dbo].[tblSixClicks_Report_Criteria] ([Rehab_SixClicks_Campus],[Rehab_SixClicks_Department]) " & _
                "VALUES ('" & cboReportCampusSelection.Value & "','" & CBOReportDepartmentSelection.Value & "')"
    Debug.Print sqlstr
                                                         
    DoCmd.SetWarnings False
    DoCmd.RunSQL sqlstr
    DoCmd.SetWarnings True

Immediate view that works in SQL Server:

INSERT INTO [mcif].[dbo].[tblSixClicks_Report_Criteria] ([Rehab_SixClicks_Campus],[Rehab_SixClicks_Department]) VALUES ('DMM','DML 3 EAST')
Thom A
  • 88,727
  • 11
  • 45
  • 75
DaveA
  • 21
  • 1
  • 4
    When you asked your question, you were told *"Please make sure to post code and errors as text directly to the question (and [not as images](https://meta.stackoverflow.com/questions/285551)), and [format them appropriately](https://stackoverflow.com/help/formatting)."* Please follow the instructions when posting your questions, as otherwise you are likely to receive down and/or close votes; meaning your question is far less likely to be answered. – Thom A Mar 31 '22 at 13:50
  • 7
    **WARNING:** Your code is **dangerous**. It is wide open to SQL injection attacks. Always, *always, **always*** parametrise your code. [Why do we always prefer using parameters in SQL statements?](//stackoverflow.com/q/7505808) Perhaps if you fix your parametrisation problem, you'll fix your error too. – Thom A Mar 31 '22 at 13:50
  • 3
    Are you sure that you are connected to a SQL Server database? Because that error appears to be an MS Access error and NOT a SQL Server error and though it is valid TSQL, it is *not* valid Access/DAO SQL. If you are sure that you are connected to SQL Server, then you may need to set DAO into [passthrough mode](https://support.microsoft.com/en-us/office/create-a-pass-through-query-b775ac23-8a6b-49b2-82e2-6dac62532a42) to prevent it from doing local error-checking. – RBarryYoung Mar 31 '22 at 14:00
  • This is a better link for Passthrough from VBA: https://stackoverflow.com/q/17241898/109122 – RBarryYoung Mar 31 '22 at 14:05
  • Only thing about syntax I am not sure of is reference to SQLServer table. Are tables linked in frontend? If linked, why not bound form? If not linked, where is connection established? – June7 Mar 31 '22 at 18:56

0 Answers0