Access Experts: General discussion thread for Access Zone (13-Jun-2012)

Chrome and larger than FF at the moment - Left side

mx

Chrome - left:
bottom:

I'm far from civilization this week and next too.  May be you can help with this question:
https://www.experts-exchange.com/questions/27740235/MS-Access-2010-auto-populate-id-on-time-entry-form-when-clicking-Add-new-Record-Button.html

If anyone has a chance, please take a look at: https://www.experts-exchange.com/questions/27782653/Set-LinkMasterField.html

Issues is setting linkChildFields and LinkMasterFields for more than one field from code.

User accepted a couple of solutions, but never did get an actual solution.  The OP just worked around it.

Got another one that needs other experts (JD and I are stumped or do not have the time).

https://www.experts-exchange.com/questions/27786055/Calling-afterupdate-events-with-variables.html

OP is trying to figure out a way to call an event procedure associated with multiple form/controls, from within a public code module.

Example, pass a reference to a form and control object to the public subroutine, do some processing, then call the AfterUpdate event for that form/control.

A pointer to some congratulations due (hopefully) today! — (°v°)

There was some discussion on another thread about saved queries vs temp queries, and I saw a comment on a LinkedIn thread as well that implies that saved queries are no longer "quicker" than a query developed and run via VBA.

For some time, I have been under the impression that if I want to open a recordset in VBA, it is quicker to run a saved parameter query than a SQL statement written in vba.  Is that still a valid statment?

Dale

Many years ago, I did a performance comparison for Insert speed.  If memory serves me well, the parameterized query was a little faster than dynamic SQL, but not by much.  Of course, I was measuring a query that changed data as opposed to a select query. In queries that return multiple rows, the performance difference is negligible -- overshadowed by I/O.  

Also, there is an initialization overhead that you have to consider, especially if your runs are repeated in a loop with little time between runs.  Your subsequent run will make a request of the database engine before it has had time to clean-up (garbage collection) from the prior run, eliminating the initialization overhead for the subsequent run.

I distinctly remember that the best Insert performance method was the one using a recordset variable. (by a wide margin)

If you need some of that information I can look for it on my other system.

Dale,

<<There was some discussion on another thread about saved queries vs temp queries, and I saw a comment on a LinkedIn thread as well that implies that saved queries are no longer "quicker" than a query developed and run via VBA.>>

  In the past, when you used a SQL statement for a rowsource for a form, combo, or list control, those statements were always costed and executed each time you encountered them, unlike a saved query which was only costed on the first execution and then the execution plan was saved for subsequent executions.

  Microsoft made a change at one point where temp querydefs are now created for Rowsource SQL statements.   These are in the querydefs collection and have a Tilde (~) prefixed to the name.

  In VBA however, SQL Statements have always been and still are costed and then executed at each execution.

  Frankly, I think Microsoft  made a mistake with the change they made.  In some cases, it really is a benefit to have a SQL statement costed each time and then executed as some execution plans go stale and become inefficent.

  Given that, in the past, you used a saved query if you want to keep the plan and a SQL statement if you didn't.  You can no longer do that.  You now get a saved plan no matter what you do.

  To get that benefit back, you now need to assign the rowsource in code everytime.

Jim.

Dale,

 Re-reading your post, I'm not sure I'm taking about the same thing, or it may be that what is being discussed is being confused with the issue I commented on.

  But the short of it is:

 1.  SQL statements in code are always costed, then executed.

 2. Saved queries are costed once, the execution plan saved, and then not re-costed on subsequent executions.

  Which one comes out ahead depends on the nature of the data.   In some cases, re-costing each execution is not a bad thing if the data is very dynamic.

Jim.

Thanks, guys.

Jim,

What you describe is how I understood the process to work.  I'm anal with regard to my use of saved queries.  All my combos, lists, and form queries are saved with names like:

qry_frm_FormName
qry_frm_FormName_cbo_Product

and if they are cascading, they contain parameters that explicitly reference the other control(s) on the appropriate form.  While this does create form renaming issues, I use RF Find & Replace for all of those operations, so I don't concern myself with that issue.

Whenever I'm going to reuse a query for multiple purposes, I have started using TempVars in my criteria rather than form control references or create the query, and then build the criteria string on the fly.

"In some cases, it really is a benefit to have a SQL statement costed each time and then executed as some execution plans go stale and become inefficent."
On the other hand, this gets done each time a Compact & Repair is done, as I recall.

"Many years ago, I did a performance comparison for Insert speed. "
Just curious as to how many records this was against ?

mx

<<On the other hand, this gets done each time a Compact & Repair is done, as I recall.>>

  Yes, but that doesn't really get you very far.  A C&R does invalidate all the plans on saved queries, but first time their executed, the plan is saved and your right back to where you were (executing without a re-cost).  With a SQL statement in code, you always get a re-cost no matter what.

  A C&R may only get done once a week, but you might have a table that get's filled/emptied on a daily basis with the number of records varying  considerably from day to day.  In that case, you'd be better off with a SQL statement in code.

  I had an example of this once (might still have it even) that illustrated this beautifully; it invloved a multi-table join with sorted output.  With one of the tables almost empty, JET would execute the query one way, and with it full, would execute it in reverse.

  But if you used the first plan with a full table, execution time went from sub-second to almost 30.   On the whole, it was far faster to let it re-cost each time then it was to use a saved query and possibly use the wrong plan.

Jim.

so, is there a way to force a "re-cost"?

Would setting a querydefs SQL property to itself accomplish that?  Or is there a property of the querydef that could be used to do so?

How can you tell when a "re-cost" is done?

<<so, is there a way to force a "re-cost"?>>

  You have to edit the query and save it.

<<Would setting a querydefs SQL property to itself accomplish that?  >>

 Yes.

<<Or is there a property of the querydef that could be used to do so?>>

  Good question...it's always been stated that "if the query is changed", but it never has been stated what "a change" might involve.  For example, I'm not sure if changing the UseTransaction property would make a difference, but something like Unique rows certainly would (because it's changing the SQL statement).  

  It may be changing the SQL statement alone that does it.

<<how can you tell when a "re-cost" is done? >>

  You can't other then the query takes slightly longer to execute and the difference is so small, it's often hard to tell when that happens.  But there is no visible indication otherwise.

Jim.

Dale question: Would setting a querydefs SQL property to itself accomplish that?

Jim response: Yes.

If it's that simple, then a 4 line sub to loop through the query defs and reset the SQL of each query def when you open an application would force the "re-cost".  I assume that would only occur the next time the query is actually run, similar to a C&R.

Speaking of C&R, at one point, I saw a lot of recommendations not to use C&R on a regular basis.  But I have not seen that mentioned recently.  Is it considered acceptable to set "Compact On Close" to Yes with 2007 and 2010?

"  A C&R does invalidate all the plans on saved queries, but first time their executed, the plan is saved and your right back to where you were (executing without a re-cost)"
Humm.  Not sure that is accurate, but I will check the JET book tonight - seems it states this differently.

"Speaking of C&R, at one point, I saw a lot of recommendations not to use C&R on a regular basis. "
If there is anything I've ever said on EE ... TRUST ME ... that is total BS.  C&R IS the Number 1 preventative maintenance you can do to minimize corruption or pending corruption.

My (almost fully automated) nightly DB maintenance app, besides backing up ... Copies ALL 27 or so BEs in my 'data warehouse' on the server to local PC, executes a C&R, then copies back ... as well as backing up locally (with date/time stamp - 2 years history kept) and on the server.  In over 3.5 years of doing this, there has only be 1 or 2 instances of BE corruption (always repairable by a C&R) ... and those have been tracked to some very abnormal situations.

Keep in mind, as I have mentioned before - ALL of our FE's connect, in one way or another .. to the BEs over our 1Gbit WAN - 62 mile round trip from our physical location to the shared drives.  Some of the smaller dbs with 3-4 users have linked tables (usually 2-3). The larger apps with more users (10-90) employ a pseudo disconnected approach.

However, ALL users have a persistent connection to my main 'configuration' db - for the purpose of checking a forced shutdown flag every 3 minutes.  So, overall, there are multiple users with multiple FE's in use, all connecting to the config db. I have seen as many as 165 simultaneous connections, typical is 105-110.  This db has never corrupted.

Soooo ... no one is going to every convince me that C&R should not be done on a regular basis.

I should note that the each FE master (on the server) is copied to local workstation via my database loader - each time user clicks on the database loader desktop icon. User selects which FE they want to load, and loader copies that FE master to local workstation, as well as a library MDA and app icon.  No FE takes longer than 10-15 seconds to copy. App is opened, load releases.  App copies down some local lookup tables from config db (in most cases).

Long Live C&R.

"Is it considered acceptable to set "Compact On Close" to Yes with 2007 and 2010? "
That would not apply in my environment/scenario.  No need. User always gets a fresh, pristine copy of FE (fully C&R'd) each time they need to access a given FE.  And I don't recall an FE ever corruption on a local workstation. Even if it did, user simply closes and reloads from loader.

mx

Joe,

It's good to know that you are not passionate about this subject!

Despite our political differences, I'd love to sit down and have a beer, or something stronger, some day.  So if you ever head out to Virginia, let me know.  I'll do the same with CA.

Dale

:-)

<<If it's that simple, then a 4 line sub to loop through the query defs and reset the SQL of each query def when you open an application would force the "re-cost".  I assume that would only occur the next time the query is actually run, similar to a C&R.>>

   Yes, that would.  I'm not 100% positive that just setting it to the same statement would be enough though (think it would be).  You might have to set it to something else, save it, then put it back the way it was, and save it.

  But for some trivia, that actually was recommended years ago in the Access 2.0 Developers handbook as a way of getting the best possible performance on a DB that you just delievered, with the added step of executing the query as well so that is was saved in a compiled state.

<<Speaking of C&R, at one point, I saw a lot of recommendations not to use C&R on a regular basis.  >>

 Not sure where...C&R's have always been recommened to be done and on a regular basis.  In the past, a repair was seperate from a compact, and years ago it was suggested not to repair on a regular basis, but to only compact.  That may be what your referring to.

Jim.

@mx

I usually work with different sized blocks, commonly powers of 10 (=1,2,3,4) with 500-record transactions employed in the 1000 and 10000 block runs.

I see.  I'm thinking like a million records ....or I suppose, whatever recordset size might exist in a given environment.

I just recently saw somewhere ... possibly like say on Allen Browne's site (example) ... where ONLY saved queries s/b used ... like for combos, etc  But as JD noted, Access now saves SQL type Row Sources/Record Sources as ~WhatEverName saved queries ... sooooo ?

mx

@mx

I don't remember timing 100k or 1M records, but it is possible to do that if there is enough room.  The 500-record transactions allow us to look at the per-transaction times, regardless of the size of the block.  I tried larger transaction batch sizes, but saw diminishing returns past 500. There is probably some underlying formula that could tell us the optimal transaction batch size, but I've never seen one.

I tested the database on a local drive and on a network drive.
I tested the target table in the local database and as an attached table.
I also tested the time it would take to copy the raw data to a directory on the network.  That would also allow me to calculate the time it would take to copy the file and have a (server resident) folder-watching task insert the data into the server-local database.

A long time ago, I created a small program that would handle remote database changes. All of those changes were done by dynamic SQL, rather than just data inserts.  It would be slower than the recordset method I've performance tested, but offers much more flexibility.

I see.

" I tried larger transaction batch sizes, but saw diminishing returns"
Funny ... I once tested GROUP BY vs DISTINCT on a table with one field and increasingly number of records.  I forget which was faster initially, but after some number of records ... it reversed !!

Joe,

Now that you mention Allen Browne's site is where I got the information about not using Compact on Close.

As I recall, I actually had this happen to me.

I can see maybe ... in a single user enviroment, 1 db app (no be)  running some sort of relatively simple app - this might be useful, but other than that ... too many issues.

I'm late to the party.

About the saved query debate, I did use several versions ago a procedure to convert all SQL strings from forms, reports, and controls to saved queries, which is automatic now. Today, I often delete all automatic “~sq_*” queries to force them to be rebuilt.

I've also had my run-ins with Jet using the wrong execution plan, like Jim's case in http:#a38255446, many years ago. For one application, I could not take the BE home with me (for security reasons) so I used a fake BE with just enough dummy data for my tests. When connecting the FE, finalised at home, to the real BE, some queries were sometimes horribly slow. I learned that, for some reason unknown to me at the time, I had to build the FE on site. I admit that I had no idea what an execution plan was, only that it existed somewhere internally.

One last thing on Dale's initial question (way back in http:#a38254683) about a saved parameter query versus  a SQL statement written in VBA: I sometimes use anonymous QueryDef objects when I need the same SQL statement several times (e.g. in a loop) with changing parameters. This would be an intermediate solution, as the parametric SQL is evaluated only once, and then executed several times.

Interesting discussion!
(°v°)

So, to wrap up this discussion about saved queries.  

Saved queries are good, but not necessarily better.  They get reset after a C&R, and thus the execution plans are reevaluated the first time each query is run after a C&R.  Since there is apparently no good reason not to do a Compact on close, this would solve the issue of execution plans not getting reevaluated on a regular basis.

They are also reevaluated after the content of the SQL property of the saved query is changed.  So simply setting the SQL property of the saved querydef to itself and executing the query again would be another good way to force the execution plan to be reevaluated.

I agree with Markus, good discussion.  Thanks for your feedback.

I would have posted this as a question, but didn't want to have to give any of you guys any more points!

Dale   ;-)

