slqserver express can't add records on ms/access front end

I have migrated my ms/access app from single file to a split database and now to a more convenient sqlserver express backend. The migration process went smooth but for some tables, on the front end side I cannot add records.
You may see on the attached screenshots (one screenshot for which I can add records and one for a table where I can't add records)
This came automatically and even when I just created a new table on MSSMS, when I link in the ms/access client, I can't add records either... (ut's the one in the screenshot).
Can anyone help?
João serras-pereiraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Máté FarkasDatabase Developer and AdministratorCommented:
You did not attach that screenshot.
Anyway you have to define a primary key in SQL Server for each table what you want to add/modify records. This is not a requirement in Access but mandatory in SQL Server - Access infrastructure.
João serras-pereiraAuthor Commented:

all of them have a key...
I don't see problem on screenshots. Both tables has row for additions. Do you have an error when enter value to ID or to any other field?
SolarWinds® VoIP and Network Quality Manager(VNQM)

WAN and VoIP monitoring tools that can help with troubleshooting via an intuitive web interface. Review quality of service data, including jitter, latency, packet loss, and MOS. Troubleshoot call performance and correlate call issues with WAN performance for Cisco and Avaya calls

Máté FarkasDatabase Developer and AdministratorCommented:
OK, you have Primary Keys.
How do you populate these keys when you want to add a new record?
Do you have any error message?
João serras-pereiraAuthor Commented:
both! I need to edit and need to add/delete records
João serras-pereiraAuthor Commented:
There are no errors. Ity looks ly a multi-table select without keys so we cant add records...
João serras-pereiraAuthor Commented:
To Maté - I see that you are willing to go live; never saw that before
can you confirm?
Máté FarkasDatabase Developer and AdministratorCommented:
No, I absolutely don't want to go Live just try to figure out the cause of your problem.
However you Access screenshots shows a possibility to add new rows to those tables.
If you cannot find the solution then you can request a Live Help (you need your credit card or EE balance).
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try changing the datatypes in tblBoletimMocao to nvarchar(255).
Dale FyeOwner, Developing Solutions LLCCommented:
I don't think the problem is nchar vs nvarchar.

Generally, when working with SQL Server, I make sure that every table contains:
1.  A primary key
2.  a RowVersion column (many call this a TimeStamp).  The RowVersion allows SQL Server to resolve multi-user conflicts.

So, after making sure that every table contains a PK, then go back to every table and add a RowVersion column

Alter Table [TableName]
ADD SSMA_TimeStamp RowVersion

You never indicated how you are adding records.  Are you simply trying to do it in the table for now? or are you using a form based on the table or query?

One thing you need to understand with SQL Server is that unlike Access, if you have autonumber (Identity) field in your table, that value will not be populated until after a new record is saved.  As an example, when you start typing in any field of an Access table which contains an autonumber field, the autonumber field gets populated.  With SQL Server, this field does not get populated until after you save the record.  This might be an issue if you have code behind a form which is performing validation and references the Identity columns.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Please show how you are adding rows to SQL Server from your front end (did I see linked tables ?)

How are you connecting to SQL Server ?

It does seem odd that you can add to tblFiles, but not the other(s), so there must be something very subtly different.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you show the linked table in Access, in Design view? Access will complain, but it will allow you to show the table in Design view.

Dale: I had a similar issue where columns declared as nchar had troubles in Access and converting them to nvarchar fixed the issue. The nchar datatype is not specifically mapped to an Access datatype, at least according to this:

To be fair, I've also seen nchar columns that worked fine in Access. I think it has to do with the driver being used, as well as the version of Access and SQL.
Dale FyeOwner, Developing Solutions LLCCommented:

I've never seen an issue with Access recognizing both nchar and nvarchar, but that doesn't mean it doesn't happen; most likely as a result of using an outdated driver, as you stated in your last sentence.


What SQL Server Express version are you using, and what SQL Server driver are you using?  Be sure that you have and are using the latest driver to get a good translation of data types between the Access and SQL Server.
João serras-pereiraAuthor Commented:
Hi -
wow ..
so many comments THANKS!!
I am back to my db and look batter and will come back asap!
Mark WillsTopic AdvisorCommented:
@Scott, @Dale,

In the screenshots, why does dbo.tblFiles appear as tblFiles (grid) but dbo.tblBoletimMacao appears as dbo_tblBoletimMacao (grid) ? Both have PK.
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
did you get any errors?
Check if your used logins  (sql) have access to "change" data?
Dale FyeOwner, Developing Solutions LLCCommented:
Depending on the method you use to link the SQL table to Access, it may automatically add the "dbo_" prefix to the table name, because Access doesn't recognize the period in the reference that SQL Server recognizes.  However, you can change the local name of the table and delete the "dbo_" prefix, which is what I do when I link the tables.  This allows me to use generally the same syntax to query the linked table as I would if I generated a pass-through query.
Mark WillsTopic AdvisorCommented:
Thanks Dale :)
João serras-pereiraAuthor Commented:
After a few days days, I could finally come back to the project. After re-reading all comments, I am pretty sure that the problem relies  on having some tables with keys and some other tables without them.

I am a real newbie to sqlServer and overlooked the migration. And now I scan't simply change the data structure (sqlSwerver is not allowing it to me). I do need to delete the table and import from ms/access with a key (now it will be an auto-number - easier. The is a result of bad design and overlooking questions.

So I will come back  VSN (hopefully) with the outcome of the solution pointe out by Dale plus the timestamp idea!
João serras-pereiraAuthor Commented:
Dale's solution worked fine. I just need now to now how to do the
So, after making sure that every table contains a PK, then go back to every table and add a RowVersion column

Alter Table [TableName]
ADD SSMA_TimeStamp RowVersion

because I saw no command line and the "alter table" was greyed out...
Thanks for the help
Dale FyeOwner, Developing Solutions LLCCommented:
glad you were able to get this resolved.

When you do the migration from Access to SQL Server, there is a checkbox somewhere, (it's been a while since I've done it), which allows you to add the RowVersion (timestamp) automatically.  Look for that during the migration in the future.

João serras-pereiraAuthor Commented:
well... but now that I have all migrated?
Dale FyeOwner, Developing Solutions LLCCommented:
understood.  I only added that comment in case you have to do this again.
João serras-pereiraAuthor Commented:
no... please help me.. how do I...

Alter Table [TableName]
ADD SSMA_TimeStamp RowVersion

should I put this on a new question?
Dale FyeOwner, Developing Solutions LLCCommented:
in SQL Server, you create a new query, then paste that code into the query window, change [TableName] to the name of the appropriate table and then run the query.

Depending on your permissions on the Server, you may need to change that to read:
Alter Table [DatabaseName].dbo.[TableName]
ADD SSMA_TimeStamp RowVersion

Open in new window

and change [DatabaseName] to the name of your database on the SQL Server.
João serras-pereiraAuthor Commented:
gee. Thanks!!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.