Solved

Import Access Data to SQL Server Database in code

Posted on 2013-07-01
14
1,297 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
Comment Utility
SQL Server has a bulk utility called bcp.

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

Expert Comment

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

Expert Comment

by:mbizup
Comment Utility
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
 

Author Comment

by:l2Bravo
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:l2Bravo
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now