want to come to an online user group meeting?

[strive4peace]

here is a most excellent presentation by Colin @isladogs. The content is interesting and useful ... and the delivery is one of the best I've ever seen. Colin is such an amazing teacher.

AEU: Optimising Queries & the JET ShowPlan (Colin Riddington) (1:08:39)

 

[Pat Hartman]

Thanks Colin. That was excellent.
I would like to make a couple of points.
1. the choice between WHERE vs HAVING isn't actually arbitrary. When you use the QBE to create a totals query, Access always puts your criteria in the HAVING clause, probably for simplicity because it actually knows which items can/should go where and should probably create both clauses when necessary. Any item can go into the HAVING but the only items that must go into the HAVING are values that do NOT exist prior to running the query. So, that would be anything that is not a Group By or possibly an Expression. Expressions might go either way depending on the Expression. So for example, if you want to see the customers for New York with order totals that are > $500, "New York" is a value in the input and therefore you would use a WHERE to include only rows for New York. But Order totals is calculated with a Sum() and so the total order value for each customer in New York would not be known until the aggregation is complete. The other thing to note is that even though you use the state in the criteria, it does not need to exist in the Select clause so it doesn't need to be in the output recordset. The use of the WHERE when appropriate can greatly reduce the number of rows that need to be aggregated therefore saving Access a lot of work. Why would you want to process 50,000 rows when you can process 300? Always use the WHERE whenever you can.
2. "Index your Joins". When your BE is Access you should NOT create explicit indexes for join fields because behind the scenes, Access knows it is smarter than you and it can be very helpful. This is one of those situations. When you use the Relationships window to define your joins and enforce RI, Access automagically creates a hidden index on the FK field because it knows one is needed. On the other hand, when you open Access for the first time which none of us has done in a hundred years, Access has set a default for you in File/Access Options/Object Designers/Table design view to AutoIndex on Import/Create all fields with the listed suffixes. I don't remember the exact list but it includes "ID" and "CD" and a couple of others. We have already turned this off because we know enough to create the necessary indexes but novices can end up with both over and under indexed tables due to this setting. It will create a visible index for the "ID" fields while the Relationship definition will create the exact same index but hidden. The only place you'll see the hidden indexes is the Indexes of the tabledefs collection. To prove this to yourself, Create a new table. Add a PK and then add a FK to a different table. Save it. If you open the indexes dialog, you will see only the PK index. Run the code below to loop through the indexes. You will see two indexes. One for the PK and the second for the FK. Go back to the table and add an index to the FK. You will now see two indexes in the indexes dialog. Run the code below to loop through the indexes and you will see three indexes for your new table. The PK, the one you created, and the one Access created for you.

In the pictures below, you see with the pink lines linked to the Indexes dialog but the indexes in blue were created by the Relationship definition.

This code is taken out of context. It is from a documentor that puts the results in the table. You can modify it to just print to the immediate window and add your hardcoded path to the BE.

Sub Create_tblTableIndexes()

    Dim db As DAO.Database
    Dim ThisDB As DAO.Database
    Dim tblLoop As DAO.TableDef
    Dim fldLoop As DAO.Field
    Dim idxLoop As DAO.Index
    Dim TD1 As DAO.TableDef
    Dim QD1 As DAO.QueryDef
    Dim TempSet1 As DAO.Recordset
    Dim Position As Integer
    Dim CountIndexes As Integer
    Dim strDatabase As String

    'strDatabase = "C:\hartman\ImportDB2.mdb"
    strDatabase = Forms!frmPrintDoc!txtDBName
    
    CountIndexes = 0
    Set ThisDB = CurrentDb()
    If strDatabase = "" Then
        Set db = CurrentDb()
    Else
        Set db = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
    End If
    
    db.Containers.Refresh
    
    Set TD1 = ThisDB.TableDefs!tblTableIndexes
    Set TempSet1 = TD1.OpenRecordset

    ' Loop through TableDefs collection.
    For Each tblLoop In db.TableDefs
        ' Enumerate Fields collection of each
        ' TableDef object.
            
