Solved

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

Posted on 2008-10-21
5
752 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

11 Experts available now in Live!

Get 1:1 Help Now