SQL Server 2005 bigint missing?

Question

I am trying to set a column as a 64-bit integer, but my only available options are tinyint, smallint and int. Where did bigint run off to?

Notes: I'm using Access 2008 to access my SQL Server.

Answer 1

The problem is not with SQL Server. In SQL Server 2005, you can declare a column as bigint like so:

Create Table Table( Col1 bigint )

The problem is that MS Access does not recognize bigint as a data type.

Here's an article on the problem which discusses some workarounds when using ADO. Here is help article from Microsoft which states that Access still does not recognize bigint (see the section on Comparison or mapping of data types between an Access database and Access project).

Answer 2

Access 2007 does not have a bigint data type, which is why you're not seeing it as an available option.

If you need an integer that large, you can store it in a CURRENCY type (MONEY in SQL Server), and multiply or divide by 10000 to perform the conversion.

myCurrency = myBigInt / 10000

myBigInt = myCurrency * 10000

http://sqlserver2000.databases.aspfaq.com/what-are-the-main-differences-between-access-and-sql-server.html

Answer 3

Here's a table comparing Access datatypes with their ODBC equivalents. No bigint there.

And here's an article called Handling the SQL Server bigint data type in Access 2007 using ADO which offers two approaches to handling this: convert the bigints to varchars or variants. Sure, those make you queasy, but they sound like they might handle your problem.