Since there is apparently no good reason not to do a Compact on close, this would solve the issue of execution plans not getting reevaluated on a regular basis.

Here's the issue I'm running into -- I, and most of my co-workers, have 2k3 on our computers. A few have 2k7. So I have to program in 2k3 and the 2k7 user just have to deal with the security warnings. I'm just wondering if the compact on close would work from a 2k7 to a 2k3 db?

I would try it on a backup copy, but I don't remember any issues with that. In other words, A2k7 respected the A2k3 format. However, A2010 does NOT respect the A2007 format. Do NOT C&R a 2007 accdb using 2010. Your 2007 users will not be able to open the db.

Lee

"Do NOT C&R a 2007 accdb using 2010. Your 2007 users will not be able to open the db."
Amazing, since theoretically the file formats are the same ...

mx

That's what I thought too; until I had to create a new backend on a 2007 box, open the new backend on the 2010 box, copy my tables from the "corrupted" db, close the new db out of 2010, open the new db in 2007, set the tabe relationships, and finally C&R. I searched and found other users and Access dbas

Mobile version Submit button is too close to the bottom of this text window. >:/

I searched and found other users and Access dbas with the same frustration. My first and only indication of the problem was all my 2007 users seeing the "unrecognized database format" message, but the db opens fine in 2010. Actually, you can open and use the db in 2010, but do NOT C&R. I'm not sure what else might cause the problem, but C&R definitely does.