'' add error checking for 3024 - not found in collection
'' add error checking for 3110 - no permission to read msysmodules2

        On Error GoTo ErrorHandler
        For Each idxLoop In tblLoop.Indexes
            CountIndexes = CountIndexes + 1
            Forms!frmPrintDoc!txtIndexCount = CountIndexes
            Forms!frmPrintDoc!txtIndexName = tblLoop.Name
            Forms!frmPrintDoc.Repaint
        
            If Left(tblLoop.Name, 4) = "MSys" Or Left(tblLoop.Name, 1) = "z" Or Left(tblLoop.Name, 1) = "~" Then
            Else
                Position = 1
                For Each fldLoop In idxLoop.Fields
                    TempSet1.AddNew
                        TempSet1!IndexName = idxLoop.Name
                        TempSet1!TableName = tblLoop.Name
                        TempSet1!Unique = idxLoop.Unique
                        TempSet1!PrimaryKey = idxLoop.Primary
                        TempSet1!OrdinalPosition = Position
                        TempSet1!FieldName = fldLoop.Name
                    TempSet1.Update
                    Position = Position + 1
                Next fldLoop
            End If
        Next idxLoop
    Next tblLoop

    db.Close
Exit Sub

ErrorHandler:

Select Case Err.Number
    Case 3110
        MsgBox "Open " & strDatabase & " and change the admin security to allow read for MSysModules", vbOKOnly
    Case 3043, 3055
        MsgBox "Please select a valid database. Error #" & Err.Number, vbOKOnly
    Case 91   ' db was not opened so it cannot be closed.
        Exit Sub
    Case 3044
        Resume Next
    Case Else
        MsgBox Err.Number & "-" & Err.Description
End Select
Exit Sub
Resume Next
End Sub

 

[isladogs]

Crystal / Pat
Thanks to both of you for the positive feedback on this session

Responding to the 2 points raised by Pat:
1. HAVING vs WHERE - Yes I agree with the comments. I believe this was discussed in some detail at around 11:33 & again at 51:44

2. INDEXING - whilst indexing is generally important, you are correct that it is possible to end up with 'duplicate indexes' on the same field. However I believe that is only the case where the index properties have different values - Primary (Yes/No), Unique (Yes/No), Ignore Nulls (Yes/No).
If you manually set the index properties to be the same as Access believes should the case, then Access does not create another index.

 

[Pat Hartman]

You're welcome.

I only commented on the WHERE and HAVING because although you did mention when they ran and even why the WHERE was faster in some cases, you were not specific on how to decide which clause you should use. I know it is a small point but I wanted to make it clear that the choice isn't arbitrary. For every field you want to use as criteria, the placement into the WHERE or HAVING isn't optional even though everything will work in the HAVING.

The index on the FK is not unique. I also see multiple indexes on the PK created also when I look at other peoples applications. They have a primary key and then Access helpfully creates a second index based on the suffixes in the AutoIndex setting. So there are two cases whee Access "helps" you to create indexes.

The point is that Jet/ACE work differently. Access automagically creates HIDDEN indexes on FK's when you define a relationship whereas SQL Server does not do this automatically. You need to manually create the index on the FK.

 

[strive4peace]

for those of you starving for an online user group meeting to attend for Access ... Mike Wolfe (nolongerset.com aka @NoLongerSet ) posts an article each week about Access that includes online user group meetings that are coming up, as well as what's come to his attention for the past week that folks have posted about Access or VBA.

Here's the current link:
https://nolongerset.com/week-in-review-2022-10-22/

I try to keep this thread especially updated with links to meetings presented by those who post on AWF that sound interesting ... and most of us love anything Access!!

Please comments and share your ideas

kind regards,
crystal

 

[Pat Hartman]

Updating it a few days before the meeting and including the meeting link would be very helpful although you shouldn't be responsible for doing this, the meeting organizers should I tried to join last weeks europe meeting but the link I had in my calendar didn't work and I couldn't find a current one.

 

[isladogs]

Updating it a few days before the meeting and including the meeting link would be very helpful although you shouldn't be responsible for doing this, the meeting organizers should I tried to join last weeks europe meeting but the link I had in my calendar didn't work and I couldn't find a current one.

There wasn't an Access Europe meeting last week - perhaps you meant Access Lunchtime?

As the organiser of Access Europe, I always send out reminders & connection info about a week before each meeting which are held on the first Wednesday of each month at 18:00 UK time (currently UTC)

The next meeting is on Wed 2 Nov - see info here: Access User Group – Europe | Page 3 | Access World Forums (access-programmers.co.uk) and here: Access Europe – George Hepworth (Power Apps for Access Developers) – Europe – AccessUserGroups.org

 

[Pat Hartman]

Thanks Colin. It was actually the west coast group that meets friday evenings.

 

[isladogs]

Thanks Colin. It was actually the west coast group that meets Friday evenings.

Hmm....fairly close to Europe

 

[strive4peace]

