CODEWITHSUNDEEP
sqlms-accessbiginteger
Mr Deans
Mr Deans

Reputation: 369

MS - Access BigInt SQL SERVER Issues

I have an access database that we use for simple reporting solutions, this pulls data from a remote data base through an ODBC link. The data-warehouse provider has recently added a new data field to all of their tables which is formatted as a 'BIGINT'

Access now shows all records as deleted as it cannot deal with the BIGINT linked table.

As the data warehouse will not change their tables is there anyway that I can get the MS-Access to display correctly and ignore the 'BIGINT' field in the table linking?

I am having to work around this at this moment in time by copying the entire data warehouse minus this column to a MYSQL DB daily which is far from ideal...

I cannot for the life of me work this out.

Upvotes: 1

Views: 5030

Answers (4)

iDevlop
iDevlop

Reputation: 25262

Instead of using a linked table, just write a passthru query in Access. Eventually CONVERT your BigInt into a string or Integer, depending on the contents.

Upvotes: 2

Jinlye
Jinlye

Reputation: 2234

This is an old thread but:

Casting your bigint as an int as someone has suggested isn't going to work if any of your values in your bigint column are bigger than the maximum value for an int (and if none of them are bigger than the max value for an int, it makes you wonder why a bigint is being used in the first place).

MS Access (from Access 2000 onward) does have a decimal data type, which is good for numbers of up to the maximum size of a SQL Server bigint and more. So if you make your MS Access field to be of type decimal, it can handle anything a SQL Server bigint can throw at it. In your process of taking the data from the SQL Server database into your MS Access database you would need something done programatically along the way to slurp your bigint values from SQL Server and squirt them into MS Access as decimal

Upvotes: 0

user1091524
user1091524

Reputation: 865

This is an old thread, but you can create a view and cast the bigint as int, and then Access will link to it.

Greg

Upvotes: 0

Nick.Mc
Nick.Mc

Reputation: 19184

This link suggests loading the data into a local table with a data type of string:

http://social.msdn.microsoft.com/Forums/office/en-US/fb6f99ec-2ed7-487b-ba39-0777a0b44d5f/the-bigint-problem?forum=accessdev

Perhaps consider that MS Access's usefulness is limited here and it may pay to use SQL Server in future as you will continue to run into these kinds of problems. Is there any reason you can't use the datawarehouse directly?

You may also wish to consider using an .ADP (a file type of MS Access) which has a native OLE DB connection to the SQL Server database (no ODBC flimflammery) but also all the usual forms and reports.

ADP's are deprecated but I have had great success with them.

Upvotes: 1

Related Questions