Well I really got behind on this thread as I meant to comment again quickly some time ago.
Just some brief thoughts.
I have to agree with Gemma's recommendation of the Chipman & Baron book.
FWIW ADP's haven't been deprecated as such. Sure they're more effort to get to if you're using Access 2007 - but then some would argue that everything is. lol ;-)
I agree that they're not MS'
recommended connection strategy anymore - that's certainly the case. But they exist and AFAIK intend to stay until they are deprecated some stage in the future (which I
would agree will happen).
But that book has much more to offer than ADP advice. Yes they mention them plenty due to their prevelence at the time of writing (quite a few things have changed since then in 2000 - in SQL Server and in Access' support of it and techniques they describe).
They data access is focussed though ADO that's inevitable given the time frame.
(Though for direct server access that's not inconsistent with the current state of affairs again - given that Access 2007 has removed ODBCDirect support from DAO and MS theselves still advocate ADO as the replacement for that when direct server access is required).
But the overall axiom and methodology of data access that they convey is the key point. (And offer some good ideas on how to go about that - ADP or not).
Alas, as I understand it, there's no plan for an updated version (it'd have happened by now).
Maybe if Access gets some cool new Client Server tools in a couple of versions time it'll be merited. (So let's make sure we all champion that as an idiology to MS! ;-)
On to individual items
>> 1) My queries are always made in query design, so nothing fancy. I use a few functions in queries that I make and these invariably relate to extracting character, strings from text. A lot of IIFs, Left, Right, Mid, Len, InStr.
Right. Not necessarily ideal - though if you're a linked table myster then Jet and the ODBC provider will be able to interpret some of what you ask into T-SQL requests - but once you move to UDFs then obviousy that's guaranteed to not be parsed.
Just make sure you don't
filter on those calculated fields. Then you're into a world of performance hurt.
>> 2) Lots of Access to Word bookmarks, saving the files as date/time, reopening then based on the file name being in Access texboxes.
That's presumably all local FE code manipulation. It's about how you use Access to acquire that data before insertion into the Word document that's key (that you use Bookmarks implies this is done in code rather than datasourced by Word - in which case temporary tables / databases would be an asset).
>> 3) I have a lot of VBA that relates to general file management but that would probably stay as it is usually a stand alone DB.
Yep - as above.
>> 4) Pretty much whatever you can do from Access to OutLook and Access to SMTP
Same ol' same ol'
>> 5) I have a lot of VBA on forms, but apart from Access to Word and Access/Email it is all simple stuff that can be done with macros. In fact it is only done as VBA because the MDE does not block out macros.
Depends slightly on what you're doing (for example if that VBA in your forms was calling an unnecessary amount of requeries - it could be a problem once pushed into Client Server scenario.)
The general advice to
always be data aware - think about every request you make applies in each case.
Other snippets to comment on...
>> unable to create crosstab queries in SQL Server.
Indeed - isn't it wonderfully gratifying how comparatively slow off the mark everyone was compared to little old Jet? ;-)
SQL Server 2005 did introduce some Pivot functionality - though it's relatively limited (well - certainly less fluid) by comparison.
>> I incoorporate custom functions within select queries, this again is not acheivable in SQL server.
Indeed - though as stated earlier those functions can be maintained if you're using an Access FE - and as long as you don't
ever filter on them.
Of course since SQL 2000 we've had UDFs on the server. Naturally T-SQL doesn't offer the huge diversity that VBA gives in making these UDFs (and there are various things not supported in SQL UDFs - not least of which is dynamic SQL) but they can offer Server side processing for
some functions that can be interpreted in T-SQL successfully.
>> I have used SQL server to hold the data but used OBDC linked to the SQL server to use it. Although Access still uses Jet to interrogate the data. If someone can offer solutions to the above I would be interested in hearing them
That Jet wraps the calls isn't necessarily a problem.
As mentioned (in the case of continuing to use VBA UDF's etc) it can be a true blessing. And the parsing that goes on prior to request can result in some perfectly reaonsable server requests on the server (alas surrounded by some Jet/ODBC wrapping calls too).
Linked tables offer a great entry into Client Server development.
Naysayers often cite them as a reason for not liking Access for the purpose. But they're probably being mis-used in the scare stories thrown around, but in fact have offered a quick conversion method with the existing FE in place. Improvements could then be made when required - we have passthroughs to replace appropriate requests, and
then moving out into code when we need to.
It astounds me that folks don't acknowledge Access as a viable CS development environment. It's just
easier to build a bad application using it. That's not the application's fault - but the developer's. It would be pretty easy to make a C# SQL Server application perform terribly with appropriately poorly formed data requests.
>> I tend to use VB 6 alot and using ADODB, by setting up a mdb and linking in OBDC SQL Server tables then referring to the Access mdb container seems illogical when you should be referring to SQL server direct.
But surely it's all about
options.
Linked tables, when used well, can afford very acceptable performance with minimal redevelopment. As I've been mentioning, Access offers various functionalities that other environments don't offer. Direct code data access (as from VB) is equally possible from Access - and FWIW I would personally lean towards doing so myself in application development - but there are choices of various levels of development effort against runtime return and functionality offerings. Having the choice is what gives Access power.
>> When using Access to build an app it is dependant on the client needs and sizing issues.
Isn't every solution created dependant upon client needs and sizing in some way?
We should design well from the outset so that sizing (scaling) doesn't become an issue.
If local client side data filtering is occuring instead of Server side then something has fundamentally gone wrong. And the blame should likely lie at the feet of the developer.
To give some specific links as Banana began...
http://support.microsoft.com/kb/128808
http://msdn.microsoft.com/en-us/library/bb188204.aspx
The former general advice but important to read. The latter much more specific - but very much geared towards linked tables.
(There are many forum posts, articles and books out there for more on code data access and so on).
Must dash.
Cheers all!
After a few months there is usually announcement from the insurance company along the lines of.....we wish to announce we will be introducing an updated quote system and we have out sourced this to the world's best etc and etc