This week, UK is on standard time and America is still on daylight-savings time (we don't switch till next weekend) ... so

for Colin's meeting Wednesday TODAY! if you're used to going and think it is the same time ... the time is 1 hour later in America. George is presenting
https://accessusergroups.org/europe/event/access-europe-2022-11-02/
EDIT: meeting over now -- will post a link to the video once it is available.

for George's meeting Thursday, the time is 1 hour earlier in London -- this time only, instead of 2:30 am, it is just 1:30 am (Friday morning, lol). It is 6:30 pm Pacific time
https://accessusergroups.org/pacific/event/257-2022-11-03/

@isladogs @GPGeorge

 

[strive4peace]

recently ... this may be of interest to many of you:

Using SQL Server with Access, by Maria Barnes (1:02:31) read video description for links

 

[strive4peace]

There a new Microsoft MVP for Access, yay! Juanjo Luna was born in Spain, and is living in Austria. MVP profile

Juanjo leads a Spanish online Access user group

Grupo Virtual Access España!
https://accessusergroups.org/espana/

YouTube video playlist for Access España
https://www.youtube.com/playlist?list=PLwIULo1M2EWusQy_C7mDIQcLWe0BFRAT3

 

[strive4peace]

The video of George Hepworth's @GPGeorge excellent presentation to Access Europe on 2 Nov is now available on YouTube:
AEU: PowerApps for Access Developers (George Hepworth) (1:04:36)

 

[strive4peace]

This Wednesday, Colin Riddington @isladogs presents Annotating Google Maps in Access for Access Europe.
Join meeting:
https://accessusergroups.org/europe/event/access-europe-2022-12-07/

 

[strive4peace]

Colin @isladogs did a presentation today for Access Europe about rendering Google maps in Access, and adding drawings. Every time Colin does a presentation, I am awed. He's a great teacher with a lot of knowledge to share. Being a participant of the meeting also provides camaraderie with others who love Access. Surprisingly, considering that many hours, days, and months went into creating it, he posted his demo database, which will be useful to see how the logic is implemented. He also shared lots of links to learn. Overall, it was brilliant! I will also watch the video on YouTube once its posted to glean even more -- and definitely Like it, and post link here too

Lovely to see @jdraw and @Minty there too ... and who else?

 

[strive4peace]

-- Upcoming AccessUserGroup meetings --

Access Lunchtime – Loop and Link CSV Files - crystal (strive4peace)
February 28, Tuesday, 12 noon Central, 6 pm UK
https://accessusergroups.org/lunch/event/access-lunchtime-4-2023-02-28/

Access Europe – Optimizing Queries in SQL Server - Maria Barnes
March 1, Wednesday, 6 pm UK, 12 noon Central
https://accessusergroups.org/europe/event/access-europe-2023-03-01/

Access Pacific - Bad Data is Bad for Business - Pat Hartman
March 2, Thursday, 6:30 pm Pacific, 8:30 pm Central
https://accessusergroups.org/pacific/event/bad-data-is-bad-for-business-with-pat-hartman/

 

[strive4peace]

If you missed Colin's (@isladogs) recent presentation, or were there and want to watch the video to better absorb some of the great stuff he shared, here it is:

AL: Translation Tools using Access - Colin Ridington (isladogs) (1:10:34)

Tomorrow, Tuesday, I will be presenting for Access Lunchtime. Join us live!

Loop and Link CSV Files - crystal (strive4peace)
February 28, Tuesday, 12 noon Central, 6 pm UK
https://accessusergroups.org/lunch/event/access-lunchtime-4-2023-02-28/

 

[strive4peace]

-- Recent presentations posted On YouTube --

AL: Loop and Link CSV Files in Access using Queries, by crystal (strive4peace) (54:51)

AEU - Optimizing Queries in SQL Server, by Maria Barnes (51:38)

AP: Bad Data is Bad for Business with Pat Hartman (1:07:18)

@Pat Hartman , @isladogs, @GPGeorge

 

[strive4peace]

AL: Meet new Access PM, Accessibility is for Everyone - Linda Cannon, Tom van Stiphout, Kim Young (57:30)

 

[strive4peace]

join us Tuesday 31 October!
noon Central time. NOTE: 5pm London time since y'all switched clocks back and we didn't yet ... normally 6pm for Brits (thanks, Colin @isladogs) . All are welcome and it's free.

Two free tools that run in Access: (1) List Objects from Access databases and (2) VBA Code Documenter for Access, Excel, Word, and PowerPoint

Access Lunchtime - Lunchtime - AccessUserGroups.org

Come join us on the last Tuesday of every month @ 12 noon CST. Maria Barnes will be your host and we will cover lots of interesting topics related to Microsoft Access. This is a free webinar for 1 hour, we look forward to meeting you! === MEETING DETAILS === We meet every last Tuesday of […]

accessusergroups.org