Key Violations

[shylock]

I have two tables to append to. The first table (RebootScheduleLogicalServer) consists of 2 fields (Schema shown below). The second table has several fields(Schema below). I must insert a row into the first table and then a row into the second table using the RebootScheduleID from the first table. When I run the append query (shown below) i get a key violation error. I am also showing another append query that I know works to indicate the syntax is correct.





The LogicalServerId for the RebootScheduleLogicalServer is provided by the rowsource where I enter the Server Name.

How can I determine which of the "Values" in the first query is causing the key violation and can you indicate how to correct the problem?



I have tried the functions StringfromGUID and GUIDFromString as follows:

    ''' Save the new reboot ID to the RebootScheduleLogicalServer table first
    vLogID = StringfromGUID(Me.cboSvrNm.Column(0))
    
    DoCmd.RunSQL "INSERT INTO RebootScheduleLogicalServer ([RebootScheduleID], [LogicalServerID]) " & _
             "VALUES ('NewID()', '" & vLogID & "')"

AND:

    ''' Save the new reboot ID to the RebootScheduleLogicalServer table first
    vLogID = Me.cboSvrNm.Column(0)
    
    DoCmd.RunSQL "INSERT INTO RebootScheduleLogicalServer ([RebootScheduleID], [LogicalServerID]) " & _
             "VALUES ('NewID()', '" & GUIDFromString(vLogID) & "')"

Neither function works, both result in key violations.

[CJ_London]

you are using replication ID's as your number type - they are used for synchronisation and should not be used as primary keys (at least within a JET/ACE backend). Instead, use long integer. If you need synchronisation, include a replicationID as a separate column (and set the primary key new value to random), you will also need a number of date fields as well to determine the latest version and identify clashes.

[Micron]

you are using replication ID's as your number type - they are used for synchronisation and should not be used as primary keys (at least within a JET/ACE backend).

I've seen a few examples GUID's being used as server ID's in this forum lately so I figured they ought to be OK for some types of data. Not sure why you say they shouldn't be used as PKs? I cannot think of a case where I would use them as PK values because they use up a lot of resources, but they are guaranteed to be unique so what would be the issue? In fact, in a replicated db, they are primary keys.

[shylock]

The tables are SQLServer tables. I am not permitted to alter the table schemas. I am using Access as the front end. What I don't understand is why one append query (the second query) works and the first query throws the key violation.

[Micron]

All I can suggest is that you examine the values of variables before executing code that attempts to run a query. That could be just mousing over the variables, or outputting the sql to the immediate window (debug.print) or using a watch window. The message is indicating that you're attempting to violate the constraints around the field values needing to be unique OR needing to be provided (e.g. you are trying to append Null). It also looks like you've got a composite pk field - maybe you don't realize you already have whatever value(s) you're attempting to enter. As for one query vs the other, I don't see how you can compare those 2 queries that you're calling 1 and 2 that are in the images.

In the end, if you're saying you can run query 1 but not query 2 after that, AND you can run query 2 first but if you do, you cannot run query 1, then it has to be that either query is attempting to duplicate values in fields where it isn't permitted OR you are trying to append/update values that are not permitted (such as Null or empty string) - that's what the message is trying to tell you. Verify whatever it is that you're attempting to write to the table before it attempts to execute.

[shylock]

Thanks, Micron. I am pretty sure of the values that I am trying to insert. I moused over and used debug,print, both, and they showed the expected values. The second query should have read as follows (See NewID() in red:

DoCmd.RunSQL "INSERT INTO RebootSchedule ([RebootScheduleID], [RebootWeek], [RebootTime], [Mon], [Tue], [Wed], [Thu], [Fri], [Sat], [Sun], [IsTemp], " & _
             "[RequireSpecialInstructions], [RequireEveningReboot]) " & _
             "VALUES (' NewID()  ', '" & sWk & "', '" & TimeValue(Me.txtTime) & "', " & Me.chkMon & ", " & Me.chkTue & ", " & Me.chkWed & ", " & Me.chkThu & ", " & Me.chkFri & ", " & _
             "" & Me.chkSat & ", " & Me.chkSun & ", " & Me.chkTemp & ", " & Me.ChkSpecial & ", " & Me.chkPM & ")"

This query works and give me a new RebootScheduleID. My concern is that, logically the new RebootScheduleID should be created in the RebootScheduleLocalServer table first as it is the link between the LogicalServer table and the RebootSchedule table as in the following query:

SELECT LogicalServer.Name, RebootSchedule.RebootWeek, TimeValue(RebootSchedule.RebootTime) AS BootTime, RebootSchedule.Mon, RebootSchedule.Tue, RebootSchedule.Wed, RebootSchedule.Thu, RebootSchedule.Fri, RebootSchedule.Sat, RebootSchedule.Sun, RebootSchedule.IsTemp, RebootSchedule.RequireSpecialInstructions, RebootSchedule.RequireEveningReboot, IsNull([LogicalServer].[DecommissionDate]) AS Expr1, RebootScheduleLogicalServer.RebootScheduleID
FROM (LogicalServer INNER JOIN RebootScheduleLogicalServer ON LogicalServer.LogicalServerID = RebootScheduleLogicalServer.LogicalServerID) INNER JOIN RebootSchedule ON RebootScheduleLogicalServer.RebootScheduleID = RebootSchedule.RebootScheduleID
WHERE (((IsNull([LogicalServer].[DecommissionDate]))<>False))
ORDER BY LogicalServer.Name;

What do you think?

[CJ_London]

thought I had responded, but can't see it so here it is again.

In fact, in a replicated db, they are primary keys.

no - they ar not. I used to support a number of replicated apps (user out in field, syncs on return to office) and experimented with using replication ID's as PK's but hit a variety of issues on joins. Using them how MS used to use them when they provided the replication facility works well - i.e as per my previous post

@shylock, if your BE is sql server and using replication ID as PK, then suggest use a passthough query or stored procedure so the processing takes place in the BE

[shylock]

Ajax:

Sorry, what is BE?

[CJ_London]

BE=Back End - where your tables are stored
FE=Front End - where you have forms, reports, queries, code - effectively the application

[shylock]

Thank you Ajax. I once worked for a company that published a 25 page report of acronyms. Many of them were the identical but had different meanings. Very confusing.
Ever since, I have tried to avoid acronyms.

I will try a pass through and/or stored procedure.

[Micron]

no - they ar not. I used to support a number of replicated apps (user out in field, syncs on return to office) and experimented with using replication ID's as PK's but hit a variety of issues on joins. Using them how MS used to use them when they provided the replication facility works well - i.e as per my previous post

I don't profess to be an expert on the subject as I've never worked on a replication db until I was asked to do so recently and I had to read up on it. It's possible that they are experiencing mis-matching of ID's (if that's what you're saying) as they've asked me to look at that now.

Here's a couple of images (had to blur for confidentiality). If you can educate me on why we seem to have that difference of opinion, I'd appreciate learning more about it. What I wrote was based on what I see in this db:

You should be able to tell that darkened field is pk, autonumber, replication id


That same field (selected row) is fk in more than one table and is type replication id. It is also joined in relationships.

If I need to move this to a new thread please let me know as I don't want to hijack this one (assuming I haven't already).

[shylock]

Micron:

Please do not move this discussion to a new thread. I find this very educational and apropos to the project I am currently working on. Just about all of the tables I have to use have replication ID's (GUID's) as PK's and FK's. It does make things interesting, to say the least.