Insert Query Access to SQL Server

[XelaIrodavlas]

Hi all,

I've been working with Access for a few years now but this is my first real test on SQL server.

I have just transferred the back end of an access database onto SQL Server and connected the front via linked tables, so the fe database works as normal, except for one insert query which is bringing up the error:

didn't add x record(s) to the table due to key violations.

Thing is the query hasn't changed, and the table should be an exact copy of the original, so is there some difference in syntax I'm missing??

Please help,

For information the fields looked like this in Access:
ID (autonum) *PK
UserID (Num)
Username (ShortText)
Loggedin (Date/Time)
LoggedOut (date/time)
DBName (Short Text)

And now they're in SQL it looks like:
ID (Int) *PK
UserID (Int)
Username (nvarchar(255))
Loggedin (Date/Time)
LoggedOut (date/time)
DBName (nvarchar(255))


And of course here's he SQL query (which worked in Access) this was a simple insert to record when a person logs onto the database:

INSERT INTO Login (PersonnelID, Username, LoggedIn, DBName )
SELECT [Forms]![Home]![PersonnelID] AS UserID, [Forms]![Home]![UserName] AS UserName, Now() AS Login, "DB1" AS DB;

 

[pbaldy]

Does the new table have a primary key? It needs one. Also make sure the LoggedOut field allows nulls.

 

[XelaIrodavlas]

Hi Paul,

Thanks for the quick reply! Actually I forgot to add the Primary Key; but it wasn't enough to fix the problem

All fields (except the PK) currently allow nulls.

Thanks

 

[pbaldy]

Did you re-link the table, so Access knows it has a key now?

 

[XelaIrodavlas]

I did, but that didn't help either haha...

Actually I found it after some poking about, I had to set the new primary key's identity specification to Yes (in column properties for anyone else reading), the field just needed to know it was an Autonumber.

Thanks for the help you really pointed me in the right direction

 

[pbaldy]

Ah yes, that would be important.