2007 ADP doesn't recognize BIGINT data type

[anthony.marchesini]

I have an ADP that I allows me to do table design work on tables with
BIGINT fields if I use Access 2003 but not when I use Access 2007. It
looks like someone 'fogot' to tell Access 2007 that BIGINT is a valid
SQL Server data type. This is the error I receive:

Table 'Table Name' could not be loaded.

The table being loaded into memory has a user-defined data type
('bigint') that is not recognized.
Close all of your open database diagram and table designer windows.
The new data type will be recognized when you re-open the diagram or
table designer.

This error message is obviously bogus as BIGINT is certainly not a
user-defined data type. Anyone else have this problem? Any
solutions?

- Anthony

 

[aaron.kempf]

what is it called 'compatibility level'?

have you changed this from the default back to 7.0 or somethign?
hope that helps

-Aaron

 

[Norman Yuan]

I have no problem in 2007 ADP to set a table's column as BigInt.

 

[Sylvain Lafontaine]

Access 2007 has made you the suggestion of « Close all of your open database
diagram and table designer windows. The new data type will be recognized
when you re-open the diagram or
table designer. ». Have you tried it?

Second, you are using a user-defined data type; did you try setting the
field type directly to the BigInt type without using your used-defined data
type?

 

[ARM07470]

I hadn't messed with the compatibility level of the database but
changing it doesn't help either. I'm NOT using a user-defined data
type, Access just happens to think that I am whenever I have a table
with a BIGINT column.

Norman Yuan mentions that he has no trouble creating BIGINTs in Access
2007 which was puzzling to me as I had verified this behavior on
several different machines, servers and databases before posting.
More testing revealed that Access 2007 DOES recognize BIGINT when
connected to SQL Server 2000 but DOES NOT when connected to SQL Server
2005. Norman, if you could verify that you were using SQL 2000 that
would be helpful.

- Anthony

 

[Norman Yuan]

In your OP there was no mention of SQL Server version. I only tried on SQL
Server2000.

So, I tested it again on SQL Server2005(Express) and yes, the same problem
as you described. It does not happen if the SQL Server is 2000.

It looks like, when MS Access2007 is connected to SQL Server2005, it does
not recognize BigInt type.

When connected to SQL Server2005:

1. When you create a new table inside ADP, the column type list does not
include "BigInt" type. If you type in "BigInt" instead of select from list,
you cannot save the table design with the said error message.
2. If you create a table with column of BigInt type in other tool, such as
SQL Server Management Studio, then you can still open the table in
Access2007 in datasheet view, but you cannot modify the table's design. When
trying to open it in design view, you get the said error. That means, if a
table has bigint column in it, it become read-only in ADP because of this
bug

However, if the back end is SQL Server2000, MS Acecss2007 does not run into
above issue.

So, it is obvious a bug when Access2007 is combined with SQL Server2005.

Since I do not make effort in ADP any more, and all my previous ADP projects
do not use BigInt, the bug would not bother me, forever. So, your solution
would be either avoid to use BigInt, or drop ADP development/maintenance.

 

[aaron.kempf]

I'm not entirely positive that I've had this problem and I'm fairly
confident that I've use SQL 2005 against an Access 2003 ADP (edited
using Access 2007)

I'd bet my last 5 dollars that it worked flawlessly for me; and I use
bigints a lot for some PKs

on the 2000,2002,2003 side; yeah for sure I've used bigint extensively
without a problem

 

[ARM07470]

Sorry I didn't mention the SQL Server version in my OP. We're all
switched over to SQL 2005 except for one old legacy application
running on a VM that still requires SQL 2000. It never occurred to me
that the behavior would vary based on the version of SQL Server until
you posted that you weren't experiencing the same problem. Thanks for
confirming that your original testing was on SQL 2000 and that you
also experience the same problem when using SQL 2005.

Getting this bug fixed would be nice but it certainly isn't critical.
All of our forms and reports work just fine and I can always use SQL
Server Management Studio when I need to do design work on a table that
has a BIGINT in it. I do, however, prefer Access for table editing
for the simple fact that it shows field descriptions alongside the
fields names and types. SSMS buries this in the property sheet for
each individual field which makes it impossible to review the
descriptions of several fields at once.

- Anthony

 

[aaron.kempf]

yes; I much prefer the design view in Access for tables also

and sproc design and view design

and forms design..

and reports design

oh wait a second; when IS 'SQL Server Forms Service' going to be
released?
If Reporting Services is here and now; then maybe Forms Service will
come along soon-- so that we can enter data ROFL

 

[aaron.kempf]

I also was a really really big fan of the table designer in the Access
2000 world-- I really enjoyed having more properties-- more columns
available at top-- I wish that they would offer that for SQL Server
here and now

and a better ability to copy and paste columns in between tables

yeah-- we could do it via scripts; but copy and paste is always faster
if it's a single table LoL

 

[ARM07470]

I actually don't care for Access when designing stored procedures and
views. I prefer to edit SQL using a fixed-width font, tab stops at 4
and color syntax highlighting. I was disappointed to find that Access
2007 is still unable to meet such modest demands.

- Anthony

 

[aaron.kempf]

why.. because you like typing out the text?

I can do it faster than you can kid

I'm not going to be a linux-command-line fag when I grow up-- you can
-- but i'll choose drag and drop over typing any day of the week

 

[sorayal]

Is there a solution?

Is there any known solution to this problem? How can I access my tables in design view to edit if I have SQL Server 2005?

I hadn't messed with the compatibility level of the database but
changing it doesn't help either. I'm NOT using a user-defined data
type, Access just happens to think that I am whenever I have a table
with a BIGINT column.

Norman Yuan mentions that he has no trouble creating BIGINTs in Access
2007 which was puzzling to me as I had verified this behavior on
several different machines, servers and databases before posting.
More testing revealed that Access 2007 DOES recognize BIGINT when
connected to SQL Server 2000 but DOES NOT when connected to SQL Server
2005. Norman, if you could verify that you were using SQL 2000 that
would be helpful.

- Anthony