Link to SQL server with Bigint field

[ChrisHiggins]

Does anybody know of a sqlserver ODBC driver that will allow for the Bigint data type, as the normal Microsoft one does not if you are linking tables.

Thanks

Chris

 

[genomon]

Depends what you link to. No problems with bigint & Access, as long as you remember that Access calls bigint a double, int a long, smallint an int, and tinyint a byte...

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[ChrisHiggins]

I am trying to link it to Access. If I import the table it is OK and the ODBC driver converts the data, but if I link to the table when I look at the data all I get is #Deleted in all of the fields.

Chris

 

[tampaneil]

Is the bigint column the identity column? Access seems to freak out over this; something about associating identity with autonumber (type int). If this is the case, tone down bigint to int if you can. Otherwise, change it to the massive uniqueidentifier with newid() and not null in place, and don't forget to set the column to have select only rights for your users.

 

[ChrisHiggins]

Thanks for your help - The bigint is the identity column and as the database is out of my control I am unable to change any of the field definitions. Looks like I am stuck with this one.

Thanks again for your help.

Chris

 

[sparkyrose]

My solution to this problem is kind of a fudge and will only work in certain situations.

I also couldn't alter the SQL database, but could create my own Db on the server. I made a 'dummy' copy of the table with the Bigint PK but made the datatype 'Float'. I find I am able to copy the data directly using a DTS package from the first to the second table and link Access to that one. (The two types are similar enough that no data is lost)

I wasn't able to work out a dynamic link so the data is refreshed nightly (incidentally, if anyone can suggest a way to make the link dynamic I'm all ears!). This only works for me because the data doesn't change often and one day behind is not an issue.

As I say, a fudge without doubt, but the best a novice could come up with!

 

[ChrisHiggins]

Thanks for your ideas - I am sorry to say that this will not work for me as the table is being updated all the time (30000 records a month).