Lee

I'm just going to go with a SQL BE at this point then.

The company doesn't have a standard desktop model that everyone has to adhere to and I'm sure at some point the 2010 will sneak in and cause further havoc.

"I'm just going to go with a SQL BE at this point then."
ok, be that way then :-)

I'll be away for two weeks. Don't hesitate to take over any neglected question in which I participated. Cheers!

(°v°)

Markus,
Thanks for the heads-up!  Have fun...

Anybody have time to take a look at:  https://www.experts-exchange.com/questions/27829711/DSUM-not-auto-updating-on-Access-2010-Form.html

I'm sure I'm the only one here that didn't realize you could actually set a table as the SourceObject for a subform, but that is what he has, and it apparently is not performing in 2010 the way it did in 2003.

"set a table as the SourceObject for a subform, "
A query also ....

Joe,

I guess it never occurred to me; it says subform, so I always assumed it required a form.  Not sure why I would use that feature, but you never know.

I learned that here on EE a couple of years ago :-)

Not sure why I would use that feature, but you never know.

If you have a tabbed form with a lot of subforms 5-7+, you can open up only the data when the subform gets focus. That way you aren't loading all the data on the main form load, but only as you need it.

Hi

I think you might be mis-understanding the topic of conversation a bit Jim? (Sorry if you're not - just checking.)
The assignment of a Table or Query as the source for a subform control directly.  Not a form bound to such. (Where such assignment is indeed common.)
The fact that a subform control can accept a table or query as a source object would always very strongly imply it can be so assigned in code (in case that was the distinction you were making :-).

It's not anything I've strongly advocated, but it's a solution to providing truly open querying (creating or modifying a qdf on the fly and then showing those results in your UI) while housing said results in a form and so offering a modicum of control (e.g. preventing updates by locking it).  You get a form object that exposes the properties and events of a traditional form.  You've got enough to control your data.
You don't get events raised by the virtual child form though - so it's not a true form in the traditional sense.

That it might not work in 2010 is interesting (and disappointing - why provide the functionality, then remove it without highlighting as much).
I'll have to test that when I get a chance (never had cause to - but then don't use 2010 all that much).  No doubt 2013 follows suit. :-s

As I've always learned it you can build a sub-form as a standalone object, and then use it in the "master" form.

Let me relate how I used it, and then we can figure out if we are on the same page. I helped build a help desk/HW/SW/project tracking/etc. system. The data was nominally connected but not always convergent. So pulling up all 7-10 sub-forms and populating them with data for the help desk person to take a call was a waste of queries, memory, CPU, etc.

The way we did it was have the master form open and the first tab was a "dashdoard". The other 7-10 unseen sub-forms that were opened was the same "dummy form" with no objects attached to it.

Then when you clicked on tab 2, it had an on click event that opened the real "helpdesk" sub-form with the query assigned to it on the fly based on the userid. On some of the tabs the sub-forms it was assigned a generic query just because restricting the hardware generally didn't make sense. But if you were switched from the help desk ticket to the hardware tab it would filter it on opening.

The sub-forms did have a query bound to it, just to prevent the issues you have when you open a form in view mode.

Does that make more sense?

jimpen,

You can do the same with actual subforms, each designed for a specific purpose.  The difference is, that with actual subforms (even if they are in datasheet mode) you can assign code to events, which you cannot do when you use a query or a table as the sourceobject for the control.

That is why I indicated I cannot think of any circumstance where I would do it that way.

