Access to MSDE

  • Thread starter Thread starter Brian Scott
  • Start date Start date
B

Brian Scott

Hi,

What is the most effective way to migrate an access DB to MSDE / SQL Server
using only code, C# in this case. I do not have access to the MSDE other
than via code and I was looking for a reliable solution.

Thanks.
 
Does the PC with MSDE have MS Web Data Administrator? It's a freeware tool
(you can download it from MS) that allows you to access SQL Server DBs
including MSDE. All you need is the .NET framework installed on the PC along
with IIS. MSWDA has an import feature that works well as well as just
generally being an all purpose (though lightweight) GUI for SQL Server.
Additionally if you enabled network connections when you installed MSDE, IIS
and MSWDA don't even have to be on the same PC as the MSDE instance you're
trying to access.

This is really more of a hack than a method but it works and it doesn't
involve any C#, however, this will only work for DB tables and queries.
Anything else (forms, reports, modules, etc) really fall outside the database
anyway and involve windows forms or web forms and for these things you'll end
up completely rewriting your code whatever you do.:

------------To Create the DB------------
1) use OSQL or MS Web Data Admin to create your database and assign user
rights

------------To Copy your tables from Access to MSDE------------
1) create a system DSN to your MSDE instance and new database
2) do an export to an ODBC database and then use the DSN you just created to
connect to your MSDE instance.

as alternate to 1 & 2 you could do the following but this method takes
longer and is more error prone. however it does give you a chance to tweak
your settings.
1 alt) export each table from access as a .xls file
2 alt) use Excel to do whatever formatting you need to do on the data and
when you're done save the data as a text file. Then go back in and create a
table defintion at the top of each table's data file.
3 alt) use MS Web Data Admin to import your text file or run it through OSQL

-------To copy your queries over, (i.e. you want to use as stored
procs)-------
1) view each query's SQL in design view and copy the sql to the a text file
2) use a text editor to do any formatting on the SQL that's necessary
3) use MS Web Data Admin to import your text file or run it through OSQL

mike
 
The most effective way is to purchase the Developer edition of SQL
Server to migrate your Access database ($49 US, see
http://www.microsoft.com/sql/howtobuy/development.asp for more info)
and use MSDE for deployment-only. MSDE doesn't have the tools you
need, and you'd have to write an awful lot of C# use-one-time-only
code to adequately create and secure the SQLS database. It isn't just
a matter of migrating the data--you're talking about two completely
different database engines here, with Access being your bicycle with
training wheels, and SQL Server being your Porsche. Bear in mind that
the Developer edition licensing agreement prohibits using it as a
production server or to manage the deployed app.

--Mary
 
Mary,
different database engines here, with Access being your bicycle with
training wheels, and SQL Server being your Porsche.
Click to expand...

Did you not have anything more general productive as sample than a Porsche

A bicycle with training wheels gives mostly at least a better shape.

:-)

Cor
 
Mike,

Thanks for the time you took but the original question was based on
programatically migrating the data instead of manually calling the import
mechanism. The reason it has to be programmatic is that we have over 100+
customers using systems with an access database backend. Since I joined the
company I am now working for I had taken the decision to move these
databases over to MSDE. I cannot therefore have access to each pc I need to
perform the migration on.

I have started writing a generalised table mapping class which takes a
source connection and a destination connection and moves data within
respective transactions. I wondered if this was the best approach at this
point. Also, I had a look at DTS services and these seemed ideal but I would
have to set them up manually which as desribed before is not feasible in
this situation.
 
Access to SQL Server?
You are asking a much wider question than you might think, as this is a
multi-step process. In theory, you can do everything with the Access Upgrade
Wizard. I wish you the best of luck if you go that route, as it pretty much
sucks.

Step 1:
Query the schema of the Access database. I would ask in an Access group on
this one, as I am not sure the best way to get structure from Access. Once
you have the schema, you can set up DDL to build the schema in SQL Server.

Step 2:
Set up the database(s) in SQL Server. You can do this by issuing DDL against
the master database (create database commands) or through SQL-DMO (need to
use interop for this).

Step 3:
Set up database objects (tables, relationships, etc.). Same options as
above, but you must be connected to the user database (created above) before
issuing DDL, or your master will end up corrupt.

Step 4:
Migrate the data. This can be done by creating a DataSet from each source
and then moving the data from the Access DataSet to the SQL DataSet and
issuing an Update Command. You can also set up the data as XML and use the
XML features of SQL to put it in the database. DTS is also an option. It
cannot be programmed with C# directly, but you can use VB COM to program.

One of the easiest options is to programatically pull the Access tables to
flat files and upload using a Bulk Load process: This is not as easy on the
system, but it is quite easy to BCP or BULK_COPY data from flat files and
relatively easy to export from Access via the Access objects. This is not a
great long-term solution, but it appears you are writing a one-time process
for tons of databases, so pretty is not necessary, speed of coding is.



--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside the box!
*************************************************
 
As Mary indicated:

Just install the SQL Server Tools onto your development system you can then
use the "Import and Export Data" tool in the "Microsoft SQL Server" menu to
create a DTS to Create a new SQL database from your Access database. This is
a farily trivial effort. Just select the Access Driver for your source and
slect your MDB file. Then you can use the transformation to make any changes.

There are some bugs in the DTS (fixable by some reg edits) if you have some
extra Long fields, but in general its a piece of cake.

Your next problem is figuring what you'll do with it once it's there...

Bob Hall
 
Back
Top