Hi
I have access database with two tables With a Relationship i can insert records into the first table with no problems but when im trying to insert a record in the second table i got error message
Error message : Syntax error in INSERT INTO statement. - Microsoft Office Access Database
Second Table Name : Orders
Column Name of the second table : OrdersID
OrdersID Datatype : Numbers
Code :

  Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source='" & My.Settings.report & "';" & "Persist Security Info=False;" & "Jet OLEDB:Database Password=" & ";")
        Dim IDnum As String = "4"
        Dim insertCommands As New OleDb.OleDbCommand("INSERT INTO Orders (OrdersID) VALUES ('" & IDnum & "')", Connection)
        Try
            Connection.Open()
            insertCommanddss.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message & " - " & ex.Source)
        Finally
            Connection.Close()
        End Try

The Same Code Works perfectly on the First table
So Whats Wrong ?!
thanks

  1. is this compiling at all because there seems to be an extra d and s in the insertcommands under try.
  2. if idnum is autonumber, primary key, you cannot insert it, you have to let access generate it for you.

  3. if idnum is a foreing key and the value does not exist in the related master table then you are violating integrity.

It would help if you could let us have a look at the structure of you tables.

Good luck.

  1. sorry my bad (its not the problom though :D)
  2. no its not autonumber or primary key its just a number
  3. the number already exist in the first table according to the relation

these are not the problem.. im going crazy over this
4c32c86188ccaf954b57332654ff9fbc

cf7074b4bd528eb1456822fe6d4f0ef5

thank you PerplexedB

VALUES ('" & IDnum & "')" inserts this as a string, where you defined it as a number in your table. Did you not get an error message from VB?
Good luck.

i tried these statements :

"INSERT INTO Orders (OrdersID) VALUES ('" & IDnum & "')"



"INSERT INTO Orders (OrdersID) VALUES (" & IDnum & ")"




"INSERT INTO Orders ([OrdersID]) VALUES ('" & IDnum & "')"



"INSERT INTO Orders VALUES ('" & IDnum & "')"



"INSERT INTO Orders ([OrdersID]) VALUES (" & IDnum & ")"



Dim IDnum as Integer = 4
"INSERT INTO Orders (OrdersID) VALUES (" & IDnum & ")"

all with the same error message, Syntax error in INSERT INTO statement.

Oussamah, you have been telling us that you had 2 tables that are related right. So does this relationship join orders.ordersid into customer.id? if that is the case, access would not let you insert a record where orders.orderid=9 if there is no customer with id=9. Don't think that would produce a syntax error though.

Have debug.print the sql string. Often that exposes syntax errors.

Alternatively, would you show is the insert statement that does work for you?

Good luck my friend.

Edited 3 Years Ago by PerplexedB: typos

yes you are right about the relation but none of the codes works for me.
say there's a customer with ID = 1 and i would try the code above or any of the codes to insert the number 1 into ordersID still wont work
i think it got something to do with the fact its a combobox am i right ?
thank for your help

ok Here's the access file
Dropbox.com
user oussama.j.zeidan@gmail.com
pass 123456

OK. OrdresID is definitely in a 1 to many relationship with Customers.id. In access I was able to add a record in Orders with OrdersId = 9, but it needs a customer record with id 9.

The fact that Orderid is a combobox should not be a problem. But look at it, it will not allow you to enter values under 9!

You should

  1. open your customers table and look at what id's are available in it. If there are none, you should add a record and take a note of which id is generated.

  2. execute

    Dim IDnum as Integer = <any value of customer id>
    "INSERT INTO Orders (OrdersID) VALUES (" & IDnum & ")"

Good luck.

Edited 3 Years Ago by PerplexedB

wont work :(
you have the file so please try this code :

     Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source='" & yourlocation\InvoiceProgram.accdb & "';" & "Persist Security Info=False;" & "Jet OLEDB:Database Password=" & ";")
     ' the record already exist in customers with the ID 9
    Dim IDnum As String = "9"
    Dim insertCommands As New OleDb.OleDbCommand("INSERT INTO Orders (OrdersID) VALUES (" & IDnum & ")", Connection)
    Try
    Connection.Open()
    insertCommands.ExecuteNonQuery()
    Catch ex As Exception
    MessageBox.Show(ex.Message & " - " & ex.Source)
    Finally
    Connection.Close()
    End Try

and tell me why it wont work
thank you

This worked for me on your original .accdb. :

Module Module1

    Sub Main()
        Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=t:\_recent\130922 oussama\InvoiceProgram.accdb;Persist Security Info=False;" & "Jet OLEDB:Database Password=" & ";")
        ' the record already exist in customers with the ID 9
        Dim IDnum As String = "9"
        Dim insertCommands As New OleDb.OleDbCommand("INSERT INTO Orders (OrdersID) VALUES (" & IDnum & ")", Connection)
        Try
            Connection.Open()
            insertCommands.ExecuteNonQuery()
        Catch ex As Exception
            Console.WriteLine(ex.Message & " - " & ex.Source)
        Finally
            Connection.Close()
        End Try

    End Sub

End Module

My previous answers were based on a state of the your .accdb after I had somewhat played around with it.

Maybe you should try your program on the .accdb as you sent it to me?

I dropped my version back. Hope that will help you find out your problem.

Good luck.

Edited 3 Years Ago by PerplexedB: completing answer

so we did the same thing but it only worked for you :(
ill check my access file in dropbox tomorrow
10x PerplexedB

my code works!
this was the problem
e258ebbdad9a9d57b3519e4c7d368863
my access file needed some query fixes
thanks PerplexedB for your time

This question has already been answered. Start a new discussion instead.