I have numerous applications which use tab controls and have subform controls on each tab.  And I use the tab change event to check whether the subform control has a source object assigned, and if not, I assign it to the appropriate form, and add the master child relationships.

Hello all; I'm back from two weeks vacation in the French Jura region, very nice!

I was going to react to the query as subform topic, especially to challenge the notion that form events are not available in that context, but then realised that this is a prefect topic for an expert question: About Queries used as Subforms.

Cheers!
(°v°)

I've searched around EE and I couldn't find the answer to this.  I'm going to be embarrassed if the answer is so obvious.  

What do the color dots represent in the list of top experts?/Ron

Green means that these members have moved up on the list.
Thus, some other are red.
The remaining are yellow.

/gustav

That makes complete sense.  Thanks gustav, I don't think I would have ever figured that out.  Is this explained anywhere on EE?  Or is this just knowledge passed on?

I was blind, but now I see :-)

Is it just me, or has the quantity of new Access questions asked each day gone down significantly over the last year?

<<Is it just me, or has the quantity of new Access questions asked each day gone down significantly over the last year? >>

 Hard to say.  From what I've seen, I think were close to what it's been for the past few years.

I tried getting some numbers out of the advanced search feature, but no matter what I tried, it kept saying "No Results".

Jim.

I'm sure one of the analysts at EE has the numbers.

SELECT Format(PostDate, "YYMM"), Count(*) as NewPosts
FROM someTable
WHERE ParentID IS NULL
AND Topic Like = "Access"
GROUP BY Format(PostDate, "YYMM")

I suppose this is a step up from being called 'dude' or 'the man'  :-)

LOL, I wonder how he came up with Matt?

My first thought was actually that he was confusing me for Patrick, who gets called "Matt" occasionally by Authors and Experts alike.

Hi All,

  As you know, EE has been promoting a series of webinars and podcasts to further the EE brand and promote the site over the past year and for Experts, it means more exposure. In the past, several of us have posted questions for "panel" discussions, some have done articles, and some presentations for user groups.

  With all that in mind, I recently fired off some ideas to EE HQ:

1. Access Panel Discussion - We've been having some off and on Expert discussion threads in the Access TA - Office 13 is almost here and I thought it might be good to get a group together for a Pod Cast and see how they feel about the new Office (possibly extend that to one or two experts for each of the Office products?  or do a series?)
 
2. Doing another webniar - "Your top ten Access questions answered" - Either me or a panel of Experts.   Alternate - Submit your Access questions and if chosen, win a EE T shirt.
 
3. Along the same lines as #2, a "stump the Expert" contest - not quite sure how to formulate this though.  Maybe nothing but a Q&A webinar?  If you stump the Expert, get a t-shirt.

 and the response was basically "What would you like to do?"   So is there anyone interested in doing any of the above?  This will take some commitment.  The webinar I did for example took a solid three days to put together, then a couple more between practice and and delivering.  Multiple people involved with one would probably compound things a bit.  Podcasts in contrast are easy, but would require some prep time if were to talk on a specific subject like Office 13.

 I'm willing to coordinate and spearhead the effort if anyone is willing to try.  So the question is, anyone interested in doing something and if so what?

Jim.

Newsworthy note:

Happy Birthday Access!

Who is twenty years old today...

Jim.

Actually yesterday (10-28):


Long Live JET, DAO & VBA !
Picture Credit: MVP Arvin Meyer

As Access 2013 approaches, it's interesting to note what will be dis-appearing from the product:

http://technet.microsoft.com/en-us/library/cc178954(v=office.15).aspx

Jim.

BTW the lid is off now on what's in Office 2013 and you'll start seeing content on Mcirosoft's web sites.

This is a good place to start:

http://msdn.microsoft.com/en-us/library/jj162978(v=office.15).aspx

 In regards to Access, unless it involves the cloud or office 365, you can forget it.  There's nothing new beyond that and many features have been removed.

 From a personal perspective, quite disappointing to see the direction it's going, but I guess that's the way the world is heading.

 I'm just wondering if by the time they get enough power back into the product they will have lost most developers.

Jim.

One notable feature removed - this totally blows:

AccessAccess 2003 toolbar and menus
      
Access

Fully removing the feature      

You can no longer create Access 2003 toolbar and menus that display in Access 2013. Access 2003 toolbars and menus are displayed in Access 2007 and Access 2010 without the Ribbon. They will not display in Access 2013 but will appear in the Add-ins tab in Access 2013. Custom Ribbon commands are supported.
      
Removing legacy feature.

====

"but I guess that's the way the world is heading."
Trust me ... write this down:  Eventually, the Cloud is going to burst!

