Solved

Access Experts: General discussion thread for Access Zone (31-Dec-2012)

Posted on 2012-12-31
118
733 Views
Last Modified: 2013-05-28
This thread is intended for general discussion among Experts participating in the Access zones.  

Discussion topics can include (but are not limited to) Access and zone related issues, tips, tricks, news, events etc.

This thread is publically visible, so please keep comments professional.  If you do have topics that should be handled off-line, please contact myself or mbizup at our e-e.com email addresses (i.e. JDettman@e-e.com).

Thanks,
Jim Dettman
MS Access Topic Advisor

Previous Discussion Thread:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27754981.html
0
Comment
  • 22
  • 21
  • 20
  • +9
118 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Don't know if this is applicable,

But I would like to say that it was a pleasure working with all of the Experts here over the past Year.
;-)

Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
But on a more technical note I do have something to discuss..

On the topic of Forms.

Over the past year there have been many posts regarding "One Table Per Form".

This typically surrounds question like:
"I have a form that I use to enter Orders, I want to lookup the Employee info after selecting them from a combobox"

Typically I use a query for the Form's recordsource, ...that Joins the Emp Table to the Orders table.

Other Experts here seem to favor "Looking Up" the emp data from the combobox.
    txtEmpLastName=cboEmp.Column(2)
(again, keeping the Form Recordsource confined to the Orders table only)

In my quest to solidify most "Best Practices" here,  I am wondering if the "One Form, One Table" approach has any performance benefits over the "Multi-table Query Recordsource" approach.

...Or if I have simply misunderstood the context of these posts.

Jeff
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
Jeff,

You do have a valid point, but it really depends on experience and approach.

I started out knowing some Access and always used Access forms and subforms. And it is still a valid approach. They have the validity of ease of creation and ease of use, especially for a newbie DBA who doesn't want to be a programmer.

But a programmer by training will be used to writing and editing to individual tables programmatically.

I do it both ways depending on the need, and how normalized the DB is. An additional consideration is the business need and who is using the DB. For a CRM (Customer Relation) type DB, the person entering the initial contact data will be entering mostly 1-1 data, such as address, phones, contract number, etc. But if the customer has an HQ, and multiple sub-facilities, that changes.

Then you get to the actual help desk doing support. They can have 50 calls from facility F, five from Facility O, but none from facility R. They will have a parent child relationship from the HQ to the sub-facility(s). But there is an additional relation from facility F to the tickets it calls in.

In that situation you have a facility to tickets which is a one to many relationship between the individual facility and an additional one to many to the parent record.

Then there is the knowledge of who is going to be using your DB and app (front-end). I have pretty much just finished my hundredth ETL Access app. I know the DB I'm extracting from. I know the specs needed for the load. The Transform has a number of steps.  But I know the people working the Transform (co-workers), so I can be a little raw in the coding because I can look over the user's shoulder. If I was giving the code to an end-user -- I would do it differently.

Just some of the considerations in how I code.

So as an expert -- I encourage the form to table approach. The amount of work to teach the coding to do a single form to multi-table code gets you into the question "Does the asker understand VBA?" I'm a volunteer expert -- not a rent-a-coder. Do you remember some of the back channels that used to go on about askers?
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Actually, I'd toss in a 3rd approach; looking up data as needed with recordsets.

I've always been leery of adding more tables to a forms recordset then I actually need and quite often, find I need something like an outer join to express things correctly.

That makes basing a form on that difficult if not impossible, so I've always stuck to "whatever the form is editing is the only table included".

So then how do you get lookup data?  I use the 2nd approach unless:

1. There are more then 3 or 4 columns worth
2. The list is large.

 and that floats a bit.   If the list is large but I only need a single column, I may or may not go for that.  If the list is small, but I need 7 or 8 columns, I might do that as well.

But quite often I take the time to open a recordset for the data I need and then push the data into controls as appropriate.  Takes a bit longer to code, but overall it's the best approach as it gives you the best possible performance and the least amount of memory (although no one seems concerned about memory usage anymore).

My .02
Jim.
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
@MX

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

 No one contacted me either; it just popped up.

Jim.
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Hi All,

 Back in Oct I posted the note below.  Well it's time for the rubber to meet the road; EE is looking for a Webinar for the end of Jan.

 Before I dive into this on my own, I'm wondering if anyone might want to join in.

 Also they are very interested in doing a Podcast series on Office 13, although frankly I'm not sure how many of us are actually thrilled about Office 13. Were going to try and bring in some of the other TA's for that, so it just won't be Access folks.

 Podcasts are easy and fun to do, and not too hard to prepare for.  Just need some ideas on what to talk about and make sure you have your facts straight.

 Webinar's take a lot more commitment, but the exposure is far greater.

 Anyone interested please contact me off-line at:  JDettman@e-e.com

 Any ideas on topics, how to do this, structure it, etc. are most welcome as well.

Thanks,
Jim.


====================
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Jim and Jim,

Thanks for the discussion.
It was just something in the back of my head.
I know that Miriam always seemed to use the combobox lookup approach, .and Joe and Scott mentioned the "One table per form" approach in a recent questions.

Again, I typically always reached for a query, as this way the way I had learned it way back when.
And many of my apps are never that "Complex"

(I just did not want to get "beat up" on this by the likes of Scott, Joe, Miriam, et al)

;-)

Jeff
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<(I just did not want to get "beat up" on this by the likes of Scott, Joe, Miriam, et al)>>

Would never happen ;)

 But seriosuly, there are a multitude of ways to do things in Access, which is the beauty of having the group we do.  Everyone approaches things in a different way, are developing different types of apps for different types of users, and in different situations (all local, some remote, all remote, etc).   That leads to a lot of differences in the way something might be done, and it's great to be able to see all those viewpoints.

 In this case, I don't believe there is a right or wrong answer, but simply what works for a given situation.  At times, one approach for a given situation might be better then another, but they all work.

 In fact I have used the same approach as you on occasion, but not as much as I used to.  I think that's more because I've gotten stuck in a rut rather then being a best practice or the "right" way to do it.  As you point out, your doing the same thing, but starting out from the other direction<g>.

 The other issue here is that computing itself has changed.  Memory for one is not the issue it once was, so pulling everything into a combo control is not as bad as it once was.  Do you remember when 8MB was a LOT of system memory?  I do<g>.

In fact in order to proerly date myself, when I started programming, 64k (yes that is Kilobytes) was the maximum size memory segment I could use at one time.  I fought for every byte back then.

Jim.
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
JD,

I remember when a 20 megabyte hard drive was a lot of room. That is now a tiny fraction of a Mini-SD card. :-|

Jeff,

I'll never beat anyone up, in general, for how they process data. But I will point out, sometimes sharply, when I find bad processing logic.

I had a junior programmer pulling in a 100K+ row text file (list of customer mortgages past and present) to an Access table on the way to a SQL server table in the end. It was taking literally three-plus hours and he couldn't figure out why. I looked at the code for 15 minutes and found a function that was running against each individual row during the import deciding whether the mortgage needed flood insurance based off of three other columns and populating a fourth Boolean column.

