Linked SQL Table - can not add new record

[Rx_]

A script is used for the Linked Tables - the ODBC setting for SQL Server Native Client 10.0 are exactly the same
One table is tblAPD_Fed_St_CO - it was copied into tblAPD_Fed_St_CO _Archive - this all worked before migrating to SQL Server 2010

see attachment: The top table has a (New) button.
the _Archive does not have a New, it also won't allow any edits. Security on both is DBO.

On the SQL Server Server Manager Studio side - it can be appended. On the Access side, it can't. Copy a row in SQL Server view and insert it in the new row - refresh the linked table - and the new row shows up.

Everything is scripted - it is the only table in Access linked tables where the new record doesn't show up. Maybe because of the lack of a primary key?
I am stumped, its Friday the 13th and I thank my Lucky Stars that I am not Superstitious.

 

[GinaWhipp]

Do you have a BIT Data Type field anywhere in that table? If yes, set the Default to ((0)) AND uncheck the box that says Allow Nulls. Once done refresh the link and you should be able to add records.

Note, this above should be done for *every* BIT Data Type you have.

 

[pbaldy]

Maybe because of the lack of a primary key?

The lack of primary key is certainly a problem. Give it one and relink the table.

 

[Rx_]

Two really great tips! I was in the process of adding a primary key late Friday and ran out of time. I will have to drop and re-create the table. And before that, I will need to do a Make Table query to archive the delete logging history. The table only logs the deleted record for one form. A record is rarely deleted.

I went back on my migration notes for the new MS Access migration tool (at home, can't remember the name). It did give me an error of no primary key for that table. It would have been nice if the message somehow gave some hint of the read-only effect.
In Access 2010 (and the version before), it worked like a champ with linked tables.

The BIT data type is interesting too. While it is probably not used in my problem table, this suggestion will come in handy for someone else.

My Delete logging function copied a record to the logging table and returned a pass / fail in code. Then the next line deleted the record from the working table. The function did its job perfectlly reporting a False from the function every time it was called. Due to a loose nut at the keyboard (that would be me), the return value was just logged in my general application health logging. It was calling out for assistance.
Mental note to self: Audit my own event logging for details!

 

[Rx_]

Re: Linked SQL Table - can not add new record - Solved

Just a followup - Archived the table with a make-table query.
Wrote a TSQL Script to drop and recreate the table with a primary key.

For existing Linked Tables in Access to SQL Server - remember to Refresh the link for the change.

Appended the Archived data back to the new refreshed table.
It is all good and working now. Access shows the * after the last record to indicate a new record can be added. Of course that was obvious since the Append query worked.

The Microsoft SQL Server Migration Assistanct for Access did give me a warning that there was No Primary Key. The Access table before migration did not have a primary key and worked just fine.
Will add this to my migration document for future reference.

For what it is worth, six of my SQL Server fields are of type Bit. And, they do allow Nulls. Wonder if it is the driver?
My tables are connected via scripting using SQL Server Native Client.
With a DSN-Less connection.

Thanks for the moral support - this is an update in case others search and have this same problem.

 

[GinaWhipp]

Rx... interesting, perhaps it is the Driver, but I do use SQL Server no scripting with Native Client! I will have check that out...

 

[Rx_]

I wrote code for the linking, would you like a copy?

 

[GinaWhipp]

Yes, that would be nice... then I can see why your code is so special

 

[michaelH]

I wrote code for the linking, would you like a copy?

Hi Rx_, I was wondering if you are able to share the code for linking you mentioned?

 

[michaelH]

I am in the process of transitioning my Access Backend database to an MS SQL database and I am having a couple of issues. My primary issue is stated below and related to this post I believe.

My control source for the form is a query created from a single linked table from the Access Backend. All functionality works i.e. displays all records and allows the users to delete and add new records. When I change the control source query to use the new MS SQL Backend table the form is blank and no form controls are displayed and the user no longer can scroll through the records, update, delete or add.

The form displays the data based on a default date range set in the form which is set for the current day. This causes no results in the query then causing the form to be blank, when I change the default date range to a range that contains data the form opens correctly with the form controls and all functionality EXCEPT entering a new record.

Any help getting to a solution will be greatly appreciated.

 

[GinaWhipp]

I am in the process of transitioning my Access Backend database to an MS SQL database and I am having a couple of issues. My primary issue is stated below and related to this post I believe.

My control source for the form is a query created from a single linked table from the Access Backend. All functionality works i.e. displays all records and allows the users to delete and add new records. When I change the control source query to use the new MS SQL Backend table the form is blank and no form controls are displayed and the user no longer can scroll through the records, update, delete or add.

The form displays the data based on a default date range set in the form which is set for the current day. This causes no results in the query then causing the form to be blank, when I change the default date range to a range that contains data the form opens correctly with the form controls and all functionality EXCEPT entering a new record.

Any help getting to a solution will be greatly appreciated.

You should really start a new thread. This one is 8 years old and won't get the attention it deserves as only the people within this thread will your post.

 

[michaelH]

You should really start a new thread. This one is 8 years old and won't get the attention it deserves as only the people within this thread will your post.

Thanks, Gina...I have done what you suggested.