Updating attached SQL Server Tables 
Author Message
 Updating attached SQL Server Tables

Hi,

I am attempting to update SQL Server tables

Set rst_Org = CurrentDb.OpenRecordset("qry_Org", dbOpenDynaset)
If rst_Org.EOF and rst_Org.BOF Then Exit Function
rst_Org.MoveFirst
rst_Org.Edit

On the edit line I get an error "3027, Can't Update Database or object is
read only."

I have proper rights to the table, because I can do the same thing in an
update query with no problem.  However, I have a series of tables must all
update successfully or non at all, and thus I am using ws.BeginTrans and
ws.CommitTrans surround all the update code.

Is there a way to do this?

Thanks!
--
Mark Bruso

Portland, Oregon



Sun, 31 Mar 2002 03:00:00 GMT  
 Updating attached SQL Server Tables
1) you should make a reference to the db and use it for the recordset such
as:
    set db=currentdb
2) when you linked the table, did it prompt you for a primary index column?
Are you sure it is a table and not a view
3) does your Query have outerjoins etc that cause the recordset to be
readonly? (e.g. is your SQL syntax correct)

4)Have you thought about a passthrough query?
5)have you thought about creating an ODBC workspace, then a Connection
object, and executing SQL against the Connection (you will also be able to
do transaction processing)

Patrick


Quote:
> Hi,

> I am attempting to update SQL Server tables

> Set rst_Org = CurrentDb.OpenRecordset("qry_Org", dbOpenDynaset)
> If rst_Org.EOF and rst_Org.BOF Then Exit Function
> rst_Org.MoveFirst
> rst_Org.Edit

> On the edit line I get an error "3027, Can't Update Database or object is
> read only."

> I have proper rights to the table, because I can do the same thing in an
> update query with no problem.  However, I have a series of tables must all
> update successfully or non at all, and thus I am using ws.BeginTrans and
> ws.CommitTrans surround all the update code.

> Is there a way to do this?

> Thanks!
> --
> Mark Bruso

> Portland, Oregon



Sun, 31 Mar 2002 03:00:00 GMT  
 Updating attached SQL Server Tables
you should have a primary key (and timestamp) field in SQL server table


Quote:
> Hi,

> I am attempting to update SQL Server tables

> Set rst_Org = CurrentDb.OpenRecordset("qry_Org", dbOpenDynaset)
> If rst_Org.EOF and rst_Org.BOF Then Exit Function
> rst_Org.MoveFirst
> rst_Org.Edit

> On the edit line I get an error "3027, Can't Update Database or object is
> read only."

> I have proper rights to the table, because I can do the same thing in an
> update query with no problem.  However, I have a series of tables must all
> update successfully or non at all, and thus I am using ws.BeginTrans and
> ws.CommitTrans surround all the update code.

> Is there a way to do this?

> Thanks!
> --
> Mark Bruso

> Portland, Oregon



Mon, 01 Apr 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Acc2000: Access tables attached to SQL-Server won't update

2. Attaching to SQL server tables

3. SQL Server with Access Attach table?

4. Open attached SQL Server table w/out prompting

5. Attached SQL Server Table

6. Attached SQL Server Table

7. Attached table and not attached table

8. Updating A SQL server table through DAO and ODBC In Access 2000

9. Simple update to a row in a table in a SQL Server database

10. Help me: Update SQL Server table through sqlDataAdapter...

11. SQL Server table update

12. Simple update to a row in a table in an SQL Server database

 

 
Powered by phpBB® Forum Software