Solved

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

Posted on 2008-10-21
5
778 Views
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.
0
Comment
Question by:yurysta
[X]
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
5 Comments
 
LVL 3

Expert Comment

by:richard_crist
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!
0
 

Author Comment

by:yurysta
ID: 22773990
Richard,
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.

cheers

0
 
LVL 3

Expert Comment

by:richard_crist
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.

0
 

Accepted Solution

by:
yurysta earned 0 total points
ID: 22823093
Richard,
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.

Cheers
YS
0
 

Author Comment

by:yurysta
ID: 22823150
Richard,
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

763 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