Eliminating that function (and others) during the import and changing it to post-import set of update queries changed the processing to about 10-15 minutes.

Later on I had the responsibility of reprogramming his whole process. It used to be an overnight process. I was able to get it down to about 3-4 hours to import over a 35+ files and sometimes millions of rows of data per file.

I try to avoid RBAR (Row By Agonizing Row) but sometimes it's the only way. But a lot of my stuff is ETL as I said.

If I have to build a data entry user interface, I usually resort to the Form/Subform model. Why write queries, when I can use the native Access stuff.

I agree with JD -- Because Access has a native DB structure, ADO/DAO and ODBC connectivity, pretty much all the VBA/VB and the rest of the Office Suite available -- there is no wrong way, just some more, or less, efficient ways to do the same thing.
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Curious: Anyone using Access 2000/2003 under Windows 8?    Miriam just posted a MSKB article in a thread that basicaly it's a non-starter and don't even try it.  OP was having a problem with conditional formatting under Windows 8.  Here's the thread with the link:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27992244.html#a38766857

  I do understand not being able to support old software forever, and there may be something fundamental that does make it incompatible, but this seems long a strong arm tactic to me.

Jim.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Because Ron suggested it here:
http://www.experts-exchange.com/Microsoft/Development/Q_27993042.html#a38769356

And I had a little time on my hands...
ClickIt.accdb
ClickIt.mdb

Have fun :-)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Ha Ha Ha Ha Ha Ha Ha Ha Ha Ha Ha Ha
LOL LOL LOL LOL LOL LOL LOL LOL LOL LOL
:-)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Disclaimer: I know that I frequently answer questions about 64 bit Office with an unequivocal "DON'T DO IT" response.

However, I have a client who is running 64 bit Office 2010 on a handful of computers in their office, because they need it for the Business Intelligence software they purchased about 18 months ago.  Don't ask, I told them to look for another BI program, but they didn't listen.

Several of the modules in one of my application use treeview controls, and I know that these will not work in 64bit Office, heck, the presence of the control on a form in the application causes the application to fail, even if you don't open the form.

So, what if anything are y'all doing to overcome this hurdle?  So much of what I am doing lately can be described in the form of hierarchical data sets which are so easy to display in treeview and listview controls.  I need a concept that will work in both 32 and 64 bit environments, so I don't have to create two separate applications.  Any ideas?
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Dale,

Similar databases I'm sure.  We're still using Office 2007 in our environment, so people needing 64 Bit Office 2010 has not been a problem for us yet.

However, we have been exporting our parts lists (an most of our other reports) to Excel reports.  The list view export is very straight forward.  The tree view reports are created by looping through the data, adding lines and partnumbers to the spreadsheet to show the heirarchical structure.  The code is really ugly and the treeview reports are slow, but the customers wanted it in Excel.  It has been a real workhorse for years, and with 2010 on the horizon I think these reports are going to work out seamlessly.

We just have the reports, though and have not been displaying the tree-view in forms.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Dale,

I can't vouch for any of them, but a quick google search turns up several tree view solutions for 64-Bit Access 2010 -- some commerical products and some shareware/ freeware.
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Wow, expert of the year awards just announced; Access TA for the first time I can ever remember is no longer one of the top 50 TA's.

Talk about the writting being on the wall...

Jim.

BTW, contrats to anyone on that list, and even if not on the list, hat's off to a lot of you for a lot of effort!   It's really hard to keep up any more with so many of you!
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Jim,

The Access Topic Area is definitely much less traveled than it was when I first joined.  If memory serves me correctly, Jim Horn and Shane had months where they earned on the order of 700K points.  There just aren't that many Access questions anymore.

That said, I think Access is still in the top 50.... just under the heading "Microsoft IIS Web Server".

 :-)
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Clearly that cannot be correct.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Jim,

I would agree that it seems to be much less traveled, even than just two years ago.  It may be that there are a few more of us that are answering questions every day, but I find it difficult to get more then 5K points a day (may be because miriam is hogging all the points), where a year ago, I was getting 8 or 10K.

Is this a result of less Access use, or EE 10.0?  Not sure, but I'm convinced we lost a lot of traffic on EE after we went to 10.0

Would be interesting to see how many points have been awarded across each zone over the last couple of years, although with the combining of all of the specific Access zones into the one overall zone, those statistics could be deceiving.
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
The other possibility is the PAQ gives a lot of the answers now as well. I think between all of us we pretty much have done of form and fashion of VBA, DAO, and table manipulation that can be done.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Talk about the writting being on the wall...
And I used exactly that same expression just two months ago in this same thread, see here:
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Meanwhile, there are a LOAD of jobs on Monster requiring Access database knowledge.

mx
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
I think M$ still has a form of schizophrenia that the ego wants to  beat Apple in the home market, the ex-IBM blue suiters want to beat IBM and the rest of anyone in the business market, and they can't understand the tech guys just want the damned SW to work.
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<That said, I think Access is still in the top 50.... just under the heading "Microsoft IIS Web Server". >>

  Yeah right<g>.  I don't anyone is using web databases to any degree.  Three releases in now and we just have started to see a handfull of questions on it.  Seems like Microsoft has a pretty big flop on their hands with Access Web databases.   Some seem pretty excited about them; I don't know...maybe I just lack vision.

  I think they waited too long...2003 to 2007 was too big of a wait, and then with the ribbon and other changes in 2007, I think many developers jumped ship at that point.

  Will be interesting to see if it picks up any steam in the next year.

<<I would agree that it seems to be much less traveled, even than just two years ago.>>

   I have noticed a definite downturn here and just about everywhere else.  On another developers list I belong to, the same thing has been noticed and remarks made about the overall lack of jobs anymore in the Access area.

<<but I find it difficult to get more then 5K points a day (may be because miriam is hogging all the points), >>

 Yeah Miriam, what's up with that<g>?  You all of a sudden have turned into wonder woman.  Don't think I've seen a thread yet that you haven't posted too LOL.

 Speaking of postings, haven't seen much of cap lately.  Hope everything is OK there.  And both LSM and MX seem to be taking a bit of a vacation.  Busy time of year for many of us though.

<<Is this a result of less Access use, or EE 10.0?  Not sure, but I'm convinced we lost a lot of traffic on EE after we went to 10.0>>

 We lost a lot of traffic before that.  Hardest thing to hit EE was Google's changes in their searches.  EE was always at the top of searches and then after that, ended up somewhere on the third or forth page of results.  Slowly climbing back to the top.  In fact just the other day for the first time in a LONG time, an EE link was the top pick.

<<The other possibility is the PAQ gives a lot of the answers now as well. I think between all of us we pretty much have done of form and fashion of VBA, DAO, and table manipulation that can be done. >>

 That's an excellent point Jim.  Access has been around a long time now and there's really nothing new (at least that anyone is using anyway).

<<Meanwhile, there are a LOAD of jobs on Monster requiring Access database knowledge.>>

 I haven't been out there lately, but everyone I've spoken to over the past two years or so have noticed a definite decline in the number of Access jobs.

 I think the other thing that factors into that is VS and SQL coming so far down to the desktop.

