SQL Server 2017 with Access front-end can read data, but not write

[MushroomKing]

Hi everyone,

Yet another question i seem to be stuck on for 2 days now.
I successfully setup my SQL server 2017. My backend is imported and my Access front end reads all data and my databases work....kinda....

Problem:
I have a form that has an on open event GoTo new record in my vba.
This worked fine, but not with my new SQL server setup. It does not seem to be able to write.
I checked the permission settings but i can't get it to work. What am i doing wrong?

When i open the form and it wants to open on a new record. i get error:
"you cant go to the specified record" (DoCmd.GoToRecord , , acNewRec)

Could someone please advice me what to check? Thanks so much!

 

[arnelgp]

Do you have primary Key on your table?

 

[MushroomKing]

Hi Arnelgp, thanks for your swift answer!

Right! I do.

When i have my Access front-end, and i link the (odbc) SQL tables to it, it will prompt for a unique identifier.
So i select the primary keys and the import goes well. Also, they are editable and new records can be added!

BUT...since it's an autonumber field originally, it just doesn't work anymore.
So when i open the form, and it wants to create a new records, it gives problems and my autonumber field doesnt work.

My autonumber field has been changes to a number field in my front end.
On the SQL server side the field has the property of INT and allow nulls is UNchecked.

So how to deal with these i have no clue

 

[Isaac]

UT...since it's an autonumber field originally, it just doesn't work anymore.

I'm kind of confused. Is the table actually SQL server or Access? SQL Server doesn't have autonumber datatype.

My backend is imported

What does this mean?

 

[Minty]

It should have set the SQL table data field to Is Identity = Yes

This is the SQL equivalent of an auto-number field.

 

[MushroomKing]

Yes! Correct Minty.

While i import the backend into the SQL server, i was able to edit the SQL code.
So before final import, you can edit the code and all i had to do was add IDENTITY(1,1) PRIMARY KEY,

Now it is indeed autonumbering in SQL server. Thanks guys!

 

[MushroomKing]

I'm kind of confused. Is the table actually SQL server or Access? SQL Server doesn't have autonumber datatype.


What does this mean?

The table is SQL, but as an ODBC link in my front end.
Technically there is no autonumber datatype indeed, but the function does exist!
When i go to my SQL server, to my table and add a new record manually, it WILL autonumber by increment of 1! (yes automatically)
Personid int IDENTITY(1,1) PRIMARY KEY,

So that way it also continues where it left off. So you dont have the hastle that it starts from 0.

 

[Isaac]

well we're on the subject, i'll just throw in--typically when migrating access data to sql, with existing PK's, you can SET IDENTITY INSERT ON (on sql) first, then import your key values (even to an Identity column)......then when done, SET IDENTITY INSERT OFF, then the auto increment kicks back in. you don't need to trash your PK's from access

sounds like you probably used an ETL system that did that for you automatically.