Access Error 3219; Timestamps Valid in Primary Key?
Good morning, all.
I've migrated my Access BE to MySQL, mostly smoothly. I'm getting an error (3219) when running INSERTs on one table, and my only guess is that it doesn't like my primary key. It is based on a CHAR(5) and a TIMESTAMP. Access won't let me specify a value for the TIMESTAMP field when INSERTing, which is fine, but perhaps because of that it can't create a complete primary key on its own. The query runs fine directly at the console.
This is my table def:
CREATE TABLE Progress (
Project CHAR(5) NOT NULL,
Progress TINYINT UNSIGNED NOT NULL,
Marked TIMESTAMP NOT NULL,
User INT UNSIGNED NOT NULL,
PRIMARY KEY (Project, Marked),
FOREIGN KEY (Project) REFERENCES Projects(Project) ON DELETE CASCADE,
FOREIGN KEY (User) REFERENCES Contacts(Contact) ON DELETE RESTRICT
) ENGINE = INNODB;
This is my VB code:
Globals.MySQL.Execute ("INSERT INTO Progress (Project, Progress, User) VALUES (""" & Globals.Project & """, " & NewProg & ", " & Globals.User & ")")
This is the query:
INSERT INTO Progress (Project, Progress, User) VALUES ("05014", 95, 123)
I've seen how TIMESTAMPs can both fix and cause problems in MySQL, and we all know how finicky the Access-MySQL links are! Any ideas? Should I drop this primary key and create an INT AUTO_INCREMENT?
Thank you.