Link to home
Start Free TrialLog in
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)Flag for United States of America

asked on

Expert Notify Thread - For those answering questions in the MS Access Zones

Question: Hi Everyone,

  This "question" is for those contributing in the MS Access Zone.  If your are not contributing as an Expert, please do not post comments here.  This question provides the ability for an Expert to ask for assistance from other Experts in getting member questions answered.

 If you have general discussion comments, they should be made here:

https://www.experts-exchange.com/questions/26767044/Access-Experts-Discussion-thread-24-Jan-2011.html

Experts:
  The idea behind this "question" is to allow an easy means of getting other Experts and the Zone Advisor or Page Editor to look at something.

   It is a "notify* question in the sense that you initially only *subscribe* to the question (by using the "monitor" button), but *don't* post any comments.

  When you want to notify other Experts, you post a new comment in this question that might look something like this:

Subject: Expert Topic - Member needs help with a tough one

"Member xyz needs help.  Please see

<link>

AccessExpert"

  All of us will then receive an e-mail notification (because we are subscribed to this thread) and can click on the link to find out what needs to be done or talked about.  Please do *not* post general comments or hold a discussion in this notify question.  Keep in mind that everyone receives an e-mail each time a comment is added and it takes longer for the page to load as it grows larger (some of us are still on dial-up).

 Keep in mind that anyone will be able to see these comments (both members and Experts).

Jim Dettman
MS Access ZA
Avatar of aikimark
aikimark
Flag of United States of America image

This question could use some Access expertise
https://www.experts-exchange.com/questions/26085662/Add-Multiple-Records-to-Table-from-Data-Entry-Form-in-Access-2003.html

It fell through the cracks, but the OP recently asked an identical question, showing interest in a solution.  The duplicate question has been deleted.

aikimark -- zone advisor
This question also needs additional help:
https://www.experts-exchange.com/questions/26919325/Combo-Box-won't-autoexpand.html
Very strange Access behaviour. May be anybody have something like this case.
Has anyone else had an email regarding  'Top Tipper' from www.NocPlace.com?

Peter
Avatar of Jim Dettman (EE MVE)

ASKER

None here, but I did join the site just to see what it was about.

JimD.
Do any of you have experience with Access & SharePoint?

Pass-thru connection string for SharePoint
http:/Microsoft/Development/MS_Access/Q_27060640.html

(°v°)
Web Forms, anyone?

Reference Pages in Tab Control for Macro Filter on Access Web Form
http:/Microsoft/Development/MS_Access/Q_27112787.html

(°v°)
New Button styles in Access 2010. If BackColor is changed, Style (button gradent) is lost. May be anybody have solution.
https://www.experts-exchange.com/questions/27146214/Reset-the-theme-for-a-command-button-in-Access-2010-VBA.html
For those of you that don't subscribe to the EE team blog, a recent post that will be of interest to all:

https://www.experts-exchange.com/blogs/EE-Team/B_5305-Experts-Exchange-v10-0-Redesigned-from-the-Ground-Up.html#discussion

Jim.
Did you notice that this question was removed from the top:

SAP login script session count

I was so sick and tired of looking at it, that I had to provide a decent answer even though I know next to nothing about SAP.

/gustav
Tanks! — (^v°)
And really, why ... was it even in the Access zone ?
Wasn't an update announced for the 29th to improve speed?
Nothing seems to have changed here ...

/gustav

I don't remember the exact wording, but I had understood it was about “finding answers”. Anyway, I thought it had to do with the search pages (perhaps a new full word indexing of PAQs and Articles or something similar)...

(°v°)
Another SharePoint problem... I'm out of ideas.

Sharepoint 2010 opens Access database as read-only
http:/Q_27148715.html

(°v°)
Unexplained and new bug (at least to me). Any MVP available to examine it and file the report?

Access 2007 Date-picker calendar not consistent in subforms
https://www.experts-exchange.com/questions/27205842/Access-2007-Date-picker-calendar-not-consistent-in-subforms.html