quite disappointing to see the direction it's going
This has been going on for sometime.  In other words the writing has been on the wall for years.

Trust me ... write this down:  Eventually, the Cloud is going to burst!
I think I will just do that.  Any time-frame in particular? :)

I've got clients that are still running Office 2003, and only one has migrated to 2010, so I'm certain that 2007 and 2010 will be around for quite a while.

In the mean time, I think it may be time to learn C#, VB.Net, ASP.Net or some other platform.

I'm still doing my Access in 2003. I can't stand the <expletive deleted> ribbon. I can connect to pretty much anything with it. The security standards are reasonable.

I'm going to hang onto it as long as I can.

<<In the mean time, I think it may be time to learn C#, VB.Net, ASP.Net or some other platform. >>

 I took a detour with VFP (great product, but I got in that game a little too late, so it was a bad mistake) and am working getting on C# and VB.Net now for development.

 It really is a shame what their doing with Access as there is defintely a niche for a product that can connect to just about anything and has a simple yet flexible programming language to tie everything together.  Access is just a good all around handy tool to have in the toolbox.

<<I'm going to hang onto it as long as I can. >>

 For Access, that's what I'm doing.  Only have one client that has a few stations with A2007.  Everything else is on A2000/2003 and that's where I plan to keep them as long as possible.  If they need something beyond that, I will migrate them off Access.

Jim.

<<In the mean time, I think it may be time to learn C#, VB.Net, ASP.Net or some other platform. >>

I decided that years ago, started with Visual Studio 2005, and haven't looked back.

C# is a wonderful language, and with your database knowledge you are ahead of so many others (but behind so many more). The challenge is that there will always be something new or different to learn, but you work in an environment totally focused on development.

/gustav

Gustav,
I do want to learn something else as well.  Would you consider C# to be the best alternative?  Using an Access F/E seemed to be a good solution for a rapid development especially when working for a company that has a never ending list of software requests.  Would working in C# with Visual Studio allow us to continue to provide quick turn around times or would it take comparatively more time to develop GUIs and reports?

/Ron G.

It all depends on your preferences and earlier experience, but in any case be prepared for a learning curve that never ends. But if you intend to program for both desktop, the web, and Windows Phone, and the new Windows 8 RT (nearly similar to Windows Phone), there is no other way. And tons of learning material are offered at no cost for the browsing.

For rapid development, try out Lightswitch which is very fast but is so much different from Access.

All the basic Express versions of Visual Studio are free but - even if they lack some features - they are not crippled.

/gustav

Wow ... all you boys and girls heading away from Access. Great.  Less competition he he.
Long Live Access, JET, DAO & VBA :-)

Surface with Windows 8 RT ... a solution waiting for a problem :-)

mx

They will live long. We have clients still running powerful business apps in Access 2.0(!) and Access 97.

However, if you were in doubt before, you can't be anymore that - with the recent announcement from MSFT what has been left out in Access 2013 - Access is closing down as a development environment turning into a frontend tool for SharePoint only controlled by a lousy macro "language" aimed at power users. Not even script kiddies will touch this.

Though I will still use Access for ad hoc tasks, it is goodbye from me regarding development. It was a fun ride and I learned a lot but it was time to change horses.

/gustav

And we have been down this road before.  Anyone remember VB6, how about VBDOS or even better BASIC 7.0 PDS or Turbo Pascal.  I have used them all and they were all good tools in their time.  Yes, I have no doubt there are many applications written in those languages still running today.  And yes, I have colleagues who continually remind me that they have applications written in COBOL, that are still being used.  So what?

And let's get this straight: This has nothing to do with Microsoft.  If they could sell a million copies of MS Access or dare I say VFP they would still be producing it.  The truth of the matter is that they are pulling the plug, because sales have dropped where it is no longer feasible to keep on pumping out new versions.

There is also the issue that Microsoft Office in general as we know it is going to change radically over the next few years.  Just look at Microsoft Office 365 and you will get a glimpse of the future.  With current broadband limitations, fat clients such as MS Access are not a good option for cloud based solutions.

Yes, there will be a few die-hard fans of MS Access that will not want to part ways with it, as there was with the demise of VB6 and Karl Peterson's doomed effort to keep it afloat, and my many ancient COBOL programmers, but they are a dying breed and in years to come we will remember them fondly as we do cooper profession.

AC,

Cloud computing is supposedly the way of the future. The problem is that once you get off the east or west coast, the cloud start to become very wispy cirrus clouds.

In addition there are some functions that can't depend on the cloud as has been shown just this past week after Sandy stopped by. Hospitals have generators, but if their communications go down they have no patient data.

