Import Access Data to SQL Server Database in code

SQL Server has a bulk utility called bcp.

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

If you need to do any data manipulation along the way have a look into SSIS.

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

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.

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

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.

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

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.

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

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.

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

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.

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.