It appears as though the last subform on a tab control cannot use the date picker. It is displayed and seems to work but does not write the date back to the control...

(°v°)
But we - eh me - don't use those sites, and after the addition of the top-right box "Stay up to date" (?), sometimes it is as if EE pages loads _very_ slowly waiting for that update. No happiness.

/gustav
gustav,

  I really don't all that much either, but it's the way of the world so to speak.  Gone are the days when sites are rated soley based on links to/from and how connected they were   Sites more and more are being rated based on the content they contain (which is good for EE) to determine where they appears in the rankings on searches, which is why EE added all those features.

<< and after the addition of the top-right box "Stay up to date" (?), sometimes it is as if EE pages loads _very_ slowly waiting for that update. No happiness.>>

  They are aware of it and have done some work in that area, but with all the focus on the new site, not much is getting done unless it's absolutely critical.

Jim.
OK, thanks Jim.

/gustav
Asker is using backgroun images in a report with A2010 and is getting extensive mem usage unlike A2000/A2003.  Anyone have any thoughts?

https://www.experts-exchange.com/questions/27306688/Linked-Image-in-Report-Causes-HUGE-System-Memory-Usage-with-Access-2010-WORKS-IN-2003.html

 If not, I'll buck it up the chain to Microsoft as he has a sample DB to reproduce the problem.

Jim.
VBA and Access tutorial seekers.  I just rezoned it.
http:/Q_27362399.html
> .. after the addition of the top-right box "Stay up to date" (?),
> sometimes it is as if EE pages loads _very_ slowly waiting for that update.

I'm so tired of this that I filed a bug report:
Stay Up To Date section brings loading of EE pages to a crawl

Does anyone use this really? I wouldn't know for what purpose.
Why can't it just be switched off in your profile settings?

/gustav
<<> .. after the addition of the top-right box "Stay up to date" (?),
> sometimes it is as if EE pages loads _very_ slowly waiting for that update.
>>

  I'm not sure if it's getting worse or not, but page load times are a lot slower.  I just timed loading this page and the discussion page:

 This thread with 28 comments:  28 seconds

  Discussion thread with 65 comments:  95 seconds.

  Even regular question threads are 10 - 15 seconds.

  Really is getting annoying.  It's getting to the point where I click on an EE link, then go do something else and come back to it.

Jim.
OK, I'm not alone. Similar timing here.

Looking forward to the announced major site update!

/gustav
Just noticed this... for anyone suffering the 'lost vba' problem since applying SP1 - hotfix now available...
http://support.microsoft.com/kb/2596585
I'm not sure I understand what this means:

"Issue that this hotfix package fixes
Assume that you open a database that contains Visual Basic for Application (VBA) code. In this situation, Microsoft Access 2010 may crash when you change the database."

  Their saying if it has VBA and you open another DB?

Jim.
No.

If you haven't had the problem then lucky you.  The problem was introduced in A2010 SP1.
What happens (sometimes) is that if you make any change to the design of any code object , then the entire vba project becomes unavailable.    MS issued a workround to the problem a while ago, which was just to do a de-compile and then a compile, but it doesn't fix the problem for good and it can re-occur at any time.
I assume that now they have issued a fix that they know what was causing the problem.

I've updated my system with the hotfix because it has been a big issue for me, but it's apparent that the problem is not universal and many developers have not seen it at all.

Peter
APD_Toronto is asking about potential Front End issues when upsizing to SQL Server:
https://www.experts-exchange.com/questions/27437412/OpenRecordset-SQL-Server.html

Can someone who has more recent experience/familiarity with hurdles in upsizing an Access database to SQL Server jump in, please?
A thread you might wish to monitor:

https://www.experts-exchange.com/questions/27438942/Web-I-O-for-an-Access-application.html

  Asker is giving the service from www.eqldata.com a try for putting an Access DB (un-modified) on the web.  

  A lot of us have been pointing this service out for the past couple of years, but this is the first I've known of anyone trying it first hand.   Results will be interesting.

  Please keep any discussion of this on the discussion thread and not here.

