Solved

MS Access Questions

Posted on 2014-01-30
25
172 Views
Last Modified: 2014-10-07
I am preparing to develop a software application and am planning in using MS Access. It has been a few years since I developed in Access and I thought I could get some general questions answered and some initial guidance before I start.

#1) I want to distribute the application to all people in the company but not everyone has MS Access. Can that be done? What is required?

#2) The data already resides in MS SQL Server. In the old days you would create linked tables. Now I think they have an Access Project that connects to SQL Server intentionally. Is that correct or is there a better way to use SQL Server as the back end?

#3) Which version of Access would be best to use? I think I have Access 2003, 2007, 2010, and 2013.

#4) Any other words of wisdom?

Thank you for your help!
0
Comment
Question by:GenesisTech
  • 8
  • 4
  • 4
  • +6
25 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39822823
I will answer your 4 question directly....

if you are looking for a good bussiness application that will be used internally then I would suggest to take a look at lighswitch provided by microsoft.

This is a easy to use software, you can quickly build your screens and deploy it and the screens will be very rich and can also work on a mobile...

and no one need access installed
http://blogs.msdn.com/b/lightswitch/archive/2013/10/17/visual-studio-2013-released-thank-you-lightswitch-community.aspx
0
 
LVL 24

Accepted Solution

by:
chaau earned 167 total points
ID: 39822861
I would like to answer question #1. You can distribute you application across your computers and run it even on those PC where there is no full Access installed with an aim of Access Runtime (free version). It was introduced in version 2007, so you will need to develop your application using at least version 2007, which answers indirectly your question #3. I think you should use the latest version (2013), as it will contain more features

As for your question #2. Linked tables still exist in the latest versions, and you can still use them.
0
 

Author Comment

by:GenesisTech
ID: 39822867
chaau,

Thank you for your answer.

Isn't it better to use an Access Data Project to connect to SQL Server rather than using linked tables?

Doesn't it expose more SQL Server functionality? AND, didn't they remove that in Access 2013?
0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 167 total points
ID: 39823088
Isn't it better to use an Access Data Project to connect to SQL Server rather than using linked tables?

No. Even Microsoft recommends using a mdb/accdb.  ADP's have reach their end of life and are now dead.  I would avoid them,

About:
#3) Which version of Access would be best to use? I think I have Access 2003, 2007, 2010, and 2013.

Access 2003 has reached its end of life. Support ends in April 2014. Office  2003 should be avoided.

So what version should you use?
After 15+ year deploying Access Runtime based solutions to 1000s of PCs, I have learned to always use the same version of Access as the other Office Apps installed. If the PC has Office 2007, use Access 2007, etc. If all your user's PC have Office 2010 then use Access 2010.

Since I must support Office 2007 and later (2003 has reach end-of-life) I do all my development in Access 2007. I test with 2010 and 2013 for compatibility. This way I can match the version of the Access Runtime version to the version of the other office apps installed.

If you want to avoid all the issues with dealing with all the versions of Office/Access hen I would go with .NET.

If data security is important then use .NET not Access.

@chaau,
I would like to answer question #1. You can distribute you application across your computers and run it even on those PC where there is no full Access installed with an aim of Access Runtime (free version). It was introduced in version 2007, so you will need to develop your application using at least version 2007

That is not actually true. The Access runtime was first introduce with Access 97. I have been deploying Access Runtime based applications with Access 97, 2000, 2002, and 2003. With these versions you had to purchase the Access developer extensions to get the license to distribute the Access Runtme. Starting with Access 2007  the Access developer extensions and the license to distribute the runtime become FREE!

Boyd Trimmell, Microsoft Access MVP.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 166 total points
ID: 39823150
>#2) The data already resides in MS SQL Server...is there a better way to use SQL Server as the back end?
Taking a different angle then what's been covered, if there is any odds that the server or database will change (DEV-TEST-PROD, upgrade, whatever..), you'll eventually want to add functionality to manually relink all tables, and maybe even the ability to enter a server.database name somewhere, and the function links to that.

That question has been answered here multiple times, so you can search for it.

>#3) Which version of Access would be best to use?
>#1) I want to distribute the application to all people in the company
In this case you're likely going to be bound to the version of Office everyone has.  Failure to do this may result in bigtime dependancy issues if you want to write custom VBA code to anything outside of Access, such as Excel or Outlook.  This also means that you're on the hook for upgrading the app when your company upgrades its version of Office.  There are a few pain points, specifically going from any 32-bit to 64-bit Office/OS, but for the most part upgrade issues are minor.

