Access via an ODBC link cannot view Sql table data that uses
a BigInt as a primary key. Access can
update the data, insert data ands delete data to these tables – but viewing (by
design) returns the actual count of the rows but each field is populated with #deleted.
I can view the table data if I use a passtyhrough query but
performing row level and field level edits from there is impossible. Not all people have Studio manager so that is
not an option.
We are moving away from our legacy application and the
development company has already developed the schema using the BigInt so
changing that to a numeric or integer is out of the question. My boss has determined that using a set of
bridge tables with identical tables in a different database where we replace
the bigint with int will work – we can view the data in the “bridged” database
and even update it from select queries and simply by opening the table in view
mode from Access.
Here is the hard part (for me) because I have never done
this. I can write SQL and understand the
security/roles etc, but I have never administered SQL server regarding server
side triggers and stored procedures – I have always used the command object or
passthrough queries to deal with sql stored procedures. I need to either
some trigger behind the tables in my bridge table that will fire a stored
procedure to replicate the data to the bigint database. They are both on the same sever.
create a “mirror” that always replicates from my bridge database tables (5 of
them) to the new database.
Any ideas or pointers would be of great help – my deadline
is Friday LOL