TRIGGER a VIEW with SQL for a LINKED SERVER (ACCESS DB)

Hi,
I have an ACCESS DB that I would like to import to a SQL DB.
I linked the ACCESS DB to my SQL SERVER.
For this, no problem.
After that I create a VIEW from my SQL DATABASE and I can see all RECORDS
from the ACCESS TABLE.

Now , I am looking for updating my Table from SQL DB by all new records
inserted in the ACCESS DB.

I create a TRIGGER from my VIEW. You can see below the source code :




CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT in Log
INSTEAD OF INSERT
AS
BEGIN

My code

END
----------------------------------------------------------------------------
-----------------------------

I can execute my TRIGGER but I cannot see the new records inserted in the
ACCESS DB.

Is anyone can help me about that.

Does the linked table have both a primary key AND ALSO a timestamp field
exposed?

I would make sure of the above, and then delete/re-crease the link in
ms-access for the problem table. Likely when you created this table you did
not select/set a primary key.

Also, are you using a native sql project in ms-access (ADP project), or
odbc?


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

Dunno. Will SQL Server trigger on events on foreign tables?
I don't think there is anyone here who has ever tried it.

(david)

"youssef" wrote in message
...
Hi,
I have an ACCESS DB that I would like to import to a SQL DB.
I linked the ACCESS DB to my SQL SERVER.
For this, no problem.
After that I create a VIEW from my SQL DATABASE and I can see all RECORDS
from the ACCESS TABLE.

Now , I am looking for updating my Table from SQL DB by all new records
inserted in the ACCESS DB.

I create a TRIGGER from my VIEW. You can see below the source code :




CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT in Log
INSTEAD OF INSERT
AS
BEGIN

My code

END
--------------------------------------------------------------------------
--
-----------------------------

I can execute my TRIGGER but I cannot see the new records inserted in the
ACCESS DB.

Is anyone can help me about that.

Hi,

I selected the primary key.

I find in a forum a solution but I don't understand how it is working.

Please find the link :

With Oracle :
http://www.experts-exchange.com/Data...8.html#9102669

With SQL :
http://www.experts-exchange.com/Data..._20823443.html

Can you help me about that ?



Best Regards



"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
Dunno. Will SQL Server trigger on events on foreign tables?
I don't think there is anyone here who has ever tried it.

(david)

"youssef" wrote in message
...
Hi,
I have an ACCESS DB that I would like to import to a SQL DB.
I linked the ACCESS DB to my SQL SERVER.
For this, no problem.
After that I create a VIEW from my SQL DATABASE and I can see all
RECORDS
from the ACCESS TABLE.

Now , I am looking for updating my Table from SQL DB by all new records
inserted in the ACCESS DB.

I create a TRIGGER from my VIEW. You can see below the source code :




CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT in Log
INSTEAD OF INSERT
AS
BEGIN

My code

END

--------------------------------------------------------------------------
--
-----------------------------

I can execute my TRIGGER but I cannot see the new records inserted in
the
ACCESS DB.

Is anyone can help me about that.

Hi,

I selected the primary key.

I find in a forum a solution but I don't understand how it is working.

Please find the link :

With Oracle :
http://www.experts-exchange.com/Data...8.html#9102669

With SQL :
http://www.experts-exchange.com/Data..._20823443.html

Can you help me about that ?



Best Regards



"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
Dunno. Will SQL Server trigger on events on foreign tables?
I don't think there is anyone here who has ever tried it.

(david)

"youssef" wrote in message
...
Hi,
I have an ACCESS DB that I would like to import to a SQL DB.
I linked the ACCESS DB to my SQL SERVER.
For this, no problem.
After that I create a VIEW from my SQL DATABASE and I can see all
RECORDS
from the ACCESS TABLE.

Now , I am looking for updating my Table from SQL DB by all new records
inserted in the ACCESS DB.

I create a TRIGGER from my VIEW. You can see below the source code :




CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT in Log
INSTEAD OF INSERT
AS
BEGIN

My code

END

--------------------------------------------------------------------------
--
-----------------------------

I can execute my TRIGGER but I cannot see the new records inserted in
the
ACCESS DB.

Is anyone can help me about that.

With Oracle:
He has an Oracle table.

With SQL:
He has an SQL Server table.

Do you have a SQL Server table? What is the table name? What is the name
of the database?

Does the trigger fire when you do an insert? Does it insert a new record in
the second table? Where is the second table? What is the table name? What is
the name of the database?

(david)


"youssef" wrote in message
...
Hi,

I selected the primary key.

I find in a forum a solution but I don't understand how it is working.

Please find the link :

With Oracle :

http://www.experts-exchange.com/Data...8.html#9102669

With SQL :
http://www.experts-exchange.com/Data..._20823443.html

Can you help me about that ?



Best Regards



"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
Dunno. Will SQL Server trigger on events on foreign tables?
I don't think there is anyone here who has ever tried it.

(david)

"youssef" wrote in message
...
Hi,
I have an ACCESS DB that I would like to import to a SQL DB.
I linked the ACCESS DB to my SQL SERVER.
For this, no problem.
After that I create a VIEW from my SQL DATABASE and I can see all
RECORDS
from the ACCESS TABLE.

Now , I am looking for updating my Table from SQL DB by all new
records
inserted in the ACCESS DB.

I create a TRIGGER from my VIEW. You can see below the source code :




CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT in
Log
INSTEAD OF INSERT
AS
BEGIN

My code

END


--------------------------------------------------------------------------
--
-----------------------------

I can execute my TRIGGER but I cannot see the new records inserted in
the
ACCESS DB.

Is anyone can help me about that.

With Oracle:
He has an Oracle table.

With SQL:
He has an SQL Server table.

Do you have a SQL Server table? What is the table name? What is the name
of the database?

Does the trigger fire when you do an insert? Does it insert a new record in
the second table? Where is the second table? What is the table name? What is
the name of the database?

(david)


"youssef" wrote in message
...
Hi,

I selected the primary key.

I find in a forum a solution but I don't understand how it is working.

Please find the link :

With Oracle :

http://www.experts-exchange.com/Data...8.html#9102669

With SQL :
http://www.experts-exchange.com/Data..._20823443.html

Can you help me about that ?



Best Regards



"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
Dunno. Will SQL Server trigger on events on foreign tables?
I don't think there is anyone here who has ever tried it.

(david)

"youssef" wrote in message
...
Hi,
I have an ACCESS DB that I would like to import to a SQL DB.
I linked the ACCESS DB to my SQL SERVER.
For this, no problem.
After that I create a VIEW from my SQL DATABASE and I can see all
RECORDS
from the ACCESS TABLE.

Now , I am looking for updating my Table from SQL DB by all new
records
inserted in the ACCESS DB.

I create a TRIGGER from my VIEW. You can see below the source code :




CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT in
Log
INSTEAD OF INSERT
AS
BEGIN

My code

END


--------------------------------------------------------------------------
--
-----------------------------

I can execute my TRIGGER but I cannot see the new records inserted in
the
ACCESS DB.

Is anyone can help me about that.

Hi,

Thank for your time.

So, I have one Table in the ACCESS DB.
This DB, I linked with SQL SERVER.
At this moment, I can connected to the ACCESS DB from my SQL SERVER (LINKED
SERVER NAME : MTX_BTS_LOG)

Now I create in my SQLSERVER a DB with the same structure TABLE than the
ACCESS DB.
the Name of the Table is : [Router Status]

In this way, I create in my SQL DB, a view (VIEW_MTX_BTS_LOG) that represent
the TABLE of the linked server( ACCESS DATABASE).
The name of the VIEW : VIEW_MTX_BTS_LOG

SELECT * FROM MTX_BTS_LOG...[Router Status]