>#4) Any other words of wisdom?
Compile it as an .mde, which will (I think) bloat less, requiring fewer repair-compact.
How many users will get this app?  My guess is if it's more than a dozen, you'll realize quick it will be a big pita to deploy new versions of the app whenever something changes, and wish to automate that.
<Shameless plug> I wrote an article on Table Based Access Variables that is damn-handy, and is necessary setup for one time when I pulled off the above using a little VB6.exe executable, and a variables table for the version number.
If there is an auditing component to your project, i.e. the need to know who changed what when, SQL Server triggers on your tables, inserting to an audit table for each actual table, would be a good solution.
Keep asking questions on EE.  Just in this question, the experts here have enough firepower that they've likely done exactly what you're doing now.

<Another Shameless Plug>  Hey Boyd, if you can give me some helpful tips on how you got your MVP, let me know.  My award deadline is tomorrow.
0
 
LVL 84
ID: 39823593
Is everyone in the company located in the same physical space? If not, and if you have remote users you will need to consider that as well. You could still use Access, of course, but with remote users you'd need to consider something like a Remote Desktop system.

If you have an IT department you need to get their input as well. Often you'll find significant push-back from IT regarding Access applications, and you'll find very little support among that group. They have absolutely no problem supporting a VERY poorly built .NET application, but they'll scream like wounded wildebeest if you give them an Access app regardless of how well it's built.
0
 
LVL 57
ID: 39823935
One comment on developing in the same version; you really don't have to, but it helps a lot and is somewhat dependent on the app.

If your Access app won't use any other office component (like Outlook for sending e-mail), then you can develop in any version you want, as long as you use SageKey installation scripts.

Doing so gives you a totally isolated install.  Users can have any version of Office installed and there is no repair/reconfiguration process when they switch back and forth between their version and yours.

 I think though you need to detail out a bit more about this app, number of users, etc before we can go further.  For example, Scott's point about remote users is an important one, SQL Server back end or not.

 I guess the only other comment I'd make is I would forget about Light switch.   It seems to be going no where and the few that I know that have tried it were not all that impressed.

The other point when thinking about Access is that it's clear from the last three versions that Microsoft has no intentions of furthering the desktop development capabilities in Access.   It's all about the web now.   All the new work in Access since 2007 has been all geared towards that.

 Certainly you will be able to do desktop development in it for some time, but make sure it will do what you want it to do.   For example, 2013 eliminated command bars, so from 2013 and up, your stuck with the ribbon no matter what.   DBF support has been dropped.  And things like addressing the VBA reference issues are never going to get fixed, nor feature requests like extending the Screen. object for better control of the main Access window will happen.

Jim.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39827400
Hi,

to mention something about the "ADP is dead" or "A2003 is dead" and such things: I often hear such arguments and the explanation is that MS has announced that these are dead. That's often the only reason why people stops using such software - because MS said you should not use it anymore as there will be no further support for it.

I say: That's not a reason to stop using a software as long as there is an operating system which is able to run that. In the last 30 years of developing programs with MS products I simply NEVER needed to call MS support for anything, they normally have done everything they can to create a good software and the support lifetime for the very most products was long enough to have any issues fixed so you have anything you need in all the service packs and hotfixes which you can download and use for any future installations, even if MS would remove them for download.

I know people who are using A97 to develop new systems - why not? I personally think that A97 doesn't look very good and is not very comfortable for the developer - but if you would invest enough time to create good looking frontends, nobody would even know that it is A97 except for a splash screen (if there was one in A97, I don't remember).

The argument "new is better" is of course completely wrong, especially in Access. Access seems to be the playground for MS to insert and remove technologies like they want to see if that's good for selling the product. It is clear that this was never meant as a professional development platform, at least not since .NET was invented and Access was a concurrence for their own new development product line. MS recommended using DAO, then ADO, then DAO again. MS recommended MDBs, then ADPs, now MDBs (ACCDBs) again. MS has inserted Datapages for Intranet usage, then removed them. MS has inserted database replication for Access databases, then removed them. They have inserted ADPs and now in A2013 removed them. They had nearly no change in Macros all the time until A2007, then pumped that up to a "development system" with debugger and so on in A2013, although VBA was the only real professional development system in this product, and it is until now. But it seems that MS wants the users to use Macros instead of VBA (web applications can't be build with any single line of VBA, you are forced to use Macros).

And, and, and....

So please, go away with arguments like "the newest is the best" or "MS has said this product gets no support anymore, so don't use it anymore".

