Cannot Insert Linked SQL Table via Access

[amdrit]

Hello All,

I have a Access database that links a table from SQL. Currently the table
only has a primary key defined in SQL. When I add additional keys to the
table, Access is no longer able to insert rows. Without the keys, fetching
data is really slow. Granted the table is quite large, a little over 2
million rows.

Does anyone have any ideas or suggestions about this?

 

[Stefan Hoffmann]

hi,

I have a Access database that links a table from SQL. Currently the table
only has a primary key defined in SQL. When I add additional keys to the
table, Access is no longer able to insert rows.

After changing the tables definition you need to relink the table.


mfG
--> stefan <--

 

[Dale Fye]

You mean indexes, right?

Where are you adding the "additional keys", (SQL Server or Access)? It has
been a while since I have used SQL Server, but the last time I tried, I was
not able to add indexes via Access, this had to be done in SQL Server.

Have you refreshed the link to the table? If not, do so.

When you created the index, did you specify that the values should be unique
and tell it to allow nulls (not quite sure whether these are the right terms
in SQL Server, but they are the options when creating an Access index)? One
of these settings could affect whether you are able to insert a new record or
not. Actually, I would think that if you set this index up incorrectly, SQL
Server would have warned you if you already had records that violated the
index properties.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[John Spencer (MVP)]

What version of MS SQL Server are you using?

In most cases, you need a TIMESTAMP field (RowID in SQL 2008?) in the SQL
Server table if you want to modify an existing record.

Once you have modified the SQL Server table, relink the tables from Access.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[a a r o n . k e m p f]

you _CAN_ build indexes (on SQL Server) using the MS Access interface.

File, New Project (existing data)

open a (SQL Server) tabel in design view.
build an index

 

[Dale Fye]

Thanks, Aaron. I'll keep that in mind

message
you _CAN_ build indexes (on SQL Server) using the MS Access interface.

File, New Project (existing data)

open a (SQL Server) tabel in design view.
build an index

 

[James A. Fortune]

What version of MS SQL Server are you using?

In most cases, you need a TIMESTAMP field (RowID in SQL 2008?) in the
SQL Server table if you want to modify an existing record.

Once you have modified the SQL Server table, relink the tables from Access.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Really? The way I understand it is that in earlier versions of SQL
Server at least, you don't actully need a TIMESTAMP field in order to be
able to modify an existing record, but a TIMESTAMP field, if available,
will allow SQL Server to use only that field to check when the record
has been modified rather than checking each field, resulting in better
multiuser performance. In the past when I moved Access tables to SQL
Server, creating a new primary key (RowID if you please), usually named
by appending "SS" to the Access primary key name, was all that was
needed to be allowed to modify a record in SQL Server. Perhaps we are
saying the same thing, but if not, could you point me to some
documentation explaining why a TIMESTAMP field is now required for editing?

James A. Fortune
(e-mail address removed)

 

[Rick Brandt]

Really? The way I understand it is that in earlier versions of SQL
Server at least, you don't actully need a TIMESTAMP field in order to be
able to modify an existing record, but a TIMESTAMP field, if available,
will allow SQL Server to use only that field to check when the record
has been modified rather than checking each field, resulting in better
multiuser performance.

It depends on the DataTypes of the other fields. If all map directly to
Access types then a TimeStamp is merely a good idea. If one of them
doesn't then a Timestamp is required or you run the risk of getting the
"another user has edited this record..." error.

 

[James A. Fortune]

It depends on the DataTypes of the other fields. If all map directly to
Access types then a TimeStamp is merely a good idea. If one of them
doesn't then a Timestamp is required or you run the risk of getting the
"another user has edited this record..." error.

That makes perfect sense. It hinges on the ability of SQL Server to
make the proper field comparisons.

James A. Fortune
(e-mail address removed)

 

[Stefan Hoffmann]

hi James,

That makes perfect sense. It hinges on the ability of SQL Server to
make the proper field comparisons.

Keep also in mind, that the error mentioned by Rick may occur when you
have unprecise data types. "Directly" in these cases means rounding may
be an issue, too.


mfG
--> stefan <--

 

[a a r o n _ k e m p f]

The 'requirement to have timestamps' is a bug with linked tables.

Get rid of linked tables and you get rid of _ALL_ the extra overhead

-Aaron

 

[a a r o n _ k e m p f]

there is no such thing as a field conversion.

All it comes down to is 'should you use one set of datatypes' or
'should I use two different sets of datatypes'.

get rid of linked tables and all your difficulties go away

move to ADP

File, New, Project (existing data)

 

[Tony Toews [MVP]]

The 'requirement to have timestamps' is a bug with linked tables.

Timestamps aka RowVersion fields are a feature of SQL Server to make it work better
especially with Access.

Get rid of linked tables and you get rid of _ALL_ the extra overhead

Wrong.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[a a r o n _ k e m p f]

Timestamps are not created _SPECIFICALLY_ for MS Access.
but linked tables _REQUIRE_ them-- because Jet is too ****ing buggy
for real world usage.

So sorry that your database doesn't work against SQL Server unless you
change the schema.

Maybe if you learned how to keep your logic in a database with a
future (SQL Server) then maybe, just maybe-- you'd stop making
excuses.

Get rid of linked tables and you get rid of _ALL_ the extra overhead
Get rid of linked tables and you get rid of _ALL_ the extra overhead
Get rid of linked tables and you get rid of _ALL_ the extra overhead
Get rid of linked tables and you get rid of _ALL_ the extra overhead
Get rid of linked tables and you get rid of _ALL_ the extra overhead

-Aaron