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

[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...

 

[Pat Hartman]

It is better to define the primary key at the table level. Then, you will never get prompted for tables but you might still get prompted for views. If you get prompted for a PK for a view, be verry careful to select ALL the PKs from ALL the joined tables. If the view joins 3 tables, you need to select all three of the underlying table's PKs.

 

[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...

 

[Pat Hartman]

I'm sure the confusion is caused because the PK of the table is not the identity column. If you do what arnel suggested, Access might prompt you but probably not since it seems to have made its decision. So, you can delete the table's PK and use the identity number instead and Access will be happy.

 

[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,

 

[Pat Hartman]

Since the identity column is unique, what is the problem with just removing the old GUID and using the identity column instead? This might mean having to change child tables if there are any with data.

You also might coerce Access into "seeing" the Identity column and choosing it as the PK rather than the actual PK if you put a unique index on it and renamed it to aaaaasomething so it is the first unique index in the list.

I don't like workarounds when there is an obvious and easy fix. Why perpetuate a problem?

 

[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,

 

[Pat Hartman]

If the GUID is needed for some reason, that is fine. I included instructions on how to coerce Access into choosing the Unique Index on the Identity column but one must be defined in order for Access to even consider the Identity column as a candidate.

There is really no reason to keep two artificial unique identifiers which is why I suggested getting rid of the GUID.

 

[bastanu]

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

 

[Pat Hartman]

Change the GUID from PK to unique index to see if that works. Then change the identity column to the PK if that still doesn't work. Why do you need the GUID if you have another artificial unique identifier?