<<and they can't understand the tech guys just want the damned SW to work. >>

 Amen to that!

 Microsoft I don't think will ever get that; their too much of a marketing company.  Their focus is too much on the consumer, who will put up with a lot of things that business simply won't.

  Looks like in the Access world, there will be a real watershed moment in a few years with Access 2000/2003 not being runable under Windows 2008; wonder how many business will hold back because of that?  or simply move to something else because their forced to totally re-write anyway (the other piece to that puzzle is that A2013 dropped support for command bars - so now it's the ribbion and nothing but the ribbon).

Jim.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"A2013 dropped support for command bars - so now it's the ribbion and nothing but the ribbon)"
Guess I missed that memo !

mx
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
All commandbars?  As in you cannot even create them with code?

Dale
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Jim ... are you saying that if you have bars say in A2003 and you run this mdb under A2013 ... they will not show up on the AddIns ribbon ?

mx
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
For a list of all the features removed, see:

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

Yes, you can no longer deal with them in code and they will not display.  However they will display on the add-ins ribbon.

Jim.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
so I guess that means no more popup (right click) menus?
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Actually, I haven't tried the code part.  Given that they display on the add-ins ribbon, you still might be able to manipulate them by code.

But what I originally heard was that command bar support was dead, which made sense with MS's focus on the ribbon.

Jim.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
My users demand and make good use of right click context menus. Every major software product / application on the planet supports right click menus.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Mine too, Joe.  It would be a big blow if those go away.  Jim, you have time for a test (I don't have access to A2013)?
Public Sub MenuText(Optional Reset As Boolean = False)

    Dim cbr As Object
    Dim cbrButton As Object

    On Error GoTo TextMenuError

    DoCmd.Hourglass True

    Set cbr = Application.CommandBars.Add("MyTextMenu", 5, , True)

    With cbr

        Set cbrButton = cbr.Controls.Add(1, , , , True)
        With cbrButton
            .Caption = "&Copy"
            .Tag = "Copy"
            .OnAction = "=fnMessage('Copy')"
        End With

        Set cbrButton = cbr.Controls.Add(1, , , , True)
        With cbrButton
            .Caption = "Cu&t"
            .Tag = "Cut"
            .OnAction = "=fnMessage('Cut')"
        End With

    End With

    DoCmd.Hourglass False
    Exit Sub
    
TextMenuError:
    MsgBox Err.Number & vbCrLf & Err.Description, vbInformation + vbOKOnly, "TextMenu error:"
    
End Sub

Public Function fnMessage(Message as string)

    msgbox Message, vbOkOnly

End Function

Open in new window

, then in the Immediate window:
Call MenuText
CommandBars("MyTextMenu").ShowPopup

Open in new window

0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
I'm sure A2013 and beyond with continue to support the CommandBars object ... not seeing how it would be possible to remove that.

mx
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Sorry for the delay...I tested and they do still work (commandbars).

Jim.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Thanks, Jim.  Appreciate the update.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
This is what make me sick and tired and ready to leave the EE-game at once.

With an overall rank as no. 17 after 14 years membership, nearly 10K comments, and answering 2.360 of 3.913 participated questions (not bad I think), I receive this impertinent mail is if I was a rookie aged 14 signed up a month ago:

We noticed that you have yet to earn 3,000 points this
month to keep your Qualified Expert status.

Don't worry! You still have until the end of the month to
earn points and keep your free Premium Membership. To start
earning points, answer questions in your area of knowledge
or write an article. Either way, we know you'll have 3,000
points in no time!

- Start answering questions

- Create a saved search to alert you of new questions

- Write an article

If you do not earn 3,000 points by the end of the month
you will become unqualified and your free Premium
Membership will end. If you are a paying member, your
automatic billing will resume on your billing date until
you re-qualify as a Qualified Expert.

"start earning points" ... I believe there must be at least one polite member of the board who could initiate a make-over of this system to make it fair for long standing expert members and respect that some of us do have real work to do which, for a while, may push volunteer work like this in the background.

"re-qualify as a Qualified Expert" ... Give me a break. My clients don't pay USD 160 for anything unqualified.

/gustav
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<"start earning points" ... I believe there must be at least one polite member of the board who could initiate a make-over of this system to make it fair for long standing expert members and respect that some of us do have real work to do which, for a while, may push volunteer work like this in the background.>>

 I get these e-mails too...so what would you like to see instead?  No e-mail at all, different e-mail for certain level of Experts, etc?  What parameters would you want applied?

 Right now, it's the same e-mail that goes to everyone if you don't qualify for premium membership, which is earning 3000 points in a month.  That is about three accepted answers in a month.

EE did BTW add life-time membership once you hit 5 million points at which point you'd never get another e-mail.

Jim.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
First, I would like to see a wording that did not seem to address a newly signed up member.

Then, as a job assignment or vacation easily can expand to a month or so, a grace period of three months or at least an average of the last three months should be used.

Further, 5 mio. points for a life-time membership is way to high; it doesn't even cover the top 10 members of our TA.

Also, answering questions is becoming harder and harder as the developer activity in Access has dropped dramatically. This both cuts the possible count of questions to answer and - as fewer questions relate to pure development - makes the questions to become "weirder" or more and more less-Access related.

/gustav
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
First, I would like to see a wording that did not seem to address a newly signed up member.

I can agree with this sentiment. But I haven't had but a few of them.

Further, 5 mio. points for a life-time membership is way to high; it doesn't even cover the top 10 members of our TA.

That's overall, and not limited to a single TA.

Also, answering questions is becoming harder and harder as the developer activity in Access has dropped dramatically. ...

I looked at your profile and you are almost  to Ace in the Access TA, but limited in the other TA's. We've been discussing the demise of Access as a good programming platform in this thread. Maybe it's about time you consider expanding your horizons.

I'm already in SQL Server, but I'm going to have to learn how to build standalone apps at some point in the not-so-distant future. Just like I had to adapt to Windows Server as Novell's portion of the market went away.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"We've been discussing the demise of Access as a good programming platform "
Meanwhile. there are LOADS of jobs on Monster, Dice, Indeed & CyberCoders (et al) requiring Access skills. So, I think the term 'demise' is a bit of a stretch.

mx
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
> Maybe it's about time you consider expanding your horizons.

At this time you ought to be aware that several times I have written that I do all new app development in VS and C#. And, after perusing my profile, you should have noticed that even though I don't call me an expert in C#, I have answered several questions in this TA.

/gustav
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
So, I think the term 'demise' is a bit of a stretch.

I didn't say it was imminent -- but that it appears to be happening. When I left my last company in 2009 the primary file server was still Novell. But the primary sys admin was seeing the writing on the wall. My new company's IT guys liked Novell back when, but weren't interested in even looking at it.

The same thing appears to be happening with Access. Is the new version's VBA using VB6 or is it VB.Net oriented? What about the rest of the Office Suite?
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
At this time you ought to be aware that several times I have written that I do all new app development in VS and C#.

My bad. I was not aware of that. My apologies as well. No insult was intended.

If you look at my profile, I have certs all over the board. Some of them are honest that I would consider that I have a decent knowledge in, others are side TA's. For example my Master in Windows Server 2008 is a side from delving into the OS and Server TA's. I have about a 100 hours administering Server 2008. But the SQL 2008 is valid because I have extensive in SQL 2005, but only about a 18 months caring and feeding the newer version.  But the similarities are enough that it is no stretch.
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
Gustav, you should write an article. You have excellent functions for calculating dates and experience with international dates. I think you can get 3K points every month only on article
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<Also, answering questions is becoming harder and harder as the developer activity in Access has dropped dramatically. ...>>

  That's been voiced several times and I believe it to be true.  There is no doubt the existing Access market is mature.  And I don't see what MX see's; in fact I see the exact opposite.  I (and many others) have noticed a definite decrease in the number of Access apps out there or more correctly, the lack of any new Access apps being done.  I also see existing apps being pushed to other products as the need to handle remote users has grown.

 Acceptance of 2007 and up from developers has been at best lukewarm (and I think that's being nice).  We are starting to see the aftermath of that.  The majority of developers I know have already moved on and I haven't seen anything new in the Access world in quite some time.  The new web features of Access may take off at some point, but I fear it's too little too late.

<<Maybe it's about time you consider expanding your horizons.>>

  I can't agree with this more.  In fact in the past couple of weeks I've been asking myself why I don't do more in other TA's.   I have a wide range of knowledge and could easily answer questions in any of the Windows OS TA's or SQL Server, but for some reason I've always stuck to the Access TA.

  This really hit me after looking at a a couple of experts profiles and noticing the large range of TA's that they answer questions in.  I've also had Miriam's move to .Net and other TA's in the back of my mind for some time now and keep asking why am I still fooling around with Access.

 Bottom line is I've gotten stuck in a rut and it's time to move on.  

 Personally, I don't think it's un-reasonable to be asked to answer three questions a month.  But if I don't, I just look at the e-mails as a reminder.  And there is a thirty day grace period after that, so it really boils down to answering three questions every two months.

 I will make sure though that gustav's comments are passed along, as he is not the first to take offense at the e-mail.

Jim.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
No problem jimpen. I can see you cover a broad range. I forgot that I have some points in SQL Server as well, but I must admit it is more by accident (cross-posted questions) than by an active effort from my side.

Alexey, you are right, of course. However, it's hard to find time for writing such because I'm so busy with work and there are so much more to do, indeed getting better with C#.
I can manage answering a question in Access now and then when I need a break or to think about something else.

Thanks Jim. I can add that I - like many of us - really don't try to answer questions because of some potential points. It is because I often can see that a tiny bit of my time can save much more time for someone else because "I've seen it before". This, of course, creates a mismatch to the board's demand for a constant delivery of points as if we were milk cows.

/gustav
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
-->>  And there is a thirty day grace period after that

If I'm not mistaken, that 30-day grace period is no longer there -- it's 3000 points every thirty days (which is still not that much),

Those emails, IMO shouldn't be sent to Experts who have been around for a while/reached a certain level, because they are already fully aware of what is needed to maintain premium services.

<< write an article >>

Writing articles does have some perks and it can be an enjoyable balance to answering questions but I'd be extremely hesitant to suggest this as a quick or good way to earn or earn back premium services.  That is potentially setting members up for disappointment and frustration.

Articles can earn many more points than questions, and *if* an article is published with additional accolades awarded immediately, it can be enough to cover PS... but the points from articles are often earned over a longer period of time - months or years.  

A member can pour a ton of effort into writing a great article and earn 500 points right off the bat when it is published (not multiplied by four like question points):
http://www.experts-exchange.com/articles/articleWizard.jsp

A great article might earn additional awards immediately, or it might be months or longer down the road.

The difference between a great article and a great answer (and a perk for articles for some) is that articles continue to earn points as they are viewed, liked or used in solutions.  They are also a great way to present detailed information for questions that come up frequently (ie: "this is how you resolve xyz.... and you can find more detail in article abc")

However, if a member is simply looking to earn back premium services quickly or maintain it... it is much, much easier and faster to do this by answering questions.
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
gustav,

<<Thanks Jim. I can add that I - like many of us - really don't try to answer questions because of some potential points. It is because I often can see that a tiny bit of my time can save much more time for someone else because "I've seen it before". This, of course, creates a mismatch to the board's demand for a constant delivery of points as if we were milk cows.>>

 Agreed...I'm in the same place.  I do it simply because I like to help people, not for the points.

 But it is a chicken and the egg type situation; if you don't have people answering questions, then people won't ask.  And if you don't have people asking questions, then there won't be questions to answer.

  So encouragement in some form is needed.  In many forums, it's just recognition.  EE however has the paywall, so it becomes another form of encouragement.  But it's not meant to get in the way, which is why the bar is set so low.  You have to admit, three questions a month is not all that hard to get if you spend any amount of time on EE.  Even as little as an hour a week should be enough.  But it certainly is designed to keep you coming back.  If people don't stay active, it soon withers and dies.  Access D is a prime example of that.  How many times have we discussed that the list has become so in-active?

  The problem now for people like us that stick primarily to the Access TA is that the volume of questions has gone down, so it's less often that we find questions that are specific to something we've worked on. As a result, we end up spending more time looking for questions to answer then answering questions.  The Access TA used to be one of the most active TA's on EE, now it's not even in the top fifty.

  The answer for many of us is as Jim P said (and was the best advice); branch out.  Certainly with recently learning VS and C# you could answer questions of any beginner (maybe even mine as I am about to dive into C# big time) and that would be helpful to you in re-enforcing your C# skills, which is something your trying to learn.

 And I would second Miriam's comments on articles; they are a poor way to earn points.  They take far more time then answering questions.

Jim.
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
All

  Anyone running Access 2000 under Windows Server 2008 R2?  Physical or VM or both?

  I've got a client moving to a hosted VM setup and we are having issues.  Not sure if it's related to the VM or the OS.  Currently they are running A2000 under Windows 2003 on a physical server.

Jim.
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Just a note on my last comment; I was getting random (although consistent) hangs with A2000 apps under Windows server 2008.

After a bit of dectective work, I pinned it down to Access hanging while quitting.   Changing Application.Quit to DoCmd.Quit seems to have fixed it.  I have now run the automated tasks three days in a row without a hang.

I plan to go back to Application.Quit today and see if the hangs return.

Jim.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Anybody want to take a look at this post about importing from Excel and providing a method for the user to specify which fields from the inconsistently formatted Excel spreadsheet are inserted into the appropriate fields in the destination table?

I provided a broad overview of the technique I used once, but that application was proprietary, and I failed to get a copy of that particular routine.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
continued - 64 bit Office

Back on 12 Jan, '13 I posted a question in this thread about 64 bit Office and Treeviews, looking for 3rd party replacements.

Miriam commented that Googling presented a number of potential 64 bit solutions, but after Googling and contacting a number of the vendors that come up, none of them appear to actually be offering a 64 bit solution that provides a Treeview functionality for VBA.

So, I'll try again, and see if anyone has seen or heard anything more regarding this.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
fyed,
I replied in that: "Import Excel Fields" thread.
FWIW

Jeff
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
@Dale
http://www.dbi-tech.com/ComponentPage_ctTree.aspx
This package of tools is PRICEY ... use to be a lot less. Doesn't say specifically re 64 bit.
You can call them and ask whatever questions. Also, you can download the Trial.

Most state of the art TV I am aware of.  I've used this in the past (circa 2003) ... and it was very cool.
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<fyed,
I replied in that: "Import Excel Fields" thread.
FWIW
>>

 Watch it that no one gets sucked in...this could end up as the never ending question.   Looks like he wants this, but doesn't have a firm grasp of what it is going to take.

Needs to get broken down into a series of questions at the very least.

Always fun when the rubber meets the road...
 
Jim.
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
All,

  I've pushed ahead with a webinar for Feb 28th and have gotten a few questions from members.  Here's the list:

-More about working with forms, I am designing a product and work order database.

-front end search for t-sql / other data

-New info

-Whatever i can learn about updating legacy systems to current versions

-updating an existing form

-Best practices with SharePoint?

-how to speed up queries that have lots of joined (lookup type) tables

-How to populate a shared Outlook calendar with an event or task, e.g., when a report due date is recorded create a future task

-WIN8


 Is there anyone that would like to join in on anything and present?  One in paticular is the Sharepoint, which I have zero experience with.

Time is short, so you'd need to make up your mind pretty quick.   If no one joins in, I'll just drop those questions as I have enough of my own material already to present.  What I had was:

1. My DB is corrupt, what do I do now?

2. How can I sync two subforms with the second synched to the first?

3. How can I start a DB and pass it some parameters?

4. How can I cascade combo boxes?

5. How can I pause execution in a calling form until the called form is closed?

6. How can I color the background of a continuous form?

7. How can I force page headings in a sub-report to print on each page?

8. How can I print page X of Y over a group on a report?

9. How do I pass a string within a string?

10.  I've got a type mismatch error, what does that mean?

11.  What are some general tips and best practices when writing code?

Jim.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
re: moving articles assigned to the old Access topic areas into the new "MS Access" topic area.

A while ago, I made a recommendation on one of the Community Support topics about this idea.  I think it was in Expert Input but cannot seem to find it now, and realize that it may not even have been a separate "question", but may have been mentioned in a discussion with a moderator regarding another EE10 issue.  

Earlier this evening I decided I wanted to take a look at some of the Access articles, but all I could find were those in the MS Access topic area.  I was able to find my articles, because they are in the "my Articles" section of the menu, but I could not find a way to simply browse all of the articles that have been submitted to the Access topic areas over the years.

I have created a new thread that I thought you all might be interested in contributing to.  Maybe if enough of us comment on this thread, it will get some traction.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Anybody have time to take a look at this thread?

I'm off to a meeting and unable to assist for about 4 hours.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Anyone have time to participate in this one?
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28055562.html

It would be nice to show bill201 that EE members can be "very helpful in providing help" too :-)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Anyone have time to look at:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28070709.html

I'm off to an all afternoon meeting and will only have iPad access to the internet for about 6 hours or more.

Thanks
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
It's been quiet on the Experts thread lately, or did Jim create a new one and I'm just a day late/dollar short.  

Interesting that I have not received a single notification in two days on any of my expert notification searches.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Nope... this is it :-)

It just has busy and quiet times.

Regarding expert notifs, they are coming in for me, just in very low volume since it is the weekend.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Miriam,

I'm not getting any notifications at all. It is now 2:08 Eastern, and I have not received a notification of your post to this thread.  I've checked the junk mail handler on my email server, and there is nothing in there that would route my EE mail to spam or delete it automatically, wonder what is going on with that.
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
I have three notifications for this Q. I had some EE Alerts this morning. I'm with Miriam. Just a quiet weekend.

I've done the same thing -- I've surfed myself out some time early on a Sunday so want to pick up some EE points and there is no activity. :-(
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Still no notifications as of 17:14 Eastern time
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
I've been getting notifications,  but it has been quiet.  Keep in mind that Access traffic is going down.   There's also some decent spring weather around the country this weekend :)

As for these threads,  they will always be closed by an admin comment with a pointer to the next, so no worries on missing any thing.

Jim
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
I'm still not getting any notifications.  Only way to tell there are posts is to stay on the Access Topic page and refresh it occasionally.
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
Has anyone played around with the Attachment data type in Acc2010?

I think I know what the problem is, and my suggested solution may work, but it isn't pretty. And does anyone have M$'s ear?

http://www.experts-exchange.com/Q_28100811.html
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
I just NOW (16:25 PDT, 04-24-2013) started get notifys again ...
none prior since 4-19-2013 !!

mx
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Yeah, I've had 170 flood my inbox between 7:26 and 8:40 Eastern.  Nice to see that they finally got it working again.
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
But have either of you used the attachment data type
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
JP ... sorry, no I have not ...

mx
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
@jimpen,

 Me nither.  Frankley I don't think you will find any of us that have.  Developers figured out how to store attachments way before MS added the attachment data type, which I'm sorry to say, is a half-hearted effort for the end users.

There's virutally no docs on them and like embedded OLE images, there's not a lot of ways to work with them.

Most that start to use them soon stop.

Jim.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
To be fair, although I have no professional interest in Attachments (the fact that there's no SQL Server upsize path alone means I ignore them) - they do offer more than the age old default OLE image embedding.  
They can still be rendered inline, by default, on continuous forms (as they're bound) like OLE images - but by huge contrast, the file is compacted as it's inserted.
(I've no objection to the OLE data type - it's just a Long Binary field and so is very important to have available, but the UI method of file insertion... <gag>.)

You can use the specifically provided Attachment control to work with them, or the DAO object model exposes their methods.

If there was any one thing that I disliked about them most, it'd be that they're a MVF.  I don't see the need for that and it's that which kyboshes their use as much as anything IMO.

Though it's easy to dismiss them as imperfect - they were an attempt at offering new UI (data and VBA) functionality (you can argue whether or not it was to end users rather than developers - but then why the VBA/DAO support?)
Even that sort of offering would have been something on the client side in Access' latest release.

Just another viewpoint...
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
@jimpen,

I have sparingly used attachments as a method to distribute images (for use in treeview controls) and Word/PPT templates to clients as part of my applications.  You can obviously do this with some of the packaging software, but I have used this to some extent.

Attached file includes code for attaching and detaching documents from an Access 2007 database.  I believe this uses the GetOpenFile API that you can download from here.

Unfortunately, this does not address the issue of the "too long filename" associated with the question you referenced in your post above.
mod-Attachments.bas
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"kyboshes"
An extremely advanced technical term I have not seen used for years :-)

mx
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
I thought MS had finally gotten smart and created an external storage system so you didn't run in the DB size limits.

Ah well. Thanks.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Hey All,

It's been 15 months since the cutover to the new, stare-at-the-lightbulb interface.  It seems it will NOT get fixed.  Sigh.  I've never gotten the hang of using the new interface to find open questions--and after you need to rack up 3000 point to keep your membership open getting slapped with 'you've used your one freebie this month' is highly de-motivating.  Support for A2003 ends next April.  While I have been working with ASP.NET, my main app will continue to be in Access.  Soon, that'll mean developing on A2010.  I really need a decent replacement for the NavPain.

But what really kills productivity is the inability to right-click an object and go immediately to one of its events.  So, what I've knocked together is some VBIDE code to open the code window to an event of the active control.  I then have a simple macro that calls the code--and I can put a button on the add-ins tab to call it.
Function OpenCodeWindow()

Dim objVBE As VBIDE.VBE
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim ThePane As VBIDE.CodePane
Dim TheParent As String
Dim TheGrandParent As String
Dim TheActiveControl As String
Dim ProcedureNames() As String
Dim Startlines() As Long
Dim myProcedureName As String
Dim MyLineCount As Long
Dim MyDeclarationCount As Long
Dim i As Integer
Dim r As Long
Dim z As Integer

Set objVBE = Application.VBE
objVBE.MainWindow.Visible = True

z = 0
ReDim ProcedureNames(z)

TheParent = Screen.ActiveControl.Parent.Name
If TheParent Like "page*" Then
    TheParent = Screen.ActiveForm.Name
End If
TheActiveControl = Screen.ActiveControl.Name

Set VBProj = objVBE.ActiveVBProject
    For Each VBComp In VBProj.VBComponents
        If VBComp.Name = "form_" & TheParent Then
            Set CodeMod = VBComp.CodeModule
            ' Count lines in module.
            MyLineCount = CodeMod.CountOfLines
            ' Count lines in Declaration section in module.
            MyDeclarationCount = CodeMod.CountOfDeclarationLines
            
            ' Determine procedure name for each line after declarations.
            For i = MyDeclarationCount + 1 To MyLineCount
                ' Compare procedure name with ProcOfLine property value.
                If myProcedureName <> CodeMod.ProcOfLine(i, r) Then
                    ' Increment counter.
                    i = i + 1
                    myProcedureName = CodeMod.ProcOfLine(i, r)
                    If myProcedureName Like TheActiveControl & "*" Then
                        ReDim Preserve ProcedureNames(z)
                    ' Assign unique procedure names to array.
                        ProcedureNames(z) = myProcedureName
                        ReDim Preserve Startlines(z)
                        Startlines(z) = i
                        z = z + 1
                    End If
                End If
            Next i
            If z = 0 Then Exit Function
            Set ThePane = VBProj.VBComponents("form_" & TheParent).CodeModule.CodePane
            Set objVBE.ActiveCodePane = ThePane
            
            For z = 0 To UBound(ProcedureNames) - 1
                Select Case True
                    Case UBound(ProcedureNames) - 1 = 0
                        ThePane.SetSelection Startlines(z), 1, Startlines(z), 1
                End Select
                
            Next z
            Exit Function
        End If
    Next VBComp
ReDim ProcedureNames(0)
ReDim Startlines(0)

End Function

Open in new window


It's still rough.  Any thoughts?

Another lifesaver was creating the UseMDImode property in the A2003 app, and then those who open it in A2007+ can have the tabbed interface of the get-go
Dim db As DAO.Database
Dim prp As DAO.Property
'On Error Resume Next

Set db = CurrentDb
Set prp = db.CreateProperty("UseMDIMode", dbByte, 0)
db.Properties.Append prp
Set prp = Nothing

Open in new window


    If Application.Version > 11 Then
        CurrentDb.Properties("StartUpShowDBWindow").Value = False
        CurrentDb.Properties("UseMDIMode").Value = 0
    Else
        CurrentDb.Properties("StartUpShowDBWindow").Value = True
    End If

Open in new window


MS seems determined to shoot themselves in the feet.  We'll have to deal with the limping

Nick67
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
> ..  right-click an object and go immediately to one of its events.

I just click the object while the property sheet is open. No big deal.

/gustav
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"can have the tabbed interface of the get-go"
You like the tabbed (serial, DOS like) tabbed interface?
I HATE the tabbed interface. Very first thing I change. Last thing I need is object windows maximized in front of me.

mx
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
@gustav

That's what I do in A2003
In A2007+ while a form is in form mode, the properties sheet is entirely greyed out.
You either have to flip out into design mode, or open the editor and go to the event through the IDE. Highly inconvenient.

@mx
Don't get me wrong.  I hate the tabbed interface--but it beats overlapping windows.  We get 5 or 6 maximized forms going at the same time.  In A2007+, you can't use the windows taskbar to switch between them.  That means either minimizing forms to get to other open forms, or the tabbed interface.  Tabs are the lesser evil

Nick67
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
for what it is worth, I would strongly advise against modifying code related to events on a form that is currently open in form mode.

Lots of opportunity to screw up a form or to save information WRT the forms design that you don't really mean to save.

I work in dual screen mode all the time, especially when coding, and it great to be able to step through code as I view it on one screen, and (unfortunately in my opinion), stepping through code frequently offers you the ability to modify the code while it is executing.  I used to do this but found that I was frequently losing my changes or saving various other aspects of a form that I did not mean to.  

Now, when I run into something that is not working properly, I close the offending form, then edit the code and save those changes, then reopen the form and continue testing.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
That's my experience too.

/gustav
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
@fyed and @gustav
8 years of experience the opposite of yours.
In A2003, if I am editing code while the form is live, I either use the save in the IDE or the menubar to save changes immediately -- or save changes when the object closes.  I've never inadvertently saved changes that I didn't want--and if I DON'T use my own UI elements to close (because most of them have acSaveNo in there) I've never thrown away changes I've wanted.

A2007, on the other hand, was doing an autosave each time I flipped from design to form.
Very bad!
I only want to save changes AFTER I've tested live to see if they've done what I want.

I know that pre A2003 everyone was very gun-shy of editing live forms because it was a VERY good way of corrupting an object or a db.  Never had a problem with it because I started with A2003.

Now, auto-creating a form in A2010 and then editing it in A2003 -- that's an extremely good way to corrupt an object!
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
I only use bound forms when I have no other choice. And the majority of those are simple add or update data.

If I have to present to a user, for doing something like de-duplication, the data is tied to something like two different listboxes and has a button to do the actual work.

And then if it is batch processing that will be built into a function called by a button on the form. But the function is in it's own module. Most of my forms have less than a hundred lines of code, with many of them being blank lines.

I will grant that a lot of my coding though is for running ETL processing though. But even in that, I find if you give the end-user a certain subset of choices, and pre-address those options it goes much smoother.  And most of the buttons are double-click. ;-)

Sample Form
Private Sub ImportData_DblClick(Cancel As Integer)

Dim SQL As String
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim FacCd As String
Dim FileDir As String


SQL = "SELECT AssignedCode, FaclityPath " & _
    "FROM Export_History_Tbl " & _
    "WHERE IDNum = " & Me.FacNmTxtBox.Value & " "

Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)

