I have designed a MS Access Database with three tables: CUSTOMERS, BOOKINGS & HOLIDAYS.

CUSTOMERS has a Primary Key called UCI.

BOOKINGS has a Primary key called [Number]. Two Foreign keys: UCI (link to CUSTOMERS) & Code (link to HOLIDAYS).

HOLIDAYS has a Primary Key called Code.

The Database has one-to-many Relationships with Referential Integrity and checked cascade UPDATE and DELETE (The many relationship for both links being on the BOOKINGS table).

I have designed a GUI using vb.NET (Visual Studio) The connection to the Database is via Microsoft Jet 4.0 OLE DB Provider and I have got the following Datasets to work on both the CUSTOMERS and HOLIDAYS tables: SELECT, INSERT, DELETE, UPDATE records.

On coming to the BOOKINGS table (which appears to be an INNER JOIN) I've got the SELECT and DELETE records to work, but I'm having major problems getting the INSERT and UPDATE to work. View the SQL Statements  below:

I have now generated a SELECT query by going through the OleDbAdapter process and the Following query was produced and works fine:

"SELECT BOOKINGS.*, CUSTOMERS.UCI AS Expr1, HOLIDAYS.Code AS Expr2 FROM ((BOOKINGS INNER JOIN CUSTOMERS ON BOOKINGS.UCI = CUSTOMERS.UCI) INNER JOIN HOLIDAYS ON BOOKINGS.Code = HOLIDAYS.Code) "

I’m problems with the INSERT Statement, the syntax is:

"INSERT INTO BOOKINGS, CUSTOMERS.UCI AS Expr1, HOLIDAYS.Code AS Expr2 FROM ((BOOKINGS INNER JOIN CUSTOMERS ON BOOKINGS.UCI = CUSTOMERS.UCI) INNER JOIN HOLIDAYS ON BOOKINGS.Code = HOLIDAYS.Code )" _
                 & " values('" & txtNumber.Text & "', '" & txtUCI.Text & "','" & txtCode.Text & "','" & txtDate.Text & "','" & txtAdult.Text & "','" & txtChild.Text & "','" & txtDeposit.Text & "','" & txtBalance.Text & "','" & txtCost.Text & "')"

There is no error on submitting the INSERT, but the record is not being saved into the Database.


I am also having problems with the UPDATE Satement, the syntax is:

"UPDATE BOOKINGS SET [Number] = '" & txtNumber.Text _
             & "', " & "UCI = '" & txtUCI.Text _
             & "', " & "Code = '" & txtCode.Text _
             & "', " & "Date = '" & txtDate.Text _
             & "', " & "Adult = ' " & txtAdult.Text _
             & "', " & "Child = ' " & txtChild.Text _
             & "', " & "Deposit = ' " & txtDeposit.Text _
             & "', " & "Balance = ' " & txtBalance.Text _
             & "', " & "Cost = ' " & txtCost.Text _
             & "' WHERE [Number] = '" & txtNumber.Text & "'"

The error for the UPDATE is: 'Syntax error in UPDATE Statement.