strive4peace
AWF VIP
- Local time
- Today, 14:33
- Joined
- Apr 3, 2020
- Messages
- 1,032
want to come to an online user group meeting? (1 Viewer)
- Thread starter strive4peace
- Start date
- Local time
- Today, 15:33
- Joined
- Feb 19, 2002
- Messages
- 45,760
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.
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.
Code:
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
MVP / VIP
- Local time
- Today, 20:33
- Joined
- Jan 14, 2017
- Messages
- 18,580
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.
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.
- Local time
- Today, 15:33
- Joined
- Feb 19, 2002
- Messages
- 45,760
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.
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
AWF VIP
- Local time
- Today, 14:33
- Joined
- Apr 3, 2020
- Messages
- 1,032
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
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
Last edited:
- Local time
- Today, 15:33
- Joined
- Feb 19, 2002
- Messages
- 45,760
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.
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
MVP / VIP
- Local time
- Today, 20:33
- Joined
- Jan 14, 2017
- Messages
- 18,580
There wasn't an Access Europe meeting last week - perhaps you meant Access Lunchtime?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
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
- Local time
- Today, 15:33
- Joined
- Feb 19, 2002
- Messages
- 45,760
Thanks Colin. It was actually the west coast group that meets friday evenings.
isladogs
MVP / VIP
- Local time
- Today, 20:33
- Joined
- Jan 14, 2017
- Messages
- 18,580
Hmm....fairly close to Europe
strive4peace
AWF VIP
- Local time
- Today, 14:33
- Joined
- Apr 3, 2020
- Messages
- 1,032
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
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
Last edited:
strive4peace
AWF VIP
- Local time
- Today, 14:33
- Joined
- Apr 3, 2020
- Messages
- 1,032
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
Using SQL Server with Access, by Maria Barnes (1:02:31) read video description for links
strive4peace
AWF VIP
- Local time
- Today, 14:33
- Joined
- Apr 3, 2020
- Messages
- 1,032
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
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
AWF VIP
- Local time
- Today, 14:33
- Joined
- Apr 3, 2020
- Messages
- 1,032
strive4peace
AWF VIP
- Local time
- Today, 14:33
- Joined
- Apr 3, 2020
- Messages
- 1,032
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/
Join meeting:
https://accessusergroups.org/europe/event/access-europe-2022-12-07/
strive4peace
AWF VIP
- Local time
- Today, 14:33
- Joined
- Apr 3, 2020
- Messages
- 1,032
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?
Lovely to see @jdraw and @Minty there too ... and who else?
Last edited:
strive4peace
AWF VIP
- Local time
- Today, 14:33
- Joined
- Apr 3, 2020
- Messages
- 1,032
-- 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/
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
AWF VIP
- Local time
- Today, 14:33
- Joined
- Apr 3, 2020
- Messages
- 1,032
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/
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
AWF VIP
- Local time
- Today, 14:33
- Joined
- Apr 3, 2020
- Messages
- 1,032
-- 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
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
Last edited:
strive4peace
AWF VIP
- Local time
- Today, 14:33
- Joined
- Apr 3, 2020
- Messages
- 1,032
AL: Meet new Access PM, Accessibility is for Everyone - Linda Cannon, Tom van Stiphout, Kim Young (57:30)
strive4peace
AWF VIP
- Local time
- Today, 14:33
- Joined
- Apr 3, 2020
- Messages
- 1,032
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
accessusergroups.org
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
Similar threads
