I recently worked on a project where our client is selling a product throughout the USA built on Access with SQL Server, it works by importing data into SQL and then analyzing it with Access reports. Unfortunately each customer may have a primary key format as a long integer and others where it’s a string, (combination of numbers and strings), we quickly discovered it caused problems with reports not sorting correctly. The reports were designed with integers in mind and the strings messed up the order. We needed to come up with a solution that did not require adding code to our Access frontend to handle the problem, but rather use the power of SQL Server to serve up the data regardless of the format used on the primary key.
Views to the rescue
One neat thing about linking SQL Server views and tables in Access: You can give them any name you wish. I leveraged it by out the source of the linked table with a view if the primary key is a string or leave it as the original table if it was an integer. Having the same table names in Access regardless of the source meant not having to change my code.
We drive linking using a SQL Server table
Juan has blogged about driving table linking from SQL Server, where we use code to read records in a table called tblTablePermissions to create the links in our Access frontend using VBA code. Usually we link only at the beginning of the project or when a new table is added to the server, but for this project I needed to take it a step further by swapping out the table name with the view name in tblTablePermissions if the client uses a string for primary key.
There are two fields in tblTablePermissions, one called Table_Name and another called AccessName, our code uses the former to know which table or view to use as the source and the latter for the table name in Access. All I did was create a simple script my customer can run to swap out the table names with view names for each install that needed it.
Here’s the script I used:
--Update tblTablePermissions For Numeric Type Code--
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
DELETE FROM [dbo].[tblTablePermissions]
WHERE [Table_Name] = ('tablename1')
or [Table_Name] = ('tablename2')
or [Table_Name] = ('tablename3')
IINSERT INTO [dbo].[tblTablePermissions] ([Table_Name], [Access_Name], [IndexedColumns], [UserSelect], [UserInsert], [UserUpdate], [UserDelete])
VALUES ('vw_tablename1', 'tablename1', '', 'True', 'True', 'True', 'True'),
('vw_tablename2', 'tablename2', 'field1,field2,field3', 'True', 'True', 'True', 'True'),
('vw_tablename3', 'tablename3', 'field1,field2', 'True', 'True', 'True', 'True')
In my script above I first delete the tables from tblTablePermissions that I wish to swap with views and then I use an insert statement to add the views that will replace the tables, notice the view names are different but the Access_Name field are the same as the ones deleted, allowing my Access code to work regardless of which primary key field type is used.
How I used CAST to translate the primary key
In the views I used the CAST function to convert the field Type from a string into an integer:
CREATE VIEW vw_tablename1
CAST(Type AS int) AS Type,
Our client can now easily configure each installation based on the customer’s data!