By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,433 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Using Access to view and edit SQL Server table with Bigint primarykey

P: n/a
Does anyone have a suggestion to do this? I have a table in SQL Server
that has it's primary key as Bigint. When viewing directly with ODBC
all the fields and rows show "#Deleted". I've tried to create a view
in SQL Server and that helps, except that I can't edit the data in
Access.

At this point I can't change the data type on the SQL Server table.
There are too many other apps that access this table to change.

TIA for any help.

Mar 18 '08 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Oh I forgot is case someone wants to know

SQL Server 2005
Access 2003

Mar 18 '08 #2

P: n/a
On Tue, 18 Mar 2008 04:37:22 -0700 (PDT), scoots987
<sc*******@gmail.comwrote:

You could create a view, and index it. The view would cast the bigint
to another data type, perhaps varchar. Then attach to this view just
like you attach to a table.

-Tom.

>Does anyone have a suggestion to do this? I have a table in SQL Server
that has it's primary key as Bigint. When viewing directly with ODBC
all the fields and rows show "#Deleted". I've tried to create a view
in SQL Server and that helps, except that I can't edit the data in
Access.

At this point I can't change the data type on the SQL Server table.
There are too many other apps that access this table to change.

TIA for any help.
Mar 18 '08 #3

P: n/a
Thanks, I did that but the data is not editable in Access. I haven't
tried creating an index on the actual view. If the field in question
is a primary key is an index still needed?

There has to be a way to do this. Right now we have create
intermediate tables to track changes in one table that will eventually
update the data in another table.

Mar 18 '08 #4

P: n/a
"scoots987" <sc*******@gmail.comwrote in message
news:6a**********************************@v3g2000h sc.googlegroups.com...
Does anyone have a suggestion to do this? I have a table in SQL Server
that has it's primary key as Bigint. When viewing directly with ODBC
all the fields and rows show "#Deleted". I've tried to create a view
in SQL Server and that helps, except that I can't edit the data in
Access.

At this point I can't change the data type on the SQL Server table.
There are too many other apps that access this table to change.

TIA for any help.
The fact of having a bigInt field should not be casuing this problem. I
susspect somting else is at play here.

try:

#1 -- delete the linked table

#2 -- add a time stamp field to the table.

#3 -- re-link...and make sure you select the pk field.

You should have no problems working with this table at all. I use bigInts
all the time, and they work just fine....

It is possible that your experiencing a null bits problem outlined here
also:

ACC2000: Write Conflict Error When You Try to Update Records in a Linked SQL
Server Table

http://support.microsoft.com/?id=280730

However, I don't think the above applies to 2005, but check anyway....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Mar 18 '08 #5

P: n/a
On Mar 18, 7:42 am, scoots987 <scoots...@gmail.comwrote:
Oh I forgot is case someone wants to know

SQL Server 2005
Access 2003
Someone else (me) wants to know what data are being modelled that
require a primary key
ranging from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
Mar 18 '08 #6

P: n/a
>
Lets see if that works. BTW, I believe this problem with Bigint is
only if the primary key in of that data type. Might be wrong on that
and don't have time to check it.
Yes, that seems to be the case. You can use bigInt as a field type (it get
returned as a string), but you can't make it the primary key....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Mar 18 '08 #7

P: n/a

My apologies here, I'm a quite wrong on this.

You can most certainly use bigint fields in the table, and you can even set
that field to a unique identifier, and you can also set it to auto increment
also. For the most part this means that field will behave very much like a
primary key field.

However the instant you try to set that field as a true primary key on the
SQL server side, MS access will complain and it will not function correctly.
So, using int.....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

Mar 18 '08 #8

This discussion thread is closed

Replies have been disabled for this discussion.