ACCESS DB to SQL
-
Hi, I have an ACCESS DB populate by an automation. I am looking for reading this database and populate it in a sql database. For the first time, when I reading the ACCESS DATABASE, I using DTS WIZARD in enterprise Manager for create the same ACCES DB in an SQL DATASE.(I would like to use a STORPROC for this). After I would like to use TRIGGER or SQL AGENT, ... for detecting that new records were inserted in the ACCESS DB. And populate new records to the appropriated TABLE in SQL DB. If you have any suggestions for my question it is great. youssef
-
Hi, I have an ACCESS DB populate by an automation. I am looking for reading this database and populate it in a sql database. For the first time, when I reading the ACCESS DATABASE, I using DTS WIZARD in enterprise Manager for create the same ACCES DB in an SQL DATASE.(I would like to use a STORPROC for this). After I would like to use TRIGGER or SQL AGENT, ... for detecting that new records were inserted in the ACCESS DB. And populate new records to the appropriated TABLE in SQL DB. If you have any suggestions for my question it is great. youssef
I'm not sure I quite understand what you are trying to achieve. Why are you continuing to have an Access database? Why not migrate it all to SQL Server and leave it in there? If you need to access the data in Access then why not link the tables back from SQL Server?
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar The Second EuroCPian Event will be in Brussels on the 4th of September Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way! My Blog
-
I'm not sure I quite understand what you are trying to achieve. Why are you continuing to have an Access database? Why not migrate it all to SQL Server and leave it in there? If you need to access the data in Access then why not link the tables back from SQL Server?
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar The Second EuroCPian Event will be in Brussels on the 4th of September Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way! My Blog
Thanks for your answer. I can't change the DB because it isn't my Automation and the customer wont migrate to SQL Server (probably for license). So, I used Linked servers in Entreprise Manager. But How can I do for reading each table with a storproc and how can I do for create a Linked servers by storproc because the filename of the log change each day ? youssef
-
Thanks for your answer. I can't change the DB because it isn't my Automation and the customer wont migrate to SQL Server (probably for license). So, I used Linked servers in Entreprise Manager. But How can I do for reading each table with a storproc and how can I do for create a Linked servers by storproc because the filename of the log change each day ? youssef
youssef wrote: the customer wont migrate to SQL Server (probably for license) cough, MSDE, cough cough.... "Now I guess I'll sit back and watch people misinterpret what I just said......" Christian Graus At The Soapbox
-
Thanks for your answer. I can't change the DB because it isn't my Automation and the customer wont migrate to SQL Server (probably for license). So, I used Linked servers in Entreprise Manager. But How can I do for reading each table with a storproc and how can I do for create a Linked servers by storproc because the filename of the log change each day ? youssef
-
Hi, I have an ACCESS DB populate by an automation. I am looking for reading this database and populate it in a sql database. For the first time, when I reading the ACCESS DATABASE, I using DTS WIZARD in enterprise Manager for create the same ACCES DB in an SQL DATASE.(I would like to use a STORPROC for this). After I would like to use TRIGGER or SQL AGENT, ... for detecting that new records were inserted in the ACCESS DB. And populate new records to the appropriated TABLE in SQL DB. If you have any suggestions for my question it is great. youssef
Hi, I have an ACCESS DB (.mdb) generate by an Automation. Now, I would like to: 1/ Copy all datas from the ACCESS DB in my SQL Database. I using DTS. All are working. But, after copying, I would like at each time a new record is inserted in the ACCESS DB, I copy this Record in my SQL DATABASE (synchronize the SQL DATABASE). For this, I create LINKED SERVERS for the ACCESS DB (Named LOG). In my SQL DATABASE, I create a VIEW that read one TABLE from LOG (linked ACCESS DB). Now, I would like to create a TRIGGER from this VIEW for retrieve each INSERT and copy this insert in my SQL DATABASE. Please find the CODE below : CREATE TRIGGER trgLog on [dbo].[VIEW_LOG] INSTEAD OF INSERT AS BEGIN declare @Log_Date datetime, @Log_Input smallint, @Log_Level smallint, @Log_Milliseconds smallint, @Log_Output smallint, @Log_Panel smallint, @Log_Response smallint, @Log_Status smallint set @Log_Date ='05/07/2004 00:00:00' set @Log_Input =1 set @Log_Level =1 set @Log_Milliseconds =1 set @Log_Output =1 set @Log_Panel =1 set @Log_Response =1 set @Log_Status =1 exec InsertLog @Log_Date,@Log_Input, @Log_Level,@Log_Milliseconds, @Log_Output,@Log_Panel, @Log_Response,@Log_Status END So in this code I Insert a test message after hat a select "insert". But when I refresh my SQL DATABASE, I see nothing whereas I the ACCESS DATABASE the AUTOMATION insert record. Can You Help me about that ? Best Regards youssef