Unable to update data from Access w/ Linked SQL tables

[RottPaws]

--Cross-post from SQL Server forum--

I've got an application that I'm trying to migrate out of Access97.

I'm starting with moving the data into a SQL Server 7 database. I should, theorectically, still be able to use the Access front-end until it can be replaced later.

I have transferred the data and linked the SQL tables to the Access database using the SQL database owner login/password.

In SQL Server, I have gone into properties/permissions for the tables and checked all the permissions for the database owner user account.

I am able to open the tables and look at the data (I could do this before changing permissions), but when I try to modify or add records, I get an error telling me the update failed.
ODBC--update on a linked table 'dbo_orders' failed.

I know this is vague, but I'm pretty new to SQL Server. Is there something obvious that needs to be set in SQL Server to allow the updates? Something begginers miss???

My SQL tables have PK's, so that's not it.

I should probably also mention I have a diagram created for the SQL database linking the FK's and PK's of the tables to each other.

Initially, I'm just trying to edit data in one field of a table. This field is not part of any reference and has no constraints, triggers, etc. _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[FancyPrairie]

Just a thought. I have found that when I make changes to the tables on the SQL server, I need to relink my tables. Did you relink after changing your permissions?

 

[RottPaws]

Yes, I deleted and relinked the tables after changing permissions. I didn't think the permissions were the problem since I was able to make changed to the table from SQL server with the same login/password . . .

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[AnnYH]

Hi, Rott:

There are a lots of thing you should make sure.
1. error msg pop out at new order entry or edit record.
2. You said you migrate out of Access 97, you must use dao to work with Access frontend.
3. How did you setup you PK? Autonumber or no? SQL server not like Access, you must change to ADO
4. How many table you link to your form.? Make sure when you make query using more than one table, those table have a relationship.

Currently we migrate out of Access 2000 to SQL 2000. Most of part I have passed, so if you can tell me the detail of your err msg, maybe I can help.

AnnYH

 

[cmmrfrds]

When you set up the ODBC for the SQL Server tables, are you using the login and password you set up in SQL Server. Are you using NT Authentication or SQL Server Authentication or both? Paste in your connection string from the ODBC set up.

Right click on the linked table and go into design view. Right click on top of the table design Form and go into properties - this is the connection string.

 

[RottPaws]

AnnYH,

1. The error occurs both when I to edit an existing record and when I try to insert a new record.
2. I migrated the data from Access to SQL Server with a DTS package in SQL Server.
3. I've linked 3 tables. The PK of one is numeric, another is varchar, and the third is a 2-field key, numeric and varchar.
4. So far, I haven't tried to do anything with these tables from forms or queries. I just opened the linked table in Access and tried to add/modify data in the table.

cmmrfrds,
I am using SQL Server authentication and I'm using the username and password set up in SQL Server.

I linked the tables with the link table wizard. I've also got tables linked from an Oracle db and I am able to modify those tables.

The connection string is:
ODBC;Description=Staging;DRIVER=SQL Server;SERVER=dsql01;UID=staging;APP=Microsoft® Access;WSID=DU149544;DATABASE=Staging;TABLE=dbo.NST_Orders _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[AnnYH]

Hi, Rott:

I haven¡¯t used SQL Server authentication, I used NT authentication. So I¡¯m not sure it will be same or not.

For us, first time we got SQL table was not allowed me to make any change.

1; On Access link table, directly open your table ¡°Table1¡±, can you see the record navigation button ¡°*¡±, is enable or no. If no, it means you permission setup are not correct. Add you user name to your SQL Data base user, check yourself everything for every table column. You got yourself max permission.

And refresh your ODBC, and re-link all tables. Try to open your table. See what happen.

2; If ¡°*¡± button is enable, when you insert new record what¡¯s the Err msg saying?

You know what migrate Access to SQL are not easy job, it drives me nuts. Seriously, You must be carefully each small piece. Check each column ¡°Null Allowe¡±, it will be easy for you use.

Right now I thought your problem should be permission. Because you haven¡¯t passed first step.


Good luck. I hope it is working

I¡¯ll keep tracking your MSG.

AnnYH

 

[RottPaws]

I have all navigation buttons on the linked tables (first, prev, next, last, new).

When I try to modify an existing record, the message I get is:
ODBC--update on a linked table 'dbo_NST_Orders' failed.

When I try to add a new record, I get two messages:
ODBC--insert on a linked table 'dbo_NST_Orders' failed.

And:
. . . INSERT statement conflicted with COLUMN FOREIGN KEY constraint . . . . . .

The 2nd one doesn't make since. The column it's referring to has a valid entry that exists in the PK field of the other table . . . _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[AnnYH]

Ok, now
Go to your diagram, check the relationship, uncheck "enforce relationship to replication" and enforce relationship to Insert and Update"

after that you can edit you each table from table view.

Your table are enforce the relationship. So you can't just put record to one table.

See if it is work.
AnnYH

 

[RottPaws]

I already had both 'Enable relationship for INSERT and UPDATE' and 'Enable relationship for replication' checked.

I don't have 'Check existing data on creation checked. _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[AnnYH]

Sorry,

You should not have both 'Enable relationship for INSERT and UPDATE' and 'Enable relationship for replication' checked.


Both three check box should not be checked.

Leave it blank.

You will be able to insert new record and edit.

AnnYH

 

[RottPaws]

I changed the settings so only 'Enable relationship for replication' is checked.

I deleted and relinked the tables. No change.

I may just delete the diagram and see if it works then . . . _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[cmmrfrds]

Try creating a new MDB and link just 1 table and don't use any diagrams or relationships. Try to update that table, if you can't then you should look at the SQL Server permissions.

Do you have a copy of Query Analyzer on your desktop? If so, try doing an update from QA. If you can't then it is definitely sql server permissions.

What role(s) does the login "Staging" belong to? Does "Staging" have direct update permissions on your tables or does it belong to a role that has update permission? Who is the database owner (dbo) of your tables?

 

[RottPaws]

I'll have to try working with the tables in a new mdb tomorrow. I'll let you know then.

I am able to work w/ the tables from QA. In addition to moving the data from Access to SQL Server, the DB structure is changing. I migrate the data over with a DTS package and then convert it to the new structure with a T-SQL procedure I wrote (mostly a series of insert and update queries).

Staging is the owner of the database in question and has the following database role memberships: public, db_owner, db_datawriter.

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[CKCode]

I have the similar problem. Try and look at the [Properties] of the SQL database in Enterprise Manager. Under [Connection]/[Default Connectin Options], make sure quoted identifier is not selected.
After I have unselected it, I can make changes or insert new records without any problems.

 

[RottPaws]

I just got back to this today.

I tried linking a single table to a new mdb as cmmrfrds suggested and got the same results.

I checked the connection properties for the server. Quoted identifier was not checked. If there's a way to check this for the specific database, I don't know where it is....
The settings are as follows:
Connecions:
Maximum concurrent user connections: 0
Default connecition options:
ANSI warning
ANSI nulls
No count

Remote server connections:
Allow other SQL Servers to connect remotely to this SQL Server using RPC

I also tried deleting the diagram to see if that would allow me to make changes. After deleting the diagram, I deleted and re-linked the table, but it still won't let me make any changes. _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[cmmrfrds]

Try bypassing ODBC and see if you can update. Here is a function that you can use to update the Northwind database in SQL Server. If this works okay then switch to your database and try it. If that works okay then it has to be in the ODBC. The Example uses ADO. Need reference checked
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Function Atest()
Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"

sql1 = "select * from dbo.Employees "
rs.Open sql1, connString, adOpenStatic, adLockOptimistic
Debug.Print rs.RecordCount
Debug.Print rs.EOF
Debug.Print rs!lastName

'-rs!lastName = "Davalio" ' change it back
rs!lastName = "SAMPSON"
rs.Update
rs.Close
Set rs = Nothing

End Function

By the way there is a quote identifier property on the ODBC setup in addition to being a property setting on the server. Its purpose is to tell sql server not to check the text inside of quotes for being a reserved word. Unless your field names are reserved words this should not make a difference.

 

[RottPaws]

The northwind db is not on the server. I changed the connection string to my database and changed the select string and field names.

It connects and prints to the debug window, but I get an error on the update command.

Run-time error '-2147217885 (80040e23)':
Cursor operation conflict

I thought it might be because of the adOpenStatic setting, but I got the same results with adOpenDynamic.

I do not have any reserved keywords as field names.

Interestingly enough, I was able to run a delete query against one of the tables . . . . _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[cmmrfrds]

By default on the ADO connection the cursor location is server side try changing to client side.

cn.CursorLocation = adUseClient
rs.Open sql1, connString, adOpenStatic, adLockOptimistic

On a client side cursor ADO will always use static even if you put dynamic. It could be connected to how the cursor is maintained in sql server - possible option settings.

 

[RottPaws]

CKCode was on the right track. I got 'No count' unchecked in the Server properties Connections settings and now I am able to insert/update records in tables.

Thanks for all your help, everyone! _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!