Solved

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

Posted on 2008-10-21
5
760 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
  • 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

896 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now