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

Access Experts: Discussion thread (24-Jan-2011)

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 (eg: no flaming or derogatory comments about other Members).  If you do have topics that should be handled offline, please contact myself, mbizup, or jimpen at our e-e.com email addresses (i.e. JDettman@e-e.com).

Thanks,
Jim Dettman
Access Zone Advisor

Previous Discussion Thread:
https://www.experts-exchange.com/questions/25772284/Access-Experts-Discussion-thread-08-Apr-2010.html 
Avatar of Helen Feddema
Helen Feddema
Flag of United States of America image

What does everybody think about the color schemes in Access 2010 (Blue, Black and Silver)?  Personally, I think they are washed-out, low-contrast and ugly.  I would like to see some more colorful choices.  Maybe some developer will write an add-in to give us more color choices.
Ditto.  Definitely need MORE !
And really, we should be able to define our own ...

mx
Avatar of Jim Dettman (EE MVE)

ASKER

<< Personally, I think they are washed-out, low-contrast and ugly. >>

  I would agree as well.  Not a great selection :(

  Hasn't bothered me much though, because I'm using A2000/2003 for 95% of what I do.  I only have one client that uses 2007 and that's plenty; I absolutly detest the ribbon - can never find anything.

  In fact I was in Excel the other day and for the life of me, could not find Auto Format any where (I wanted to shade every other item in a list).  Had to go on-line to find out where it was.  Took me 10 minutes to figure out such a simple task.  

JimD.
I agree that we should be able to define our own ...
Am I sensing a mind outside the box ;-)
I just wanted to jump in here.  I'm fairly new to Access, I've been using it for just over a year now, and have started answering some questions, just kind of wanted to introduce myself to some of the real Access experts.  You may have seen me on some questions recently.

I'm a recent college grad. (2009) from Virginia Tech in EE.  I'm enjoying experts-exchange and can hopefully contribute to the community more, the more I learn.  I really want to get involved in the tech community, write some articles, and help out wherever I can.
@ VTKegan ... Welcome to Experts Exchange for sure ... and yes, I've 'seen' you :-)

mx
VTKegan,

I'm glad that you caught this thread - and not just because you're another Hokie.  :)

Its great to see people who join primarily to ask questions start shining as 'Qualified Experts' themselves.

You're doing a great job out there.
VTKegan,

<<I'm a recent college grad. (2009) from Virginia Tech in EE.  I'm enjoying experts-exchange and can hopefully contribute to the community more, the more I learn.  I really want to get involved in the tech community, write some articles, and help out wherever I can. >>

  It's a great way to learn and stay sharpe.  Your doing great....welcome aboard!

JimD
It takes a while to get used to the Ribbon, but on the whole I think it is an improvement, except for not being fully customizable.  And there are workbooks with information on where the commands are at this link:

http://office.microsoft.com/en-us/outlook-help/learn-where-menu-and-toolbar-commands-are-in-office-2010-and-related-products-HA101794130.aspx#_Toc268688375

Also interactive guides (select the command in 2003 and see where it is in 2010) at this link:

http://office.microsoft.com/en-us/training/guides-to-the-ribbon-use-office-2003-menus-to-learn-the-office-2007-user-interface-HA010229584.aspx?CTT=3
@Helen:
Have you found a way to create Right Click Shortcut menus (aka Popups aka Context menus) in A2010, either on a MDB in A2010 environment or  ACCDB in A2010 environment?

And/Or to modify existing Right Click Shortcuts that exist in an MDB ... inside the A2010 environment ?

mx
MX,

I have been working on an application (created in Access) but a user interface for creating/modifying right-click shortcut menus.  

I'm hoping to have a solid version up an running around March.
Really ... cool.

To say I am shocked that ... the existing Customize Dialog tool from A2003 was not made available in A2010 (or 07) would be an understatement!  Unacceptable is the better word.

mx
I never used access 2003 so I never got the luxury of the tool.

See if you remember this post MX.

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

This is what triggered my idea for the app to create right click menus.
I haven't done anything with right-click menus in A2007 or A2010.  I did a little with creating Ribbons for my Access 2007 book, but Ribbons have changed somewhat in A2010, and they are now somewhat easier to customize (though not completely customizable, as they should be).
I have recently made an Access 2007/2010 version of my LNC Rename addin -- this one just does control renaming (all you need in an existing database where the objects have the correct prefixes).  If anyone is interested, here is a link:

