Access Experts: General discussion thread for Access Zone (28-May-2013)

A long time ago I adopted a function naming standard that no one else seems to use. I feel kind of left out in the cold. What function naming standard do you use?

Here's the standard I used (copied from a source on the internet that I've long ago forgotten):

xg_FunctionName - the "x" means generic. The "g" means global
ng_FunctionName - the "n" means not generic, the "g" means global.

Right here I have something very useful as I know I can copy the xg_ routines directly into another database and use them with no changes. If I copy the "ng_" routines, then I know I have to get into them and make changes relevant to the application I'm using.

For form object module routines, I use no prefix at all. A common routine name that I run from each form OnCurrent and many control AfterUpdate event procedures is called "SetControlProperties". The name is the same but the code contained within is unique to each form.

Thanks.

My mind is else where.  I'm trying to learn C#, .Net, DevExpress, and a application framework written on top of those; it's making my head spin.  Most thinking I've had to do in a while.

Frustrating as all get out to...had to give up on VS2012; was flakey with older versions of .Net.

Jim.

I have ONE, but very large, db.
104K lines of code that runs the app that runs our business.
Very seldom do I ever need to export code to anything else
The Program is an amoeba that consumes everything else

I don't use classes.
I've never been big on prefixes.
Why name a variable strSomeDarnString when you are going to declare it
Dim strSomeDarnString as String

I've never understood why someone declares

Dim intCounter as integer
for intCounter = 1 to Something
...
Next intCounter.

Select allOpen in new window

That's what 'i','x','y' and 'z' are for.
The 'for' gives it away, that what's next is just a counter.

Dim response as integer
response = msgbox(...)

Select allOpen in new window

No question about what response is for

Dim MyInput as String
MyInput = inputBox(...)

Select allOpen in new window

No questions there either

Anything where I think a variable may clash with a keyword or Field Name gets a 'My' prefix
MyDate
MyJobID
MyCaption

I CamelCase variables and function names both
Private Function BurnACD
Dim MyImportantVariable.
Then I can type in everything in lowercase...
And if Intellisense doesn't auto-correct it I know something is fubar

For subs and functions, Public and Private are generally enough.
If the sub/function is in a code module, it is meant to be used app wide
Code modules are named after the kinds of stuff in them
modEmailReports
modWindowHandlers
modMoreAPI
modGetUserName
If it is in a form/report module, it is meant to manipulate that object.
Very few of those are scoped Public.

Tables, Queries, Forms and Reports DO get the prefix treatment (tbl, qry, frm, rpt) Because THOSE objects I mean to keep.  A table prefixed with 'temp' has data that gets programmatically murdered regularly with impunity.

Anything that I don't bother to prefix is detritus that can be swept out when I have forgotten it's purpose.

A naming convention is used to make your code self-documenting.  The level of complexity you decide to pursue depends on the complexity of your development environment.  There are folks who slag VB/VBA for its verbosity.

Thanks, I'll take that over terse obfuscation any day!

I CamelCase variables and function names both
Private Function BurnACD
Dim MyImportantVariable.
You may want to double check your definition of camelCase.

camelCase (camel case) is a term which refers to the practice of writing compound words where the first letter of an identifier is lowercase and the first letter of each subsequent concatenated word is capitalized.

I've fallen out of the habit of prefixing application-related tables with "tbl". It just seems much easier form me to say

Select * from Customers;

than

Select * from tblCustomers;

If there's no prefix, then it's a table. My queries are all prefixed with "qry" so there's no confusion there.

Utility tables are all prefixed with "tbl". These are tables that I can copy from application to application with no modifications.

xg_FunctionName - the "x" means generic. The "g" means global

I hate the prefix idea. I will suffix stuff with Tbl, Qry, Rpt, etc. especially  when I'm doing purpose built stuff that will have something like LocDemographicsTbl that will have extra columns that links to other tables, but the export will be from LocDemographicsQry that don't have those extra columns.

As for generic versus specific functions, I've built a Utility_Module that has the ExportTableCSV, ExportTableTab, EmailUsers, ZipFiles, etc. It has my own stuff and code I picked up over the years. I just import it from  DB to DB and then build around it. If I add to it, then I use it in the next.

Utility tables are all prefixed with "tbl". These are tables that I can copy from application to application with no modifications.

Actually one I picked up recently -- you can use DAO to create tables on the fly. An example of the code is here. I have a DoesTblExist function in the Utility_Module. But either way you can create for the first time if not there or blow it away and create a new blank table to import data into.

So if you have a generic table with generic data, you can use that and a set of insert statements to populate it.

Or if you have distinct customer data that you never want to come together, you can know you are starting with a blank table.

Just a suggestion for making your coding better.

@acperkins
That's why I wrote it as CamelCase.
:)
I suppose ConcatenatedTitleCase would work, but that's pretty wordy
:)

I have stuck with the prefixes for tables etc.  There's no ambiguity about tblCustomer.CustomerName.  I'm never going to declare
Dim tblCustomer as string
But I might declare
Dim Customer as string

Each to their own, but whatever the scheme, the logic behind it should be rigorous, consistent, as concise as possible and it should become self-evident as your code is perused.  I won't touch my engineer's code.  He leaves all of his unbound controls with the MS generated aut-names

All of my table names are plural - i.e.: The customers table is a collection of customers, not a single customer. I even have a table named "People" with primary key "PersonID". (I even have a routine that turns plural words into their singular form). So I could use

Dim Customer as string

... with no ambiguity.

That's why I wrote it as CamelCase.
I am afraid there is no such animal.  :)

I guess I'm in the minority here.  I use prefixes for everything as I find it helps more often then not.

Jim.

You're not alone, Jim.  I prefix all my variable names.

< I use prefixes for everything as I find it helps more often then not.>
Then that fits with what your scheme needs to be: <whatever the scheme, the logic behind it should be rigorous, consistent, as concise as possible and it should become self-evident as your code is perused.>

If it is helping, the it is necessary :)
Where @pdebaets has no need of prefixes, I very much do because
set rs = db.openrecordset("select * from tblCustomers;",dbopendynaset,dbseechanges)
and
set rs = db.openrecordset("select * from qryCustomers;",dbopendynaset,dbseechanges)
can and does happen.  I think naming conventions are like dialects--they evolve and differentiate by 'culture' and usage.  I've only ever worked alone professionally, so my dialect has evolved to suit what I code.  It's based on Hungarian notation--but truncated for brevity where practical.

Being an Access VBA guy, 95% of my variables are local to the procedure that they are declared in.  The odd one gets passed out to another procedure as a parameter.  It's extremely rare that a second procedure will pass that variable to a third -- so prefixing variables doesn't add much clarity--for me.

If you do pass variables around like a joint at a frat party, then NOT prefixing them would be foolhardy.  Much depends on how you code, and what you code.