If RS.EOF = False Then
    RS.MoveFirst
    With RS
        FacCd = !AssignedCode
        FileDir = !FaclityPath
    End With
End If

Set RS = Nothing
Set DB = Nothing

Import_BedMaster FacCd, FileDir
Import_BedCharges FacCd, FileDir
Import_Contacts FacCd, FileDir
Import_Doctors FacCd, FileDir
Import_Emloyees FacCd, FileDir
Import_Payers FacCd, FileDir
Import_GLAccts FacCd, FileDir
Import_TransBal FacCd, FileDir
Import_AcctBal FacCd, FileDir
Import_AcctBudget FacCd, FileDir
Import_LOC FacCd, FileDir
Import_LOCRates FacCd, FileDir
Import_Departments FacCd, FileDir
Import_ResAR_Balances FacCd, FileDir
Import_Census FacCd, FileDir
Import_ProContacts FacCd, FileDir
Import_Demographics FacCd, FileDir
Import_Vendors FacCd, FileDir
Import_AcctPeriods FacCd, FileDir

If Me.TieIn.Value = True Then Pull_Care_Data FacCd

MsgBox "Import completed.", vbInformation + vbOKOnly

End Sub

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
@jimpen,

Not sure I understand your last post, was there a question in there, or were you commenting on your previous discussion or the one started by Nick
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
@nick
"That means either minimizing forms to get to other open forms, or the tabbed interface. "
If I wanted to maximize forms, I would go back to DOS.  Windows is plural for a reason :-)

