1

I need to import data from a MS Access database into a SQl Server 2000 database once a day. Since I need this to be done every day automated is there a c# routine which would be able to do this?

The tables in the MS Access database will be named as the same tables in the SQL Server and it needs to delete the tables in the SQL Server database and then import the new data/tables from MS Access.

Improve this question
4
  • 2
    Sounds like a job for SQL Server Integration Services Commented Feb 22, 2012 at 10:18
  • There is no automatic process that I know of (in C#). are these tables going to be large? It may affect the choice of solution. Commented Feb 22, 2012 at 10:19
  • yes some of them are going to be Commented Feb 22, 2012 at 10:23
  • I could do it so it is triggered by clicking a button instead of being automated Commented Feb 22, 2012 at 10:34

3 Answers 3

Reset to default
1

One option is to create a stored procedure in your sql server database to do it, see here: select from access database file and insert to sql Database

As was mentioned by marc_s you may be able to use SSIS - but there could be quite a bit of learning involved in getting that right.

Another option is to control it from within C# - pulling data from the access DB and then inserting it into the sql server - this is unlikely to be what you want.

If your access db and the sql server instance are guaranteed to be on the same server then I would be inclined to try option 1. If not then it opens up dozens of additional questions ;)

EDIT: you mention it can be done by clicking a button - in this case really you should give the SSIS solution a once over at least and see if it looks like what you want, it is designed for exactly this type of scenario.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Three steps:

1 - Create a LinkedServer in SQLServer like this:
EXEC master.dbo.sp_addlinkedserver @server = 'Customers', @srvproduct = 'CustomersDB', @provider = 'Microsoft.Jet.OLEDB.4.0', @datasrc = 'C:\inetpub\wwwroot\ftp\AccessDB.MDB', @location = '', @provstr = '', @catalog = ''
EXEC master.dbo.sp_serveroption @server = 'Customers', @optname = 'collation compatible', @optvalue = 'false'

2 - Create a stored procedure inside your target sqlserver doing the import work
CREATE PROCEDURE ImportMDBData
as
TRUNCATE TABLE sqlServerCustTable
INSERT INTO sqlServerCustTable (list your columns name here) (SELECT * FROM Customers.dbo.mdbCustTable)

3 - Now you just need to call the stored procedure from your code

Improve this answer

9 Comments

Sorry ive never created a stored procedure, and in the above what do i have to replace with what? my two databases currently are called accessDB and sqlDB..the accessDB is in C:\inetpub\wwwroot\ftp
Also do i need a stored procedure per table then? i have 11 tables
well, you should replace (list your columns name here) with the column names of your sqlserver table. No you don't need a stored procedure for each table, simply repeat the TRUNCATE and SELECT INTO statements changing the name of the relevant table into the same stored procedure
ok and in step one, i dont really understand what it means the two db's are on the same server
OK I think I have made some assumptions here: First you need to use SQLServer Management Studio and connect to your sql server database and issue the commands in step 1 and step 2.
|
0

access.mdb linked servers in sql server any versions, it should be needed Microsoft.ACE.OLEDB.12.0 or Microsoft.Jet.OLEDB.4.0. those drivers are existed two types 32 bit and 64 bit. 64 bit os prefer install 64 bit jet drivers other wise linking not may work.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.