how to automatically transfer the data from access to sql server?

Question

how to automatically transfer the data from access to sql server in every 8 hours using asp & asp.net coding
plz tell me the answer

Answer 1

Here are a few options. I don't want to go into a TON of detail because it would take a long time.

1. In the C# or VB.Net code, create a procedure triggered from a button on the asp.net page which creates a connection to each data source. You can use the SQL connection objects for the SQL database, and you will use the Ole connection objects for the access database. Fill a datatable with the table from access. Loop through the datatable performing an insert command for each record into the SQL database.

2. I don't know for sure if this will work. But you might be able to create a linked server from SQL to the access database. Then you just run a single select .. insert command to transfer the data just like in a different database on the same SQL server. This works with Oracle to SQL and visa versa, but I haven't tried it with Access to SQL.

3. You mentioned that you want it to run every 8 hours. Perhaps it would be better to create a windows service with a timer that fired every 8 hours, rather than a asp.net program which would only be running if someone was connected to the webpage?

4. Create an SSIS / DTS package that will perform the transfer. The asp.net page could trigger the package to run. I'm not super familiar with this.

Answer 2

Just schedule a DTS job through Enterprise Manager in SQL Server. No need to code nothing.