Critical Status - Database-Oriented Issues Plus

It seems that everything has a critical status, especially when it comes to databases and software development! Here's a place for listing issues and ideas relating to database and development issues. I'll list problems and ideas mainly related to SQL Server 2005. Post your thoughts, advice or complaints! Later this year will be the 25th anniversary of being in the trenches of the IT industry. I specialize in database applications development. I'd like to share my experiences with everyone and learn of those of others. You never stop learning.

Wednesday, May 20, 2009

ACCESS and BIGINTs

SQL Server introduced the BIGINT datatype in 2005 to keep up with Oracle. This is a cool thing especially for data warehousing. Unfortunately, ACCESS has the Integer and Long Integer datatypes. The Long Integer is equivalent to SQLs INT but no equivalent to SQLs BIGINT. This causes issues with ACCESS. If you link to a SQL table where the primary key is a BIGINT, the linked table will display #DELETED! in each cell. NO ITS NOT A SECURITY ISSUE!!! So if you have customers that need to view SQL data thru Access linked tables, there are work arounds BUT the BIGINTs will need to be changed to INT. Create an interface NOT in Access for your customers to view data if need be MOSS 07 is a quick solution if you're using it, else try VS.NET or JAVA web apps. yes its time consuming but you can't give all users Admin rights and SQL Server to play with!!!

Here's a link to your work arounds:

2 comments:

CriticalStatus said...

OK HAVE A FAIR SOLUTION!!!

CREATE VIEWS OF THOSE TABLES IN SQL AND CAST THE BIGINTs TO INTs. GIVE RIGHTS TO CHANGE DATA IN THE VIEWS AND IT COMMITS THE CHANGES TO THE TABLES. PROBLEM SOLVED!!!

daspeac said...

I have heard about another dbf file repairing tool. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues