Best way to move data from Access backend to SQL Server

S

SF

I have an exisiting Access Database (FE and BE). I am planning tomove to SQL
Server back end and I have imported the existing Access table structure into
SQL Server, rewrite Views and made some modification to forms. I will needs
some recommendation on the following:

1 - Whether to stick on Access front end as MDB (thru ODBC) or to switch to
ADP
2 - How to move quickly the existing data in Access table to SQL Server
table.

I am using Access 2003 and SQL Server 2005.

SF
 
A

Albert D. Kallal

I have an exisiting Access Database (FE and BE). I am planning tomove to
SQL Server back end and I have imported the existing Access table structure
into SQL Server

Interesting that you moved the table structures, but not the data? A very
minor point, just for the most part when you use some of the import tools
and you've managed to get the table structures over, usually at that point
you'll likely also did transfer the data also (I guess my point is you see
that both of these tasks and processes are usually intertwined).
, rewrite Views and made some modification to forms. I will needs some
recommendation on the following:

1 - Whether to stick on Access front end as MDB (thru ODBC) or to switch
to ADP

If you're a writing something 100% from scratch, and you don't have a lot of
experience with MS access, and your goal is to use SQL server for your back
end from day one, then I would consider a ADP.

However in your case, it's not even close to it even thinking of rewriting
everything and convering to a ADP....way..way too much work. This would be a
tremendous amount of work, and for little gain. furthermore there are some
restrictions on what version of the particular SQL server you use (in your
case I believe you would have to place SQL server 2005 in a previous sql
server compatibility mode to use it with ADP in access 2003 -- in a way this
compatibility issue and potential problem is another good reason as to why
to avoid an ADP in your situation)

I would strongly suggest you simply keep your front end, and now a link your
tables instead of to the mdb back end, simply link them to your SQL server
via odbc. The end result will be probably 99% or more of your forms and code
will work as "is" and not have to be modified at all.

About the only additional thing to watch for is that ensure that **all** of
your tables on the SQL server side have a unique primary key (usually an
auto number). The other critical concept and one that really helps MS access
work well is to ensure that all tables have a time stamp field as part of
the structure. This is **especially** the case for forms that are bound to
tables/queries that edit data (so, forms and sub forms that edit data need
both a primary key and the time stamp field exposed to those forms).
Obviously for things like reports and other things the time stamp and
primary key is not a whole lot of importance.
2 - How to move quickly the existing data in Access table to SQL Server
table.

I am using Access 2003 and SQL Server 2005.

For the most part you just use the SQL server management tools and use the
import options. I always used the enterprise tools, and have not yet used
the SQL server 2005 management tools (but I assume they have a similar set
of tools you can use to import data. As mentioned, usually the idea is to
import all the tables from the back end database. This gives you the table
strucites + you get your data moved also. as mentioned a few do this to your
tables will need some tweaking to ensure that all tables have the primary
key, and for any tables that you edit directly via forms, you want to add
and expose a time stamp field...
 
A

Albert D. Kallal

In the previous version of sql server, we used what was called DTS (data
transformation system) that came with the enterprise manager.

In the sql server 2005, that been replaced with SSIS (sql system integration
services).

You start with visual studio 2005, and simply create a new project (choose
integration services project).

From that point on, you can actually setup your import by dragging graphical
icons on the project workspace to create your "from" and "to" data sources.
I never done this before, but having *just* installed a copy of sql 2005, I
decided to give this try. It was *really* fun!! Darn cool to setup the
import process by simply dragging and dropping task icons (use oleDB
datasouces for the from/to). Talk about a VERY cool way to import data.

Note that you do have to do this on a table by table bases..I don't know how
yet to push up all of the data in one shot using the sql 2005 tools.

As a side note, sql 2005 (and visual studio 2005) is new to me between the
time I first answered your post. However, I just installed sql 2005 as we
speak, so I am likely greener then you in this regards.

However, you *can* bulk transfer (upsize) upside the data to sol (2000, or
2005) using the menu in access 2003 (just open the back end directly), and
go:

tools->database utility-upsizing wizard

The above gives you options to transfer all data, and NOT touch your back
end. If you wish you can choose to have table links created to sql server
(or you can do nothing). If you make a copy of your back end, you could
choose create links. You then delete all of your front end links to the old
back end mdb, and then import (copy from) the upsized back end only the new
table links that now point to sql server (and the links should have the same
name as what you used/had in your front end). that about the least amount
of work.

I suppose it much depends on how you originally transfer the tables
structures to sql server. As mentioned, the "easy" way is to use access
upsize wizard (it even gives you a chance to add a timestamp field to ALL
tables during upsizing, and I recommend that).

I suppose it kind of depends on how now you transferred the table
structures, but not the data. As mentioned, do this upload from the backend
in ms-access (actually, use a copy). You then can get access 2003 to create
all of the linked tables to sql server also in the same data upload shot....
 
A

Armen Stein

The other critical concept and one that really helps MS access
work well is to ensure that all tables have a time stamp field as part of
the structure. This is **especially** the case for forms that are bound to
tables/queries that edit data (so, forms and sub forms that edit data need
both a primary key and the time stamp field exposed to those forms).
Obviously for things like reports and other things the time stamp and
primary key is not a whole lot of importance.

You might as well add timestamps to *every* table. They don't cause
any problems, but they can certainly prevent them.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top