"but it beats overlapping windows. "
Overlapping windows is why ... WindowS was invented :-)



mx
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
I was commenting on editing code in forms while "hot". I separate that the functions are doing the work not the forms.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
@mx
'Windows is plural for a reason :-)'
The windows taskbar was invented so that we didn't have to minimize one window to switch to another.  When I saw how the Win 7 taskbar/ Aero peek worked I was stoked!  It would be OUTSTANDING to Aero Peek all the open A2003 forms.  I was incredibly ...insert expletive-laden phrase... disappointed when A2003 did NOT Aero Peek.

MDI interfaces are evil
Imagine working with the IDE where you actually had to min/max all the open CodePanes to see a different one (...shudder...)  Despite the ugliness of Office 2013, one nice thing is that Excel is back to defaulting to one instance per workbook instead of MDI.  It was a pain to open one workbook with a double-click and then have to go to the Start Menu, fire up Excel and nav to a second workbook when you needed to do side-by-each row by row comparisons.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Just imagine if Quick Books Pro automatically MAXED every register (window), et al that you opened! Talk about a nightmare. You will *never* see a DoCmd.Maximize command anywhere in any of my apps. All max does is create a bunch of gray space.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Sage 50 Quantum Accounting (80%+ market share in Canada) does max everything that you open.
Unless it's modal.

