Solved

Import Access Data to SQL Server Database in code

Posted on 2013-07-01
14
1,415 Views
Last Modified: 2016-02-11
I'm migrating an application from using a Microsoft Access database to a SQL Server database.  The applications needs, though, to be able to import data formerly saved in the Access database into the new SQL Server database.  I did some searching, but couldn't find the syntax for doing so.  

The "easy" way would be to import the data into a datatable in C# and then save it to the SQL database, but that's slow when importing even 10,000 records.  Some of the tables have hundreds of thousands of records, so I'm looking for a bulk import statement.  

Thanks.
0
Comment
Question by:l2Bravo
[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
14 Comments
 
LVL 13

Expert Comment

by:joomla_php
ID: 39289962
SQL Server has a bulk utility called bcp.

http://msdn.microsoft.com/en-us/library/aa337544.aspx
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39289967
If you need to do any data manipulation along the way have a look into SSIS.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39290001
Have you ruled out Access's built-in upsizing wizard (specifically used for converting entire Access databases to SQL Server)?

http://databases.about.com/od/tutorials/ht/Converting-An-Access-Database-To-SQL-Server.htm
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:l2Bravo
ID: 39290015
Thanks for the suggestions, but I need to do this in code.  That is, I need the application user to be able to select File > Import Results from the user interface, then navigate to the Access database and have the application perform the SQL statement to import data from one or more tables in the Access database to an equivalent SQL Server database.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39290088
As this is a one-time operation, the easy method is to link both the new tables in SQL Server via ODBC to Access and also link/attach the old Access database file and its tables.

Then you can build normal queries in Access to move the data and perform the slight modifications you may wish.

If you don't have the new tables and wish to create these while exporting, like Miriam, I would suggest the upsize wizard in Access. It is not perfect but simple and fast and not so bad as its reputation, so it may be worth checking it out.

/gustav
0
 

Author Comment

by:l2Bravo
ID: 39290140
The problem is that I need for my customers to be able to import data, customers who often no little about computers.  It makes life for me much easier if I don't have to perform the import myself for every customer who may receive the software upgrade to using SQL Server as the database.

The customer must be able to point and click their way to importing the data, which means I must do the work for them from behind the scenes (in code).  If I forced them to use the upsize wizard or open SQL Server, that would defeat my purpose and greatly confuse them.  I , or some of my colleagues, would spend many hours on the phone teaching the customer how to get their old data, which is something I want to avoid.  

Thanks.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39290195
You didn't tell that, but then I would use an empty predefined database for SQL Server which you will have to build once, then supply as a script or part of the install routine for SQL Server and your application.

At the side, I would create a small Access helper app which the client can run for the migration. This will link to and check the database on SQL Server, attach the tables from the old Access file, and then - at a push of a button or so - run a series of queries that copy and modify (as needed) the data to the SQL Server, and finally run a check of the copied data.

Now the client can launch you new version of the application.
If anything goes wrong, nothing is neither disturbed nor lost.

/gustav
0
 

Author Comment

by:l2Bravo
ID: 39290690
Thanks again, but what I'm particularly looking for is SQL Query Syntax, which is one of my categories.  For example, with Access, I can import from one database to another using:

INSERT INTO Table1 SELECT ....FROM[; Database = filename; PWD= password ].Table as table2 ... LEFT JOIN Table1. WHERE ...


This syntax does not work for importing data into a SQL Server database.  Specifically, the ;Database = part of the Query is not valid for SQL Server.  Do you know what would work?

Thanks.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39290711
I wouldn't use that syntax except for temporary or ad-hoc use.

It is much easier to link the tables in advance using ODBC or ADO. Then you have a firm starting point, and you can create and debug the queries much easier.

/gustav
0
 

Author Comment

by:l2Bravo
ID: 39294124
I'm basically just looking for the correct SQL syntax, which is one of the categories for my question.   I'm using INSERT INTO SELECT FROM OPENDATASOURCE('Microsoft Jet .Oledb 4.0', database)...table

The error message I get is:

Cannot initialize the datasource object of Microsoft.Jet.Oledb.4.0 for linked server "(null)".
Microsoft.Jet.Oledb.4.0 returned message "Could not find installable ISAM" .  

I want to make this query work.  Is there something I'm missing?  The null linked server issue sounds like the probably, but I need also to set up the linked server by code, if that is what's missing.  

Thanks.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39294322
I have manually set up linked Access databases for an SQL Server but it was not easy; the server needs (of course) to have file access to the mdb/accdb file, and - worse - when using the views created for the purpose, SQL Server locked the Access files.
And that was in an environment controlled by me and not in unspecified environments of the clients.

Unless if you simply enjoy fighting such troubles, I would put the method low on the list of options.

/gustav
0
 

Author Comment

by:l2Bravo
ID: 39294479
OK, I understand.  I may have to get involved with every upgrade to the new software.  If you can explain how to do the following, I'll accept the solution:

"link both the new tables in SQL Server via ODBC to Access and also link/attach the old Access database file and its tables"

Thanks.
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39301388
There are several methods. In the ODBC manager you can create a connection to the server, or you can use a DSN-less connection (Google on that) where you create the connection in your code. I prefer a file-DSN which creates a file you can open in Notepad and study and easily adjust if needed.

So, in Access, go to External Data and pick ODBC. This will open the ODBC manager. Follow the wizard to create, say, a file DSN. As driver I often select "SQL Server". Fill in the info needed to reach and connect to the server.
When done, you can open the connection and attach tables or views as linked tables in Access.

For the Access file, go to External Data and pick Access. It will ask for the path to your database file.
When done, you can open the connection and attach tables as linked tables in Access.

If you later wish to adjust the links, go to External Data and pick Manage Linked Tables.

/gustav
0
 

Author Closing Comment

by:l2Bravo
ID: 39327069
I decided to use code to import the data into a DataTable and then save each imported row.  I was worried about speed with this method, but it seems fast enough and will give my customers greater flexibility.  

Thanks, though, for the suggestions.  I have granted the points even though I did not use the solution.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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.

630 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