Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003

    Question Unanswered: MS ACCESS - WHY INSERT SQL QUERY don't work

    Pls see the code below:
    I have tried to run the SQL insert query here but it gives me error.
    Syntax error in INSERT INTO statement.
    err.Number -2147217900
    I have No idea WHY? All the fields are of text type.
    I am using MS ACCESS (XP)

    Dim conntest As New ADODB.Connection

    Private Sub Command1_Click()
    Dim sqlJobOrder, cntr%

    Dim rstest As New ADODB.Recordset

    'openDb conntest
    conntest.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb;"

    rstest.Open "select * from test", conntest, adOpenDynamic, adLockOptimistic

    For i = 0 To 1000
    'sqlJobOrder = ""
    cntr = findMaxJobOrderID
    With rstest
    .Fields("name") = "name" & cntr
    .Fields("age") = cntr
    .Fields("count") = i
    End With
    'sqlJobOrder = "INSERT INTO test(name,age,count)" & _
    " VALUES('name', '" & cntr & "','" & i & "')"

    'conntest.Execute sqlJobOrder
    'Debug.Print sqlJobOrder

    If rstest.State = adStateOpen Then rstest.Close
    Set rstest = Nothing
    Set conntest = Nothing
    MsgBox "done"

    End Sub

    Private Function findMaxJobOrderID()
    Dim rsmaxID As ADODB.Recordset
    Set rsmaxID = New ADODB.Recordset
    rsmaxID.Open "select max(id) from test", conntest, adOpenDynamic, adLockOptimistic

    If IsNull(rsmaxID(0)) Then
    maxnum = 0
    maxnum = rsmaxID(0)
    End If

    '' To start the job order from 14000
    If Val(maxnum) < 14000 Then
    maxnum = maxnum + 14000
    End If

    findMaxJobOrderID = maxnum + 1
    If rsmaxID.State = adStateOpen Then rsmaxID.Close
    Set rsmaxID = Nothing
    End Function

  2. #2
    Join Date
    Aug 2009


    To solve the issue/error (Syntax error in insert query) occuring while inserting a row into Access database table using form please follow the below steps.

    Its better if you use parameters which will avoid syntax errors.I have faced the same issue, so to solve the issue i used parameters.Please have a look at the below example which explains you in a better way

    Sample code:

    Public Class frmSeo

    Dim Connection As OleDbConnection
    Dim Command As OleDbCommand

    Private Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click

    Connection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=(Mdb file path);")


    Command = New OleDbCommand("INSERT INTO table name (column1,colume2) VALUES (@variable1,@varaible2)", Connection)

    Command.Parameters.Add("@variable1", OleDbType.varchar).Value = (value to be inserted)
    Command.Parameters.Add("@variable2", OleDbType.VarChar).Value = (value to be inserted)


    End sub

    End class

    In the above code, i am trying to insert a row under click event.

    Try to use the code according to your requirement, i hope it will solve your problem.


  3. #3
    Join Date
    Feb 2004
    One Flump in One Place
    That looks like a good solution. A word to the wise though - check the date on threads - that question is over six years old I'm afraid
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Aug 2009


    Thanks for your information

    To be frank i didn't check the thread date.
    I am facing the same issue and when i search for a solution, i found the same question but no updated the solution for the thread.


  5. #5
    Join Date
    Nov 2007
    Adelaide, South Australia
    No harm there, just don't expect a response from the OP ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts