Problems using Guid as Primary Key in Access Database

[kparker]

I'm have lots of trouble using guid's in my access database.

Let's say I have a table with the column [ID] set as Number / Replication ID / Required=Yes / Indexed=Yes(no duplicates). [ID] is set as the primary key. No other indexed columns.

Routine to add 100 \"Test\" records....

Code: Select all

        Dim objOrderInfo As New GW.DAL.Order

        objOrderInfo.ConnectionString = syConnectionStringTransfer()

        Dim i As Integer

        For i = 1 To 100
            With objOrderInfo
                .AddNew()

                Dim newGuid As Guid = Guid.NewGuid

                .ID = newGuid
                .FirstName = \"John\"
                .LastName = \"Doe\"
                .PhoneNumber = \"770-555-1212\"
                .PIN = \"12345\"
                .BeginningBalance = 10
                .Purchases = 6
                .EndingBalance = 4
                .OrderDateTime = Now
            End With
        Next

        objOrderInfo.Save()


When I save the 100 orders, I get the following error:

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

(No rows are saved to the table.)


This all seems very basic, what am I doing wrong here?

[kparker]

Additional info.......

I've tried it in both Access 2000 and Access 2003. Same results.

If I \"save\" after each AddNew it works!

Code: Select all

        Dim objOrderInfo As New GW.DAL.Order

        Try
            objOrderInfo.ConnectionString = syConnectionStringTransfer()
            objOrderInfo.Query.Load()
        Catch ex As Exception
            Throw ex
        End Try

        Dim i As Integer

        For i = 1 To 100
            With objOrderInfo
                .AddNew()

                .StoreID = Guid.NewGuid
                .CardNumber = \"1234\"
                .FirstName = \"John\"
                .LastName = \"Doe\"
                .PhoneNumber = \"770-555-1212\"
                .PIN = \"12345\"
                .BeginningBalance = 10
                .Purchases = 6
                .EndingBalance = 4
                .OrderDateTime = dtNow
                .Downloaded = dtNow
            End With

            Try
                objOrderInfo.Save()
            Catch ex As Exception
                Debug.WriteLine(ex.Message)
            End Try

        Next

I thought that I should be able to do one \"Save\" after adding all the records to save the batch.

Is this not correct??

[kparker]

Anyone??

[CrankyJim]

Out of curiousity, what happens if you don't try to set the id, but instead let Access handle that for you at the save point?

I've done what you're attempting here, but I've never created the guid myself. I'm not certain why you're getting the error.