MS ACCESS Front end With SQL Server Back end

[Titlepusher]

I have an Access db with front-back structure. (both front and back are
Access now) It runs over a local network to about 5 users and also over
T1 or dsl to 4 other offices with 2 - 3 users each. Over the local network
it runs at a decent speed.. even back when there were 20 - 24 users at that
site.. (so the slowness is not just the volume of users) .. but, over the
extended net it creeps along very slowly. I have modified it for efficiency
and sped it up a bit.. but still slow. I have been told that the back end
data should be in SQL server and that I can just link my front end to it. I
have MS ACCESS XP Developers Edition which came with SQL Server. The company
on which I have my app running also has there own newer SQL server existing.

Can someone please give me some guidance on making my back end SQL server.
Is it a good Idea.. difficult? pit-falls?

Thank you. tp

 

[golfinray]

It works fine and very fast for me and I have a one million record database.
I use the ODBC connection to the sql server and it works well. There are
other ways also. Google connecting access to sql.

 

[Paul Shapiro]

JUST switching to sql server should help, but maybe not enough. Access is a
file-server type of database, which means that your remote application
directly does all the reading and writing of the Access .mdb file. SQL
Server is a client-server database so you send it sql commands and it
executes the commands on the database, returning the requested results. So
while an Access client workstation has to read the indexes from the mdb
file, SQL Server executes all that housekeeping and low-level stuff on the
server. That helps some. Reliability over a WAN improves a lot with SQL
Server, because all the file writing is done locally by the server, instead
of by the remote workstation in Access. Network interruption won't corrupt
the data with SQL Server, while it definitely can with Access.

You can get a very good speed improvement though if you are careful to make
your application just request the data it needs. Instead of opening a form
with a recordset of Select * From Person, you can open it with Select * From
Person Where personID=0 (no rows are returned). When the user chooses a
person from a dropdown list, or types part of the person's name, you can
retrieve just the requested rows by changing the RecordSource in your code.
With that kind of approach you can get decent performance at dialup speeds.

I think its a very good idea to make the change. I wouldn't use an Access
backend over a WAN because of the reliability concerns. The pitfall is that
you have some learning to do. The sql syntax changes some, the names of sql
functions change some, you can't include your vba code in a query, etc.

 

[Piet Linden]

Can someone please give me some guidance on making my back end SQL server.
You might want to have a look at Chipman & Baron's book, Microsoft
Access Developer's Guide To SQL Server
http://www.amazon.com/Microsoft-Acc...=sr_1_3?ie=UTF8&s=books&qid=1237335624&sr=8-3

for $15, I would say it's a good investment. They cover the different
options when creating a front end and the different issues you'll need
to address (and how to address them).

 

[aaron.kempf]

moving to ADP is much better than linked tabkles.

Linked Tables just plain fucking suck.
ADP has about half as much bullshit involved.

-aaron

 

[aaron.kempf]

I'd avoid her books like the plague, she doesn't know WTF she's
talking about

I'd go for 'Professional SQL Server 2000 Development with Access 2000'
by who-- Rick Dobson, is that the name?
Or 'Access Client-Server solutions' the black book one.

Those are 2 decent resources.

Or of course-- MS Press has a book about access projects.. that's a
good book.
Or you could just take any book on TSQL.. that would be a good place
to start.

-Aaron

 

[Tony Toews [MVP]]

I'd avoid her books like the plague, she doesn't know WTF she's
talking about

Ah, she's seen your postings in the newsgroups has she.

I'd go for 'Professional SQL Server 2000 Development with Access 2000'
by who-- Rick Dobson, is that the name?

That book wasn't all that good.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

m:

[re: Mary Chipman:]

I'd avoid her books like the plague, she doesn't know WTF she's
talking about

Gad you are dumber than a box of hammers. You don't deserve to be
allowed to even mention Mary Chipman's name, let alone have the
nerve to claim she doesn't know what she's talking about -- it's
*you* by far who knows nothing at all about anything.

To those reading this, a safe bet when you read something Aaron
posts is that THE EXACT OPPOSITE is the TRUTH, as is certainly the
case in this instance.

 

[PEGUY MOMPREIMER]

i understand what u are saying but ther's many of u i'll try to get to u all

 

[aaron.kempf]

I just don't think that she understands the power of SQL Server...
From what I've seen of her-- she's a biased jet _LOSER_ just like the
rest of you all.

Rick Dobson did some great books on ADP. Rick Dobson wrote a BOOK on
ADP.
Mary Chapman wrote ONE chapter ONCE.

And truly-- 'Access Client Server Solutions' is the best IMHO

Let me guess Tony-- you haven't read it.. because you've only ever
attempted one upsizing (and failed).
That's why everyone else has to be stuck using a piece of shit ass
database-- that causes corruption--

Just because Tony doesn't have the mental capacity to pass a SQL
Server certification test.
Just because Tony doesn't have the mental capacity to pass a SQL
Server certification test.
Just because Tony doesn't have the mental capacity to pass a SQL
Server certification test.

-Aaron

m:

[re: Mary Chipman:]

I'd avoid her books like the plague, she doesn't know WTF she's
talking about

Gad you are dumber than a box of hammers. You don't deserve to be
allowed to even mention Mary Chipman's name, let alone have the
nerve to claim she doesn't know what she's talking about -- it's
*you* by far who knows nothing at all about anything.

To those reading this, a safe bet when you read something Aaron
posts is that THE EXACT OPPOSITE is the TRUTH, as is certainly the
case in this instance.