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

Posted on 2008-10-21
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

801 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