Why do my linked tables not allow me to write to them?

[Guest]

I use Microsoft Access to retrieve data from a Sybase database. I used link
table functionality to do this. However, I am unable to write to the
database. If I look at database window under tables and I refer to the column
called "type" the value is "Table: Linked ODBC (Local)" Why can I not write?
How do I troubleshoot this.

 

[Rick Brandt]

I use Microsoft Access to retrieve data from a Sybase database. I
used link table functionality to do this. However, I am unable to
write to the database. If I look at database window under tables and
I refer to the column called "type" the value is "Table: Linked ODBC
(Local)" Why can I not write? How do I troubleshoot this.

If your source tables have no primary key then the links will not be
editable unless a local unique index is created. You should have been
prompted for this when you created the links (if you used the GUI to create
them). If you cancelled at the prompt or later refreshed the links the
local index would not exist and the link reverts to read only.

 

[fjbuch]

I am not sure if the source table has a primary key but I did indeed
select a primary key when prompted to do so by the link-creating
wizard.
When I first created the table in access I think it let me save the a
record after I added just one word to one field. However when I
reopened the file, I was back to being forbidden from writing. "Write
Conflict This record has been changed by another user since you
started editing it. If you save the record you will overwrite the
changes the other user made...:

Other strange behaviour includes letting me add a value to a previously
null field but not letting me edit the text in a previously saved field.

 

[Rick Brandt]

I am not sure if the source table has a primary key but I did indeed
select a primary key when prompted to do so by the link-creating
wizard.
When I first created the table in access I think it let me save the a
record after I added just one word to one field. However when I
reopened the file, I was back to being forbidden from writing. "Write
Conflict This record has been changed by another user since you
started editing it. If you save the record you will overwrite the
changes the other user made...:

Other strange behaviour includes letting me add a value to a
previously null field but not letting me edit the text in a
previously saved field.

Okay the "No Primary Key" thing is different from the "Another user has
modified this record..." thing.

The former would result in a table that is truly read only meaning it would
simply ignore your keystrokes and/or display "This Recordset is not
editable" in the status bar.

The latter can mean that the Primary Key on the server is of a type that is
not compatible with the types in Access. When this happens the type can be
casted to something else (like text) or rounding can occur and then Access
will get confused over whether the record has been edited since you began
editing it.

You basically have to avoid PKs that use these types. Edits done with
queries will usually still work and in some cases this doesn't affect edits
done in single view forms. Datasheets might not be fixable though if you
have no control over that PK field.

 

[Guest]

Both situations you describe may have taken place. Some of my tables did
indeed "ping" and ignore my keystrokes if I tried to edit anything in them.
While other tables that I created in which I know I specified a primary key
did not ping but rather gave me the "Write Conflict" message when I moved
from a record I had edited to another record.

I will ask our friendly systems analyst to assist in reconciling the
formatting/type of the primary key's in the two tables; the first table being
the one in Sybase and the second one being in my access database and linked
to Sybase via ODBC.

Is it only the Primary Key field where this difference of data type becomes
an issue? When I open the design of the table in Access I get a message
saying "Table 'CS_Rrp' is a linked table with some properties that can't be
modified. Do you want to open anyway?" The data type is text and the field
size is 10, Required is Yes, Allow zero length is Yes, Indexed is Yes,
Unicode compression No, IME Mode No Control and IME sentence mode is none
....whatever all that means.

I have not even tried adding a new record. All that I have been trying to do
so far is manually edit some fields from within Access rather than having to
open the Sybase frontend. I do not have a clue how to build queries, forms
and reports in PowerBuilder (our front end to Sybase) but I have about 10
years exeprience as an intermediate user of Acces which I would like to
utilize.

Any low level case reports of how people have solved problems similar to mine?

Thanks

 

[Rick Brandt]

Is it only the Primary Key field where this difference of data type becomes
an issue? When I open the design of the table in Access I get a message
saying "Table 'CS_Rrp' is a linked table with some properties that can't be
modified. Do you want to open anyway?" The data type is text and the field
size is 10, Required is Yes, Allow zero length is Yes, Indexed is Yes,
Unicode compression No, IME Mode No Control and IME sentence mode is none
...whatever all that means.

As usual it's not always that simple. The datatype you see in the Access link
might very well be what Access has cast the type to. You need to see what the
type is on the server. For example when one links to a SQL Server table that
uses a BigInt this problem can occur. Access doesn't have a numeric type that
will go that high so it will treat it as text.

If the table has BLOB or CLOB fields this can also trigger this behavior. In
SQL Server the addition of a special field called a Timestamp (not what everyone
else calls a Timestamp) usually solves that. That solution is not available in
non-SQL Server databases.

Again, sometimes this only occurs when editing directly in the linked table
datasheet which is something you should not be doing anyway. Try building a
simple form bound to the link and see if that will let you do edits without the
error.

 

[fjbuch]

OK I will try being a good boy and change values through forms based on
the linked table rather than in the table itself. Also I will do that
which is my ultimate goal which is to add some records to the table
(both in MS Access and Sybase) based on an Append Query and an Update
Query

 

[Guest]

I tried using forms. It did not solve the problem.
I still could not change a value in a field.
I have noticed that the ODBC link is allowing me to create a new record but
will not let me edit any record and will not let me delete a record.
At some stage in trying I saw the message "The Microsoft Jet Database engine
stopped the process because you and another user are attempting to change the
same data at the same time." I am 99% sure that that is not true.
I am wondering if this has got something to do with whatever Sybase does to
handle timestamping. I know that all our tables in Sybase have a field
recording time and date that a record was created and they also have a date
and time of the last edit. So my question now becomes. "In the history of the
universe has any mortal being ever ODBC linked - with write functionality -
Access to Sybase?"

 

[Rick Brandt]

I tried using forms. It did not solve the problem.
I still could not change a value in a field.
I have noticed that the ODBC link is allowing me to create a new
record but will not let me edit any record and will not let me delete
a record.
At some stage in trying I saw the message "The Microsoft Jet Database
engine stopped the process because you and another user are
attempting to change the same data at the same time." I am 99% sure
that that is not true.
I am wondering if this has got something to do with whatever Sybase
does to handle timestamping. I know that all our tables in Sybase
have a field recording time and date that a record was created and
they also have a date and time of the last edit. So my question now
becomes. "In the history of the universe has any mortal being ever
ODBC linked - with write functionality - Access to Sybase?"

When doing an ODBC update Access tests to see if the record has been changed
since your editing session of the record began. In most cases it does this
by comparing the before value of every field to the current value of every
field. If there are datatypes that have incompatibilities with Access then
it can be fooled into thinking that a field has been changed when it really
hasn't. This causes the error message you are seeing.

Now, in SQL Server one can add a Timestamp field which is a special binary
type that is updated every time a record is changed. When Access sees that
the table has a Timestamp field it doesn't bother with the field by field
comparison. It just compares the Timestamp field and it's done. Problem
solved (if you are using SQL Server).

You can see as described above that this will only affect bound editing. If
you issue an UPDATE SQL statement to your server there is no before/after
comparisoon going on. The rows are simply updated/deleted by the statement.
If you have an ODBC back end that has DataTypes that raise these issues and
you have no ability to change or eliminate those types them you might be
"stuck" doing all updates via SQL Statements. Some people prefer that
method anyway.

 

[fjbuch]

Bingo! Our systems administrator found a simple workaround based on
your thoughts. And quite frankly I am shocked. I had no idea that one
could use a a simple select query in Microsoft Access to edit the
underlying data. I thought the query simply created a table for display
after having read from an underlying database table. I had no idea that
a simple select query could write to a database table unless it was an
append query or an update query. By deliberately excluding the
timestamp fields that Sybase uses (KbdDate and UpdateDate) from the
query, everything works fantastically. Just to check it out, I created
another query with every field in it and it recreated the write
conflict problem. So we have determined with little doubt that this is
simply an Access-Sybase timestamp incompatibility. Surely hundreds of
people must have run into this problem and surely there should be an
easily understandable description of how to work around the problem.

When I first read your comment, "you might be "stuck" doing all updates
via SQL Statements" I thought "Whoa Mama!" I don't do SQL statements. I
am not a computer professional, just an intermediate user. I have, as a
matter of interest, seen the SQL statements that Microsoft Access
creates based on what I do in the design view of my queries. I have
also learnt how to do union queries in SQL since one cannot do them in
the design view. The idea of doing an edit here and an edit there by
SQL made my eyes roll back. This now gives me a way out - by just doing
it manually through the Microsoft Access Query datasheet view means
that Access handles the SQL side of things on my behalf.

 

[BeautyTsang]

Hi,

I got the problem of can't edit the link tables without primary keys.
Actually, I want to ask how to prompt for this when I created the links?
I try this:
Set db = CurrentDb
For I = 0 To db.TableDefs.Count - 1
If Left(Trim(db.TableDefs(I).SourceTableName), 3) = "dbo" Then
db.TableDefs(I).Connect = "ODBC;DSN=" & dsn_bcp & ";SRVR=" &
srvr_bcp & ";DB=" & syb_db
If db.TableDefs(I).SourceTableName = "dbo.CAP_PARAM" Then
Set idxNew = db.TableDefs(I).CreateIndex("CIndex1")
idxNew.Fields.Append idxNew.CreateField("PARAM_NAME")
idxNew.Primary = True
db.TableDefs(I).Indexes.Append idxNew
End If
db.TableDefs(I).RefreshLink
End If
Next I

But it said "Runtime error 3057"
"Operation not supported on linked tables"

Thanks