The reality is: Choose the product which will give you the best result for your situation and environment and where you already have the needed licences in case of older software or where you don't need licences in case of Access runtime since A2007.

SQL Server: The database in my current big project was developed using SQL Server 2000 with A2003 and when A2007 came out, it was decided to redevelop the old database (from year 2001) to A2007. The only real advantage was a little bit better look of the frontend, but it was heavy work of redeveloping that by the original programmer and he left out a lot of things because lack of time so the new one was only 30% of the original. Real step forward only to use a new Access... that was in 2008.
The backend was still on SQL Server 2000 and when I came to the project I recommended moving it to SQL Server 2005 because it has a lot of real advantages over 2000 and as there is a compatibility mode that can be done very easy (and was in fact no real problem). Since then I moved the ACCDB to ADP (although MS had recommended not to use ADP in new project..). It was really difficult at the beginning because some things doesn't work the same in ADP as in ACCDB, but it was a real step forward for more professional results as since then I could use ALL the SQL Server features directly in the frontend without any problems of converting datatypes with JET/ACE or being forced to use the really uncomfortable Access SQL (in comparison to what T-SQL can do) because I can use T-SQL in Access directly now. Not to mention the need for ACCDB to link any table/view to be able to use that (if I use DAO) and delete and recreate them any time I make a little change on the server while developing, that's really annoying and time-consuming.
Currently we decided to redevelop the whole database completely new, but not because of support lifecycles or because we want to have the newest products. It's only because the original one was developed by a hobby programmer and has so many issues with a bad data model and many more so now we create a really bigger one with a lot more features even than the original one in 2001. The environmen?
A2010 as ADP with SQL Server 2008 R2 running both on Windows 2008 R2 64bit servers.
Why ADP? Because with ADPs it is a really more professional way of creating frontends with SQL Server. It doesn't interest anyone here what MS says because all what we need is a number of licences for A2010 for the developers and all the users will be connected through a terminal server running A2010 runtime. So I have never an issue with any kind of Office update on the user clients as they never get any Access version locally installed, they work in a never changing remote environment (could also run Windows 2000 server, the user would never know and don't need to know).
Why SQL Server 2008 ? Because that's the last version which A2010 ADP supports. As an ADP is dependant on the known version an A2010 can only be developed knowing SQL Server 2008, SQL Server 2012 was of course developed later. In opposite to ACCDB/MDB an ADP can only (stable) use the latest older version than the Access version - but I often see projects where ADPs where used to create frontends for newer SQL Server versions.

So in my case I can say: I know that the database server will not be changed in the next 10 or 15 years, the same with Windows server 2008. As I know that this database will be replaced in this period by a more global product I can develop this with no problems using a software which MS has recommended not to use anymore.

So in the end you must decide on your own what you think is a good idea or not, we can only share our experiences in our environment.

.NET is really a more professional way but also more time-consuming in development, not so easy as Access development.

I also tested Lightswitch for a short time only creating a really simple single-table-frontend and was surprised that such a bad product ever reached the release state - don't use that in professional development, it may be good to give someone a quick overview what the end product could look like but a piece of paper with a frontend drawing would be better for that. If ever someone said that Access frontends are unprofessional they never saw Lightswitch...:-)
It's a try to give non-developers the possibility to create .NET applications - the same nonsense like macro and "one-click-development" in A2013 which in my eyes is a toy but nothing more, with the removement of ADPs the last professional feature was gone in A2013 so I will not buy that anymore.

My thoughts about that, not meant to be "the best way", "the only way" or anything like that, only my personal opinion.

Cheers,