And my forms strain the monitor space
Anything less than 1600 x 900 and you are horizontal scrolling on some of my more important forms.

Just about everyone of my form open commands has DoCmd.Maximize

:)

Nick67
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
@Nick:
"But what really kills productivity is the inability to right-click an object and go immediately to one of its events"
Just to be sure I know what you are talking about, can you post an A2003 screen shot of what you mean?

mx
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
A2003 properties window brought up by right click | Properties on an object
Live Form Properties windowA2013 Form Properties greyed out
A2013 can't even select the properties windowThe Properties window disappears if open when the form goes live

'inability to right-click an object and go immediately to one of its events'

Ok, more technically, right-click, choose properties, be on the event tab, and choose an event.
However, if the properties window is ALREADY open, getting the focus to a control (via right-click usually so no event fires) will do

Nick67
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
what is the file extension of the database?
what were the command line parameters when the database was opened?
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
It's an mdb.
No command line parameters
A2007+ turned off the ability to show the properties window on a live form.
It doesn't show in Form View.

And that's annoyingly destructive to my coding productivity

Nick67
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Nick,  I guess because I've been using a 2 monitor setup for so long it never even occurred to me that you cannot get to your code window when you have a form maximized on screen.

Why don't you just use Ctrl-G to open the VBA window, and then use Alt-Tab to move between your forms and your code window?
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
I had a similar experience with a dual monitor experience doing Excel development.  I had to swap the monitors containing the GUI and code.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
I've got a dual monitor setup.
But I am not navigating between hundreds of CodePanes and thousands of procedures by using the IDE nav TEXT.

Gross.

That's what they invented icons for (or in this case controls :)
I SEE the one I want and with a couple of clicks, I get the IDE doing what I want
Quite frankl,y I don't remember the names of all my controls, or all the procedures that they may have.  Nor did I need to.  I used to have a GUI to help me navigate the IDE when a form was live.  That's been badly damaged by the removal of the properties window when the form is live.

It's like the NavPain.  I don't remember objects by name FIRST, I remembered them by their relative position in the Database Window and THEN by name.  The whole flat-list-scroll til you see it paradigm is mostly useless.  Some brainiac at MS says 'just start typing  in the...' whatever '...and our handy-dandy search setup will cut your list down as you type'

I don't type.  Not if I can help it.  And I just don't remember the names.  I remember the shapes and locations FIRST and the text second.  Hopefully, after a massive fail, Jensen Harris gets skidded and we get back to a UI that me and most folks I know want--one driven by vision and position and clicking, not typing.

Sigh
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
" when you have a form maximized on screen."
OOPS!

Wow .... all this pane (pain) and suffering. WOW.  I don't have any of these issues whatsoever. I've using dual monitors since the day the concept was invented.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
Hi all.

Although Ctl G will generally work to jump to the VBA window (can't see the Immediate Window otherwise ;-), it's technically Alt F11.

I think it's important to bear in mind the extent to which the push towards Access Web Services has been playing a part in the progression off Acccess since 2007.  (Yes, I know it wasn't present in 2007 - but it was a stepping stone on the path to it - the plan was in motion.)

Maximised, tabbed forms (let's call them "pages" for a moment)... remind anyone of anything? :-p

The lack of a "Allow Design Changes" property for the form indicates the extent to which the prevention of viewing the Properties window in Form view is deliberate.
I wouldn't be hugely surprised if it was related to the creation of Layout view.
(Either the belief that it wasn't then required, or that it physically interfered in some way.)

As for UI design, naturally everyone has different opinions and preferences.  That part of development is the most subjective and even artistic than the more technical aspects of data retrieval and coding.  Nobody's really going to convince another experiened developer that they've been doing it wrong for the last 10 years.  If users accept what you give them - and do so fairly intuitively - then you're doing it right.  (Doesn't mean the other methods are wrong :-).

Cheers.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
What I experienced was that the right mouse click pop-up menu was incomplete or disabled when I used it in one of the two monitors, but not the other monitor.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"Maximised, tabbed forms (let's call them "pages" for a moment)... remind anyone of anything? :"

Yes.  DOS applications :-)
Now, Back To The Future!

"The lack of a "Allow Design Changes" property"  (ADC)
"I wouldn't be hugely surprised if it was related to the creation of Layout view."

Well, Layout View does everything ADC did and a lot more.  I found ADC to be extremely useful. As a *developer*, you could tweak - in Live Form view - control properties, tab order, etc. Very Cool. But ... you can do all this now with Layout View, as well as drag controls around, etc.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
I'll have to play with Layout view.  A quick perusal suggests that it may alleviate most or all of what I have perceived as a major drawback to A2007+

