SQL Server and Front Ends

[James Stephens]

I wasn't sure where exactly to post this. I have gotten lots of useful
information from this group in the past and was hoping for some guidance.

I have a large database that I have built over the last several years. It
started out very small and has become mission critical to our entire
operation. Two years ago it had 3 users now it has 25+ installations of the
front end and over 80 people that access it. It has 150+ linked tables and
many features. The backend is now upwards of 500mb and growing about 4-5
more a day.

That basically explains my current situation.

I am starting to get some real speed issues. We are wanting to expand the
system to 4 remote locations over our WAN. I see even more problems in the
future with speed issues. To set it up for multiple locations some major
reworking will need to be done to quite a few tables and so now is the time
to make major changes overall. As it looks now I will have access to an SQL
Server running SQL Server 2005 to use as my backend (By the way I have Access
2003). While I am new to this platform I will have some help to deal with
the learning curve. The problem is that I will not be allowed to make the
interface web based so I am stuck with front end applications.

I have looked into this some, and from what I can tell I can stay with my
..mdb (actually .mde), go to a .adp, or use something else. I am looking for
any direction on this. From what I am reading .adp's future is somewhat up
in the air so I am not sure about moving in that direction. I am self taught
but very comfortable with staying with access. I am not sure about security
issues which will be a concern. I have user level security fully implemented
now, but not sure about how that will work in the SQL Server environment.
The main benefit I use it for is to identify users and provide menus to them
based on their individual needs. I know it can be broken but with my current
user base I am not really concerned. As it expands more though, I am not so
comfortable !!!

I have a good understanding of vb.net (at least the basics). I would also
have someone that can assist me with the additional education I will need for
that. That is the other direction that I have looked at as a front end.

Other than that I am not sure what else to look at or what else I need to
know before moving forward. I have some time as I wont have the server for
about 6 months.

I am looking for any advice on directions I should look at and educate
myself on or ideas to explore. I want to have a plan in place so when the
server arrives I can hit the ground running, rebuild the application as
needed and get everything working smoother and faster. (Basically I need to
go with a front end option that will allow the quickest access to data, and
the most security possible, that is not a web based option.)

Thanks for any advice,
Jim

 

[Tony Toews]

I wasn't sure where exactly to post this.

This is an excellent place to ask such a question.

Converting to VB or VB.Net will take a *lot* of work and you will not
be as productive as Access.

ADPs will not be enhanced thus they are somewhat of a dead end. They
also have their own quirks which can cause you some troubles.

For an overview of why you shouldn't bother converting to different
technologies see Things You Should Never Do, Part I
http://www.joelonsoftware.com/articles/fog0000000069.html

MS are advocating the use of linked tables within MDBs for SQL Server
data access.

In my opinion you should stay with Access and upsize your backend to
SQL Server. Which could be a lot of work and require a lot of testing
especially by the users. Also note that until you "debottleneck" the
queries and move them over to SQL Server views and stored procedures
you won't see any performance improvement and you will likely see some
performance degradation.

I am starting to get some real speed issues.

With subreports? Anything in specific?

We are wanting to expand the
system to 4 remote locations over our WAN.

You may want to consider Terminal Server/Citrix. Access works quite
well with TS/Citrix.

The problem is that I will not be allowed to make the
interface web based so I am stuck with front end applications.

That's ok because web based apps just don't have the speed or
functionality of Access based apps.

As far as security goes you can do a lot there inside SQL Server as
well.

See my Random Thoughts on SQL Server Upsizing from Microsoft Access
Tips page at http://www.granite.ab.ca/access/sqlserverupsizing.htm

There is a new tool from the SQL Server group.
SQL Server Migration Assistant for Access (SSMA Access)
http://www.microsoft.com/sql/solutions/migration/default.mspx

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

 

[James Stephens]

In my opinion you should stay with Access and upsize your backend to

SQL Server. Which could be a lot of work and require a lot of testing
especially by the users. Also note that until you "debottleneck" the
queries and move them over to SQL Server views and stored procedures
you won't see any performance improvement and you will likely see some
performance degradation.

In this first part, is there any software or process I can use to
specifically locate bottlenecks. I have some queries based on multiple
tables and/or other queries. It is sometimes difficult to find out what is
causing performance problems.

As for the conversion, my plan is to spend several months converting the
entire thing, including the optimization with SQL Server before switching
over. I was going to use one of out remote locations that has no access
right now and lives in a world of spreadsheets as my test site while I do the
development to help me find the bugs. Would you suggest to just ugrade the
backend and run with that while I do the front end?

With subreports? Anything in specific?

For this part I have some lareg tables that run slow, one with 10,000,000+
records and searches on these can sometimes be very slow. One report has 14
subreports that pull about 10,000 records from this table and links to 4
other much smaller tables. Each subreport has its own query that runs off of
one main query (14 day employee schedule). It can sometimes take up to 8
minutes to create the 30 page report.


