Scenario
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
A) Create
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.
B) Somehow
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
FYI: A mirror will not work as SQL's mirroring tool is a point in time snapshot of your database and will not be updated.
Answering the following questions will greatly help with a solution.
- Can rows be inserted into both tables?
- What columns can be updated by your real table?
- What columns can be updated by your replicated table?
- How is the bigint column initially populated?
- Can the bigint column be modified?
- Is the bigint column your primary key?
- Do you have a primary or other unique key.
Can rows be inserted into both tables? – Yes – I think what
you mean is can I execute an “Insert Into table Values(…) or “Insert Into table(..) select filelds from
table” from either database table and the answer is yes
The “bridge” table (the one being viewed from Access and
thereby the one being edited by the user) has identical column names as the
table with the bigint fields. These
bigint fields (ridiculous to use) are part of a primary key, but when I created
the same tables in mybridge I changed the bigint to int and removed the
compound primary keys. I tested in a
server side query, in VBA (DAO) and in an Access object query whether this
would work and all three allowed me to update and insert from the bridge to the
“bigint” table in the other database
What columns can be updated by your real table? - if by real you mean the one the user sees –
all fields are updatable – I did create an extra field in my bridge table
called autoid (identity with increment of 1)
because Access cannot update data in a sql table with out a primary key –
this is the only field with no corresponding field in
Table that is to receive the data as we write it into the
bridge.
What columns can be updated by your replicated table?
How is the bigint column initially populated? – initially we
were going to do it through passing values via VBA – using DAO.ADO or
DB.Execute statements and the tests worked.
But trying to view any data from these bigint tables are resulting in #deleted
so we have to use other tables for the user and pass data to the bigint tables
and that is where I am stumbling.
Can the bigint column be modified? Once I write a value to the bigint field I
would never change that value
Is the bigint column your primary key? Part of a primary key
(3 fields comprise it)
Do you have a primary or other unique key. – the following
is the what makes up the primary key -
OrderNo bigint Unchecked
OrderDate smalldatetime Unchecked
ApplicationTenantLinkId bigint Unchecked
These fields are in my “bridge table” called orders but the
bigint fields are INT and I have not put a primary key on those 3
that's what sql server replication is for
-- Here is your solution, just adjust to your tables:
-- Create orderss table in database foo. In your case use your main order table, so you do not need to create the table.
create table foo..orders (id int identity, big bigint, i int) -- Just an examples orders table in a database called foo.
-- Insert a couple of records
insert into foo..orders values (1,1)
insert into foo..orders values (2,1)
-- Do the rest in tempdb (in your case use the database you created for Access)
use tempdb
go
-- Create a view that queries the orders table.
-- Note: We do not use a real table use a view so you do not need to update both tables.
create view orders
as
select id, cast(big as int) big, i
from foo..orders
go
-- Create an instead of trigger on the view that will insert the data in your orders table.
-- This is the magic this will insert into your real table not the view itself
create TRIGGER trg_insert_orders_insteadof
ON orders
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
-- Here is the insert
insert into foo..orders (big,i)
select big, i from INSERTED
END
GO
-- Now insert a record into the view.
insert into orders (big,i) values (3,3)
-- The view and the real orders table now has all the rows of the table
select * from orders
Enjoy,
Mark
I promise I am not slow but please bear with me
I have an ORDERS table in my Access visible and updatable from row view data base.(no "# deleted" )
The name of the database Fulcrum_Xfer
Fieldnames are [OrderNo],[OrderDate],[ApplicationTenantLinkId],[OrderStatus]
All have the data type INT other than OrderDate [smalldatetime]
I have the exact same same fields in ORDERS in my “bigint” databse. That database is called AccutrakDemo8 and row view shows the #deleted so I cannot make that available to the users
Both on the same server
I have created a view on Fulcrum_XFer called
v_Orders
The rest below is where I get stupid LOL Do I just create a new trigger under [database Triggers] – [New]
That freaking template syntax is so confusing for a sql admin wannabee LOL
Any help mark with the walk through and syntax would be so greatly appreciated
***Create an instead of trigger on the view that will insert the data in your orders table.
-- This is the magic this will insert into your real table not the view itself
Everything below is confusing for me –but I detailed above exactly what I am working with.
c
reate TRIGGER trg_insert_orders_insteadof
ON orders
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
-- Here is the insert
insert into foo..orders (big,i)
select big, i from INSERTED
END
GO
-- Now insert a record into the view.
insert into orders (big,i) values (3,3)
I have an ORDERS table in my Access visible
You do not want a table you want a view. Otherwise you need to keep then in sync and that is tricky without implementing replication.
Do I just create a new trigger under [database Triggers] – [New]
Yes, but this should be in a view not a table.
That freaking template syntax is so confusing for a sql admin wannabee LOL
Do no use the template modify the script I have you.
create TRIGGER trg_insert_orders_insteadof ON orders INSTEAD OF INSERT
You are close here just change "ON orders" to "ON v_orders" so it looks like:
create TRIGGER trg_insert_orders_insteadof ON v_orders INSTEAD OF INSERT
Note: Once you create the trigger, you can modify it by changing the "create trigger" to "alter trigger" if you need to modify the code.
Note: In the trigger there is code that looks like:
insert into foo..orders (big,i)
select big, i from INSERTED
The new code should look like:
insert into Fulcrum_Xfer..orders (OrderNo, OrderDate, ApplicationTenantLinkId, OrderStatus)
select OrderNo, OrderDate, ApplicationTenantLinkId, OrderStatus from INSERTED;
RBarryYoung gave the best answer from StackOverflow – awesome
USE [Fulcrum_Xfer]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo.trOrders_Insert
ON dbo.Orders
AFTER INSERT AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Just INSERT everything from the [inserted] pseudotable into
--the target table
INSERT INTO [Fulcrum UAT].dbo.Orders
(OrderNo, OrderDate, ApplicationTenantLinkId, OrderStatus)
SELECT OrderNo, OrderDate, ApplicationTenantLinkId, OrderStatus
FROM inserted;
END
GO
I used a similar answer from stack overflow
http://stackoverflow.
dotcom/questions/30648748/syntax-for-sql-trigger-to-insert-data-in-another-db-and-also-to-update-any-field
but you were right on target and I appreciate the followup. Mr.berry gave me the answer earlier and I implemented it - I felt so helpless - never did that before - I have always connected to sql and use VB to control what I needed - thank you so much