I don't do any developing in A2010 yet because:
a) resetting all the busted references for the A2003 clients is an annoyance since, while you can detect a busted reference with something like
'For Each myref In Application.References
'    If myref.IsBroken Then
'        MsgBox myref.Guid
'    End If
'Next myref

Open in new window


You can't actually FIX the busted references with VBA code
b) Creating any wizard driven form or report in A2010 and then editing it in A2003 leads quite promptly to a corrupted object

c) Help since A2003 has sucked majorly.
d) The NavPain is a Pain

I still need a good Database Window replacement.  I've tried, was it Gunther's, replacement but it doesn't behave quite the way the MS one did.

Nick67
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Be aware that new Dells with Office 2013 OEM come with click-to-run.
If you ask for media, that can ship two weeks later :(
And from looking at the filepaths it appears to blow in the 64 bit version of Office on 64 bit clients.
So far, the only hitch has been that I had to re-order my references and place the Script Host Object model above Outlook.

I haven't had to do the PtrSafe thing.

Nick67
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"I'll have to play with Layout view"

Be SURE ... for each Form, you set the Allow  Layout View Form property ... to Yes :-)

mx
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
>> You can't actually FIX the busted references with VBA code
That depends upon how busted it is (i.e. if it's missing, then yeah, naturally you're hosed).
If it's only deregistered, then I presume you've read Doug's
http://www.accessmvp.com/djsteele/accessreferenceerrors.html
and specifically Michka's
http://www.trigeminal.com/usenet/usenet026.asp

>> The NavPain is a Pain
I have always felt the same, but it stings less with time (what doesn't :-s) and it has the missing filter control that the DB window could have really used.

I should probably make clear that a majority of my development still occurs in Access 2003. :-)
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
I get Access 2003 to do a LOT of things.  Write emails.  Create and attached PDF's to said emails.  Put said PDF's on the web server and write HTML pages to access them and send an email saying where to fetch them.

The latest thing is to get Access to fetch said web sites and burn them to an autoplaying CD/DVD.  Code to do so (the burning anyway) is posted below, if anyone would find that of interest.

Option Compare Database
Option Explicit

Sub TestCDWrite(strBurnPath As String)
  Dim objDiscMaster As IMAPI2.MsftDiscMaster2
  Dim objRecorder As IMAPI2.MsftDiscRecorder2
  Dim DataWriter As IMAPI2.MsftDiscFormat2Data
  Dim intDrvIndex As Integer
  'The Object browser, but not intellisense, presents types for these, but they cannot be used in VBA
  Dim stream As Variant
  Dim FS As Variant
  Dim Result As Variant
  Dim FSI As Object
  Dim strBurnPath As String
  Dim strUniqueID As String
  
  ' *** CD/DVD disc file system types
  Const FsiFileSystemISO9660 = 1
  Const FsiFileSystemJoliet = 2
  Const FsiFileSystemUDF102 = 4
  
  'On Error GoTo TestCDWrite_Error
  
  intDrvIndex = 0
  ' Create a DiscMaster2 object to connect to optical drives.
  Set objDiscMaster = New IMAPI2.MsftDiscMaster2
  
  ' Create a DiscRecorder2 object for the specified burning device.
  Set objRecorder = New IMAPI2.MsftDiscRecorder2
  
  strUniqueID = objDiscMaster.Item(intDrvIndex)
  objRecorder.InitializeDiscRecorder (strUniqueID)

  ' Create a DiscFormat2Data object and set the recorder
  Set DataWriter = New IMAPI2.MsftDiscFormat2Data
  DataWriter.recorder = objRecorder
  DataWriter.ClientName = "IMAPIv2 TEST"

  ' Create a new file system image object
  Set FSI = New IMAPI2FS.MsftFileSystemImage
  FSI.VolumeName = "WO " & Forms!frmjobid![WO#] & " " & Format(Forms!frmjobid![Date], "mmm yyyy")
    
  ' Import the last session, if the disc is not empty, or initialize
  ' the file system, if the disc is empty
  If Not DataWriter.MediaHeuristicallyBlank Then
    On Error Resume Next
    FSI.MultisessionInterfaces = DataWriter.MultisessionInterfaces
    If Err.Number <> 0 Then
      Call MsgBox("Multisession is not supported on this disc", vbExclamation, "Data Archiving")
      GoTo ExitHere
    End If
    On Error GoTo 0
    Call MsgBox("Importing data from previous session ...", vbInformation, "Data Archiving")
    FS = FSI.ImportFileSystem()
  Else
    FS = FSI.ChooseImageDefaults(objRecorder)
    
  End If

  ' Add the directory and its contents to the file system
  Call MsgBox("Adding " & strBurnPath & " folder to the disc...", vbInformation, "Data Archiving")
  FSI.Root.AddTree strBurnPath, False

  ' Create an image from the file system image object
  
  Set Result = FSI.CreateResultImage()
  Set stream = Result.ImageStream
  
  ' Write stream to disc using the specified recorder
  Call MsgBox("Writing content to the disc...", vbInformation, "Data Archiving")
  DataWriter.Write (stream)

  Call MsgBox("Completed writing Archive data to disk ", vbInformation, "Data Archiving")
  objRecorder.EjectMedia
ExitHere:
  Exit Sub
'Error handling block
TestCDWrite_Error:
  Select Case Err.Number
   Case Else
   MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "TestCode.TestCDWrite"
  End Select
  Resume ExitHere
'End Error handling block

End Sub

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Is it just here or was EE off-line for about 18 hours today?

/gustav
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
It was EE.

Jim.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"It was EE."
FYI ... I sporadically experiencing the 'Remember Me' not working, wherein I have to Log  In. In fact, that just happened this morning a few minutes ago. Specifically using Chrome - latest version. This has happened on two other systems also (at work) several times recently.

mx
0
 
LVL 57

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
@mx,

<<FYI ... I sporadically experiencing the 'Remember Me' not working, wherein I have to Log  In>>

 If you have anything that clears cookies or blocks them, or you clear them manually, that's where the problem lies.

 The "remember me" works by storing a cookie on your local machine.

Jim.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"The "remember me" works by storing a cookie on your local machine."
Of course.  Nothing on any of these systems has changed.  Again, this issue is sporadic.
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
I've run into the same issue anywhere between to a week to a month. I think that EE is probably storing a version number in the cookie, so that when they do a tweak, the cookie becomes invalid. It's an annoyance that I haven't bothered to report because it's so sporadic.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
<The "remember me" works by storing a cookie on your local machine.>
Since I have IE set to delete browsing history on exit, EE only 'remembers me' if I have tabs within the same browser window on the go.

New window, and I am forgotten.

Sometimes I am forgotten no matter what, though
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
" It's an annoyance that I haven't bothered to report because it's so sporadic."
Yep ... that is pretty much the case.


"Sometimes I am forgotten no matter what, though"
Understandably in your case Nick, loL!!
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 0 total points
Comment Utility
Well it's time to move on once again.  New thread is here:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28141204.html

Jim Dettman
MS Access Topic Advisor.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

771 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

15 Experts available now in Live!

Get 1:1 Help Now