Jim.
Actually, anyone can download the demo and check it out. They wanted me to do that, but ... time ....!
Will monitor.

mx
Sister site? What for?

/gustav
For $$$$$.  All it does is dilute the Access Zone here.

It's the same thing all the online dating sites have tried to do and failed.
Sister site? What for?

/gustav

Currently  the Top Experts are:
    jimpen          6,640
    boag2000     3,960
    dqmq            1,000

EE has been trying expand their branding with "micro-sites" that are specific to a product such as Access, Excel, etc. The volume of questions is lower, but currently the competition for points is significantly lower too. If you are going to an employer and saying I'm a genius on EE and a senior Expert on AccessAnswers do you think they will question it?

I think my EE ranks impressed my current bosses.
I'm confused. One - and that includes EE - should focus, so it doesn't make sense to create duplicate sites not much different from the main site.

/gustav
Look at what StackOverflow has created with StackExchange 2.0 sites, ranging from maths to physics and cooking among others.  Then look at the RedSource interactive "micro-sites".  Just splitting hairs on programming/it-related fields is going nowhere IMvHO.
In regards to the micro-sites, one of the problems EE has always had is the ability manage zones effectively.   If you stand back and look at it, a micro-site is nothing more then a zone.

 The plan was/is that "EE" would become a collection of sites rather then a collection of zones.  That's why the micro-sites exist (not just for additional $$$$) .

Jim.
Hmm ... if cannot find out how to manage zones, how will you manage sites?

If the meaning is easier access only, then the sites could be landing pages for the zones.
And how about us experts? Were we supposed to jump between multiple sites in addition to the zones we regularly visit?

/gustav
gustav,

 While it's no great secret what direction EE is heading, I've probably already said too much.  The microsites do have a purpose outside of generating additional $$$.

 For the moment, let's just say that EE is tackling a long standing problem and has everyone in mind, including Experts.

Jim.
OK, we'll see.

/gustav
I've just stopped myself posting a quite rude reply to this Q.

https://www.experts-exchange.com/questions/27612741/Access97-Workgroup-problems.html

I'm glad I'm not the customer of someone who doesn't even know how to open the database.
<<I'm glad I'm not the customer of someone who doesn't even know how to open the database.>>

 Well not everyone knows about or understands workgroup security. In fact there are very few that understand that it's on all the time.  Many think it's not active just because they don't get a login prompt.

 Or how about that in 2007/2010 it's actually there and there is still a workgroup file?  Only thing Microsoft did to "strip out" ULS was to reset the ACL's on database objects.  And because of that, if you point to a custom workgroup, you still can get permission errors.

Jim.
I jumped in and gave a quick answer.  The OP needs to do a bit of research, but it should get him started.
If you hadn't run over this before on the Win 7 SP1 / ADO problem, this is worth a read:

http://blogs.msdn.com/b/psssql/archive/2011/10/03/yes-we-made-a-mistake-and-are-finally-going-to-fix-it.aspx

Jim.
Oops!!!  is right.
Can someone with some ASP.net knowledge take a look at this please and get this member started:

https://www.experts-exchange.com/questions/27684231/Access-Database-as-backend-for-simple-website-Suggestions.html

Jim.
N,

 I was waiting to see if anyone here would jump in first as I know many have done an ASP front end for a JET DB.  If no one does I will add the ASP TA.

Jim.
I'm involved in this Q by mistake. I thought he was talking about a SQL back-end issue.

https://www.experts-exchange.com/questions/27742335/how-can-i-change-string-encoding-with-sql-query-in-access.html

Apparently an AccDB isn't holding the UTF data. Anyone with knowledge is appreciated.
Can anyone help with this question.  I am a bit confused with it.  Thanks.