http://www.helenfeddema.com/Files/code62.zip
I am workining on an A2007-A2010 version of the full LNC Rename add-in, once I get some issues with coordinating with FAR resolved (the current version of FAR doesn't work with the .mdb add-in).
My motto to all users of any db's I create is: "When In Doubt, Right Click!"

mx
I have just uploaded the new version of my Time & Expense Billing app (Code Sample #29 on my Website).  It now supports Office 2010, as well as all previous Office versions from 97 on up.  This Word-Outlook application lets you enter time and expenses for clients, and create attractively formatted Word invoices and (for Office 2003 and up) statements.  Here is a link for downloading it:

http://www.helenfeddema.com/Files/code29.zip
Re my original message -- a friend who has both Office 2010 for Windows and Office 2011 for the Mac says that the Mac version is much more colorful -- so maybe Microsoft should move some of their Mac designers over to the Windows group!
Here's a dilemma that was brought up in http:Q_26827207.html: is it OK to use spaces in table names? if others have an opinion, let's discuss.

Recap of the previous discussion:

LSMConsulting: There's just no reason to name a table like that (i.e. with spaces in the name - v.r.), and if you EVER have to move up to SQL Server you'll find that those names aren't allowed.

me:there's absolutely nothing wrong in having spaces in table names, except the little trouble of enclosing these names in quotes whenever they are used. They are also supported in SQL Server (source: http://msdn.microsoft.com/en-us/library/ms175874.aspx )

LSMConsulting:
The document you cite specifically states this in the "Rules for Regular Identifiers" section:

"4. Embedded spaces or special characters are not allowed."

And further:

"Identifiers that do not comply with all the rules for identifiers must be delimited in a Transact-SQL statement. For example: "

So you CAN use them - they're just not allowed, as I stated in my earlier comment. You must take special care to delimit them correctly in order to use them in your code, just as you do when working with non-standard object names in Access.

me: regarding the spaces in table names: what the document says is the identifiers can be regular and delimited. There's nothing that says that delimited are somehow inferior to the regular, or that either of them are recommended over another, not to mention "aren't allowed".


LSMConsulting:
"4. Embedded spaces or special characters are not allowed."

I don't see how that is ambiguous or vague in any way. I realize that the article does go further to say  that if you decide to use characters or conventions which are disallowed, then you must take special steps to insure they can be parsed correctly.

Also, I'd be willing to bet that if you asked this question to the experienced developer community at large, you'd find a very solid (if not overwhelming) majority of those developers would come down on the side of "no spaces in object names", regardless of what environment we're talking about (i.e. db development, app developement, etc). This may change at some point, of course, but I don't see it changing in the near future.

================================

It's certainly not ambiguous or vague, only somewhat misdirected. The article does not say that they are disallowed for naming tables. They are disallowed to qualify for regular identifiers. But regular identifiers are not the only ones that can be used for table names. Making this logical transition is the same as saying that, for example, whoever does not qualify to be a citizen, has no human rights.

Good coding practice requires using explicit qualifiers in all places, such as specifying table owner in SQL Server, for one example. All script-generating tools also do produce the code with delimiters, even when the identifiers are regular and delimiters could be dropped. If we are to follow these practices, there's simply no scenario when having spaces would have any negative impact. If you know such a scenario, please name it, and that will seal the issue. Otherwise, I think, whatever increases the readability of the code, is always positive, and if a particular coder has chosen to use spaces, it's his own personal choice, and there's absolutely no objective reason to criticize him for that and suggest to change his style to the one you like better - even if you think that your style conforms to majority.
<<Here's a dilemma that was brought up in http:Q_26827207.html: is it OK to use spaces in table names? if others have an opinion, let's discuss.>>

  I don't use them, never have, never will, and will never recommend anyone use them either.  While it's true you can use them, doing so requires a delimiter of some type.

  However my biggest issue, which I learned early on, is that it is often very difficult depending on font and size to determine if you have one embedded space or two.

  So the smart thing to do is avoid the issue entirely.  

  Since there are no downsides to not using them (some might argure readability, but since coding is not typically done that way either, it's not a hard hurtle to get over) and there are to using them, I think it's an easy choice.

JimD.
> often very difficult depending on font and size to determine if you have one embedded space or two.

Doesn't the usual alternative, the underscore sign, present absolutely the same dilemma?
<<> often very difficult depending on font and size to determine if you have one embedded space or two.

Doesn't the usual alternative, the underscore sign, present absolutely the same dilemma? >>

  Most likely, but I typically don't use those either<g>  I will place an underscore in front of an object to indicate that it's a "temp" object and doesn't apply to the app, but that's it.  I never use an underscore or any special charaters (!, $, etc) in a object name as part of an app.

  All my stuff is done as:

<object type prefix in lower case><Name of object with first letter of each word in caps>  so a table would be something like:

 tblPatientVisitHeader
 tblPatientVisitDetail

 Only thing I waffle on as far as object naming is the use of a plural (s).  I often will do:

 tblCustomers

 where really it should be singular ("tblCustomer") as the table only represents a customer, but for some reason, I think of it as a collection, so I name it tblCustomers.   And yet in some cases (like the two Patient tables above) I don't because it just doesn't feel right to have it as tblPatientVisitHeaders.   Don't ask me why<g>

 No spaces or special characters in object names though ever.

JimD.
I would in fact name it Customers, because when you think about the table, it does indeed represent customers, so if it's the most intuitive, why resisting. Customer is one row, so maybe it would make sense to name a cursor (in sql server) that way :-)

One maybe interesting point is, why you need the prefix. Wouldn't not using the prefix make it more transparent, since query and table are fully interoperable (same as view and table in sql server). Sometimes, for various reasons, I even "substitute" a view for a table, i.e. I will rename the table and put view instead, so old code won't even notice.

<<One maybe interesting point is, why you need the prefix.>>

  I'm moving over to the other thread...

JimD.
I had a developer do an Access db that kept having issues when she started coding instead of just using the Append/Update queries and macros. The db had been in use about 6 months when they tried automating it with real VBA. Her key fields were named things like "Request No" and "Part No". Can anyone see the issue?

Another case -- back in SQL 2000 SP1 -- I was using the maintenance plan wizard to build the backup plans. Some expert MCSE consultant (and I use the term loosely) was brought in to help configure our Citrix. He threw a database on the SQL server named "Citrix Apps". It broke every single one of my maint plans. The worst part -- it didn't show up in the logs as an error overall -- only in the steps. It was a month that my butt was uncovered.

I was discussing the issue with the MCSE later on. His response was "Well M$ says you can do it."  My response was "While you can do something, it doesn't mean it is a good idea." He was flabbergasted at the concept. Later on the new networking manager told his firm that we never want him on our property again. We cheered.

I had an EE question on a corrupted SQL db used to archive Exchange. He couldn't understand why he was having issues working with a database named something like "Exchange Server1 1/1/2008-12/31/2008 Archive".

I avoid spaces and special characters in all my development. The only special character I use is the underscore. My preference is to use an underscore for field_names and CamelCase for variables. And if I have a choice for an export/import delimiter it is the pipe "|".

and if a particular coder has chosen to use spaces, it's his own personal choice,

I have seen, maybe, one long term coder that uses spaces/special characters for field/variable names. The reason for that is over time they have been burned by it. Yes, in the perfect world everything should be bracketed or quoted and fully qualified.

absolutely no objective reason to criticize him for that and suggest to change his style to the one you like better

I am going to  have to disagree with that. If the app/project is developed by a single person that will recode it for the rest of his life; or the application will never have to be changed -- code that way. The reality is after that coder is gone -- the line animals (production DBAs, the networking guy who got stuck with it, the junior coders) have to live with that developers choice to code that way. Also the number of applications that don't have a version 2.x.x or 3 or 4 is vanishingly small. Just like so many other places -- software is also publish or perish.
I think that using non-alphanumeric characters are a bad naming practice (underscores are allowed).  However, they are allowed in both Access and SQL Server.  Non-standard object names will require the use of [ ]
Leaving aside naming conventions: one Access topic that often causes different opinions, is whether or not, and to what degree, to use unbound forms. I personally avoid it, because if it's unbound, Access is losing too many of its advantages and there remains little reason not to use Visual Studio instead. Would be interesting to hear what others say.
I'm leaning toward a compromise configuration, where the form is bound to a single-row table for updating.  After update confirmation, the changed row data is merged into the all-record table and the form rebound to the all-record table for navigation and searching.  --  MOUSE WHEEL and n00b users = trouble
Right, that's what I usually do as well. With unbound textbox at the top, to find the record to edit. Though, if several records are found, I usually do show them in scrollable form.

I couple of times tried to to use "filter by form" as the initial state, to search for the record, instead of the unbound textbox, but somehow it did not fly.
All,

  Recently we held a discussion here:

https://www.experts-exchange.com/questions/26838925/Expert-Topic-Naming-conventions-in-Access-databases.html

 on codeing conventions.

  What I am wondering is if any of you think it might be worth while to try this out on a regular basis.  In other words come up with a list of discussion topics, then thrash them out.  Say maybe one a month.

  I was thinking that this is something that EE really doesn't offer in terms of content.  We often have discussions in threads, but they are generally limited to a few Experts and are not organized.

  If enough these come about, we could collect them and post them in an article, or announce them in the newsletter.

  I did float the idea of doing this site wide to the rest of the ZA's, but all I got basically was a yawn<g>.

 Anyway, thoughts appreciated...

JimD.
I tried this myself in http:Q_26839323.html, but there wasn't much of participation...
I think it's a good idea. Think that the main "thread" should be something different from this one, and should be nothing more than a "pointer" to the current topics-at-hand. This one tends to become cluttered with nonsense postings (some from my own keyboard) which would make it difficult for Experts to see what is currently being discussed.
Recommendations:
* Include "Expert Discussion" in the title.
* We might have several 'flavors' of such questions: Best Practices, Theory vs. Reality, Expert and Vendor Recommendations

====
I wish we could:
* 'pin' such conversations to the top of the open question list
* add links to such questions in one of the grey boxes
* limit participation to experts to badgers or above some minimal level
<<
* Include "Expert Discussion" in the title.
>>

 We've been using "Expert Topic:" in the question title up till now.  Expert Discussion sounds like a better choice though.

<<
* We might have several 'flavors' of such questions: Best Practices, Theory vs. Reality, Expert and Vendor Recommendations
>>

  Yes, there is quite a range, although at first it might be best to tag them all the same till a bunch get built up.  Maybe after that we can sub-divide them a bit.

<<
* 'pin' such conversations to the top of the open question list
* add links to such questions in one of the grey boxes
>>

  Me to<g>.  I've been asking for that for at least seven or eight years and I know others have asked for that feature as well.   I was also quite disappointed with the outcome of the landing page project.  I thought we'd end up with an actual home page for the zone that could be managed in detail.  To a very small degree we got that, but the tools for that are broken and it doesn't look like their getting fixed anytime soon. :(

<<
* limit participation to experts to badgers or above some minimal level
>>

  If needed, one of us can delete comments.  I don't have any problem doing that as long as it states in the question up front what the minimum requirements are for posting (be it rank or what ever).  But with that said, I think everyone's opinion counts, so as long it stays on a single track around a topic, I don't have problems with anyone posting if they want.

  Well overall there are a few of us on-board with the idea, so let's give it a go.  I think what we might want to do is turn each into an "article", so it appears in the questions and articles.  Then a table of contents question and article which can be kept updated as new ones are added.  

  That will make it easy to find these in a number of ways.  Last, when we've got a few of these built up, we can get a blurb in the EE newsletter pointing to the table of content thread.  I'm willing to corridinate all that.

  But first we need to brain-storm a list of topics, then pick a couple that will be good to start with (like the codeing conventions).  Here are a couple of suggestions:

1. Best way to use an Access up remotely comes up time and time again, but I think were all on agreement on that.  Probably should be an article.

2. A poll: favoriate development tools and why?

3. When would you still use ACE/JET as a datastore given SQL Express and that it's free?

4. What techniques do you use to optimize performace with Access?

5. What do you see in the future of Access?

6. What types of apps would you use Access for?  When or when not would you use it?

  Once we've gotten some more, we can pick the first few to start with.

Thanks,
JimD.

 

Maybe this could go along the lines of a blog? such as zone advisor's blog, for instance.
7. for years, Microsoft saw Access primarily as productivity tool rather than professional tool, so the common phylosophy, Help, etc., was more directed at a housewife storing recipes in the database than on professional developer. Do you agree? do you think this trend is increasing? decreasing?

8. At some point Microsoft was moving towards SQL Server, and the concept of ADP (Access Data Project, a thin client) seemed like the future. With Access 2007, it appears that the direction has been reversed, and Jet is the future again. Thoughts?
<<Maybe this could go along the lines of a blog? such as zone advisor's blog, for instance. >>

  Hum...good thought!   A Zone Advisor blog might be a good way to get around the lack of being able to manage a landing page.

JimD.
I actually have a good Access story as well, unfolding right now. Maybe experts will have some input.

It's however about specifically Access ADP. Access ADP, it turns out, has some mysterious limit of users - 20. It should be noted that unlike MDB, that holds both presentation layer and the data, ADP is only presentation layer, while all the data is on SQL Server. So the ADP file is pretty much like an executable. It can be put on a read-only file share, opened by workstation, and it will work. On opening, there will be a message that nothing will be saved (such as design of forms etc.), but it will work. If it's opened by Access Runtime, there won't be even a message.

Now get this. If 20 users have opened the ADP file from the share, the 21st receives error message that the file is in wrong format or damaged.

I opened support incident with Microsoft. MS engineer has confirmed that this is the case, however is unable to explain it (he mentioned multiple references of this effect in MS internal KB, not for the public). His only point is that placing Access file on file share is "unsupported scenario", because it has to be on local filesystem, and that is because "it's not a multiuser application". I argued that Notepad is also not a multiuser application, but that does not mean that it can't be on a share, or that only X users should be able to open it. Opening the source file by several users is not what multiuser is about. If the file is not supposed to be opened in read-only mode, then it wouldn't even for one user; but it does, even issues coherent warning, and then works. Then, if it's read-only, and if nothing is stored within the file, what multiuser are we talking about? The most interesting however is the question, how can Access on the workstation even know if he is 3rd user opening the file, or 21st? is there a parameter in the i/o request to open a file from the share that instructs not to open it if I'm Nth user? (because I guess, only server knows how many other users have already opened it). The engineer did not know the answer, so it's now being escalated. Still I wonder, how this is even possible?

In fact, this Q was asked on EE 4 years ago, http:Q_22091003.html  - with no answer.



vadimrapp1:  Thanks for your time in putting that together.  I should open a few eyes.
Ah.. ok, it only appears at the bottom, above the "Add your comment" box, and only after the original question is closed (I think?)
I just wrote an article in the Access zone about creating and using Tally Tables (number tables).
https://www.experts-exchange.com/A_5410.html
I'm close to answering a question (https://www.experts-exchange.com/questions/26943203/Access-query-to-create-new-columns-instead-of-multiple-rows.html), but my crosstab query is raising a 3070 error.  

There are no parameters.

XrefWithOrdColNames query:
SELECT Vdr_Bct_Pt_Xref_Tbl.Pt_Idn AS PartID, Vdr_Bct_Pt_Xref_Tbl.Vdr_Pt_Idn AS VendorPartID, Vdr_Bct_Pt_Xref_Tbl.Vdr_Idn AS VendorID, "AlternatePN_" & (Select Count(*) From Vdr_Bct_Pt_Xref_Tbl As V Where V.[Pt_Idn]=[Vdr_Bct_Pt_Xref_Tbl].[Pt_Idn] And V.Vdr_Pt_Idn<=[Vdr_Bct_Pt_Xref_Tbl].[Vdr_Pt_Idn]) AS ColName
FROM Vdr_Bct_Pt_Xref_Tbl
ORDER BY Vdr_Bct_Pt_Xref_Tbl.Pt_Idn, Vdr_Bct_Pt_Xref_Tbl.Vdr_Pt_Idn;

Open in new window


XrefWithOrdColNames_Crosstab query:
TRANSFORM First(XrefWithOrdColNames.VendorID) AS FirstOfVendorID
SELECT XrefWithOrdColNames.PartID, XrefWithOrdColNames.VendorPartID
FROM XrefWithOrdColNames
GROUP BY XrefWithOrdColNames.PartID, XrefWithOrdColNames.VendorPartID
PIVOT XrefWithOrdColNames.ColName;

Open in new window


The 3070 error message said that the Jet engine doesn't recognize '[Vdr_Bct_Pt_Xref_Tbl].[Pt_Idn]' as a field name or expression.

The XrefWithOrdColNames query runs without error and produces correct results.  I've tried producing the ordinal number in one query and then concatenating to the base string in a subsequent query with no success.

Got any hints for me?
Change your first query into a make table query and then call that table from the Crosstab.

SELECT
Vdr_Bct_Pt_Xref_Tbl.Pt_Idn AS PartID,
Vdr_Bct_Pt_Xref_Tbl.Vdr_Pt_Idn AS VendorPartID,
Vdr_Bct_Pt_Xref_Tbl.Vdr_Idn AS VendorID,
"AlternatePN_" & 
(Select Count(*) From Vdr_Bct_Pt_Xref_Tbl As V
Where V.[Pt_Idn]=[Vdr_Bct_Pt_Xref_Tbl].[Pt_Idn] And V.Vdr_Pt_Idn<=[Vdr_Bct_Pt_Xref_Tbl].[Vdr_Pt_Idn]) AS ColName
INTO tblXRefWithOrdColNames
FROM Vdr_Bct_Pt_Xref_Tbl
ORDER BY Vdr_Bct_Pt_Xref_Tbl.Pt_Idn, Vdr_Bct_Pt_Xref_Tbl.Vdr_Pt_Idn;
@GrayL

Is there any way to accomplish this with a query (no intermediate table)?
aikimark:  Sometimes, I am surprised with the circumstances under which a Crosstab query will not run.  I have found that by making a temp table as the intermediate step will sometimes remove the block.  Does the temp table route I proposed above work?  In the mean time I'll give it another going over. and get back to you.

Ray
@Ray

Yes.  The temp table works.  I posted that solution in the thread.  While tinkering with the query-only solution, I discovered that the string data in the source table is screwy.  Some of the rows contain apostrophe characters.  Some contain quote characters.  And some contain both apostrophe and quote characters.

I've gotten it to work without the temp table using DCount() instead of the (Select Count(*) ...) expression.  I had to add a Replace() function to get the column and value references to compare properly.

I will paste these queries into the linked question thread and see if the Replace() function could be the remedy for the (Select Count(*)...) version.
I might have an idea... By the way: hello everyone! I'm back for a while, don't know for how long this time around...

(°v°)

 Always welcome Markus and glad to see you back, even if it's only for a short while.

JimD.
In one question we have discussed ability to share our FAQ for Access users. May be anybody can write an article, where will be links to most helpful articles. We can add our proposals here or in other question
Amen, brother Jim
May be not a question, but article with links. We will advice to read it, if answer is already there. There could be advices how to prepare question, test DB, etc. We can add comments to article with new intersting references.
The article type “Resources” specifically cites such a list of links as an example (provided the accompanying text and link comments contains 300 words). I have often thought about creating such an article, but I would much prefer to see a true multi-author work.

At the moment, there is only one author per article, so if I create the article, there is no incentive for other experts to add links for me to incorporate into the list (why should I get the points?).

I remember a few questions some years ago with ended up with pretty nice collections of links. I lost these bookmarks (I should have saved them to my knowledge base), but I remember using them quite regularly at the time.

(°v°)
"Articles have to meet certain criteria for word count and content ("
Where is that criteria specified ?

mx
For example: http:/articlesGuidelines.jsp
It's also indicated on the left if you open the page “Write a New Article”: http:/writeArticle.jsp

(^v°) -- former article page editor
<why should I get the points?>

That is precisely why I (especially as a ZA) would not post this as a collaborative zone 'article'.  Since we don't have true multi-authored articles, the points for posting the article would go to a single person, as would additional points every time a link to that article was used in a solution.  It doesn't seem quite right :)

The same thing could be accomplished with a blog -- without the minimum requirements for publication, and without the points.
Some of you may find this interesting:

http://msdn.microsoft.com/en-us/library/bb188204.aspx#optaccsql_topic5

 It's a link to an article by Andy Baron on optimizing Access with SQL Server; some *very* nice detail on how JET actually implements the keyset cursor, chooses it's bookmark column(s), etc.

  I haven't read the whole thing yet, but from what I've read so far, if you work with Access and SQL server, it's definitely worth the time to give a run through.

  Interestingly enough, it's an "SQL" article, so I can see why many might have missed this.  Access types don't typicall look for SQL articles and SQL types will do the "Oh, it's Access" and ignore it<g>.  In fact I'm wondering why it was not published as an Access article...

JimD.
I guess they could republish as an Access article.  I've often wondered why EE doesn't allow you to post an Article in multiple zones.
Had a link to that one for a while Jim. ;-)
(At the end of my FAQ over the way here)

If there's one thing that annoys me more than anything else about it - it's the constant requirement to refer to "Office Access".  I've no doubt at all that it was a MS stipulation, not Andy's idea - but I'd pay real money to see that edited (and removed from history).  It just doesn't read well with it.  I experience a little niggle every time I see it.
I can't recall ever experiencing that in other walks of life... "My hands are full... Here's my key, open the House Door".  "Hang that bird seed on the Garden Tree."  "There sure is a lot of Sky Rain today."
I digress...

I think the section titled "Moving Query Processing to the Server" is a little harsh.  Perhaps "Guaranteeing" or "Encouraging greater" would have been better than "Moving". ;-)
Still a de facto resource though.
(As Andy's stuff always is.)
How about a new thread - this is getting long - been going since 24 Jan 2011.
Sounds good to me!
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