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

[jamesha]

Hello,
I have created a database in MS SQL Server that contains various tables. I am trying to use Access Forms to create an interface to allow records to be managed and have come across a problem that I hope I can get some help with.

The SQL Server table contains a field which is auto-populated with an integer value when a new record is created.

The SQL Server definition for this is valueid int IDENTITY(1000,1)

The first record created has valueid set to 1000, the second 1001 etc.

This works ok when inserting records using SQL statements in SSMS.

I have created an Access file and linked to the SQL Server table.

I am able to view records in the linked table in an Access form which displays the valueid field plus other fields.

My problem is adding new records - my form has a text box which I want to display valueid for existing records but adding it to the form seems to stop new records being added.

Is there a way to accomplish what I want? - I hope I have explained it ok.

 

[theDBguy]

Hi. Welcome to AWF!

When you linked the table in Access, did you get a prompt to select the primary key field? Try deleting the link and create it again. If you get the prompt, make sure to select valueid. Just a guess...

 

[oleronesoftwares]

Is there a way to accomplish what I want? - I hope I have explained it ok.

Does the form contain record source from one table or multiple tables?, i.e does it have subforms?

If from multiple tables, then check the relationship keys of each of the tables that are been represented in the form(i.e main form and subforms)

 

[jamesha]

Thanks for the responses. Maybe I need to give some more background. The SQL Server table in question has a primary key that uses the GUID data type. This does not need to appear on any Access form. The Access front end was working fine to view/add/delete records but my customer wanted a new field added which contains a value in the range 1000 to 9999 which they will to refer to the records in documentation.

I added this field to the SQL Server table using the ALTER TABLE command and values were assigned for valueid for all the existing records.

I then re-linked the table in Access and can view the records in an Access from just fine. The issue is adding new records - I do not know how to set up a field on the form which will show the valueid for existing records but not mess up adding new records. When I added a simple text box to the form it displays {New} in the box when I try to create the new record.

The record does actually get inserted in the SQL Server table but the form then just stops working.

Sorry - I probably need to get some screenshots to make the explanation clearer - I can do that tomorrow when I am back at work - I was not expecting to gets answers so quickly

 

[strive4peace]

hi @jamesha,

unlike Access, which generates a new AutoNumber when data begins to be entered, SS doesn't do it until after the record is saved. It's wise to add a unique index if it isn't the primary key.

odd that putting it on the form would make it stop working ... never heard of that before

Instead of relinking, try deleting the linked table and linking again

 

[arnelgp]

you delete your Linked table.
try to create new link and Pay Attention to the Wizard.
at the End it will Ask which Key is the Primary Key and you
select 1.
If you don't select, it will render your linked table as readonly.

 

[jamesha]

Thanks for the responses everyone.

I can provide a bit more detail which hopefully will make things clearer. Below is a screenshot of how the table is scripted in SQL Server.

It is the last column - MenuIndex - that is the problem. When a new record is created the MenuIndex field gets populated with an automatically incremented number starting at 1000 and going up by one for each new record. This works fine.

I have used Access to provide a front end to SQL Server by using linked tables and this has worked ok up to now but, since adding the new MenuIndex field to SQL Server I have had a problem adding records.

I added the MenuIndex record to my Access form as a Text Box and deleted then re-added the tables. For existing records the form displays the record fine including the value for MenuIndex - see below:

When I add a new record I see (New) in the Text Box for Menu Index as shown below

If I fill in some of the boxes such as Menu Description the MenuIndex box becomes empty as shown below

If I save this record it gets written to the SQL Server table ok but then the Access form seems to lose connectivity the database table as shown below:

I have read that seeing #Name? indicates that there is an issue between the data config of the form and the database table.

I am not sure how to fix this. I guess that we need to see the MenuIndex values for existing records but need to not see it when adding a record - I hope this is a clearer explanation - is this possible?

 

[strive4peace]

hi @jamesha

please read @arnelgp's last message

 

[theDBguy]

hi @jamesha

please read @arnelgp's last message

I guess my thinking was in the right direction.

 

[strive4peace]

@theDBguy -- @arnelgp also mentioned setting the Primary Key for the linked table ... that could be what's missing

 

[theDBguy]

@theDBguy -- @arnelgp also mentioned setting the Primary Key for the linked table ... that could be what's missing

I thought that's exactly what I said...

 

[strive4peace]

I thought that's exactly what I said...

aah, indeed you did, @theDBguy! ... sometimes even us experienced developers need to see something twice

Good point, @Pat Hartman

 

[bastanu]

I have replicated the error you are getting. Created a new table similar to yours, added NewID() as the default value for the PK (pkid) and the MenuIndex identity column. Once linked in Access when trying to insert using the actual linked table I get "invalid character value for cast specification(#0)" then all records display Name# as you show in the form. I think the reason is indeed your new identity field:https://stackoverflow.com/questions...ast-specification-when-importing-to-table-wit

So as a work around I have created a view (vMenu) to remove the MenuIndex (identity) field, basically replicating the original table; I also created another view (vMenuIndex) with just two fields: PKID and MenuIndex. I linked these two views in Access selecting PKID as the primary key when prompted by the wizard.
Then I changed the record source of the form from the Menu table to the vMenu view and updated the control source of the MenuIndex textbox to =DLookUp("MenuIndex","vMenuIndex","pkid=" & [pkid]); it will show #Error for the new record but the form will work as expected allowing you to add new records. To fix the small issue with the #Error you can hide the field in the Current event of the form for the new records and make it visible again in the AfterInsert.

I am attaching a small db with both SQL links and local tables so you can inspect the structures I used.

Cheers,

 

[bastanu]

This might mean having to change child tables if there are any with data.

I usually tend to assume that when someone has a db that has been working for years with a certain structure that there was\is a good reason why things are a certain way (such as a PK GUID needed to satisfy a business rule or interact with an outside system) and if that is the case here then your comment above could mean a lot more work would be needed (not just child tables but also queries\views\reports\forms,...) to implement the "easy fix" then to implement my work around.

Just my 2 c.

Cheers,

 

[bastanu]

Just tried it and it doesn't seem to work:

 

[bastanu]

Hi Pat, all good suggestions, I will let the OP (jamesha) to answer those questions....

Cheers,

 

[jamesha]

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]

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]

@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