http://www.experts-exchange.com/Microsoft/Applications/Q_27776792.html#a38145608
I don't think it's a bug (I'll elaborate in the Q) — (°v°)
I doubt they'll change it and I don't even think it's worth reporting myself.

It's always been that way as far as I can remember and as you point out, the downside is the lack of performance if they did all the totals seperately.

Jim.
Yes you are probably right Jim.  I'd never seen it myself til this Q, and it's clearly a shortcoming.  Although I have always felt that Access should not allow an expression of ..
=Sum(controlname).

Maybe I'm not thinking hard enough but I can't think of any situation where it would be valid?
Heading off on vacation for five days and need someone to cover this question please:

https://www.experts-exchange.com/questions/27785531/Event-Procedures-not-working.html?anchorAnswerId=38210119#a38210119

I've left a comment that should help them figure out what's going on, but they might need more help.

Thanks,
Jim.

=========
I'm on it. Already rezoned to Access.
aikimark -- zone advisor
Does anyone know why a domain aggregate function used inside a query comes back as a String data type.  I've tried it out and Access 97 and 2007 and the result is the same.  You could do a calculation with it so it really acts like a number, but if you use it in a recordset it comes in as a string.  Here's the link to the question.
My guess is because of the places it was designed to be used; inside expressions where a SQL statement is not normally allowed, but that's just a guess.

Of course the question begs why are you using a domain function inside a query anyway as your guaranteeing poor performance (Domain functions are not optimizeable by the query parser).

 And since they simply represent SQL statements anyway, there's no reason not to write the statement directly.

Jim.
@Jim

Sometimes, you have to use a domain aggregate or user-defined function in order to keep the query updatable.
Has anyone done a test to see if the NZ() function changes the data type of a domain aggregate function to string in some cases?
I have tested it without nz and out still makes out a string.
<<Sometimes, you have to use a domain aggregate or user-defined function in order to keep the query updatable. >>

 Still could be written as a sub-select, which will be faster then a domain function.  The query parser can't do anything with a Domain function.

Jim.
Can anyone have a look at this thread please?  I originally misunderstood the question.  I think it has to do with a datasheet that uses Lookup fields which I'm not too keen on.   Thanks.
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27867963.html
Anyone worked with web DB's yet?

https://www.experts-exchange.com/questions/27963134/Access-2010-EditRecord-Macro.html?anchorAnswerId=38679838#a38679838

Seems like a simple problem, but not having done any, can't say if he's using the right event or even if what he is attempting to do (which seems very reasonable) is possible in a web DB.

Jim.
I've got a puzzling one.  The OP has a table that shows 0 records when he does a DCount, but when he tries to do a RunSQL to delete records from that table, it notifies him that 85 records will be deleted.  I have no idea what's going on with this.  Please check it out here:

http://www.experts-exchange.com/Microsoft/Applications/Q_27969784.html#a38696710
https://www.experts-exchange.com/questions/28026372/Change-Sort-Field-on-Form-When-Filtering-is-Toggled-OFF-ON.html?anchorAnswerId=38875099#a38875099

This question is superficially straightforward.  It has obvious answers .  Which Don't Work!!!
The poster wants to detect when a form is filtered and when the filter is removed and set the sort order differently in each case.

In tackling this Q for quite a while, I have established...(Access 2010)

The Form.ApplyFilter event is broken.  If you use the Toggle Filter button on the ribbon,the ApplyFilter event ALWAYS says it is applying a filter, irrespective of whether you are applying it or removing it.

If you use the right-click menu to set and clear a filter the Applyfilter event fires when you set the filter but does not fire at all when it is cleared.

Having established this I have been trying to use the form current event to test the FilterOn property but this is causing other problems.

I suspect that a  'good' solution may not be possible and it might be necessary to use more basic approaches such as buttons for sorting or changing the recordsource.

The problem is easy to illustrate - I  just set up a continuous form in Northwind based on a few fields from the Customer table and started from there.


I'm not beyone missing the blindingly obvious so that would fine if you have such a solution.
Can someone with 2003 check out this question?  The OP's chart doesn't show unless he clicks on another control.  I've only got 2007 and I got it to work fine with a Refresh but apparently it doesn't work in 2003.
Spurious leading character in labels - maybe related to change in regional settings.

I have no idea .

https://www.experts-exchange.com/questions/28122721/Characters-Symbols-added-when-using-different-PC-Locale.html
"  Please post comments on the discussion thread if the site is faster, slower, or about the same for you."
I don't believe I ever saw this notify on this. Wonder if anyone else did, since no one has posted back.

I will however see what I notice from this point.

mx
Please post comments on the discussion thread if the site is faster, slower, or about the same for you.
I don't know about faster or slower but since then I've not been able to access the Full Site on my Android phone.  I've tried it on 3 different browsers on both the Galaxy Note and my wife's Galaxy S3 and I can only view the mobile site.  Clicking on the Full Site link at the bottom of the page just takes me back to the mobile site.  Is anyone else having this problem or does anyone have any ideas to fix this issue on my phone?
@N,

 iPhone4, Safari browser.

Jim.
The phones I've tried them on are the Galaxy Note 2 and Galaxy S3.  I tested it on the default browser, Chrome browser, and Dolphin browser.
Alright! Looks like I can access the full site again on my phone.
Works here to.  Thanks for reporting that Ron.

Jim.
Hey Jim, the OP in this question uploaded a database that is in desperate need of complete redesign.  I don't think I can help with the original question but I wanted to give the OP a link to your video on Relational databases and Normalization (the one that Miriam made reference to in http:#a39228509; however, the video is now marked as private.  Is this not available anymore?

Anyone willing to chime in with this question, please do.
Hum, EE must have done that when they started hosting the video's themselves.  All but six on You Tube are now marked as private.   Not real thrilled about that.

Here's a link to all the videos:

https://www.experts-exchange.com/videos/

and here's the link to that one specific video:

https://www.experts-exchange.com/VP_56.html

Jim.
I've run out of free time to help on this Q: http://www.experts-exchange.com/Q_28173241.html

Can anyone else dig into it?

Thanks.
Can anyone help with this question on changing the Tab colors?  These are not the tabs inside a form.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28214259.html#a39415340
How could a debug.print statement show info on my computer but doesn't show anything on the OP's computer?  Is there a setting that turns this off?

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28199995.html#a39444999
So we cannot provide a link to a previous question?
What sense does that make?

/gustav
<<So we cannot provide a link to a previous question?>>

Certainly you can.   But if you make a comment like this:

See:  www.ssjsjss.com

 It needs to be 100% spot on in answering the question clearly and concisely or it will get deleted.   Sometimes just posting a link really is the answer, but more often then not it is not and a link alone is not a qualitity answer.

 For example, you post a link to a MSKB article; is there a specific section the questioner should pay attention to?   Will they have to make changes to what's there?  etc.   Anything you can add that helps is a plus and it generates content that is found here and no where else.

 What EE is looking for is for Expert's to answer with their expertise and not an "Expert" doing nothing but a Google search and posting a link, which we've had all too much of lately.

Jim.
Ah, OK, so you just provided a "bad" example URL.

/gustav
"It needs to be 100% spot on in answering the question clearly and concisely or it will get deleted"

Sounds like you will need an entire team of people just to monitor this .... or is it automated ?
And who is going to judge if it's "100% spot on", as opposed to 98.37852615% spot on ... seriously?

mx
Joe,

The guidelines for posting links have always been there.  It is *always* best to include a explanation (even brief) with links -- letting the OP know what to look for and/or how the link answers the question.

What Jim is saying is that if you just post a link with no direction, explanation, etc - it should be very targeted, directly answering the question.  For what it's worth, the core group of Access Experts here generally post according to the site's guidelines and rarely just post links without supporting comments.
Joe,

 Really, everyone here knows what a good comment is and what is not.

 As Miriam has pointed out, the guidelines have always been there, they've just never been enforced all that much.  That's what has changed.

 EE has asked all topic advisors to be more pro-active in getting rid of the junk.  The "junk" are comments by Experts who time and time again do nothing but drop a bunch of links into a comment with no added insight and who are just hoping that something sticks.

 That's the main intent of this.  If that's caught while the question is in progress, the TA's have been told to simply delete the comment.  If it's caught after the fact, we now have tools to basically remove the thread from sight.

 Besides getting rid of the out right junk, EE would like to see the bar rasied a bit in the way links are used within comments.   Links alone in some cases do the job fine, but in the vast majority of cases they do not.

 In short, a link should support the answer and not be the answer.

Jim.
Again, I completely understand the concept and intent and I agree.

However RE:

"In short, a link should support the answer and not be the answer."
Let's take the case where the OP wants to get the Windows logged in User Name.

I'm most likely going to post a link to http://access.mvps.org/access/api/api0008.htm 

because ... the content in that link is ... the answer. So, the actual post would probably be:

"You can get the user name by using the code Here "

So, in this example ... the link pretty much is the answer ... not just supporting the answer.

In other words, I'm not going to reinvent the wheel by writing out everything in that link, then post the link to support what I write out ...

Hope this makes sense ...?
'mx has a point and it sure seems like it will be a lot of work for the TAs to police this; they'll have to follow posted links and evaluate the content to determine whether or not the comment should  be deleted.  That being said, I like the idea of 'raising the bar' here on EE.

Keep in mind there are some very good and pertinent PAQs, many made by you involved in this discussion (e.g. how decompile/recompile and Access DB ('mx I believe), 32-bit/64-bit ODBC issues (recent comprehensive post by jdettman), etc.), that very often will provide an answer to the question being asked.  The original post is comprehensive and reposting of the content is both unnecessary and plagiaristic unless we explicitly give credit to the original poster ("Four score and seven years ago EE-Expert-Guy/Gal posted a wonderfully detailed explanation of how to do exactly what you're asking.  I've simply pasted his/her original post here for your easy-reading pleasure.......).  I'm not trying to muddy the waters but we all see questions posted that have been asked, and answered, numerous times previously.  The question, in this case, seems almost in lieu of performing a search and so the answer becomes basically a search-result.  My 2 cents (might be three cents....that's a bunch of words).
OM Gang
"raising the bar' here on EE."
Of course !
Joe,

<<I'm most likely going to post a link to http://access.mvps.org/access/api/api0008.htm 

because ... the content in that link is ... the answer. So, the actual post would probably be:

"You can get the user name by using the code Here "
>>

  Right and there's nothing wrong with that.   As has been said, sometimes a link is the answer.  In this specific case, that's 100% spot on and it could stand as is.  You, I,  and everyone else here knows that's a valid link only comment.

  The ones were going after are the ones where a link does not answer the question.   I deleted one this morning.

  User says "the A2010 deployment changes my db to .accdr  I need it to be .accdb.  How do I avoid that".

  What he got was a comment with two links, one to the Microsoft Q&A forum, which talked about how to deploy an app in general, and another office link which said the same and a "read these".

  Asked a very specific question and got a comment with two links with no specific answer.  DELETED.  

  Those are the main focus.   But also in regards to rasing the bar a bit, on your example, would it hurt to answer it like this:

"The best method to get the user name is to call the Windows API and get the login name.  Code to do that is here:
 .....

   This is better then using Environ(), which can be spoofed if your user has access to the command line."

   Now your telling them something that is not found in that link alone.  Something that you as an Expert know because you are an Expert.  You've added value and they now know more then if they had Googled it themselves rather then asking the question here.

Jim.
Here's an old but very clear cut example of what we're trying to avoid:
https://www.experts-exchange.com/questions/23789115/call-a-form-event-from-another-form.html

The person who posted the first comment probably googled "call an event in another form" and posted one of the first links found.

If you glance at the article, you'll see that it did *nothing* to help LennyGray, and worse - was probably the culprit in a delayed response for a very simple question.  (Without that comment there, someone would have undoubtedly jumped in and nailed that question long before I saw it).
Sounds like you will need an entire team of people just to monitor this
We already have that - the TAs, Modmins and the group of core Experts who will (hopefully) help in this endeavor by counseling the Google Monkey in their errant ways.
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial