Office 2007 Enterprise Key Handling the SQL Server

Today’s guest blogger is Vinny Malanga. Vinny is the CTO of IMI Software, Inc., which specializes in software development for the property management industry.

I recently received a call from a client asking for help on how to use the SQL Server bigint data type in a disconnected Access .accdb file. Here’s the scenario:

There’s a SQL Server table that is using the bigint data type as an identity column. In this scenario, the Access database is completely disconnected from the SQL Server 2008 database. There is no use of ODBC or linked tables. All data access is handled using ADO.

As explained in SQL Server 2008 Books Online the range of the bigint data type is -2^63 (-9,Microsoft Office 2010 Key,223,Windows 7 64 Bit,372,036,854,775,808) to 2^63-1 (9,Office 2007 Enterprise Key,223,372,036,854,775,807). The problem is that there is no equivalent Access data type to the SQL Server bigint data type. But, you are not out of luck. There are a couple of ways that you can handle this. Both options are explained below.

The first option is to have all stored procedures or views which return the bigint value to Access explicitly cast the bigint value to a varchar. Access can then utilize the data as a string value. When sending the value back to SQL Server (such as when updating or deleting a record), the stored procedure simply casts the string back to a bigint so it can be used appropriately in SQL Server.

The second option is to utilize the Variant data type in Access. I know what you may be thinking at this point. At first glance, many of you may not like the sound of this, and as a general rule, I tend to agree, but it is worth exploring this option. By taking this approach, there is no casting in SQL Server necessary. The Variant data type can hold the bigint value that SQL Server sends to Access because the Decimal data type is a subtype of the Variant data type. You can’t explicitly declare a Decimal data type in Access but the Variant data type does store the value as a decimal. You can see this by running the VarType(VarName) function. The result is 14 which is the VB constant for vbDecimal. You could also run the TypeName(VarName) function which returns Decimal. I’ve included these methods in the sample access database in the cmdGetName_Click() event. Just uncomment them and the result will print in the debug window.

Another small caveat to using a variant is if you are going to display the value in a textbox. The large bigint values will display in scientific notation. For example, if you are displaying the bigint value 9223372036854775807, an Access textbox will display this as 9.22337203685478E+18. However, the actual value is stored appropriately so this isn’t an issue if the field is hidden. For display purposes though, you could cast the value to a string using CStr(Expression) when binding to the textbox (again, a little more overhead), or simply display the value in a label control.

As a general rule, using variants can degrade performance. Since the data type isn’t explicitly declared, VBA has the additional overhead of maintaining which data type it is actually storing. Also, variants are large, 16 bytes so they have a higher memory footprint. However, variants are there for a reason and sometimes they do have their purpose. This could be one of them. I’m not totally opposed to this method,Office Home And Stude/nt, but personally I try to avoid using the Variant data type when another option is available. So, personally, I tend to gravitate toward the first option.

I’ve supplied some SQL scripts and a sample Access database applying both methods There are also some examples in the sample database on the use of classes, disconnected ADO Recordsets, and general object oriented programming principles.

Here are the scripts to create the objects in a SQL Server database.

-- 1 Create a table:-- I have set the identity to begin at the largest bigint value-- for demonstration purposes.CREATE TABLE [dbo].[tblName]( [RecordID] [bigint] IDENTITY(9223372036854775807,-1) NOT NULL, [Name] [varchar](50) NULL, CONSTRAINT [PK_tblName] PRIMARY KEY CLUSTERED ( [RecordID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO -- Create four Stored Procedures.CREATE PROCEDURE [dbo].[procGetNameUsingString] ASBEGIN SET NOCOUNT ON; -- bigint range is -9223372036854775808 to 9223372036854775807 -- Cast RecordID to varchar(19) when sending to Access -- 19 is the number of characters needed to hold the largest -- length bigint value. Use 20 if you're going to use negative -- numbers to account for the negative sign. SELECT CAST(RecordID AS varchar(19)) AS RecordID, Name FROM tblNameENDGO CREATE PROCEDURE [dbo].[procGetNameUsingVariant] ASBEGIN SET NOCOUNT ON; SELECT RecordID, Name FROM tblNameENDGO CREATE PROCEDURE [dbo].[procUpdateNameUsingString] ( @RecordID varchar(19), @Name varchar(50), @ReturnValue bit OUTPUT )ASBEGIN SET NOCOUNT ON; -- Declare a variable to hold the bigint value -- and assign it the result of casting the incoming -- string representation of RecordID back to a bigint value. DECLARE @RecordIDBigInt bigint SET @RecordIDBigInt = CAST(@RecordID AS bigint) -- Update the record using the bigint value. UPDATE tblName SET Name = @Name WHERE RecordID = @RecordIDBigInt SET @ReturnValue = @@ROWCOUNTENDGO CREATE PROCEDURE [dbo].[procUpdateNameUsingVariant] ( @RecordID bigint, @Name varchar(50), @ReturnValue bit OUTPUT )ASBEGIN SET NOCOUNT ON; UPDATE tblName SET Name = @Name WHERE RecordID = @RecordID SET @ReturnValue = @@ROWCOUNTENDGO -- Create a view (example if using views-- rather than stored procedures).CREATE VIEW [dbo].[vwGetNameUsingString]ASSELECT RecordID,Windows 7 Ultimate Product Key, CAST(Name AS varchar(19)) AS Expr1FROM dbo.tblNameGO
Once you’ve run the scripts, your SQL database will contain one new table, four new stored procedures, and one new view. You’ll want to add a record to the newly-created table to make use of the form in the sample Access database.

You can download the sample Access database from here. You should change the connection string in the Connect subroutine of the clsDataAccess module to suit your environment. Note that the code requires a reference to the Microsoft ActiveX Data Objects 6.0 Library. The sample database already contains the reference; this is something to keep in mind if you implement the class in your database.

<div