Christian
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39827782
1) Use the Access Runtime engine for users without the full version of Access.
2) SQL Server is fine as a BE for Access.  Do NOT create an ADP.  There is no advantage and only disadvantages.  I personally never adopted the ADP because an Access database that didn't support "Access" tables simply didn't work for me.  With an ADP, you are completely locked into SQL Server and can't even link to an Excel workbook.  MS deprecated the ADP because you can get equivalent performance and much more flexibility by linking to ODBC tables.  The key is to understand how Access interacts with ODBC databases and design your application to take advantage of that.  There are lots of articles on client/server Access and they all start with binding your forms to queries that use selection criteria to limit the number of rows returned.
3) I prefer A2010.  It's been around long enough to be stable.  A2013 is all about the web and gives you no new functionality for client/server.  I also really dislike the metro look.  It is too washed out and hard to read.  As has already been mentioned, if you are not interacting with any other Office application, you should always go with the latest version.  If you do interact with other Office apps, it is easier if you use the version of Access that matches your user's version of Office.  If you have mixed environments then you will have to bite the bullet and use conditional compilation so you can do your design work using early binding and distribute using late binding.  Access has no trouble "promoting" library references but it cannot "demote" them.  So if you develop in A2007 and a user has A2013, all will be well but if you develop in A2013 and the user has O2007, the app will fail because Access cannot translate the references to O2013 to O2007 even if you are not using O2013 features.  Conditional compilation solves this problem.  Early binding gives you the advantage of intellisense while you are coding as well as compile-time error trapping.  with late binding, you get no coding help and many errors will not be discovered until run time.
4) If you think you might need some web capability, I would go with A2013 and use a hybrid solution.  The bulk of the application will be client/server and a few forms will be web enabled.  The web parts of Access are not ready for prime time so unless you have a "toy" application, the feature is pretty useless.  You have no embedded language and are limited to macros.  The only good thing about the A2013 web app is that it uses SQL Server/Azure as the data store rather than SharePoint lists which are not a relational database.  Do NOT attempt to create a web app with A2010.  MS has already deprecated that functionality and there is no conversion path.

Don't worry about distributing the FE.  There are a couple of tools on the market that will do it for you or you could write your own.  The easiest solution is to have a "version checker" database.  This is the database that the user's shortcut points to.  It compares the version of the local copy of the FE with the server copy of the FE and if they are different, downloads a new copy of the Fe from the server.  It then passes control to the local FE and closes itself.  

You will also need to use something like SageKey to do the initial installation.  This will get the runtime installed and also set up trusted locations so Access will stop giving you security warnings (A2007 and newer are real complainers).  If you have good IT support and the users are all internal and connected to your network, the IT support people can handle the installation of the runtime and push out registry key updates to fix the security warnings.

If you have off-site people, the best solution for them is Citrix or terminal services.  They can connect remotely to the same BE database all the local clients use.  Just make sure to impress on the admin that each Citrix user REQUIRES his OWN copy of the FE.  Don't let them create a shared copy.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39827849
Hi Pat,

to correct an often heard argument against ADP: You can of course link any form you want to any other database than SQL Server. ADO can open and edit any database where a driver exists and so you can have forms which opens SQL Server backend as well as others.

A not well-known feature of forms is that you can assign a recordset to it directly which can be either DAO and ADO (not only in ADPs). And so you can open a recordset by code and assign it to the form.

The only thing you can't do directly is to mix i.e. an Oracle view with a SQL Server view in one query like DAO can, but if you really need that you can simply create a linked server on SQL Server so you can create a view which uses both resources and then open that in an ADO recordset (ADP or not). That includes Excel files if you really need that.

You only need some other ways to achieve the same but of course you're able to do the same in ADP (I used the method above to let the users work with a PostgreSQL database in my current ADP frontend).

