**HELP- change from ACCESS application to sequel server

[Kristen Shealy]

I need help migrating from ACCESS application to a sequel
server. I work with a small group of researchers
(currently using MS 2002) and we need the ability for 5 or
6 of us to access the system at the same time. How would
this switch change the front end? IS it difficult? What
are the set-up steps? Please help!

 

[Joe Fallon]

You should hire an experienced consultant. (Not me.)
It isn't that hard if you know what to do.
There are books on the topic so you can't really expect a complete answer
here!

Once you have the data in SQL Server tables of the same name as the original
tables in the .mdb, you delete the original tables (from a copy!!!! ALWAYS
keep a complete original in case you mess up!).
Then you link to the SQL Serve tables and strip off the dbo_ in fron of each
table name.

Now your app should work exactly as before!
Maybe a little slower since you introduced a network connection.
You can then begin optimizing after you get it running.

 

[John Viescas]

Kristen-

For a small group of users, you probably do not need SQL Server or MSDE
(Microsoft SQL Server Desktop Engine). Use the Database Splitter (Tools /
Database Utilities / Database Splitter) to create a separate "data only" mdb
file containing your tables linked to your application "code" mdb file.
You'll end up with linked tables in the original database. Put the "data
only" mdb file on a file server that all your 5 or 6 users can get to. Fix
the links to point to the share using the Linked Table Manager (same menu).
Give each user a copy of the "code" mdb to run your application with shared
data.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Paul Overway]

What makes you think you need to migrate to SQL Server? If you only need 5
or 6 concurrent users, you can do that with Access "as is".

To share access to the database, it is best to split it...1 MDB with tables
only (BE), and 1 with your queries, forms, modules, etc. (FE). Then you
create linked tables within the FE pointing to the BE file. The BE file is
placed on a share on your server or another PC. Each user then gets a copy
of the FE file on their PC.

 

[Jeff Boyce]

Kristen

John & Paul pointed out that you may not need to migrate your data to
SQL-Server. Are you facing a problem for which someone has suggested
SQL-Server as a solution? Are you facing an organizationally-mandated
shift?

Joe described converting tables into SQL-Server, re-linking, and renaming.
There are tools in both Access and in SQL-Server to do this. I would add
that any complexly-joined queries and SQL statements you have in your
current version could end up painfully slow against a SQL-Server back-end
(personal experience -- wanna see the bruises?!). Just like my bruises, it
was possible to "heal" the slow queries, but it involved a serious effort to
work in and with the new SQL-Server tools and techniques. Converting is not
for the faint of heart.

I also feel, as Joe alluded to, that SQL-Server has a MUCH steeper learning
curve than Access, and the care/feeding is significantly more involved. ...
as it should be! SQL-Server is capable of enterprise-level database work,
and will require someone semi- or fully-dedicated to it's upkeep.

As at the top, my question is still, "Why?", as in "Why do you feel you need
to?"

Good luck!

Jeff Boyce
<Access MVP>