I like my errors to happen in the procedure that an event called.  They are much easier to find that way.  One procedure that calls another that calls a third which calls a fourth can lead to an error that manifests far away from where the problem actually originated--and is a bugger to find and fix.

So in some ways, my preferences are the opposite of what object oriented programming embraces -- reusing and multi-purposing code.  I'll only reuse and multi-purpose code if it is a) drop-dead simple, b) needs to be maintained as a block, and c) will be reused many, many times.

Complexity is something I strive to minimize.

<<can and does happen.>>

 Same here.  Tables and views work differently and I like to know which I'm working with.

 In regards to variables, the big thing I like to be aware of is if I'm dealing with a variant or not.  If not, you need to be carefull handling nulls.

  I also like to optimize by choosing the smallest possible data type.  If I have a loop that will never exceed 32767, I code it with an int.  However in doing that, then I need to be very aware of where I can use it.

  There are other situations as well were it's just handy to know without going back to the definition what I'm working with.

 And the time required to prefix is so minimal that I can't see why you would not.

 But as you said, to each his own and as long as it's consistent<g>.  Nothing worse than picking through code that's been done several different ways.

Jim.

I've just been 'unremembered' by EE within an active window.
Yesterday, I could F5 when the email showed an new comment.
Just now I had to re-log in.

And the previous comment I had just submitted went to the ether
Sigh.

I had both of the exact same things happen to me this morning as well.

Jim.

https://www.experts-exchange.com/questions/28141088/Loop-to-concatenate-list.html?anchorAnswerId=39205416#a39205416

Asker is trying to do a group concatenate in a query, and is claiming she's hitting a 255 character limit.  Works just fine for me :)

My "big" problem with prefixing variables is coding something as

Dim intMyVariable as Integer

.. then having to go back later and change it to a long data type because the value now exceeds 32767. Instead of just changing "Integer" to "Long", you have to change all occurrences of "intMyVariable" to "lngMyVariable". So I mostly leave the integer/long numeric variable prefixes off.

Dim MyVariable as Integer
Dim MyOtherVariable as Long

... but I use prefixes for other variables such as

Dim strMyStringVariable as string
Dim sngMySingleVariable as Single
Dim dteMyDate as Date


etc...

A module variable gets an "m" in front

Public mstrMyStringVariable as string
Public msngMySingleVariable as Single
Public  mdteMyDate as Date

A global variable gets a "g" in front

Public gstrMyStringVariable as string
Public gsngMySingleVariable as Single
Public  gdteMyDate as Date

@matthewspatrick

Your sample worked ok for me on A2003

Thanks Nick!

<<.. then having to go back later and change it to a long data type because the value now exceeds 32767. Instead of just changing "Integer" to "Long", you have to change all occurrences of "intMyVariable" to "lngMyVariable". So I mostly leave the integer/long numeric variable prefixes off. >>

 That's what Rick Fisher's Find and Replace is for<g>.  But it's actually rare for me to have to change something.

Jim.

In some ways, you just got to hate MS
7 years and three versions ago they introduced the split form / split report

Which blew up perfectly good A2003 reports.

7 years and three versions later the bangs still keep coming
bugged3.mdb

It is this kind of code that blows it up, by the way

'Dim ctrl As Control
'Dim OrderedControls(6) As String
'Dim x As Integer
'
'OrderedControls(0) = Me.subRegularCertDetails.Name
'OrderedControls(1) = Me.subBoomInfo.Name
'OrderedControls(2) = Me.subSideboomDetails.Name
'OrderedControls(3) = Me.subOverheadReport.Name
'OrderedControls(4) = Me.subBlockInfo.Name
'OrderedControls(5) = Me.subManbasketInfo.Name
'OrderedControls(6) = Me.subCapacities.Name
'
'For x = 1 To 6
'    With Me.Controls(OrderedControls(x))
'        .Visible = True 'screw with visible controls only
'        .Top = x * 0.144 * 1440
'        .Height = 0.0021 * 1440
'    End With
'Next x
'
'For x = 0 To 6
'    With Me.Controls(OrderedControls(x))
'        If .Report.HasData = False Then 'screw with visible controls only
'            .Top = 0
'            .Visible = False
'            .Height = 0
'        End If
'    End With
'Next x

Select allOpen in new window

Attempting to reduce a subreport's height to zero is verboten.
The pain in the arse is that I don't want any whitespace when the reports have no data
So now, they all have CanGrow equal true, they're 0.0007" tall and 0.0007" apart.

