Hey all. I'm trying to append rows to my database with some VBA code.

The code looks like this:
vInsertLoanSQL = "INSERT INTO Loan(BookID, MemberID, StaffID, BorrowingDate, ReturnDate) VALUES (" & vBook & "," & vMember & "," & vStaff & ", #" & Format(Date, "dd/mm/yyyy") & "# , #" & Format(vDueDate, "dd/mm/yyyy") & "#)"

DoCmd.RunSQL(vInsertLoanSQL)


The variables are all filled out.

The table that it's being inserted into, Loan, has an Autonumber primary key (not included in the INSERT script) and a number of other fields that have no zero-length restrictions, no "Required" fields set to Yes and no Indexes. The table in question is also completely empty, there are no rows (they have all been deleted) - so I have no idea why I'm getting that "Microsoft can't append all the rows in the append query" error. It says its a key violation but I really can't see how its possible.

Anyone out there know whats going on?

Have you tried using the date in mm/dd/yyyy format (I believe you have to use U.S. date format in SQL statements like that)?

Just tried that now. Didn't help. Mind you, I'm in Australia, where the format is dd/mm/yyyy and when in Design view of the table, those two date fields are formatted as "Short Date 19/06/2007" from the drop down list.

There's no input masks on either of those fields - would that be a problem?

It seems to have something to do with foreign keys...three of the fields I'm inserting into are FK references. It's probably the Staff table....

I haven't solved it yet, but I think I may have to do some table/relationship restructuring before I get anywhere...

that would do that

if you are tring to insert a record requiring a staffid in a staff table (with referential integrity), and there is no matching record, you will get a key violation error

How a date is formatted does not impact how it is stored. there may be a permanent code topic somewhere on this site that addresses using non-US format dates. However, as Bob mentioned, SQL server is picky about literal dates and wants them to be in month - day - year order. To avoid ambituity, the best format is dd - mmm - yyyy which solves the problem as long as you are using English. I don't know if it causes other problems when there is a mixture of languages.