Linking SQL Server 2000 to Access - Bigint datatype

[Ken McAndrew]

I'm currently trying to create an Access database that has links to SQL
Server views, so that end users can directly edit their data. When I link
these SQL views into Access, however, I'm given the "#DELETED" message
across all my rows. I've narrowed down the issue to the bigint datatype; it
appears that Access is not equipped to handle it. I tried this in both an
Access 2000 and Access 2002-2003 format database.

One of my colleagues suggested using a CAST to change the datatype of the
bigint to an int [CAST(<field> as int)], which works fine for display
purposes, the SQL view comes up in Access. However, when I attempt to edit
the data, I get the following message:

Update or insert of view or function '<name>' failed because it contains a
derived or constant field. (#4406)

In doing a Google search, I found the MySQL ODBC connector program has a
"convert bigint to int" option, but a similar tool doesn't seem to exist in
the Microsoft ODBC administrator.

Does anyone have any ideas on how to make this viable?

Thanks.

 

[Brian Moran]

What version of Access are you using? I hadn't known about this particular
issue, but I can understand why it's happening. Perhaps a newer version of
Access knows about BIGINT? Although that may not be a viable solution for
you anyway?

--

Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com

 

[Ken McAndrew]

I'm using Access 2003 to generate the database, but it's in Access 2000
format. I did try converting it to Access 2002-2003 format using the
database utilities, but it had the same problems.

--

Kenneth S. McAndrew
Software Developer, Information Concepts
(e-mail address removed)

What version of Access are you using? I hadn't known about this particular
issue, but I can understand why it's happening. Perhaps a newer version of
Access knows about BIGINT? Although that may not be a viable solution for
you anyway?

--

Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com

I'm currently trying to create an Access database that has links to SQL
Server views, so that end users can directly edit their data. When I link
these SQL views into Access, however, I'm given the "#DELETED" message
across all my rows. I've narrowed down the issue to the bigint datatype; it
appears that Access is not equipped to handle it. I tried this in both an
Access 2000 and Access 2002-2003 format database.

One of my colleagues suggested using a CAST to change the datatype of the
bigint to an int [CAST(<field> as int)], which works fine for display
purposes, the SQL view comes up in Access. However, when I attempt to edit
the data, I get the following message:

Update or insert of view or function '<name>' failed because it contains a
derived or constant field. (#4406)

In doing a Google search, I found the MySQL ODBC connector program has a
"convert bigint to int" option, but a similar tool doesn't seem to exist in
the Microsoft ODBC administrator.

Does anyone have any ideas on how to make this viable?

Thanks.

--

Kenneth S. McAndrew
Software Developer, Information Concepts
(e-mail address removed)

 

[Sue Hoegemeier]

Make sure you are using the latest Jet service pack. Some
issues with mapping BigInt were first corrected with Jet 4.0
SP6.

-Sue

 

[Ken McAndrew]

I've got SP8 running for that, going by the check Microsoft suggests. And
I've got all my service packs and such for both WinXP Pro and Office 2003
set up.

I'm having some luck on this with the Access data project filetype (direct
access to SQL Server) and using roles to limit what the user can do (like
delete tables accidentally), but a linked MDB would still be safer. Thanks
for the continued input.

--

Kenneth S. McAndrew
Software Developer, Information Concepts
(e-mail address removed)

Make sure you are using the latest Jet service pack. Some
issues with mapping BigInt were first corrected with Jet 4.0
SP6.

-Sue

I'm currently trying to create an Access database that has links to SQL
Server views, so that end users can directly edit their data. When I link
these SQL views into Access, however, I'm given the "#DELETED" message
across all my rows. I've narrowed down the issue to the bigint datatype; it
appears that Access is not equipped to handle it. I tried this in both an
Access 2000 and Access 2002-2003 format database.

One of my colleagues suggested using a CAST to change the datatype of the
bigint to an int [CAST(<field> as int)], which works fine for display
purposes, the SQL view comes up in Access. However, when I attempt to edit
the data, I get the following message:

Update or insert of view or function '<name>' failed because it contains a
derived or constant field. (#4406)

In doing a Google search, I found the MySQL ODBC connector program has a
"convert bigint to int" option, but a similar tool doesn't seem to exist in
the Microsoft ODBC administrator.

Does anyone have any ideas on how to make this viable?

Thanks.