When I exectute the VIEW, I see all records from the TABLE of my ACCESS DB.

Now, I create a TRIGGER from this VIEW with this code :


CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT in Log
INSTEAD OF INSERT
AS
BEGIN

My code = Insert the new record in my SQL TABLE

END
--------------------------------------------------------------------------

Now, the TRIGGER is created but nothing when a new record is inserted in the
ACCESS DB.

Can you help me ?

Best Regards


"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
With Oracle:
He has an Oracle table.

With SQL:
He has an SQL Server table.

Do you have a SQL Server table? What is the table name? What is the name
of the database?

Does the trigger fire when you do an insert? Does it insert a new record
in
the second table? Where is the second table? What is the table name? What
is
the name of the database?

(david)


"youssef" wrote in message
...
Hi,

I selected the primary key.

I find in a forum a solution but I don't understand how it is working.

Please find the link :

With Oracle :


http://www.experts-exchange.com/Data...8.html#9102669

With SQL :
http://www.experts-exchange.com/Data..._20823443.html

Can you help me about that ?



Best Regards



"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
Dunno. Will SQL Server trigger on events on foreign tables?
I don't think there is anyone here who has ever tried it.

(david)

"youssef" wrote in message
...
Hi,
I have an ACCESS DB that I would like to import to a SQL DB.
I linked the ACCESS DB to my SQL SERVER.
For this, no problem.
After that I create a VIEW from my SQL DATABASE and I can see all
RECORDS
from the ACCESS TABLE.

Now , I am looking for updating my Table from SQL DB by all new
records
inserted in the ACCESS DB.

I create a TRIGGER from my VIEW. You can see below the source code :




CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT in
Log
INSTEAD OF INSERT
AS
BEGIN

My code

END



--------------------------------------------------------------------------
--
-----------------------------

I can execute my TRIGGER but I cannot see the new records inserted
in
the
ACCESS DB.

Is anyone can help me about that.

Hi,

Thank for your time.

So, I have one Table in the ACCESS DB.
This DB, I linked with SQL SERVER.
At this moment, I can connected to the ACCESS DB from my SQL SERVER (LINKED
SERVER NAME : MTX_BTS_LOG)

Now I create in my SQLSERVER a DB with the same structure TABLE than the
ACCESS DB.
the Name of the Table is : [Router Status]

In this way, I create in my SQL DB, a view (VIEW_MTX_BTS_LOG) that represent
the TABLE of the linked server( ACCESS DATABASE).
The name of the VIEW : VIEW_MTX_BTS_LOG

SELECT * FROM MTX_BTS_LOG...[Router Status]

When I exectute the VIEW, I see all records from the TABLE of my ACCESS DB.

Now, I create a TRIGGER from this VIEW with this code :


CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT in Log
INSTEAD OF INSERT
AS
BEGIN

My code = Insert the new record in my SQL TABLE

END
--------------------------------------------------------------------------

Now, the TRIGGER is created but nothing when a new record is inserted in the
ACCESS DB.

Can you help me ?

Best Regards


"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
With Oracle:
He has an Oracle table.

With SQL:
He has an SQL Server table.

Do you have a SQL Server table? What is the table name? What is the name
of the database?

Does the trigger fire when you do an insert? Does it insert a new record
in
the second table? Where is the second table? What is the table name? What
is
the name of the database?

(david)


"youssef" wrote in message
...
Hi,

I selected the primary key.

I find in a forum a solution but I don't understand how it is working.

Please find the link :

With Oracle :


http://www.experts-exchange.com/Data...8.html#9102669

With SQL :
http://www.experts-exchange.com/Data..._20823443.html

Can you help me about that ?



Best Regards



"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
Dunno. Will SQL Server trigger on events on foreign tables?
I don't think there is anyone here who has ever tried it.

(david)

