Solved

Import Access Data to SQL Server Database in code

Posted on 2013-07-01
14
1,347 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 49

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 49

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 49

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 49

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 49

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

830 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