It's nonsense to say "there is no advantage using ADP", there are a lot, beginning with working with ALL SQL Server objects (including stored procedures) directly, don't having the data conversion and relink table problems (because there's always JET/ACE in between the frontend and the backend where I never need that), and ending with things like a very much faster filter using ServerFilter property - this one works always where the Access filter crashes with too many records.

Cheers,

Christian
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39827890
Hi Christian,
FoxPro had some really good features also.  In fact, I'm having trouble replicating one with Access.   Access doesn't seem to be able to support down then across for multi-column subreports (I have another thread going on this one). That doesn't mean that I would recommend creating a new application using FoxPro.  I know that ADP's had some fervent supporters and it sounds like you are one of them but I wouldn't call it Access.  Same as I wouldn't call web apps "Access".  ADP's haven't been improved since A2003 and may not even work at all in A2013.  They have reached the end of their life cycle.  MS makes decisions based on usage and we may not always agree (personally, I think they should have fixed ULS rather than trashing it) but ADP's had very few adopters.  A lot had to do with the fact that forms/reports were different as well as having to learn T-SQL, etc.  You would have been better off creating a project using VB and not had the other limitations imposed by "Access".

I agree that Jet/ACE are way too chatty when it comes to communicating with the server.  I was appalled when I first saw the traffic they generate.  But, somehow they  get good response if you understand how to build queries with criteria and you still have the option of stored procedures and views if you need them.  Perhaps MS could spend the time saved by deprecating ADPs on tightening up communication with the server to reduce the traffic.
Pat
0
 
LVL 57
ID: 39828025
<<It's nonsense to say "there is no advantage using ADP", there are a lot, beginning with working with ALL SQL Server objects (including stored procedures) directly, don't having the data conversion and relink table problems (because there's always JET/ACE in between the frontend and the backend where I never need that), and ending with things like a very much faster filter using ServerFilter property >>

  While it may be true that it's too strong of a statement to say "there is none", I'd have to say there's not many upsides and a lot of downsides to ADP's

 Working directly with SQL objects, sure, but you can work with SQL objects indirectly as well, and it's not all that much extra effort.  ADP's are also limited to what Access knows about in SQL.  Already there have been a few instances where an ADP cannot take advantage of things in SQL, and Microsoft needed to come along and make adjustments (new data types probably being the biggest issue).

 Moving forward, that's no longer going to happen.   You could easily get stuck.  SQL only stays compatible for three releases prior, so at some point, your are probably going to run out of options.

 As for the relink and data conversion, there are ways of working without JET in the picture and not use an ADP.  And for filtering, there's always stored procedures and views.

 Personally I've never seen a clear "blow my socks off" advantage to using an ADP.

 With all that said, certainly if a language/product gives you what you need and as long as you can continue to run it under an OS, then your set.  Visual FoxPro apps are a good example of that.  New ones are still being written even today.

 But without a product being updated, you always run the risk of having a compatibility problem at some point, and no way to fix it.

 I'm waiting to see how VFP apps fair in the Win 8 world.   We got a bit of a reprieve with everyone avoiding Windows 8 for the most part, but sooner or later Windows will fundamentally change and we won't have any good options staying current.

 Access desktop/client applications are not quite as bad off as VFP, but are in the same boat.   If your paying attention, nothing is being done on the desktop side anymore and hasn't been since A2007.  

 It's all the web and nothing but the web for Microsoft (and Access) now.

Jim.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39828424
Hi Pat,

I wouldn't say that it is not Access only because of the lack of the database engine. The most parts of Access are forms, reports, macros and modules where you have only one database engine with tables and queries. And that's the big advantage of the other parts of Access without the database engine: A very good and quick development possibility which no other development environment currently offers (and that include VFP also although I loved that in the past when it was FoxPro for DOS and FoxPro for Windows until Microsoft bought them, ripped their Rushmore technology and implemented that into Access when Access was a real bad database engine before).

I left that product only becaue it was too expensive for me to buy that for private use when it was moved to VFP. Again a sad software story where a very good product was moved to a dead rail by Microsoft only because of concurrency and marketing.

"MS makes decisions based on usage"

That's a big laugh, sorry - you do not really believe that? There are a LOT ADP developers and even companies which are developing only using that, the forums are full of developers which are angry about the removement of ADPs. But independant of Access or ADPs - MS makes the same as any other company which want to earn money: They implement or remove features where they think that the people need that (in their eyes). If the usage would be a decision base - why do we have Windows 8 with Metro or Office with Ribbons (although I personally like them) or movement to web, macros and cloud and such stuff? You can't be innovative by looking how many people using a feature. You can only try out and see how it sells and if the cost to develop something is higher than the cash you earn selling the product then it is time to remove it. As Access especially was always meant to be an "easy to use Office product for the end user or power user" and not for us developers the main focus clearly is on selling the product to as many people as possible. Really NO professional developer EVER wanted to use macros and you see only rare questions about that as even no developer works with that - but MS thinks an end user would be interested in that to create a database "on the fly". THAT is the focus, and if they would look on the usage macros would have been removed after first try to implement it. What we see is that indeed only the more professional features are removed one by one, like ADO, like replication, like ADP and so on. I wait for the day when VBA is removed and macros are the only way to "program" Access. That's the day when Access dies.
I don't know what you mean with the report thing but maybe you can post the other thread link, would be interesting to see.

Hi Jim,

ADPs are only limited in the SQL of the older SQL Server versions (like I said above, i.e. A2010 knows only up to SQL 2008). That's a problem only if you are forced to use a newer database server. Or maybe I misunderstood your sentence.

"SQL only stays compatible for three releases prior"

That I really didn't understand - or do you mean because SQL Server 2008 will be the last one really supported as A2010 is the last with ADP? That's true, but I personally can live with that as newer versions have currently no really interesting features for me personally (as I said, it's only my opinion, in my environment that's no problem).

"As for the relink and data conversion, there are ways of working without JET in the picture and not use an ADP.  And for filtering, there's always stored procedures and views."

Yes, but only the same which I have in ADPs: Using ADO and assigning a recordset. But in ADP I can also use a name of a view or a name of a stored procedure as RecordSource and use the InputParamaters property (which does not exist in ACCDB) to fill the parameters dynamically without any code.
I would be interested in the way you think of to do similar things with ACCDB. You can't use stored procedures as linked tables (or to be more exact: You can, but only with some dirty tricks) and views cannot have parameters except if you are filling a parameter table and using some joins to get what you want - really very roundabout way but not what I think of an easy development system. Not to think about filters which are really slow and full of bugs in Access.

"But without a product being updated, you always run the risk of having a compatibility problem at some point, and no way to fix it."

That's of course true, you would i.e. not be able to install Windows 95 on a modern computer as all the needed drivers are not available anymore. But we do not talk about an operating system where you have two worlds which must be compatible: The hardware and the software. We talk about pure software which only have the need to run in the current OS. And surprisingly, you are able to start Word for DOS in Windows 7 64 Bit if you really want to use such old software. So what I want to say all the time: It is not that the software is not able to be used anymore, it is the hype of always be at the most modern software with the best lifecycle support. Where in fact I'm sure that 90% of all Office users would only use the same 10% of the software which i.e. a Word 2000 offers the only reason to go to a new version is to be file compatible with other Word users in the world. We do not have that issue in Access, so in the described environment where you have a terminal server as frontend you do never need to update the frontend because of a new Office version. My users get Office 365 these days and so I can never be sure which version they currently running. With the Remote App from Windows 2008 server that is no longer an issue and so I do not see any reason why I ever should update the Windows server, the Access ADP frontend (file type is A2003 up to A2010) or the database server. They all do what they should do very good, this is the absolute dreamteam, and so they will also be able to do their job in 2040 although there will be Windows 19 or so at that time.

I personally do not follow this hype because I'm sure that there will always be a way of using the software without the need of buying new licences only because MS tries to want be buy new software. The best reason comes from Microsoft itself, if you look at the big desasters like A2013, Windows 8/8.1, Windows Server 2012 with Metro to only mention a few...maybe Bill should come back...;-)

Cheers,

Christian
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39828675
Christian,
If the usage would be a decision base - why do we have Windows 8 with Metro
Apparently some people in high places drank some bad Kool Aid.  It seems the whole senior Windows team now has other positions.  I guess the public reaction hit the fan and it was ugly.

When MS Added the ADP "feature" it wasn't something they could simply plug in.  They completely rewrote Access. You must have noticed that there were differences in how all the objects worked.  So essentially they created a parallel universe.  It looked similar but acted differently.  That's why I said it wasn't Access.  Not because of using SQL Server as a BE but because the functionality in the FE was different and grew further behind with each new release of Access.  It is very expensive to maintain two parallel code bases and that is why MS abandoned the ADP.  They added DAPs and then removed them also.  So they innovate and then discard the features that are too expensive to maintain for the few users who adopted them.  Macros are different and the majority of Access users are more likely to use macros than VBA.  That is why they exist and even grew more prominent with A2007 when they got error trapping features.  Professional Access developers are a tiny minority.  In fact, "real" programmers think that using the words "professional" and "Access" in the same sentence is an oxymoron.   I happen to disagree but then I left mainstream IT 20 years ago because the inmates were running the asylum.

The really worrisome thing about A2013 is that it no longer reads A97 .mdb's.  That is going to cause a HUGE issue in a few years as people are forced to upgrade because of the licensing model MS is now pushing and they find they have to do it in steps.  I recently converted a 2.0 database to A2010 and it was truly a PITA.  I couldn't install 2.0 on any of my computers because they are all 64-bit and didn't support the 16-bit software and Access stopped reading 2.0 several versions ago.  I ultimately found an old copy of A97 that I installed on the client PC to do the first step of the conversion.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39828861
Hi Pat,

yes, there are some additional properties in some objects in ADP which are not available in ACCDB (as you cannot use them there like InputParameter or ServerFilter) but there's really  no big difference between ACCDB and ADP in case of functionality. The big difference was the implementation of ADO in the past which needed a lot of rewriting and I think with the lost of ADP now it will not take much time until they also completely remove ADO from Access (as ADO is also a concurrency to .NET).

"Macros are different and the majority of Access users are more likely to use macros than VBA."

You do not really believe that? Where do you have this information from? I really know nobody who uses macros. I also write in a German beginners forum where a lot of newbies are writing and even there there is as good as nobody who ever asks something about macros. Maybe you live in another Access universe than I...;-)

What is the problem in removing A97 support? You always said that we should not use old software because of lifecycle and MS, so why is A97 removement a big problem?

Cheers,

Christian
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39829521
I spend a lot of time helping people in forums (not just this one) and I work as a consultant so I am constantly running into applications that need changes and they use macros for a large part of their processing.

The problem with loosing support for 97 is that many people never actually converted the data bases even though they are using newer versions of Access.  I also have a lot of old databases that I use for reference that I will need to convert before I install 2013.  I haven't installed 2013 because MS added no functionality that affected client/server apps but did deprecate some features so A2010 is the end of the line for things like TreeView.
0
 
LVL 57
ID: 39829782
@Christian,

<<ADPs are only limited in the SQL of the older SQL Server versions (like I said above, i.e. A2010 knows only up to SQL 2008). That's a problem only if you are forced to use a newer database server. Or maybe I misunderstood your sentence.>>

 That's what I was getting at.   It's tough to tell a client they need to stick with an older version of SQL, which Microsoft will no longer support.  Many companies won't operate like that.

<<"SQL only stays compatible for three releases prior">>

 I was talking about compatibility mode in SQL Server.  For example, 2012 supports DB's in the 2012, 2008, and 2005 formats only.  It doesn't support 2000.

<<but I personally can live with that as newer versions have currently no really interesting features for me personally (as I said, it's only my opinion, in my environment that's no problem).>>

  and many cannot.  For some being "on maintenance" is a requirement of the way they operate.  For companies like those, you may be forced to upgrade and unable to do so.

 That's true of any product/technology though.   It just seems like lately, that has become more of an issue as the world moves to the web.

  I should add by the way that I'm in agreement with you and squarely in the camp of "if it's not broke don't fix it".    I still mostly use A2000/2003 when I'm dealing with Access and have not bothered with A2007 and up.

  As long as it does the job with a minimum of hassel, then that allows a client to leverage their investment as much as possible.

<<I would be interested in the way you think of to do similar things with ACCDB. >>

 What I was speaking to was ADO recordsets based on a stored procedure.  From A2000 and up, you can bind a form to a recordset in code.   Certainly not as neat as an ADP, but it's doable.

 As for filtering, I've never relied on Accesss built-in filtering or orderby.   I present controls to the user and do everything based on SQL.

<<The best reason comes from Microsoft itself, if you look at the big desasters like A2013, Windows 8/8.1, Windows Server 2012 with Metro to only mention a few...maybe Bill should come back...;-)>>

 Can't argue with you there!  Seems like Micosoft has shot itself in the foot to a certain degree with this massive move to the web and leaving the desktop behind in the process.

Jim.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39830074
Hi Pat,

I didn't knew that A2013 also lost support for ActiveX controls which the common controls are, is that really the case? If yes, then another reason for me to never buy that.

Hi Jim,

thanks for clarification in all the points.
I thought you maybe have another way of using stored procedures with ACCDB, sometimes such discussions shows some non usual ways of doing something and I'm far away of knowing anything about Access. But using ADO and a recordset would be the only way as far as I see (except some VBA manipulations on a linked table) - and that's what I meant with "using all SQL Server objects". As long as ACCDB has no real support other than assigning recordsets for stored procedures it is really no serious alternative for me.

I think our discussion has now brought enough light from all sides for the thread owner...;-)

Cheers,

Christian
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39830077
Hi Pat,

I wouldn't say that it is not Access only because of the lack of the database engine. The most parts of Access are forms, reports, macros and modules where you have only one database engine with tables and queries. And that's the big advantage of the other parts of Access without the database engine: A very good and quick development possibility which no other development environment currently offers (and that include VFP also although I loved that in the past when it was FoxPro for DOS and FoxPro for Windows until Microsoft bought them, ripped their Rushmore technology and implemented that into Access when Access was a real bad database engine before).

I left that product only becaue it was too expensive for me to buy that for private use when it was moved to VFP. Again a sad software story where a very good product was moved to a dead rail by Microsoft only because of concurrency and marketing.

