Entering a new record using an Access form for a linked table in SQL Server that uses an auto-incrementing primary key (1 Viewer)

bastanu

AWF VIP
Local time
Today, 00:30
Joined
Apr 13, 2010
Messages
1,401
Hi Pat, all good suggestions, I will let the OP (jamesha) to answer those questions....

Cheers,
 

jamesha

New member
Local time
Today, 07:30
Joined
Dec 7, 2021
Messages
4
Thanks everyone - particularly bastanu for testing the approach he suggested.

I am not easily able to change the structure of the SQL Server DB to use IDENTITY rather than GUID as a whole lot of work in other systems has been done to integrate with the DB using GUI.

The actual use case for this is to store routing information for a contact centre. Based upon the number called a bunch of database calls are made to dynamically create an IVR menu from which callers can select options. The contact centre software scripts have a lot of database calls all based around GUID to create these menus.

I gave a working copy of the database to the customer over six months ago and they should have finished testing a month after that so it was pretty frustrating when they asked for numbering for menus a week or so ago. If they had tested properly it might have been possible to redesign the database and re-write the contact centre script but the other stakeholders are not willing to accept the delay and cost necessary to make the change hence me trying to add this IDENTITY column - I know it is not very good DB design :(

Anyway back to progress. I have created two new views as suggested in SQL Server and imported them as Linked Tables to Access.

Linking the Access form to the first view which has all the columns from the Menu table except the MenuIndex column allows me to enter data ok.

I have not been able to successfully add the MenuIndex field to the form using a DLookup statement as suggested - the field just returns #Error

I spent a couple of hours over the weekend playing about with this without any luck. I had a suspicion that using the GUID based pkid field might be causing an issue so tried using two new views linked by the description field (which should also be unique).

Below is my DLookup statement

=DLookUp("[MenuIndex]","[vMenu2]","pkid=" & [pkid])

I am not sure why this is not working with my linked tables/views.

I created an Access DB for testing using local tables and a similar DLookup statement worked fine with that.

Thanks again for the help so far - it has been really useful and most appreciated.
 

strive4peace

AWF VIP
Local time
Today, 02:30
Joined
Apr 3, 2020
Messages
1,003
hi @jamesha,

perhaps it would be helpful to add a UNIQUE ondex on the identity field.

Did you try deleting the linked table and linking again?
 

bastanu

AWF VIP
Local time
Today, 00:30
Joined
Apr 13, 2010
Messages
1,401
@jamesha :
so tried using two new views linked by the description field (which should also be unique)

The views should not be "linked", the second one should simply have two fields ([pkid] or [Description], and [MenuIndex]). Please have a look at the updated sample, I replaced the dLookup approach with a subform based on the second view linked to the main form by pkid. Seems to be working fine for me (I hide it on the Current event of the main form for new records).

Cheers,
Vlad
 

Attachments

  • Database28.accdb
    584 KB · Views: 74

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:30
Joined
Feb 19, 2002
Messages
42,981
Not clear which of my suggestions you tried.
Leaving the GUID but making it a unique index and making the identity column the PK shouldn't break anything that already exists and should coerce Access into using the Identity column as the PK. I don't ever use GUIDs since they are over kill for most applications. I just don't have gazillions of rows of data. I'm in the millions category so the Identity column is fine.
 

Users who are viewing this thread

Top Bottom