"youssef" wrote in message
...
Hi,
I have an ACCESS DB that I would like to import to a SQL DB.
I linked the ACCESS DB to my SQL SERVER.
For this, no problem.
After that I create a VIEW from my SQL DATABASE and I can see all
RECORDS
from the ACCESS TABLE.

Now , I am looking for updating my Table from SQL DB by all new
records
inserted in the ACCESS DB.

I create a TRIGGER from my VIEW. You can see below the source code :




CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT in
Log
INSTEAD OF INSERT
AS
BEGIN

My code

END



--------------------------------------------------------------------------
--
-----------------------------

I can execute my TRIGGER but I cannot see the new records inserted
in
the
ACCESS DB.

Is anyone can help me about that.

OK, the examples you quoted are for
SQL Server tables
and
Oracle tables

You have an
ACCESS DB Table

I'm sorry, but I don't think you can use SQL Server Triggers
for an ACCESS DB Table.

However, if I am wrong, if you can use a SQL Server Trigger,
then you should try to use an
UPDATE TRIGGER
instead of INSERT TRIGGER.

It is normal to see external database inserts done as a blank insert, with a
real update.

(david)



"youssef" wrote in message
...
Hi,

Thank for your time.

So, I have one Table in the ACCESS DB.
This DB, I linked with SQL SERVER.
At this moment, I can connected to the ACCESS DB from my SQL SERVER
(LINKED
SERVER NAME : MTX_BTS_LOG)

Now I create in my SQLSERVER a DB with the same structure TABLE than the
ACCESS DB.
the Name of the Table is : [Router Status]

In this way, I create in my SQL DB, a view (VIEW_MTX_BTS_LOG) that
represent
the TABLE of the linked server( ACCESS DATABASE).
The name of the VIEW : VIEW_MTX_BTS_LOG

SELECT * FROM MTX_BTS_LOG...[Router Status]

When I exectute the VIEW, I see all records from the TABLE of my ACCESS
DB.

Now, I create a TRIGGER from this VIEW with this code :


CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT in Log
INSTEAD OF INSERT
AS
BEGIN

My code = Insert the new record in my SQL TABLE

END
--------------------------------------------------------------------------

Now, the TRIGGER is created but nothing when a new record is inserted in
the
ACCESS DB.

Can you help me ?

Best Regards


"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
With Oracle:
He has an Oracle table.

With SQL:
He has an SQL Server table.

Do you have a SQL Server table? What is the table name? What is the
name
of the database?

Does the trigger fire when you do an insert? Does it insert a new
record
in
the second table? Where is the second table? What is the table name?
What
is
the name of the database?

(david)


"youssef" wrote in message
...
Hi,

I selected the primary key.

I find in a forum a solution but I don't understand how it is working.

Please find the link :

With Oracle :



http://www.experts-exchange.com/Data...8.html#9102669

With SQL :
http://www.experts-exchange.com/Data..._20823443.html

Can you help me about that ?



Best Regards



"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
Dunno. Will SQL Server trigger on events on foreign tables?
I don't think there is anyone here who has ever tried it.

(david)

"youssef" wrote in message
...
Hi,
I have an ACCESS DB that I would like to import to a SQL DB.
I linked the ACCESS DB to my SQL SERVER.
For this, no problem.
After that I create a VIEW from my SQL DATABASE and I can see all
RECORDS
from the ACCESS TABLE.

Now , I am looking for updating my Table from SQL DB by all new
records
inserted in the ACCESS DB.

I create a TRIGGER from my VIEW. You can see below the source code
:




CREATE TRIGGER TG_INSERT_LogStatus ON [dbo].[VIEW_MTX_BTS_LOG]
---------------------------------------------------------- INSERT
in
Log
INSTEAD OF INSERT
AS
BEGIN

My code

END




--------------------------------------------------------------------------
--
-----------------------------

I can execute my TRIGGER but I cannot see the new records inserted
in
the
ACCESS DB.

Is anyone can help me about that.