"MS makes decisions based on usage"

That's a big laugh, sorry - you do not really believe that? There are a LOT ADP developers and even companies which are developing only using that, the forums are full of developers which are angry about the removement of ADPs. But independant of Access or ADPs - MS makes the same as any other company which want to earn money: They implement or remove features where they think that the people need that (in their eyes). If the usage would be a decision base - why do we have Windows 8 with Metro or Office with Ribbons (although I personally like them) or movement to web, macros and cloud and such stuff? You can't be innovative by looking how many people using a feature. You can only try out and see how it sells and if the cost to develop something is higher than the cash you earn selling the product then it is time to remove it. As Access especially was always meant to be an "easy to use Office product for the end user or power user" and not for us developers the main focus clearly is on selling the product to as many people as possible. Really NO professional developer EVER wanted to use macros and you see only rare questions about that as even no developer works with that - but MS thinks an end user would be interested in that to create a database "on the fly". THAT is the focus, and if they would look on the usage macros would have been removed after first try to implement it. What we see is that indeed only the more professional features are removed one by one, like ADO, like replication, like ADP and so on. I wait for the day when VBA is removed and macros are the only way to "program" Access. That's the day when Access dies.
I don't know what you mean with the report thing but maybe you can post the other thread link, would be interesting to see.

Hi Jim,

