Bulk export from SQL Server Express to MS Access (disconnected DataSet ?)

Posted on 2008-10-21
Last Modified: 2008-11-02
In a Windows Forms C# application I need to save programmatically a bunch of tables (selected fields) from SQL Server to Access.  With a full version SQL  I'd use a DTS (2000) or SS Integration but Express Edition lacks export functionality.

I'd like to save data to the DataTable (no problem) and then import it to Access. I am not sure how to set up a DataAdapter for Access to use a table already filled with SQL adapter.

I can create 2 DataTables: one really filled from SQL and another filled from Access and emptied, then transfer data from the 1st to 2nd and Update Access adapter, but this seems like a clumsy solution.

Advice is much appreciated.
Question by:yurysta
  • 3
  • 2

Expert Comment

ID: 22769359
I am not a Microsoft developer, but for my own learning purposes I would like to ask about your problem.

Are you trying to run the application from within a Microsoft Access environment?

Could you link the Access database table to the SQL Server table?  Or does the Access database need its own copy of the data so that the SQL Server table is not modified by the Access user?

Again, I am asking to possibly help, but mostly for my learning ability and perhaps to clarify your question.

Thank you!

Author Comment

ID: 22773990
thanks for your interest. It's a commercial .NET Windows Form application in C# for a range of clients. I started it with Access as a backend (on request of a primary client) but then switched to SQL Server because it's difficult to implement complex logic without Stored Procedures. I am also using SQL Reporting. SQL Express is free, its only major problem is lack of assisted import/export and scheduled jobs.
At certain moments (after some user inputs) the program is exporting data from SQL to Access mdb  and then FTPs an mdb to a Web Server to feed a web site.

There is no much difficulty with programmatic export to Access if one is happy to read SQL tables record by record and insert them into Access tables. This is not a very scalable or elegant solution but it works OK for my current circumstances - less than 1/2 second per whole export task .

I just wondered if somebody could suggest something more elegant in case the database grows dramatically.



Expert Comment

ID: 22778930
This is a very interesting scenario.  I have some other questions to help us and hopefully some knowledgeable experts figure out a possibly more elegant way.

How many tables are in the mdb that is ftpd to the web server?

Of the table or tables in the mdb, are they all created and populated into a new mdb or is the mdb an existing copy/template which is copied and then then new data added?

Or is the mdb an existing one that is updated and then the updated one is ftpd?  That is, there is one mdb maintained by exported data with the most recent copy of the mdb ftpd?

Can the web site communicate via soap or some other method to the sql server and have the data pulled directly from the sql server?

I believe that some real experts will have to jump in as I do not have advanced knowledge in all possible solutions to the Microsoft based scenario here.  However, this is a very interesting scenario in which I am interested to find out how some experts might implement this kind of thing.


Accepted Solution

yurysta earned 0 total points
ID: 22823093
This thread seems to turn into a dialog.
The mdb has about a dozen of tables that are heavily inter-related.
Definitely, the data in the web server database can be updated via a web service or another similar method, eg direct ASP commands, however it's a lot of hassles.

This FTP solution is a triumph of laziness and hastiness. As the data is already co-ordinated in SQL Server - and SQL is not accessible from outside - I decided to export a subset needed for a web site to mdb and push it to web server.
To me this issue is no longer pressing - I've implemented data export through record by record copying - no performance problems.
Let me know if you are interested in examples or details.


Author Comment

ID: 22823150
more specifically re your questions - a master copy of data sits in in SQL server. Most of this data is manipulated locally, on the same box where SQL is running. Access mdb is used only as a messenger - to update web content and bring back new online entries.

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question