Prior to 9/11 MAE-East resided in a barely climate controlled, first floor room in a parking garage in Tysons Corner, Virginia. It carried between a third to half of all internet traffic on the east coast. If it had gone down it probably would have shut down NASDAQ, NYSE, most of the major media outlets, AOL, and a good chunk of the internet.

Add to that the number of hacks, cracks, and other data theft that is going on -- I don't want my data stored in the cloud.

"The truth of the matter is that they are pulling the plug, because sales have dropped"
Meanwhile ... Access 2013 is alive and well.

As far as the Cloud paradigm ... Eventually, the Cloud is going to burst!

Yes AC ... WE have been down this road before.  And with all due respect, I don't get that you are really all that familiar with Access ... and what it's **actual** capabilities are. Stop by some time, and I will show you ... and I suspect it will change your attitude.


Just sayin' ....

As far as the Cloud paradigm ... Eventually, the Cloud is going to burst!
Right you said that already and I asked for a time frame when you thought this would happen.  I am still waiting...

I don't get that you are really all that familiar with Access ... and what it's **actual** capabilities are
I have no doubt it has many fine qualities.  But the real question are they relevant?  And more importantly are they relevant going forward.

Stop by some time, and I will show you ... and I suspect it will change your attitude.
We can certainly do that at the 2013 MVP summit in February.  Together with the other dozen or so MS Access MVPs.

In the end it is not my attitude you have to change, but rather the corporate world.

Incidentally to all those that think I am a proponent of cloud based computing, I trust I am fully retired when that comes about.

<<
As far as the Cloud paradigm ... Eventually, the Cloud is going to burst!
Right you said that already and I asked for a time frame when you thought this would happen.  I am still waiting...
>>

 I don't think the cloud will outright burst, but it's going to be a while (ten years at least I think) before everyone commits to it whole sale and it will be mostly smaller companies.

  Right now it's on the up swing, but the more that start to use it, then the closer we come to some event occuring that will make everyone stop re-think what they are doing.

 It boils down to who do you trust to manage your data?  In the end, I think that will always come back to yourself and for a large company with the resources to run their own data center, the choice seems rather obvious.

  Where I see the connected world heading is back to the old centeralized main frame concept of running multiple users off a server.  With internet connectivity of sufficent bandwidth, you get the best of both worlds; fat app type processing, but with the added benefits of web style apps (no installs, no inter-connected systems, etc).  In short, low management.

Jim.

"and it will be mostly smaller companies."
At best. I can assure our large company will never be putting data on the cloud - starting with security reasons.

I read a report about 4 months ago about 'cloud data'.  Your data - whether housed by Google, Microsoft, IBM (et al) ... could physically reside in any part of the World (China, New Jersey, Mongolia, Japan et at). The report was pretty scary. And when a natural disaster occurs - like in Japan, your cloud could likely burst.  

There is no way in hell I would put any of my data in the cloud.  It's tricky enough just keeping hackers out of my laptops.  And the hacker community is escalating rapidly beyond the security community.  So, do the math.

"We can certainly do that at the 2013 MVP summit in February.  Together with the other dozen or so MS Access MVPs."
Actually, I meant stop by here at work.  Gonna be hard to transport the 1Gb fiber optic WAN to the Summit :-)

Anyway ... I'm not really disagreeing with any of you as to where 'things are headed'. However, Access has no sign of dying off yet ...

Love your optimism, Joe!

I and I suspect many of you who can recall back a few decades, have already heard all of this before:
"PC are just toys"
"I will be damned if my application/data will run on a GUI" in reference to Windows.
"I don't need no stinking mouse, I do things faster with the keyboard"

And much of what has been said here regarding cloud computing was said in the early 90's about  the Internet.  Trust me I feel your collective pain, but it is inevitable.  It will not happen this year and with any luck probably not even this decade, but it is just a question of time.  And don't feel that I am picking on MS Access, I am not, MS SQL Server as we know it is dead.  It is just a matter of time before it morphs into whatever is the latest incarnation of SQL Azure.

So true. And SQL Azure is reported as quite fast.

Recently I made some small Lightswitch projects. Interesting, a desktop app or a Silverlight web app by the flip of one switch.

And now an HTML5 client is in the oven:
Announcing the Microsoft LightSwitch HTML Client Preview for Visual Studio 2012

Note that it is a complete development environment in a virtual machine for download at more than 5.7 GB:
Microsoft LightSwitch HTML Client Preview for Visual Studio 2012

/gustav

Tuesday’s webinar: What is Access (and preview Access 2013) 15-Minute Webinar on Tuesday at 9:15 am Pacific Time:
http://blogs.office.com/b/microsoft-access/archive/2012/11/05/office-webinar-what-is-access-preview-access-2013.aspx