ADPs are only limited in the SQL of the older SQL Server versions (like I said above, i.e. A2010 knows only up to SQL 2008). That's a problem only if you are forced to use a newer database server. Or maybe I misunderstood your sentence.

"SQL only stays compatible for three releases prior"

That I really didn't understand - or do you mean because SQL Server 2008 will be the last one really supported as A2010 is the last with ADP? That's true, but I personally can live with that as newer versions have currently no really interesting features for me personally (as I said, it's only my opinion, in my environment that's no problem).

"As for the relink and data conversion, there are ways of working without JET in the picture and not use an ADP.  And for filtering, there's always stored procedures and views."

Yes, but only the same which I have in ADPs: Using ADO and assigning a recordset. But in ADP I can also use a name of a view or a name of a stored procedure as RecordSource and use the InputParamaters property (which does not exist in ACCDB) to fill the parameters dynamically without any code.
I would be interested in the way you think of to do similar things with ACCDB. You can't use stored procedures as linked tables (or to be more exact: You can, but only with some dirty tricks) and views cannot have parameters except if you are filling a parameter table and using some joins to get what you want - really very roundabout way but not what I think of an easy development system. Not to think about filters which are really slow and full of bugs in Access.

"But without a product being updated, you always run the risk of having a compatibility problem at some point, and no way to fix it."

That's of course true, you would i.e. not be able to install Windows 95 on a modern computer as all the needed drivers are not available anymore. But we do not talk about an operating system where you have two worlds which must be compatible: The hardware and the software. We talk about pure software which only have the need to run in the current OS. And surprisingly, you are able to start Word for DOS in Windows 7 64 Bit if you really want to use such old software. So what I want to say all the time: It is not that the software is not able to be used anymore, it is the hype of always be at the most modern software with the best lifecycle support. Where in fact I'm sure that 90% of all Office users would only use the same 10% of the software which i.e. a Word 2000 offers the only reason to go to a new version is to be file compatible with other Word users in the world. We do not have that issue in Access, so in the described environment where you have a terminal server as frontend you do never need to update the frontend because of a new Office version. My users get Office 365 these days and so I can never be sure which version they currently running. With the Remote App from Windows 2008 server that is no longer an issue and so I do not see any reason why I ever should update the Windows server, the Access ADP frontend (file type is A2003 up to A2010) or the database server. They all do what they should do very good, this is the absolute dreamteam, and so they will also be able to do their job in 2040 although there will be Windows 19 or so at that time.

I personally do not follow this hype because I'm sure that there will always be a way of using the software without the need of buying new licences only because MS tries to want be buy new software. The best reason comes from Microsoft itself, if you look at the big desasters like A2013, Windows 8/8.1, Windows Server 2012 with Metro to only mention a few...maybe Bill should come back...;-)

Cheers,

Christian
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39830086
...hmmm...I really don't know why this old posting appears again now...?
0
 
LVL 57
ID: 39830234
Quite odd...something ran amok.  You didn't have a second page open by chance did you?  Maybe to check spelling or something?

I've done that in the past (or done a cut and paste and miss doing the cut, so I end up with the previous paste data).

Jim.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39830871
Not that I would know...doesn't matter. Maybe some Gremlin...:-)
0
 

Author Comment

by:GenesisTech
ID: 39837437
WOW - You guys are great. So much information. It will take me a day or 2 just to read, digest, and understand it all.

The hardest part will be figuring out who gets the points because there are a few of you that deserve the points at a minimum.

I will post again after I have read and digested everything.

THANKS TO EVERYONE!
0
 

Author Closing Comment

by:GenesisTech
ID: 40366216
Thanks for everyone's input. Great info here and it is much appreciated!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now