Other than that just some sporadic issues, I have used some local tables to
take care of some of these. Some of the more complex queries sometimes
really bog down. Our overall issue is that the backend is currently stored
on my second desktop computer (1.4 processor, 512mb ram and 20gig hd). In our
office it is not bad at all really other than the one report listed above.
It is at the other building about 1 mile away. It is behind a different
firewall than our office and access from there is much slower (the network is
the same speed, to access everything else, ie the internet, but to go through
that small WAN (I think are main servers are about 500 miles away), and then
get the info from my computer and getting it back to theirs seems very slow.
Startup in the office, 8sec (my startup form does a lot, some checks for
computer location, version, auto lockout, auto shutwdown, updated menu
access), startup at our other building, 45 to 60 seconds.

I am really hoping that a good SQL Server with a improved table structure
and stroed view and procedures will help this. If I am barking up the wrong
tree please let me know.

If there is anything else I can do to get better performance I am happy to
learn. Additional software can frequently be a problem however because of
network policies and getting approval. i.e.

You may want to consider Terminal Server/Citrix. Access works quite
well with TS/Citrix.

Anything else I should take a look at to begin my journey or other paths to
look at.

Thanks for all the info, I am actually kind of glad to hear someone tell me
to stick with where my current strenghts lie (ie .mdb)

Thanks,
Jim

 

[James Stephens]

I had one other though to mention, with the SQL Server we are going to get MS
Sharepoint. I have read up some on this application and see some really good
uses for it. I have been told by the people that will adminster it that it
will take the place of alot of databases, but from what I can tell it really
wont work for the size and extent of mine. Any thoughts on this? Like I
said just looking for the fastest option when it comes to processing the
data, and this is one I don't know much about. I guess this might fall back
under your first point about not starting over from scratch.

Thanks,
Jim

 

[aaron.kempf]

Tony

you are a lying cocksucker.

ADP _ARE_ enhanced.. just as much as MDB are enhanced in the next
version of Access 2007.

Get it through your thick skull; fucknut

ADP are a superior platform; don't listen to obsolete retards that tell
you otherwise.


Terminal Server?

HELL NO.

Use Access Data Projects; and dont listen to anyone that tells you
otherwise.


And Tony;

this quote:

MS are advocating the use of linked tables within MDBs for SQL Server
data access.

this MIGHT pertain to Access 2007.. but I've NEVER heard that come out
of Redmond pertaining to Access 2003.

GROW UP AND LOSE THE TRAINING WHEELS TONY
ACCESS MDB ISNT AN ENTERPRISE-LEVEL DATABASE: AND IT NEVER WILL BE

the maintenance of linking tables and refreshing tables and refreshing
tables
the overhead of scripting out queries and recreating them in each copy
of the front end is laughable.

TALK ABOUT A MAINTENANCE NIGHTMARE

keep your tables and queries in ONE PLACE using Access Data Projects.
it is a MUCH MUCH simpler architecture

 

[Tony Toews]

I had one other though to mention, with the SQL Server we are going to get MS
Sharepoint. I have read up some on this application and see some really good
uses for it. I have been told by the people that will adminster it that it
will take the place of alot of databases, but from what I can tell it really
wont work for the size and extent of mine. Any thoughts on this? Like I
said just looking for the fastest option when it comes to processing the
data, and this is one I don't know much about. I guess this might fall back
under your first point about not starting over from scratch.

I am unimpressed with what I see in Sharepoint from the viewpoint of a
sophisticated database app. It is very flat filish with a bunch of
drop down combo boxes. And you can't put much data validation in
behind the scenes.

It has it's place, such as say, tracking the status of bids or jobs or
simplish stuff. Hmm, ok, let me put my thoughts another way. From
what I've seen of it SharePoint would be an excellent tool for
replacing data that is currently in spreadsheets and making that data
available to multiple users inside your firewall and outside with
authentication.

But only for stuff that we database developers haven't and likely
never will get around to putting in a database.

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

 

[aaron.kempf]

Sharepoint is nice for Data Entry.. I would MUCH rather enter data into
a Sharepoint List than into a spreadsheet for example.. I mean; you can
have real validaiton and data types and it's just a beautiful
environment; as opposed to excel which is pretty worthless in my book.

I tried for months to push 17k records into sharepoint; maybe 13
columns wide.. and Access couldn't do jack shit with it.. I was
basically using Access for ETL to crunch and shape the data; to do a
bunch of text parsing.. pretty crazy date parsing stuff.. and then i
was trying to push data into Sharepoint through a MDB... and it just
blatantly didn't work.

I tried for months; i opened a ticket with microsoft and I spent 10
hours troubleshooting it.. it was difficult because there was NO WAY i
could get any information out of sharepoint-- i didn't have jack shit
for perms except thru the sharepoint UI.. and it drove me crazy because
I had NO IDEA what was happening; but I couldn't write 17k records in
90 minutes

I really like Sharepoint; I just think that MDB - Sharepoint is kinda
overrated

and you're right-- if you're working on a large dataset I wouldn't
touch sharepoint with a 10 foot pole

the punchline of course
is that I had a coworker come over and paste this data into a
sharepoint spreadsheet and it worked in about 15 seconds; it was pretty
out of control

 

[Brendan Reynolds]

You might be interested in what Albert Kallal has to say about Access and
WANs in the article at the following URL ...

http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html

 

[Jeff Conrad]

in message:

Polite request: can you provide a citation (e.g. article on MSDN) for
this claim, please?

Hi Jamie,

http://blogs.msdn.com/access/archive/2006/07/27/680772.aspx

"Access project files, also called ADPs or .adp files, provide an alternative to using the Access
database engine by allowing an Access application to connect directly to the tables in a SQL Server
database. ADPs will continue to be supported in Office Access 2007. To take maximum advantage of the
new features in Office Access 2007, Microsoft recommends the use of linked tables to connect to SQL
Server data."

 

[aaron.kempf]

that is the single-most overused quote anywhere.

It's not an official quote; it's an 'offhand remark' by some
disgruntled Access Project Manager that is too fat and lazy to learn
SQL Server.

Access Data Projects are a superior solution.

You don't need to re-link, re-fresh; setup connections; deal with awful
performance.

Does the linked table manager even update SQL passthru statements?

Until it does; why dont you midget Access users STFU and grow some
balls

 

[aaron.kempf]

and again; you kids are fucking retards

Access Data Projects work FABULOUSLY over a WAN or a VPN.
I do it all day; every day for a bunch of clients.

it's because ADP are inherently more efficient than silly old MDB.. I
mean; does your database REALLY need to pull a million records to find
20?

ROFL

 

[aaron.kempf]

and I disagree with THIS claim:

http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html

JET is the default engine for ms-access

Jet isn't the default.. when you go to create a new application using
Access; two of the shortcuts are for projects; and one for MDB.. I
would think that if 2/3rds of the shortcuts were for ADP; then maybe
ADP would be considered the 'default'


Also this is a ridiculous omission:

Consider a Web based system.

Another possible solution to limited bandwidth is to turn the
application into a web based system. However, this really means that
you are not using ms-access at all anymore. In fact, you are talking
about re-writing the application and setting up a web server.


he doesn't meantion that Microsoft STRICTLY RECCOMENDS _NOT_ USING MDB
FOR WEBBASED SYSTEMS. So if you're EVER going to go this route; it is
highly reccomended to use SQL Server instead of silly little MDB

 

[Van T. Dinh]

This was also mentioned (top of page 10) in a Microsoft Word doc titled
"Microsoft Office Access Vision_Final.doc" posted by Clint Covington of
Microsoft and available for download from:

http://www.tinyurl.com/y2yjzn

The blog (by Erik Rucker) and the Word doc obviously come from the same
source as the wordings are exactly the same ...

 

[aaron.kempf]

I disagree; it is beta information from a non-microsoft site

ADP is for winners; MDB is for losers that are still stuck in the 90s..

while we're at it; why dont we bring back windows 95 ROFL

 

[aaron.kempf]

here is my url

microsoft.com/downloads?docname=MDBIsDead.doc


ROFL

 

[aaron.kempf]

I just think that ADP is the most important feature to ever ship out of
redmond.

Simple bound data entry and reporting.

None of this InfoPath and Sharepoint bullshit... just simple bound data
entry and reporting..
right-click SORT, right-click FILTER

and don't make people learn 2 different dialects of SQL in order to
build a simple app

and I think that it's a great platform; i've been reccommending it- in
the open market- everywhere i've been for the last 8 years.

all of the benefits of Access-- none of the drawbacks.. none of the
workarounds.

-Aaron

 

[James A. Fortune]

There are numerous similar sound bites. So did all new development from
Access 2000 onwards follow this recommendation and use ADO? Did the
MVPs and regulars start posting, "Microsoft recommends ADO" when
someone stated a preference for DAO? It would seem not.


I would urge posters to remember the ADO vs DAO debate when saying,
"Microsoft recommends..."

On the other side of the fence, although I understand that DAO is back
in favour with the Access team I would not expect to find a MSDN
article titled, 'We've changed our mind about DAO'. So there is a good
chance that Microsoft is trying to back out of ADP and doesn't want to
be too vocal about it.

They can't get too negative about it since it's going to be the surprise
new feature of Access 2011 .

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

They can't get too negative about it since it's going to be the surprise
new feature of Access 2011 .

James A. Fortune
(e-mail address removed)

Instead of 'new feature,' perhaps 'mostly working' might be a better way
to categorize what would suprise everyone. Then they'll start backing
out of DAO -- quietly, until 2015 .

James A. Fortune
(e-mail address removed)

 

[aaron.kempf]

James;

I need a clarification on this; I think that DAO is roadkill and I
could care less.

But seriously.. are ADP going to make a big comeback in 2011?
Is there a version Access 2009?

Also does anyone know when the next damn visual studio comes out; I was
thiking next summer.. but it will be January-ish.. right??

-Aaron

 

[aaron.kempf]

I just really dont get this whole DAO kick.. I could do anything in ADO
that you kids could do in DAO.. what enum fields tables, queries?

ROFL

create indexes?
ROFL


why is DAO coming back?
performance?

ROFL