@ ""PC are just toys" et al
I never subscribed to any of those :-)

mx

"Tuesday’s webinar: What is Access (and preview Access 2013) 15-Minute Webinar on Tuesday at 9:15 am Pacific Time:"
Is anything happening ?  How to get audio ?

there is an 888 number that they flashed up at the start.

I'm not having issues, but it sounds like they had some.  

BTW, love the response to Mark's question:

"Access a very different technology from LightSwith (from a technical perspective). Also, from a user perspective, Access is targeted at non-programers, while LightSwitch is more for people who are already comfortable in Visual Studio and just want a quicker way to accomplish simple tasks"

 response by Andrew Stegmaier

Jim.

I got in. thx

OMG these guys (Microsoft) are in total denial about desktop databases.  I'm not sure what kinds of apps their looking at, but I don't have a single Access app that would convert to the web without major loss in functionaility.

Basic CRUD operations is not an "app".

Jim.

Basic CRUD operations is not an "app".

Have to agree. I've front-ended many SQL and other DB's but there are somethings that are just better done locally.

This is my take:
I've now seen numerous demos of 'Access web apps'.  It's always the same thing ... they show what more or less looks like a table data sheet view and/or some very simple form.

But ... this is 2012 folks.  What I 'visually' see is what I saw on the web in 1998-2000 - simple HTML based tables, etc. Where is the sex ?

Today is the world of Facebook, LinkedIn and 1000's of others - wherein the Web UI has sex!  Cool stuff. For example ... "You and 3 others Like this" on FB.  You click Like, and another window opens and displays those names - and then you can further go to a persons profile - and on and on and on. Many, many cool things like this.

Where is all of this in Access Web apps ?

I can and do emulate all of that on the desktop with Access.  I mean, with all due respect, it's laughable.  I'm not seeing that you can build a 'professional looking' web app with A2013 web apps.  

Maybe it's all there, and these demos as just reeeeeally simple ?  But some how, I don't think so. It just all seems silly to me. I applaud the attempt, but ... really ?

mx

@mx,

<<Maybe it's all there, and these demos as just reeeeeally simple ?  But some how, I don't think so. It just all seems silly to me. I applaud the attempt, but ... really ?>>

 No, I don't believe your missing anything...this is it.  Whether you, I, or anyone else here, or Dan in the next cubby "developes" a web app, they will all look the same.

<<I can and do emulate all of that on the desktop with Access.  I mean, with all due respect, it's laughable.  I'm not seeing that you can build a 'professional looking' web app with A2013 web apps.  >>

  It's not meant to be professional.  Again, they have strictly targeted the end user with the web apps and even with desktop databases over the last few releases.

  That response I posted to Mark's question speaks volumes about where Access is going, what it will become, and where it fits in their vision.

Jim.

"and even with desktop databases over the last few releases."
Even though nothing new in A2013 (desktop), at least they did not remove anything per se. And starting with A2010 (for me), the overall cosmetics are a HUGE improvement.

"strictly targeted the end user"
Right ... but, the question is ... will these 'end users' be savvy enough to REALLY, actually build 'web apps' ?  Or will they end up calling developers/consultants to build them - as they ultimately did in Access.

mx

There's been so much talk and emphasis about the web app features that the traditional features are diminishing to a point that developers from SMB clients are worried about the continuity of the product to support our clients' apps.

I'm not happy with the web app only talking to a SQL Server back end.  It will increase the cost of such a configuration.

I downloaded Office 2013 a couple of weeks ago, and the first thing I noticed is that the UI really embraces the whole minimalist design.

I was at the PASS Summit a couple of weeks ago for SQL Server, and they had a keynote that showed off Excel 2013 / SQL 2012's self-service BI functionality (that they promised with 'Crescent' a couple of years ago, but when they delivered they tied it to SharePoint).  Before I get into anything Access I have to create/give a demo on this.

This week's MVP round-up featured our very own Mr Dettman.  Well-deserved!

Thanks.  Bit of shock to open that up this week as I wasn't expecting it.

 But I'm only the last of a long line of people before me that have been featured.  MX was up not too long ago, as was Brad recently.  And I expect we'll see a few more MVP's from EE and the Access TA on there before long<g>

Always a pleasure to hang out with so many of you (that includes the non-MVP's too)!

Jim.

Happy New Year! 0:26 in Moscow

"MX was up not too long ago, "
Not me ...
?

<<"MX was up not too long ago, "
Not me ...
? >>

Could have sworn you were up already in the MVP spotlight...maybe I'm thinking of your savant announcement?

 I can't check for sure as some have deleted out already.

Jim.

If it was, I missed it an no one contacted me.