Using Jet Oledb as linked server and trigger rows from VB6.0
-
Hi Experts, I would like to Synchronize MS-Access database from sql server 2005 database realtime. One way was to alter the code and make a separate connection string for MS-access in VB for inserts. But what modifying in code is not allowed. So I have linked the MS-Access using addlinkedserver and established a connection in that and than I have created a trigger in Sql database so that whenever the table of sql will insert it will get inserted in MSAccess too. Its working from sql server browser very well but not working from vb front. Giving error like 'linked server 'ecapacitor' does not support the required transaction interface. My trigger is this: create trigger updmth on day_trn AFTER insert As begin commit insert into ecapacitor...mth_trn (ldaytrn_id,lgl_id,lacc_id,nentry_id,sdate,snchcltr,damt,nchqno,sparticulars,bdc) select ldaytrn_id,lgl_id,lacc_id,nentry_id,sdate,snchcltr,damt,nchqno,sparticulars,bdc from inserted begin tran end Please let me have a solution for this. An quick reply will be highly appreciable. Thanks & Best regards Jay Khatri
-
Hi Experts, I would like to Synchronize MS-Access database from sql server 2005 database realtime. One way was to alter the code and make a separate connection string for MS-access in VB for inserts. But what modifying in code is not allowed. So I have linked the MS-Access using addlinkedserver and established a connection in that and than I have created a trigger in Sql database so that whenever the table of sql will insert it will get inserted in MSAccess too. Its working from sql server browser very well but not working from vb front. Giving error like 'linked server 'ecapacitor' does not support the required transaction interface. My trigger is this: create trigger updmth on day_trn AFTER insert As begin commit insert into ecapacitor...mth_trn (ldaytrn_id,lgl_id,lacc_id,nentry_id,sdate,snchcltr,damt,nchqno,sparticulars,bdc) select ldaytrn_id,lgl_id,lacc_id,nentry_id,sdate,snchcltr,damt,nchqno,sparticulars,bdc from inserted begin tran end Please let me have a solution for this. An quick reply will be highly appreciable. Thanks & Best regards Jay Khatri
-
What happens when you remove the "commit" and "begin tran" statements?
I are Troll :suss:
If I remove commit and begin trans, it stops working even in sql browser. But not start to work in Vb6.0. My connection string in VB for sql server is "Provider=SQLOLEDB.1;Password=" & CURRENT_Password_sa & " ;Persist Security Info=True;User ID=sa;Initial Catalog=" & CURRENT_DATA & ";Data Source=" & ty My question is if Microsoft Jet oledb insert can be done from sql server why it is not being from vb front end. One more point Is it a problem of begin, commit of transactions. Please reply soon. Thanks & Best Regards Jay Khatri
-
If I remove commit and begin trans, it stops working even in sql browser. But not start to work in Vb6.0. My connection string in VB for sql server is "Provider=SQLOLEDB.1;Password=" & CURRENT_Password_sa & " ;Persist Security Info=True;User ID=sa;Initial Catalog=" & CURRENT_DATA & ";Data Source=" & ty My question is if Microsoft Jet oledb insert can be done from sql server why it is not being from vb front end. One more point Is it a problem of begin, commit of transactions. Please reply soon. Thanks & Best Regards Jay Khatri
JayKhatri wrote:
One more point Is it a problem of begin, commit of transactions.
That was my first guess; the
begin tranandcommitlook like they should be swapped. The "begin" marks everything that gets wrapped in the transaction, the commit says that the transaction can be saved to disk.JayKhatri wrote:
My question is if Microsoft Jet oledb insert can be done from sql server why it is not being from vb front end.
It seems that Access is the main problem here. Microsoft Access doesn't have triggers, that might be the reason why it works from the SQL Browser, and not over the JET-engine. Are you doing the insert in the SQL Server database with a stored procedure? If so, then you could add your
insert-statement for the linked server at the end of that stored procedure.I are Troll :suss:
-
JayKhatri wrote:
One more point Is it a problem of begin, commit of transactions.
That was my first guess; the
begin tranandcommitlook like they should be swapped. The "begin" marks everything that gets wrapped in the transaction, the commit says that the transaction can be saved to disk.JayKhatri wrote:
My question is if Microsoft Jet oledb insert can be done from sql server why it is not being from vb front end.
It seems that Access is the main problem here. Microsoft Access doesn't have triggers, that might be the reason why it works from the SQL Browser, and not over the JET-engine. Are you doing the insert in the SQL Server database with a stored procedure? If so, then you could add your
insert-statement for the linked server at the end of that stored procedure.I are Troll :suss:
Hi I tried all the combinations of commit tran and begin all got failed. Secondly, It is true the access don't have triggers, so do I go for another sql server db instead of ms-access. I think finally I must leave try with MS Jet. Please suggest. Thanks & Best Regards Jay Khatri
-
Hi I tried all the combinations of commit tran and begin all got failed. Secondly, It is true the access don't have triggers, so do I go for another sql server db instead of ms-access. I think finally I must leave try with MS Jet. Please suggest. Thanks & Best Regards Jay Khatri
JayKhatri wrote:
Secondly, It is true the access don't have triggers, so do I go for another sql server db instead of ms-access. I think finally I must leave try with MS Jet.
Microsoft Access doesn't focus on being a good database-server, that's what it's big brother is for. You can convert the Access database to a SQL Server database using the Upsize Wizard[^]. Next, you could use the SQL Express server to host the database, which is available for free (just like Access).
JayKhatri wrote:
I tried all the combinations of commit tran and begin all got failed.
From the trigger, or from the SQL Workbench?
I are Troll :suss:
-
JayKhatri wrote:
Secondly, It is true the access don't have triggers, so do I go for another sql server db instead of ms-access. I think finally I must leave try with MS Jet.
Microsoft Access doesn't focus on being a good database-server, that's what it's big brother is for. You can convert the Access database to a SQL Server database using the Upsize Wizard[^]. Next, you could use the SQL Express server to host the database, which is available for free (just like Access).
JayKhatri wrote:
I tried all the combinations of commit tran and begin all got failed.
From the trigger, or from the SQL Workbench?
I are Troll :suss:
please see this, the last working trigger from sql server. Now vb giving no error but not entering anything in access. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER trigger [updmth] on [dbo].[Day_Trn] AFTER insert As begin declare @tmp table (ldtrn decimal, lgl_id int,lacc_id decimal,nentry_id int,sdate datetime, snchcltr tinyint,damt decimal,nchqno varchar(9),spart varchar(50),bdc tinyint) insert @tmp (ldtrn, lgl_id,lacc_id,nentry_id,sdate, snchcltr,damt,nchqno,spart,bdc) select ldaytrn_id,lgl_id,lacc_id,nentry_id,sdate,snchcltr,damt,nchqno,sparticulars,bdc from inserted commit insert ecapacitor...mth_trn (ldaytrn_id,lgl_id,lacc_id,nentry_id,sdate,snchcltr,damt,nchqno,sparticulars,bdc) select ldtrn, lgl_id,lacc_id,nentry_id,sdate, snchcltr,damt,nchqno,spart,bdc from @tmp begin tran end GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO Best Regards Jay Khatri
-
please see this, the last working trigger from sql server. Now vb giving no error but not entering anything in access. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER trigger [updmth] on [dbo].[Day_Trn] AFTER insert As begin declare @tmp table (ldtrn decimal, lgl_id int,lacc_id decimal,nentry_id int,sdate datetime, snchcltr tinyint,damt decimal,nchqno varchar(9),spart varchar(50),bdc tinyint) insert @tmp (ldtrn, lgl_id,lacc_id,nentry_id,sdate, snchcltr,damt,nchqno,spart,bdc) select ldaytrn_id,lgl_id,lacc_id,nentry_id,sdate,snchcltr,damt,nchqno,sparticulars,bdc from inserted commit insert ecapacitor...mth_trn (ldaytrn_id,lgl_id,lacc_id,nentry_id,sdate,snchcltr,damt,nchqno,sparticulars,bdc) select ldtrn, lgl_id,lacc_id,nentry_id,sdate, snchcltr,damt,nchqno,spart,bdc from @tmp begin tran end GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO Best Regards Jay Khatri
-
and please refer to this http://sqlblogcasts.com/blogs/piotr_rodak/archive/2007/10/29/distributed-transactions-and-triggers.aspx[^]
The example puts the trigger on the SQL Server-table, and this trigger is executed by the server (and writes to the Excellsheet). If you're adding data to the MS Access table using OleDb or ADO, then you'll bypass that trigger and it won't execute.
I are Troll :suss:
-
The example puts the trigger on the SQL Server-table, and this trigger is executed by the server (and writes to the Excellsheet). If you're adding data to the MS Access table using OleDb or ADO, then you'll bypass that trigger and it won't execute.
I are Troll :suss:
Yes this code was for excel sheet, I got an idea for commit that I had written after insert. Other I had ignored in my case. and yes it is bypassing the trigger. Is there still any Ray of Light as I have spoken to my senior who is less interested to select another sql db. Best regards Jay Khatri
-
Yes this code was for excel sheet, I got an idea for commit that I had written after insert. Other I had ignored in my case. and yes it is bypassing the trigger. Is there still any Ray of Light as I have spoken to my senior who is less interested to select another sql db. Best regards Jay Khatri
JayKhatri wrote:
Is there still any Ray of Light as I have spoken to my senior who is less interested to select another sql db.
There's some options; - Find another location to update the Access-table (execute your insert manually, instead of a trigger) - Use a database that has triggers - Reverse the entire idea; you can create a linked table in the Access-database. Link it to an existing SQL Server table, and read the SQL Server data from Access. (From Access, go to the tab "external data", choose "more", choose "ODBC", and link to the SQL table) I don't know which of these three would be most appropriate to you, as that largely depends on what you're trying to accomplish.
I are Troll :suss: