Connecting to a SQL Server Database

[Guest]

I currently have an application that is developed in Microsoft Access 2000.
And I have an access backend. Now I want to know is it possible to use sql
server as a backend and what DB Connection Technology should I use. I have
done a bit of reading up on ado and access ADP.. What is the best to use to
connect to sql server and if possible can you give me a brief explanation on
how adp works.

 

[Alex White MCDBA MCSE]

If you want it working, with the minimum of fuss, then upsize your data into
SQL, keep the front-end MDB and link to SQL server via the MDB. MDB's use
DAO as the default underlying database technology (can use ADO as well but
not for bound forms etc) it is fast and lean when working with mdb type
data. ADP projects use ADO (by default and can use DAO as well) and ADO is
better under the hood for SQL databases (not any faster!), MDB files are
more flexible than ADP's in that you can have access/sql/other database
tables linked into a single front end for bound forms.

If you are only going to use SQL as the data store and you have lots of
data/users and you don't mind learning loads of new theories/technologies
then ADP is the way to go, it is a start from scratch situation (if you want
to do it properly), it will require that you have a good understanding of
SQL, in terms of good queries/stored procedures/good table design.

If I was starting a project from scratch and it was SQL based then I would
go with ADP.

How large is your existing project in terms of mdb size/total users/amount
of tables etc?

Just my personal view, this is a hotly debated topic and there are lots of
different points of view, some of which will contradict my views, you will
have to make your own mind up, but both MDB and ADP are solid technologies.

 

[Joe Fallon]

Alex,
Nice detailed answer.
The only thing I would change is that even for a new project I would use
linked .mdbs over .ADPs.
MS has been recommending this for over a year now. Seems like .adps are
another dead end technology.

 

[Guest]

if i might join this thread....

let's suppose we have been merrily developing our mdb in a2k and have it
sitting on a folder on shared network drive which is available to a handful
(but a growing handful) of trusty users.

would it be prudent to think about somehow offloading the tables in this mdb
file to SQL server and leaving the rest of the file intact (perhaps even to
be converted to an mde mode file)? i've heard that SQL Server has security
which exceeds a2k's. also, if the tables were to be 'in' SQL Server i would
imagine that should the a2k mdb file get copied and offloaded and 'fall into
the wrong hands' that it'd be next to impossible to acquire the information
from the SQL Server tables since they would not be 'in' the mdb file.

am i barking up the wrong tree here.

tia,

-ted

 

[Joe Fallon]

Nope - you are not barking up the wrong tree. <g>

You have describe one of the key reasons for moving to SQL Server.
There are many more. Note: Speed is not one of them. JET dbs are faster than
SQL Server in many cases.
It takes a lot of tweaking to make them comparable. But it can be done.

If you "simply" move your tables to SQL Server and re-link them in your mdb
then your app will probably keep working without change. But it may be
sluggish as it was not designed with a network DB in mind.
(e.g. never bind a form to a full table. Always select 0 or 1 row when the
form opens and have users enter a filter to retrieve other records.)

HTH

 

[Guest]

i don't know which version of SQL Server the IT/Network people at this
organization use (finding out who knows and asking would settle that), but
assuming it didn't make all that much difference, what approach would you
take to "simply" move the a2k's tables to it. i'm doing some exploratory
research at this point w/ a view to determining how much effort the process
would take. the a2k mdb are currently not fe/be types. they are unary mdb
files sitting in their 'home' folders on a LAN shared drive that is
configured to restrict access to a subset of the community of the org'ns
users who have need to get to the mdb files. the mdbs are not those large
multi-user millions of records affairs --- just 'modest' databases that might
have hundreds of records with at most two people hitting on them
simultaneously.

-ted

ps: i haven't actually used SQL Server but have read about it in this and
that book talking about a2k development.

 

[Joe Fallon]

There is an Upsizing wizard in Access which is supposed to be able to create
the SQL Server tables for you.
I assume it gets better with newer versions of Access.
e.g. A200 may be weak, A2002 better, A2003 good.

If your tables have relationships defined it should also build them on the
server for you.
It should even move your data. (I think)

If not, just attach to SQL Server and use append queries to move it
yourself.

Play around with it.
Use Enterpise Manager to review the tables in the new DB.
You will need a DBA to give you access to the server anyway.
Have them compare the Access and SQL Server versions and make any small
tweaks that may be required.

 

[Guest]

thank you. that's a staret and i'm looking into getting some documentation on
it (the upsizing wizard -- sounds like a friend of harry potter's).

-ted