It works--but you cannot see that the subreport controls are there unless you look for them.
:(

It works--but you cannot see that the subreport controls are there unless you look for them.  :(

Been there, done that, got the T-Shirt.

Since the subreport would always have at least one item and up to ten, I created a header paragraph -- a box that could hold up to ten items and then a footer para. Since it was being sent to mortgagees that had loan deficiencies, we never got any complaints about white space. ;-)

I HAD forgotten, but it was CanShrink that was part and parcel of the BANG!
In the sample I posted, if you turn off CanShrink for all the subreports, make them all teeny and close together, then it won't hang Access when you close the report.

The maddening thing is that the error that reports is 'no records found' when very clearly all the objects have a workable recordset.

A2007 SP1 fixed a complete and immediate BANG!, and A2010 delays the BANG! on this particular sample until you close the object..  Maddening!

As posted in another Q

Sigh.

It'd be simpler if MS would just give us what we want
<CustomUI>
<Ribbon noFrigginRibbon = "true"><QAT noFrigginQAT = "true"></QAT></Ribbon>
<BackStage noFrigginBackStage = "true"></BackStage>
</CustomUI>

But alas, 7 years and three versions in and we still can't use that syntax

Instead we get SharePoint integration :(

You know, some day, some version, MS will get around to actually providing some decent documentation about the stupid Ribbon, and its XML
<customui>
<ribbon>
<qat/>
</ribbon>
<backstage></backstage>
</customui>

Ok fine.
Did you know there is <command> too?
And that, if you can find the command string, you can then cut off its event at the pass?
http://blogs.office.com/b/microsoft-access/archive/2007/10/10/ribbon-customization-repurposing-commands.aspx

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <commands>
        <command idMso="SetDatabasePassword" onAction="OnSetPassword"/>
    </commands>
</customui>

Getting any detail on this subject is tough, though

is it XAML spec?

IF you have VSTO, you can load an XSD for the Ribbon and get Intellisense support.
How much documentation goes with that, I don't know as I don't have VSTO

<is it XAML spec? >
I don't know what that really means.

XAML is a UI specification.  I first heard about it in either a WPF or a Silverlight presentation.
http://msdn.microsoft.com/en-us/library/ms752059.aspx
http://msdn.microsoft.com/en-us/library/cc189054(v=vs.95).aspx
http://msdn.microsoft.com/en-us/library/cc295302.aspx
http://en.wikipedia.org/wiki/Extensible_Application_Markup_Language

Well, some are stubborn beyond belief:

https://www.experts-exchange.com/questions/28151622/Update-field-in-subform-from-unbound-control-in-main-form.html

/gustav

Right. Good example.
Thick-skinned we call these types.

/gustav

Takes all types, I guess. Saturday's editorial on SQLServerCentral was musings about why MS has GUI tools for building SQL.  Why you'd want to build the majority of your SQL in text is beyond me.  Just like /gustav's guy.  Some folks get an idea in their head and won't let it go.
Like not using DAO code for inserts and updates.  You have to be dealing with 10's of thousands of records before SQL outstrips DAO.  But some folks don't believe it.

http://www.sqlservercentral.com/articles/Editorial/99811/

This Phil doesn't know what he's talking about. In VS you can perfectly well design classes with a designer, neither has he obviously never created a compound key in Access' query GUI designer.

But just let him consume his time with the command line. It lowers the competition for the rest of us.

/gustav

To begin with, the article had to do with the Designer tool in SQL Server Management Studio and I can tell you I don't know any experienced SQL Server professional who will touch it with a barge pole.  Why?  For one it is crippled: You can only do very trivial queries with it.  Even if you are successful building your query it will add a whole bunch of unnecessary code that goes from bad performing to plain wrong.  Just a cursory look at SQL Server Connect will give you an idea of all the problems users have discovered with it.

This Phil doesn't know what he's talking about.
I beg to differ with you on this one.  Phil Factor is one of the best writers on current trends in SQL Server.  Just go to Simple Talk and read some of the articles that have the pen name Phil Factor and you will see what I mean.

@acperkins
I'm not really sure that the gist of Phil's article was about SSMS, per se.  I think we'd both agree that Access's Query By Example editor is far superior to SSMS's.  For complicated sprocs, I'll use Access to get the more complex parts of the SQL beat into shape, and then paste them into SSMS for further refinement.  I keep hoping that SSMS's GUI tools would catch up to what Access offers.  It never happens.  Back to Phil though:

However, this thought remained with me: are we inclined to want to make things harder for developers with this sort of cosseting? Do developers really need protection from a simple, rational CREATE TABLE statement?

 Clearly, Phil is thinking about GUI's in general here.

 I don't usually go anywhere near these GUI fripperies inherited from MS Access...
Microsoft, unlike Oracle or Sybase, has had a strange cultural aversion to SQL...
MSQuery in Excel and the Access GUI provide elaborate tools to shield us from SQL...
Microsoft have somehow made the whole business of creating and altering tables, and their relationships, more difficult than it would be if one stuck to the standard SQL Syntax...

If you aren't going "...anywhere near these GUI fripperies inherited from MS Access" then just how are you generating new tables?  Handwriting the CREATE TABLE script, presumably--which is certainly not something I'd find productive or enjoyable.

SSMS's GUI leaves a lot to be desired.  But providing a GUI to generate SQL certainly isn't 'cossetting' and it certainly isn't making things harder.  SSMS's GUI tools for creating relationships are VERY sketchy compared to Access's and I certainly don't actually allow them to create relationships--I'll use them to generate scripts that I can trust are ACTUALLY going to do what I think they will.

Phil's tone here reminded me very much of the old-school Novell and Unix admins: "GUI? GUI!?! Why the hell does a server need a GUI!  Real admins don't need no stinkin' GUI"  /gustav's poster had pre-conceived ideas he was unwilling to exam or test.  I think Phil is showing some too.

Now, his later point I certainly have no qualms with:"...we get a scary mumbo-jumbo full of angle brackets and elaborate syntax that is unnecessary because it repeats the defaults. We get weird computer-generated names for constraints for which we don't want to provide explicit names. We get an awkward and unnatural syntax for foreign key constraints, separating them from the column to which they refer. And so on. This is a language dialect created by machines for machines. "

Yup,  the machine-generated language contains a lot of stuff to ensure uniqueness and no ambiguities.  When I start hand-editing I get rid of a lot of it to make it more readable.  So in a way we are back to where the thread began--naming conventions.

And, at the end of the railroad diagram article linked to at the end of Phil's editorial, he says "I realize that, for most purposes, a well-designed GUI is the easiest way to do a job on a Windows system but just occasionally,  code is just plain easier."

So, I don't know what to think--but then Phil may have clearly been having a bad day:

"The other day I was staring a SQL Server Management Studio and saw the impossible. There, in front of me, was a table build diagram showing that a table had two primary keys. "  That might throw me for a loop too -- because I'd never permit a table to be architected with anything but an autonumber primary key, and I'd enforce the composite key in other ways--probably in code.

I think we'd both agree that Access's Query By Example editor is far superior to SSMS's.
I am sure you are right, I have no clue.

Handwriting the CREATE TABLE script, presumably--which is certainly not something I'd find productive or enjoyable.
Unfortunately, if you want anything more than the basic CREATE TABLE using the Designer in SSMS, you are simply out of luck.  For example:
Table: Page or row compression. Sparse columns. FileStream, Triggers, Partitions.
Indexes: Compression, Fill Factor, Pad Index, Filtered Indexes.
Views: UNION, CASE, Indexed Views

And sometimes the actions the SSMS Designer takes are plain stupid.  For example, supposing you decide that you want to change a column from varchar(20) to varchar(25) in a table, it will attempt to rebuild the whole table when just a simple:
ALTER TABLE YourTableName ALTER COLUMN YourColumnName varchar(25)
would suffice.  Why is this important?  For one, rebuilding it will take your table offline, this may not be a big deal as you should not be doing this in Production in the first place. But if the table has several million rows, rebuilding the table will blow up your Transaction Log space as well as the data file and could take hours to complete.  A meta change using ALTER column is practically instantaneous.  As an aside the timeout in the Designer in SSMS is 30 seconds and no way to change it, so you cannot make these type of changes directly.  You can however copy the script generated and run it from a query window.  In SQL Server 2008 they made a change to the Designer and by default (it can be turned on) you can no longer make any changes that will involve a table rebuild.

And then you have the SQL that is plain wrong.  For example at one time (I am not sure if that is still the case) it used to produce something like this:
SELECT TOP (100) PERCENT Col1, Col2
FROM YourTableName
ORDER BY Col1
When anyone that has had but a moderate use of SQL Server knows that the ORDER BY clause in this case is totally pointless.

I do understand that most of these features you may never need and large tables may not be a factor in your environment, but for someone like Phil Factor that lives and breathes SQL Server and depends on it for their livelihood the simple fact is that the Designer will never catch up and from all my conversations with the Product Group at MS, they are not too concerned by that either.  The only reason that it was added in SQL Server 7 was to encourage MS Access developers to migrate to SQL Server.

I'd never permit a table to be architected with anything but an autonumber primary key,
And that in my view is a mistake, but that is quite another subject.

P.S. Most people here call me by my name: Anthony

@Anthony

SQL Server (up to 2008 anyway) doesn't listen to an ORDER BY without a TOP, so in order to get a view delivered in order, that SQL

SELECT TOP (100) PERCENT Col1, Col2
FROM YourTableName
ORDER BY Col1

may be needful. (Whether that desire is wise is a completely different story!  And whether you can get Access a small, ordered, editable result that doesn't bog the server, clog the network or need to be substantially swaddled by code because it isn't editable is another completely different story)
The discussion here
http://blog.sqlauthority.com/2010/08/23/sql-server-order-by-does-not-work-limitation-of-the-views-part-1/
goes back and forth on whether TOP with ORDER BY will keep working -- and whether TOP (100) PERCENT is even working the way naïve folks think it should.  Views can be funny monsters when viewed through the glasses of an Access guy.  An Access guy wants a view to behave like a table--be presented in order of the 'primary key' and be editable.  Views don't like to work that way.
http://stackoverflow.com/questions/15187676/create-a-view-with-order-by-clause

On the other hand, when you start googling up how to make a view play 'nice' the TOP + ORDER BY bit is what will come up.  I am subscribed to SQL Server Central because they do good stuff -- most of it only of academic interest to me, but worth perusing.

I'd never permit a table to be architected with anything but an autonumber primary key,
And that in my view is a mistake, but that is quite another subject.

And much like naming conventions, very much informed by one's circumstances :)

SQL Server (up to 2008 anyway) doesn't listen to an ORDER BY without a TOP, so in order to get a view delivered in order, that SQL
Just to be clear a SELECT statement can of course have an ORDER BY without a TOP clause and you can also have a TOP without an ORDER BY (when you don't care which rows are selected).  I suspect you meant that when creating a VIEW in order to have an ORDER BY you have to have a TOP clause and that is true.  Which is another good reason never to attempt to add an ORDER BY clause to a VIEW.  Any hack (see my next point) that you come up with to get this to work is just that, and will probably break with the next Service Pack.  (As it did when we went from SQL Server 2000 to 2005).

The point I was making is that in the specific case of TOP (100) PERCENT ... ORDER BY the QO is smart enough to know that you have selected all the rows and will promptly return them with no guaranteed order, in other words it will ignore the ORDER BY clause.  Connor Cunningham wrote a very good article on this subject that should have put this subject to rest.  Unfortunately, you will always get users who try and outsmart the QO by doing stupid things like this:  SELECT TOP(2147483647) ... FROM ... ORDER BY ...

So far, in in all my Access 32/64-bit work I have yet to use the Win64 compiler directive constant. I just use "#if Vba7 then ...". Has anyone found a situation where they HAD to use the Win64 compiler directive constant?

<<So far, in in all my Access 32/64-bit work I have yet to use the Win64 compiler directive constant. I just use "#if Vba7 then ...". Has anyone found a situation where they HAD to use the Win64 compiler directive constant? >>

They do two different things.   VBA7 tells you only if it is Access 2010 VBA code running and not if it's 32 or 64 bit.  WIN64 tells you if it's 64 bit (it's false if it's 32 bit).

The places where you need to use WIN64 is where the 64 bit API call is different then that of the 32 bit.

VBA7 just let's you know that PtrSafe is available to make an API call safe.

Here's a good example of that:

#if Win64 then
   Declare PtrSafe Function MyMathFunc Lib "User32" (ByVal N As LongLong) As LongLong
#else
   Declare Function MyMathFunc Lib "User32" (ByVal N As Long) As Long
#end if

Note that the API call is actually different under 64 bits (LongLong for argument and return vs Long).  If I was using VBA7 in that check and I ran the code in 32 bit office, it would fail as I would execute with LongLong, yet a 32 bit call into the API would be made.  LongLong is valid for VBA7 though.

Contrast that to this:

#if VBA7 then
   Declare PtrSafe Sub MessageBeep Lib "User32" (ByVal N AS Long)
#else
   Declare Sub MessageBeep Lib "User32" (ByVal N AS Long)
#end if

 Doesn't matter if I'm running VBA7 32 or 64 bit as the API call is the same.

 Since there are only a handfull of API's that were changed for 64 bit, it's not often you'd actually run into having to use the Win64 directive.

Jim.

Indeed:

a view delivered in order

The circumstances when the ORDER BY gets ignored remain ..murky.
A TOP (100) PERCENT hasn't always been ignored, especially if a primary key is the first column selected.  But it isn't hard-and-fast guaranteed to work either.  And an indexed view is a different monster all together.  And while something like SELECT TOP(2147483647) ... FROM ... ORDER BY ... will come up when you google up how to force a view to be ordered, it gives me the heebie-jeebies

But it isn't just the end-user that comes up with that syntax;

USE [TI_Data]
GO

/****** Object:  View [dbo].[qryAllSN]    Script Date: 06/11/2013 11:31:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[qryAllSN]
AS 
   /*
   *   Generated by SQL Server Migration Assistant for Access.
   *   Contact accssma@microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
   */
   SELECT DISTINCT TOP 9223372036854775807 WITH TIES tblSerialNumbers.SerialID, tblSerialNumbers.SerialNumber
   FROM tblSerialNumbers
   ORDER BY tblSerialNumbers.SerialNumber
GO

Select allOpen in new window

And Phil Factors point about monstrosities is certainly well taken
Here's another SSMS script-out

USE [TI_Data]
GO

/****** Object:  View [dbo].[qryCalEquipDue]    Script Date: 06/11/2013 11:35:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[qryCalEquipDue]
AS
SELECT     TOP (100) PERCENT CalEquipSerialNumber AS [S/N], ReCalDate AS [Due Date], Inspector AS [The Inspector], CalEquipID, CASE WHEN IsDate([recaldate]) 
                      = 1 THEN DateDiff([d], [recalDate], GetDate()) ELSE 22 END AS TimeRemaining
FROM         dbo.tblCalEquip
WHERE     (CASE WHEN IsDate([recaldate]) = 1 THEN DateDiff([d], [recalDate], GetDate()) ELSE 22 END BETWEEN - 3 AND 0)
ORDER BY [S/N]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "tblCalEquip"
            Begin Extent = 
               Top = 6
               Left = 236
               Bottom = 125
               Right = 429
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'qryCalEquipDue'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'qryCalEquipDue'
GO

Select allOpen in new window

But this one has an Order by that is not a PK

CREATE VIEW [dbo].[qryCalEquipDue]
AS
SELECT     TOP (100) PERCENT CalEquipSerialNumber AS [S/N], ReCalDate AS [Due Date], Inspector AS [The Inspector], CalEquipID, CASE WHEN IsDate([recaldate]) 
                      = 1 THEN DateDiff([d], [recalDate], GetDate()) ELSE 22 END AS TimeRemaining
FROM         dbo.tblCalEquip
WHERE     (CASE WHEN IsDate([recaldate]) = 1 THEN DateDiff([d], [recalDate], GetDate()) ELSE 22 END BETWEEN - 3 AND 0)
ORDER BY [S/N]

Select allOpen in new window

And a query in SSMS does NOT return the results in the order requested.  And Access doesn't receive them in the order requested either.  So TOP (100) PERCENT is being ignored.

Thanks Jim. That's useful to know.

Seems like it is just API calls and types where LongLong is used that need special Win64 handling.

The circumstances when the ORDER BY gets ignored remain ..murky.
Actually no, the rules are pretty clear.  Conversely if you do not use an ORDER BY clause then the results are returned in no guaranteed order.

A TOP (100) PERCENT hasn't always been ignored
It is the ORDER BY (and not TOP (100) PERCENT) that has been ignored since SQL Server 2005.  The fact that sometimes it is returned in the "correct" order is never guaranteed and nothing to do with the ORDER BY clause.  Just a cursory look at the Execution Plan can confirm that.

especially if a primary key is the first column selected.  
I suspect you meant the PRIMARY KEY CONSTRAINT was created before any other index.  Typically (but not guaranteed) results are returned using the CLUSTERED Index (not surprising since a CLUSTERED index is stored with the data).  And typically a PRIMARY KEY is created as a CLUSTERED index, but it is not necessarily true:  A PRIMARY KEY can be defined with a NONCLUSTERED index and then all bets are off.

And while something like SELECT TOP(2147483647) ... FROM ... ORDER BY ... will come up when you google up how to force a view to be ordered, it gives me the heebie-jeebies
And so it should.  It is an accident waiting to happen.  Not to mention a quite unnecessary one, too.

Generated by SQL Server Migration Assistant for Access.
Yep.  You hit the nail on the head and another reason why that tool probably should be avoided like the plague.

And Access doesn't receive them in the order requested either.  So TOP (100) PERCENT is being ignored.
And again it is not TOP (100) PERCENT that is getting ignored, but rather the ORDER BY clause.

<Not to mention a quite unnecessary one, too.>

Alas, not always unnecessary.  Sprocs and functions don't return editable recordsets to Access.  Editable views can be successfully constructed and used as linked tables by Access.

But if you want them to behave like linked tables do, then you've got to get SQL Server to return the results in an ordered fashion.  It's a disconcerting thing to open one linked object (a linked SQL Server table) and get your records in a predictable, ordered fashion, and then open another object that looks exactly the same in Access (a linked SQL Server View) and have predictability go out the window.

It would be one thing if all the linked objects always presented things in an unordered fashion--you'd remember to always explicitly order things in the end.  But the inconsistency isn't fun.
And applying a sort on the Access end of things doesn't always provide consistent results.

Worse, applying the sort on the Access end can result in the underpinnings deciding that the whole tables involved need to be sent over-the-wire to Access for processing.

Which, when you are on the far end of a 3G connection, or a terminal services session, is something you dearly want to ensure does NOT happen.

Rightly (because many expect a TOP (100) PERCENT ...ORDER BY should return all records in order) or wrongly (you shouldn't have a TOP (100) PERCENT ...ORDER BY in a view's definition in the first place) MS has classified the fact that queries of views that possess TOP (100) PERCENT ...ORDER BY don't return records in order as an issue
http://support.microsoft.com/kb/926292

Note that this 'fix' is for a query of the view, and not for the view itself.  It is designed to provide fidelity with how SQL Server 2000 behaved -- however the new, unordered behavior is by design and future versions of SQL Server will continue to display the unordered behavior

This behavior is by design. This behavior in SQL Server 2005 will remain in the future releases of SQL Server.

Alas, not always unnecessary.
I beg to differ with you on that.

>> Sprocs and functions don't return editable recordsets to Access. <<
You can certainly return a editable recordset from a Stored Procedure using ADO, but again that in my view is a questionable practice at best and defeats the whole point of an RDBMS such as SQL Server that relies on set based result sets for maximum performance.

But if you want them to behave like linked tables do, then you've got to get SQL Server to return the results in an ordered fashion.
And again, that defeats the whole point of a VIEW.

have predictability go out the window.
Absolutely.  The only way you can guarantee the results 100% is by using an ORDER BY clause.  It is just that using an ORDER BY clause in a VIEW is short-sighted at best.  Rather it should be implemented as SELECT Col1, Col2 FROM vw_MyView ORDER BY Col3.  This provides the maximum flexibility and you do not end up with several duplicate VIEWs with a different ORDER BY clause.

Which, when you are on the far end of a 3G connection, or a terminal services session, is something you dearly want to ensure does NOT happen.
Absolutely and another reason that editable result sets (at least with SQL Server) have become a thing of the past.  (I last used one circa 2001).

the new, unordered behavior is by design and future versions of SQL Server will continue to display the unordered behavior
Yep.  See here for a good explanation from one of the QO architects.
TOP 100 Percent ORDER BY Considered Harmful.

And this blog is also appropriate:
http://blogs.msdn.com/b/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx

Anyhow, I think we have beaten this dead horse and there is no need for me to further hijack this thread.  I also suspect everyone is tired of my ranting.  Do I hear a sigh of relief?  :)

Have a good one.
Anthony

<<Anyhow, I think we have beaten this dead horse and there is no need for me to further hijack this thread.  I also suspect everyone is tired of my ranting.  Do I hear a sigh of relief?  :)>>

 No, discussions like this is exactly what this thread is for.

Jim.

These are the types of discussions I enjoy in this thread.  

Unfortunately, we don't have enough of these and generally end up using it to ask for help with a particular question that someone has posted or pass of little tid-bits of information.

No, discussions like this is exactly what this thread is for.
Fair enough.  What part would you like me to rant about?
The use (or misuse) of updateable resultsets produced by Stored Procedures.
The rule that all tables should have an IDENTITY column and that should be a Primary Key.

As you all know I am just a tad biased, but if you insist...
:)

Does anyone have any problems searching for a question you know you participated in?  Lately, it seems like the number of results returned by the Search Within Results box in the My Participated Questions section seem less than expected.  

For instance, this question does not come up in the result set no matter what word I search for.  I have tried words in the question title, the body of the question, the body of any expert's answer, and the Author's name as well.  Interestingly, I was able to find this question by using the main search box at the top of the page by including my name along with the search string.

Well, using Advance Search is another way, but what if I wanted to search those that I put in My Knowledgebase or My Monitored Questions where I am not a participant?  I have a feeling that this bug is not isolated to the section I mentioned earlier.  

I just tried searching for the word "browser" along with my name using both search methods.    I received 5 results in the Participated section and 13 results using the main search.  Something is definitely amiss.

I never use the EE search....I always use Google.

Even to this day, I find that the EE search doesn't work all that well.  Maybe it's me, but I never seem to get the results I expect.  

 I've always found EE's interfaces and functions counter-intuitive to the way I think (can't tell you how many times I've messed up when uploading a file).

Jim.

Jim,
     I like to mark some threads as Monitored even though I'm not a participant.  Also, I add the ones I find useful to my Knowledgebase.  Whenever I want to go back to a question I saved for my benefit, it's easier for me to do a search through the Knowledgebase rather than through the main search and have to shift through all the results to find the one I'm looking for.  It seems rather regressive since I had already saved it my Knowledgebase for convenience.  

     Take for instance, the Expert Notify and General Discussion Threads.  Whenever a new thread is started because the thread is getting long, I save them to my Knowledgebase on the assumption that I can easily find them later.  However, if I search for the word "Discussion," only one thread that I saved last year comes up.  Searching for "Notify" doesn't bring up any results.  

     Sure there are other ways to go about this, but it just seems that since this search feature is offered, someone needs to check and see what's broken.

:-)
Ron

@Jim,
Sorry, I thought it was you who responded and didn't realize it was Netminder; that's why I addressed you in my previous post.

@Netminder
I wasn't aware that this was a known problem, sorry if I sounded like I was pushing the issue.  What's strange though is that I have always used this feature and have only recently started observing this problem.  I thought it had to do with one of EE's upgrades these past few months.

until you try to find that question you saw two years ago

Normally I would agree with this except that I do frequently use this feature and have many times looked for the same questions. I have had no problem before, yet now these same search terms I always use return next to nothing; hence, my question.

May be anybody can look at this question: Q_28171688
I don't have any elegant solution for it

Thanks, Jim :)
I understand question, but author seems like not any solutions except own, when I don't like to go this way.

Just in case anyone is not on the notify thread as well, the Access 2013 runtime is now available for download.  Link is in the comment here:

https://www.experts-exchange.com/questions/26838771/Expert-Notify-Thread-For-those-answering-questions-in-the-MS-Access-Zones.html?anchorAnswerId=39291486#a39291486

Jim.

I'm not getting through to this Asker, and I am leaving for vacation today, so if another Expert can jump in it could be helpful:

https://www.experts-exchange.com/questions/28173241/help-with-query-and-vba-code.html?anchorAnswerId=39295342#a39295342

The Asker wants a way to have separate user-specific tables, but I don't think that's the right approach at all.

Just tried messing around with an app that was written as a web database in A2010; just about the most frustrating thing I have ever worked on.

  Trying to place controls in approximate positions in layout view of forms and there seems to be no rhyme or reason to empty cells.  UGH!

   I've spent hours just trying to get controls into the basic positions I want and can't seem to really control anything.

  This is nuts.  Tools are supposed to make my job easier, not harder.

Jim.

I've spent hours just trying to get controls into the basic positions I want and can't seem to really control anything.

Don't you remember the days of Access2 where you had to turn off the snap to grid feature each time you put the form into design mode?

Then of course all monitors and resolutions being different -- what may look good to you as a designer can look like garbage on the end-user's screen.

Trust me, this is nothing like that.

 Sometimes empty cells are created, and once created (because of changes you made) they then need to be deleted.  OK, I can live with that.

  Sometimes I can merge empty cells, sometimes I can't and there's no obvious reason why.

  Sometimes I can delete an empty cell and sometimes I can't and there's no obvious reason why.

  There doesn't seem to be any way to change what row/column a control is in the grid.  I can check an empty space before I move the control there and see the row/column it's in, but when I move the control there, it stays grouped with it's original row/column group.

 Some controls in a column are streched the full width of the design surface, some are not even though they are part of the same column.

  It's just plain weird.  And I've read quite a few articles on-line and there's nothing I haven't already tried.

  The real bothersome statement that I found was "Note:   As you split and merge multiple cells in a layout, the underlying row and column structure can become somewhat complex.  As a result, it can be difficult to resize just the cell you want.  We recommend that you first adjust row heights and column sizes as much as possible, and then split and merge cells to create the layout you want"

  So basically, the further you edit and move things around, the more problems your going to have and that's just what I've found.    I'd love for there to be a command to reset the entire layout and then let me start over, but you can't even do that.

 Like I said UGH!

Jim.

The real bothersome statement that I found was "Note:   As you split and merge multiple cells in a layout, the underlying row and column structure can become somewhat complex.

WTF? It's editing your DB design because you are building a form?

What if I want a solid ten digit number for a phone but want the normal 3/3/4 to appear to the user? Will it split the field? What about displaying only the last 4 of an SSN but wanting to be able to enter the 9 digits?

That is a design flaw to me.

No, the rows/columns it's talking about are in a web form in layout view.

It's basically a table of cells consisting of rows and columns.  These all exist within a layout (and that's typical for any product that does web forms; a series of cells within a layout control).

You drop controls into a cell and it no longer becomes empty.  If you type into an empty cell, it becomes a label control.

 So think of these cells as basically "blank" controls.

 The splitting /merging of these cells is what controls the layout (think of Excel).

Jim.

Sounds like the "layout" in form design view as well.
Sometimes I have to remove the layout completely because it has gone nuts.

And the "help" is really no help.

/gustav

Access 2003 is the last version with useful help.  Subsequent versions with 'web help' lack the needful cross references to be useful.  Combined with the brutal and predictable corruption of database objects when created in A2007+ but edited in A2003, the help keeps me dev'ing in A2003

Nick,  life moves on, you must too or you will become obsolete!  And you will find that most of your clients have moved on from 2003 to 2007 and 2010.  Most of my clients are still using 2007, but I've got one that moved to 2010 (64 bit version, which I hate).

The biggest problem I have with the 2007/2010 help is there are very few code examples in the help, but you can generally find everything you need searching through EE or the web, just takes a bit longer.

Dale

I grow more fearful with every Office release that doesn't contain anything substantive for the Access developer that we are all obsolete.  Since I am the client, and the client is me, I have no fear that the client will move on without me.  We run a mixture of O2003, 2010 and 2013.  Office 2007 was frankly unusable, and the few versions of it we had around got promptly upgraded to 2010 upon its release.  Much like Vista, some bastard stepchildren die out of order :)

Anything to do with SharePoint is a complete waste of time.  I shared the following with my sister, who works in a big shop that was frog-marched to SharePoint by the PHBs.  http://paulswider.com/sharepoint-jokes/
The reaction was pretty much like the one here
http://spreactions.tumblr.com/post/46246995895/when-people-make-sharepoint-jokes-spreactions

MS doesn't really give a damn, though.  To them, SharePoint is a billion dollar division.  To us, it is a pointless diversion from the things we really need.  The nice folks at MS ask for feedback.  I sent some:

And please pass a note to the Office folks.
Developers (especially MS Access devs) need some real joy.
And joy has NOTHING to do with SharePoint -- at all.
Importation of Word documents as the layout for Access Reports.
Export of Access reports to Word
Export of Access forms to .Net
The return of the Database Window.
Improvements to VBA
Paths to move VBA code to .Net
It has been a full decade.  Give devs what they've asked for--and not more SharePoint integration, which no sensible person without a PHB wants.

I don't think anyone is listening.  The folks at MS just don't seem to grasp that granular control of the UI is what Access developers presently have, and what they continue to require.  GridView controls are all nice  -- but completely useless for most folks who dev Access solutions.  None of us here would turn users loose on a datasheet Form view for anything serious.  There are far too many ways for the end user to bugger the data, and far too few ways for the dev to keep that from happening.  And yet, all these 'wonderful codeless, all-purpose controls' are little more than glorified datasheet views.

So I am pretty much obsolete already.  The backend stays in SQL Server, and I am forced to take a quantum leap in complexity in the jump to .NET.  The only question remaining is whether to go with ASP.NET and gain the wide reach of the web, at the loss of the power of the desktop; or to go with VB.NET and keep the richness of the client.

Either way, it's a steep learning curve and 105K lines of code, and 130+ forms to redo.

And then there is reporting.  Just what am I going to use to replace my existing Access reports?

<< don't think anyone is listening.>>

 I think they are.   Access 2007/2010 landed with a very dull thud, and as far as 2013 , that has landed with an even bigger thud from what I can tell.

 I think they've got one more release to get the web side right and give it some power.  If they don't, it will be too late and they will have lost too many developers.   Access will end up squarely in the end user category.  And that's just moving forward.

 With no real migration path for existing users, they have alienated a large portion of their installed base.

 I would harzard a guess that anyone using Access seriously for app development cannot move 99% of the apps they have to the web format without a total re-write and significant loss in functionaility.

<<And please pass a note to the Office folks.
Developers (especially MS Access devs) need some real joy.>>

 The problem is, as with any large company, the folksnear the bottom (and I mean the ones actually working on the products) can only work within the guidelines their given despite what they hear from people like us.

  At this point, anyone with half a mind can see that for Microsoft, it's the web, the web, and nothing but the web.

  To that end, that means:

 Office 365
 Sharepoint
 Exchange
 Azure

  and nothing else.   That's where Microsoft is heading full steam ahead and that's why I think two things:

1. Access desktop development is dead.  I mean really, what have we gotten since 2007?  If you stand back and look at what was done, most of the features were geared for getting to the web (ie. layout view for forms and reports comes to mind).

2. Access will become a front end/UI for basic CRUD operations and that's it because they have moved it so far into the end user camp.

  This became even clearer this past week as I worked on a A2010 web database.  Yikes, I found it hard even to do my job properly.   Relationships created automatically to the point where I wasn't sure actually what was being stored in a table, names of objects defaulted, an autonumber PK assigned to every table automatically, etc.

<<Just what am I going to use to replace my existing Access reports? >>

 Crystal Reports or SQL Server Report writer are the two basic choices.

Jim.

The web stuff that they have attempted to  graft on to Access has been mostly a non-starter because you cannot use VBA code with it.
Strike One.
In the 2007/2010 frame ~50000 rows in a SharePoint list was the top end for table size
Strike Two, fouled back out of play.
They've 'fixed' that by putting the data in SQL Server proper, rather than SharePoint lists, but...
In 2013, all the web stuff REQUIRES SharePoint -- and you don't have full control of the backend in the manner you expect
Strike Three.

<<Crystal Reports or SQL Server Report writer are the two basic choices.>>

Crystal Reports is an expensive steaming pile of ...
And licensing full-blown SQL Server to get reporting ain't cheap either.

I am seeing what MS's vision is.  The missus has a Nokia 610 and a Lenovo IdeaTab K3110W.  Combine those with a three monitor desktop all running Win 8 and an Xbox One and you have a very powerful and capable ecosystem.  Apple is sooooo dead.  They just don't know it yet.

But Office without VBA is ...neutered.  The whole power of it as a productivity platform is how you can sew it together.  Without VBA, you may as well run OpenOffice.  The vision of the future being HTML5 and javascript is delusional.  Even with HTML5, a browser lacks the true power of a rich client.  To throw away VBA to get web portability is a deal that only a certain subset of users and applications are going to make willingly.

And let's not even talk about network latency and bandwidth.  Some things are never going to make sense.  It's a nice niche--but it is a niche

I already have one open for the same asker that (s)he hasn't completed.
When you hang your Answerer out to dry--and it has happened more than once--you don't tend to want to go back.

Especially with stuff that complex.

And an asker who seems more interested in turnkey solutions than learning.
Gander here for a look
https://www.experts-exchange.com/questions/28128064/How-Can-I-Loop-Through-Table-and-Get-Data-Numbers-and-then-Format-in-a-SQL-Table.html

From comments made, seems like he's been busy with some other things.

No doubt though, his questions are on the complex side.

Jim.

@JDettman

We're all busy.  That's no excuse to disregard site etiquette
Especially if all of your questions wind up Neglected

For the Excel one, I'd haul all the code into Access and just do it, if it were me.
Failing that, one could build a form to catch the final desired filename, open excel, fire in the data, set off the macro, force the save out  and then give control to the user.

All eminently doable.  Generally, I don't code the Excel, I code Access to do the Excel.
But there's a fair bit of work in it, and since the poster has a habit of not following through on anything that isn't turnkey, I'm not inclined to take it on.  We'll see whether he bites on your posts, or abandons the question.

Or, to be perverse, I may post...or not :)

@JDettman
He posted another question that is grinding at the same functional issue. :)
On a personal note, I seem to be very Sage today.

Nick67

Anyone notice a severe decrease in EE email notifications?  I have not received a single notification in over 4 hours? (18 July, 07:30 to 11:30 Eastern)

I see that the MVP Summit has been announce for 18-21 Nov.  Is it worth the cost of trip?  For those who have attended, do you go with the room-mate option, or go with the 50% coverage?

I usually go with the room mate option, going solo would be prohibitive for me.  Check out the rates for the hotels and multiply by 4 and you will see what I mean.  As to whether it is worth it, that really depends on your area of expertise and where you are travelling from.  If it is MS Access, Patrick would be a good person to ask.  I can tell you that for SQL Server it is well worth it and the only reason I am not going in November is because I already went to the one that was held in February and because I am going home in November.

Dale,

I'm an Excel MVP, and I have found the Summit to be very, very worthwhile.  The opportunity for me to network with other MVPs is amazing, and the relationship between the Excel MVPs and the MS product team is very good.  For insight into how the Access MVPs see it I would defer to Access MVPs, although the Access MVPs who go seem to be having a great time.

I too go with the roommate option, even though that usually means bunking with zorvek :)

Patrick

And I should have mentioned if you do not know your roommate, ear plugs are a necessity. :)

Not to mention the fact that it is the week prior to Thanksgiving...

I noticed that the invite includes those who were in the last 5 quarterly selections, is that normal?  I cannot imagine that the weather in Seattle is any better in Feb than in Dec.  is it common to have more than one of these summits in a year?

I' seriously considering attending.  When I used to attend the Access Advisor Summits, I always learned a lot and met some great developers.

Do you guys generally just attend the sessions for the group you are an MVP in, or do you attend some of the others as well?

anyone else disappointed with the new search feature?

To me, this looks like a subset of what we used to have.

is it common to have more than one of these summits in a year?
No.  It has been always at the end of February (at least for the last 10 years), but they want to change that so that it is closer to PASS.  From my understanding, they are also going back to using the Microsoft campus as a primary location.

Do you guys generally just attend the sessions for the group you are an MVP in
There are usually more SQL Server sessions than I can attend, which makes going to any other group impractical.

Eric,

OK, I was looking for how to search for posts by a particular member.  I see that that is now accomplished by simply putting that userid into the standard textbox, along with other key words.

How do you do the OR search?  I'm assuming the "And" button in the attached image is supposed to change to "Or" when clicked, but mine is disabled (Firefox 22.0)What do I need to do to enable that command button so that it changes back and forth between "AND" and "OR"?

Ok,

Has anybody hacked together a Word mailmerge document that uses a query that has a form parameter as a datasource?  It's looking like parameterized queries are verboten on the Word side.  Can it be VBA'd from the Access side?  Or does it just go bang?

I may knock the data out to Excel, and then link Word to Excel -- and replace the Excel sheet using VBA each time before running it.  That'd work but is not truly elegant.

And no, sigh, an Access report is not feasible in this case

Nick67

Stupid pet trick of the day:

Ever wanted to change the actual Windows default printer, and not just Access's?

Dim oapp As Object
Set oapp = CreateObject(Class:="Word.Application")
oapp.ActivePrinter = "Some other valid printer"
oapp.Quit

That's it
Of course when you expect Word to behave like Access does, and NOT mess with the actual system default printer, it is a bit of shock to discover that this works :(
I swore I wasn't using API code to mess with the default printer...and I wasn't

Old but probably still serviceable, a complete Access / Word mail merge interface from the Access side.

This was based on the original work of Paul Litwin.   Please keep all notes intact if you use the code.

Jim.
mmA2002.zip

Thanks Jim!

Looking at it, it made me realize that I could temporarily change the query I want to base the merge on to a make-table query and create a local table (since the front-end deploys down to each station)  I can then dump-and-fill that local table in VBA and run the merge document.  That'll work!

Thanks,
Nick67

Anyone by chance have an install of Crystal Reports RDC?   Trying to help someone out moving an old app to a new server and we can't locate an install.

 The app display's reports from Crystal in a form and uses the RDC componets.  I've tried to move and register some of the DLL's manually, but no joy so far.

The app has references set to:

crystl32.ocx
craxdrt.dll
crxaddrt.dd

Jim.

Any ideas here?
https://www.experts-exchange.com/questions/28218128/Form-calculating-until-click-on-form-or-control.html

The OP wants some calculated controls to immediately reflect updates made from a calendar control (without setting focus elsewhere with a mouse-click)

I've looked at the one mbizup noted just above.
There's now a sample to muck with.
Very interesting.
If you use the keyboard to alter the control, the conditional formatting changes kick in immediately.  If you use the DatePicker, however, the conditional formatting changes don't occur without some action, any action, from the UI

I don't use the DatePicker myself, since I used the ActiveX calendar in A2003 and have gone to the trouble of ensuring that it works in my environment in spite of MS deprecating it (curses on their hides) -- but it is quite interesting that if the DatePicker is the source of the change that causes the OnChange event that the conditional formatting doesn't update, while if the keyboard is the source of the change that it will.

its not possiable to sMPLE T

Hi lavini,

Welcome aboard - I see you just signed up here.

I don't quite understand your comment.  Can you explain again?

What ever happened to the option to identify a solution as helpful?  At some point in the past, didn't we have a way to award additional point to the solution provider, well after the fact.

I so rarely have to actually ask a question in the Access Topic area any more, but frequently find answers when I am searching for something, or when another expert answers a question.  

Why don't users that use the search feature have the ability to select a solution as "helpful" or the "answer" to their unasked question, and award that individual points?  I guess we could just "post a comment" at the bottom of a questions thread to acknowledge that their solution was useful to someone other than the original poster.

Dale,

Those voting buttons were removed a long time ago.  They were a way to raise/lower a numeric score that was visible in the upper right hand corner of the question.  That score was meant to reflect on the overall quality of a thread, but the score and buttons in the comments had no effect on the points or ranks of the Experts who posted the comments.

Here's an interesting little tidbit from
https://www.experts-exchange.com/questions/28232172/Forcing-a-Date-TIme-into-an-Access-Make-Table-Query.html?anchorAnswerId=39472186#a39472186

If I execute
SELECT format(#12/30/1899#,"short date") AS TheDate INTO tblTimes;
What value will go into the table?

And if I execute
SELECT format(#12/30/1899#,"Medium date") AS TheDate INTO tblTimes;
What goes in?

At first blush, I thought they should be the same--ultimately, zero.
But they're NOT

The first hammers in the expected zero date of 1899-12-30 12:00:00 AM
The second one punches in 1999-12-30 12:00:00 AM

I suppose I should have expected that.
Format(#12/30/1899#,"Medium date") --> 30-Dec-99 --> 30-Dec-1999
But I was still a bit surprised.

@Nick67

You ask "Why?"  I say "Y-2k."

Or BEDMAS
I just didn't expect off-hand that the format would truncate and then the insert would assume.
It makes sense in a way.
The format is going to occur and then the insert, and the format leaves 30-Dec-99, which the system is going to assume is 1999
But it was a bit of a surprise.

Since you are inserting a string (output of the Format function) into the date field, you shouldn't be surprised to experience some conversion issues.

Ha Ha. Yes, I'm more surprised that you are surprised by this. "Everyone" knows that in SQL you must format a literal date value to a string expression for a date value:

SELECT Format(#12/30/1899#, \#yyyy\/mm\/dd\#") AS TheDate INTO tblTimes;

or create a straight date value:

SELECT DateSerial(1899, 12, 30) AS TheDate INTO tblTimes;

/gustav