January 4, 2006 at 8:56 pm
Start writing your reports in Crystal and you'll be back to Access pretty quickly. The limitations of Access related to the number of users also goes away when you use SQL as a backend to Access. You can distribute the runtime version to avoid cost (extra initial one-time expense) and distributing updates is also very easy.
Using the upsizing wizard in Access your data is converted quickly, your tables are attached (linked) in Access and you're ready to go. Learn about stored procedures and how to use them within Access and maintenance becomes rather an easy task. (By the way, I agree with the poster above about going to Access Projects. They offer a much better interface to SQL Server.)
We have a continual debate about whether to move to .net and replace Access. We mostly decide against it for two reasons. Users can write their own queries to quickly gather information they need and the report writer in Access, which is very simple for non-technical folks to use (both require the full version, not the runtime).
Bill
January 5, 2006 at 1:39 am
Bills right there -
crystal is so buggy (or was) that access 2000 was always better for a report generator than crystal could ever be
but still give reporting services a try as well
MVDBA
January 5, 2006 at 8:55 am
I agree that Access should only be used with less than 5 users, especially in a corporate environment. It is great for data conversion projects and simple stand alone programs but I recommend writing more complex issues with C# or VB.net where feasible. Crystal Reports has a couple of drawbacks - it costs a lot to deploy in a network environment and it is not good for interactive use (i.e. a form). I use Access a great deal to rapidly create tables and reports from Excel spreadsheets and in one instance I use it as a Quality monitoring system that our Oracle ERP system does not have, but otherwise I use SQL Server 2003 and devlelop web based systems using C#.Net.
John Minkkinen
Senior Analyst
Sulzer Pumps Houston Inc.
January 5, 2006 at 10:02 am
so we're all agreed - there isn't a good reporting solution that does it all and is cheap/easy to deploy
hint hint microsoft - there appears to be a gap in the market!!!
MVDBA
January 16, 2006 at 4:00 am
Am I right in thinking that MS Access will always pull the total contents of a table to a client machine before querying? Is this true of all versions? Can this seriously clog up networks?
Presumably SQL Server handles this better?
Thanks for any help.
AlanB
January 20, 2006 at 7:54 am
Yes, one of the key differences between Access and SQL Server is the way it handles requests for recordsets. Access is a client application which pulls the whole table (or tables) and does any filtering and sorting locally. In a network environment that is a potential perfomance problem.
SQL processes requests on the server side.
I also am a long-time Access developer and I am just getting my feet wet with SQL Server because my primary client is growing so fast we need the horsepower of SQL Server. It's more or less a race, in fact, for me to keep ahead of the curve as I learn how to use SQL Server more effectively to meet our needsd.
I upsizes one app from Access 2000 to SQL Server, but I kept the Access Front End to it as it seems pointless to me to rewrite tons of forms, reports, etc. that work just fine in the current environment with only minimal changes. In that regard, I would say that the decision to move from Access as a front end to a different development language (VB.net, C#, etc.) depends on how well you know that other language and how quickly you need to convert.
One of the main advantages I've seen so far is in regard to the way SQL Server handles requests for recordsets. By moving complex Access queries into SQL Views, we did gain some significant performance improvements. As our database grows, I'm sure that will become more important.
George
January 20, 2006 at 12:51 pm
My experience with Access:
I'm developing new projects in .NET after using Access 97 for more than 5 years as a frontend to SQL Server and now finally moving on to .NET.
History:
first: separate access frontend access backend (LAN)
10 concurrent users
Frontend always locally on the computer (not run from the network)
Updated by a frontend updater routine
pro: easy fixing bugs (type a correction and press F5), every computer nearly had office installed, easy to generate flexible reports, nice querydesigner, developing speed, vba (interaction with word, windows api,...), Access does a lot in the background, easy linking tables across database platforms, intellisense,almost no corruption ( a new version every 3 days), excellent helpfile (opposed to Access 2000)
cons: limited gui options, no executable (quite big in size), security, locking issues, uses cursors
second step: caused by issue: data grows beyond 2 gb & needs to be accessible through WAN
Access front end to sql server backend with linked tables, storing tempory data in a freshly locally created access database when needed (countering bloating), optimizing some queries, security
Cons: slower than before since Access sometimes pulls the whole table in(horrible for performance on WAN)
concurrent users: 20
third step: after reading much on performance boosting Access on http://www.mvps.org/ modifying most queries to passthrough queries & vba code to do inserts, querying...
pro: more control on process flow, quircky locks reduced, quite speedy (controlling the amount of data transfered, sql server), still easy to develop/modify. (rapid deployment)
Cons: the ui does not respond whilst a passthrough query was executing, some functions of vb6 are not implemented, need to learn T-SQL for sql server, passthrough limited to 1 recordset.
concurrent users: 70
Studio.NET :
Pro :a more robust environment, unicode support, object oriented, a lot of usefull functions, relatively small executable opposed to Access mdb's, offline datasets, support for xml , ready for the web,receiving multiple recordsets from 1 command, copy & run, ability to reimplement existing code, ability to have a more responsive ui (opposed to Access97 in step 3), eliminating cursors
Actually requires some analysis. No worries about the different service packs & jet versions.
Cons: slow to open IDE, needs a recompile for every change (no correcting and hitting F5 to continue), very limited builtin reporting, still no service pack, a whole lot to learn (threading,...)
Reporting : with Crystal Reports (not so easy as in Access and little support for generating barcodes)
September 11, 2008 at 6:18 pm
Don't one just need a barcode font? I have done it in Crystal Reports, one font the text is displayed and another font text is displayed as a barcode. Did have to install font on web box.
September 12, 2008 at 5:31 am
Jo Pattyn (1/20/2006)
second step: caused by issue: data grows beyond 2 gb & needs to be accessible through WAN
The 2GB Access "limit" is something of a red herring. You can have links to as many "back end" .mdb files as are required, so long as each is kept under 2GB. But the total data available is effectively unlimited. (To be fair, no single table can exceed 2GB.)
And I'm sure Jo knows that; it's just one of the questionable points often raised by Access bashers without Jo's experience who rely on Access "myths" to discourage its use.
September 12, 2008 at 6:53 am
Hi all
My first time post here, but just thought i would add my thoughts as well since like jo i have spent my last 10 years or so working with access mssql and mysql.
I agree with ssc that sometimes to many of the access bashers quote what are myths with no real experience of access as a front end.
But here are my thoughts on the matter
If the Database was already in access and that it is for an upgrade for what ever reason.
Access is perfectly ok as a frontend providing you do the following
a) Convert the database to a project
b) re write all the queries to stored procedures or views
c) do not read all table data in one go, use the above to give only the results need for a view and then return a detailed record when the user
requires a detailed view of a record
( this also applies if written another language as cuts down unneeded network traffic)
d)Access as frontend for reports is probable as good as anything else and i'll stand by that for ever more
It is certainly far far easy then trying to rewrite reports in a language
e)The security is taken by MSSQL
f) the big dissadvantage is that update have to be rolled out, but that the same unless you re-write it to be a internet/intranet application ie
via ASP.net or php.net
G)A complied Access project will run just about as quick as a Visual project, and not so users will notice and you will be up and running quicker
September 12, 2008 at 7:29 am
Hi All,
Perhaps I can add a few thoughts here... I've been working with Access, Crystal Reports, and SQL Server databases for the last 13 years, and I can tell you that Crystal can give you capabilities that Access has a great deal of difficulty with. However, it can also spoil your entire day when it comes to subtracting values from the same field that appears in a group footer, where you want to subtract the value for one particular group from the value for another group. I've done that kind of thing in an earlier version of Crystal than I'm using now, and it doesn't appear to work in XI.
However, Access can be no picnic too. It has it's own quirks, including the tendency to self-corrupt, especially if you don't run a regular "Compact and Repair". Perhaps the best way to use Access is when you store the data in tables on a SQL Server, as that kind of scenario can really rock. I've also got high praise for MS's migration tool for going from Access to SQL 2005 or SQL 2008. It beats the upsizing wizard HANDS DOWN. See the Microsoft web site to find that FREE tool, and you'll be VERY glad you did. Because you can develop an application rather quickly in Access, then use that tool to move the tables up to SQL Server (including having the tool link the tables for you), you can get a darned good app out the door and with some serious robustness surprisingly quickly.
On the Crystal Reports front, I don't abandon that either. It's an extremely useful tool, but I've learned that it's best to get your entire result set to come out of your query, and then just pretty up the results in Crystal by supplying it a COMMAND (T-SQL text) instead of having it do the table joins (and then re-do them without concern for how that might break your report or destroy it's performance).
Given my current situation (coming to the end of a contract with limited local prospects), if there's anyone out there that needs help with Access or Crystal or SQL or a combination thereof (that goes beyond what you can get help with here), and can handle me working remotely via VPN (unless you're local to the Grand Rapids, MI area) on nights/weekends (for now, anyway), please contact me via PM.
Steve
(aka smunson)
:):):)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
September 12, 2008 at 9:34 am
You are absolutely correct. Write module level code using ADO and use an Access .adp Project, NOT linked tables or .mdb file. Use Access only for you forms and reports. You can do 95% of your development using stored procedures and even avoid client recordsets if you wish. Manage security by granting permissions only on stored procedures, not tables. This is a good "bridge" solution to use until you have more experience with SQL Server. As Access integrates well with Excel, Outlook etc there is a robust object model. The .adp can even connect via http over the internet directly to the SQL Server from anywhere you have an internet connection, provided SQL Server ports are open and configured for the internet.
September 12, 2008 at 9:46 am
While I agree that the .adp is nice as a best practice, many larger companies simply no longer have the time to invest in the additional complexity, and would more likely decide to take the speed of development with an Access .mdb file (table on SQL) that retains significant flexibility for changes and requires less "Access expertise" than a project file does. That speed then provides the bridge to a .NET app, built entirely for web access, that can exist in parallel with the Access front-end. It's hard to resist the flexibility and speed that goes with that, because often there just isn't time to waste getting the initial application functional. So, basically, in theory, theory and practice are the same, but in practice, they are not. (side note: I can't take credit for that statement - someone else here already uses it in their signature.)
Steve
(aka smunson)
:):):)
gene.stebley (9/12/2008)
You are absolutely correct. Write module level code using ADO and use an Access .adp Project, NOT linked tables or .mdb file. Use Access only for you forms and reports. You can do 95% of your development using stored procedures and even avoid client recordsets if you wish. Manage security by granting permissions only on stored procedures, not tables. This is a good "bridge" solution to use until you have more experience with SQL Server. As Access integrates well with Excel, Outlook etc there is a robust object model. The .adp can even connect via http over the internet directly to the SQL Server from anywhere you have an internet connection, provided SQL Server ports are open and configured for the internet.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
September 12, 2008 at 10:00 am
Steve
Of course a .NET application may be the ultimate goal but there is no reason any Access.mdb database could not be migrated to an adp with minimal effort. The adp is no silver bullet for a poorly designed database, but that is another topic. An adp will also outperform any mdb based solution.
G.S.
September 12, 2008 at 11:25 am
Minimal effort? I've been working with Access for a long time, and while I haven't spent much time making the attempt, the effort I did spend led to a total dead end as to how to make changes once you have the database in project form. I gave up on that two versions of Office ago (Office 97), in total frustration, as it appeared to leave flexibility for change in the dust. Perhaps someone can enlighten me as to what I'm missing in the way of how to do that easily, as I'm always willing to learn. While I have access to Office 2007, but can only develop for Office 2003 for numerous usability reasons associated with Office 2007 as a whole, so keep that in mind with any responses.
Steve
(aka smunson)
:):):)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply