Author Archives: Susan Pyne

About Susan Pyne

I started with the firm as a Sales Assistant and over the years worked my way up to Senior Access developer, I’m certified on Power BI, with more certifications coming. I'm also the chapter coordinator for AccessUserGroups.org, a worldwide organization of Access virtual chapters that meet each month online to discuss a range of topics in different languages. I LOVE working with Access and learning new technologies!

Alternative solution to DCount and DLookup with SQL Server Backend

One of the major issues we have encountered with Access is the use of DLookup and DCount when using SQL Server tables.  We recently worked on migrating a pure Access solution to SQL server and encountered delays on the loading of several forms.  This was due to the use of DLookup and DCount in the VBA code.

We then came up with a solution to quickly resolve the multiple instances with a couple of functions.  We were guided by another solution provide by Allen Browne who designed the Extended DLookup here in this link.

Allen’s solution improves the performance of the DLookup by:

  • Including a sort order to ensure you get the result you need.
  • Cleaning up after itself.
  • Correctly differentiates a Null and a zero-length string.
  • Overall improvement in performance.

We have now taken this one step further to work specifically with SQL tables or views, these will not work with Access local tables as we are specifically using an ADO connection.

You will find the script here.

If you have an instance that requires the use of DSum then you can easily adapt the DCount function to give you the required result.

After applying this solution we found a dramatic improvement in the performance of the forms loading and the design helps us to apply this solution to multiple projects.  I hope this solution is helpful to you and if you have any other issues that we can help you with then please reach out to us at accessexperts.com.

Where is Access heading? What changes are coming?

UPDATE. This meeting’s video recording can be seen below:

LIVE, next Tuesday December 13th, join Juan Soto for the LATEST in Access news from Redmond. What is going to happen with Access web apps? How does PowerApps figure in the picture? What is Microsoft flow? All your questions answered, (if it’s not under NDA!)

Don’t miss out on the latest news next week on Tuesday at 6:30 pm CDT.aug-share-01

To join our meeting, please use the following link and phone number or use your computer’s speakers with audio:

Join Skype Meeting

Join by Phone

Toll number: +1 (872) 703-5321

Find a local number

Conference ID: 8028922

The Evolution and Future of Access

What a fantastic meeting and a great turnout!  Thank you everyone for joining us and a special thank you to Luke Chung.

For those of you who missed this awesome event, it was an online interview with Luke Chung from FMS Software.  Luke is a popular speaker at conferences in the US, Australia and Europe, and has published many articles in industry magazines and the Microsoft Developer Network (MSDN). Microsoft has recognized him as a Microsoft MVP for his expertise and community influence. 

Luke & Juan discussed the future and evolution of access, where the product is headed, why is it still the number one desktop database in the world and other great topics!

Here is the recording!

AVOID NUMBER CONFLICTS WITH SQL SEQUENCES

 

In case you missed out on the September meeting presented by Robert Aaron, we now have the recording available.  If you enjoy watching the recordings then register with the group and you will be notified of future meetings, that way you get the chance to meet with us online and ask questions!Avoid-number-conflicts-with-SQL-Sequences

 

 

About Robert Aaron

Robert has been working with Access since Version 2. He has always loved how flexible it is and can be adapted to so many situations. Since he joined IT Impact, Inc. a year ago he has expanded his skills into SQL Server.  He believes Access and SQL Server are a perfect combination for many things. Over the years he has also done programming in other areas including writing 3 computer games that have been published. He is continuing to expand his knowledge in Access and SQL Server, and hopes to bring you more interesting things along that journey.

Creating Dashboards in Microsoft Access

Congratulations Juan Soto on a great presentation this month!  I also want to thank everyone who joined the meeting and hope that you found the webinar informative and worthwhile.

The video is now available for you to review:

Here are the dashboard files: NorthwindDash

Your presence helped to make this event a great success. We wish you all the best and hope that you continue to be engaged with the Access User Group. Stay tuned for upcoming events by visiting accessusergroups.org.

NOTE: You can see the stored procedure by restoring the database in the zip file NorthwindDash above, but if you just want to see the stored procedure here it is:

USE [NorthwindDash]
GO
/****** Object: StoredProcedure [dbo].[usp_Dashboard] Script Date: 8/14/2015 8:08:56 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Juan Soto
-- Create date: November 17th, 2014
-- Description: Create table for Dashboard
-- =============================================
ALTER PROCEDURE [dbo].[usp_Dashboard] 
 -- Add the parameters for the stored procedure here
 @UserStartDate DateTime
AS
BEGIN

 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 Declare @Dashboard Table
 (
 myMetric VarChar(50),
 myTimeFrame VarChar(50),
 Qty Money
 )

 DECLARE @StartDate DateTime
 DECLARE @EndDate DateTime

 DECLARE @Year DATE 
 DECLARE @Quarter INT


 DECLARE @QuarterStartDate DateTime
 DECLARE @QuarterEndDate DateTime

 -- User supplies dates for analysis
 --Set @UserStartDate = CONVERT(DATETIME, '2014-11-08 00:00:00', 102)

 SET DATEFIRST 1; -- We want Monday as first weekday

 SELECT @StartDate= DATEADD(dd, -(DATEPART(dw, @UserStartDate )-1), @UserStartDate ) ;
 SELECT @EndDate= DATEADD(dd, 7-(DATEPART(dw, @UserStartDate )), @UserStartDate ) ;

 -- Calculation Quarters
 set @Year = DATEADD(day,-day(@userstartdate) + 1 ,@userstartdate)
 set @Year = dateadd(month, -month(@year)+1 ,@year)

 set @Quarter = datepart(quarter,@Userstartdate)

 set @QuarterStartDate =DATEADD(QUARTER, @Quarter - 1, @Year) 

 set @QuarterEndDate = DATEADD(DAY, -1, DATEADD(QUARTER, @Quarter, @Year))
 
 --Calc customers created today
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Customers', 'Today', COUNT(ID) AS Qty
 FROM Customers
 WHERE DateCreated = @StartDate

 --Calc customers created this week
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Customers', 'ThisWeek', COUNT(ID) AS Qty
 FROM Customers
 WHERE (DateCreated BETWEEN @StartDate and @EndDate);


 --Calc customers created this month
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Customers', 'ThisMonth', COUNT(ID) AS Qty
 FROM Customers
 where (month(datecreated) = month(@UserStartDate) and year(datecreated) = year(@UserStartDate) )


 --Calc customers created this quarter
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Customers', 'ThisQtr', COUNT(ID) AS Qty
 FROM Customers
 WHERE (DateCreated BETWEEN @QuarterStartDate AND @QuarterEndDate)


 --Calc customers created this year
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Customers', 'ThisYear', COUNT(ID) AS Qty
 FROM Customers
 WHERE (year(DateCreated) = year(@UserstartDate) )

 --Calc orders created today
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Orders', 'Today', COUNT([Order ID]) AS Qty
 FROM Orders
 WHERE [Order Date] = @UserStartDate;

 --Calc Orders created this week
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Orders', 'ThisWeek', COUNT([Order ID]) AS Qty
 FROM Orders
 WHERE ([Order Date] BETWEEN @StartDate and @EndDate);


 --Calc Orders created this month
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Orders', 'ThisMonth', COUNT([Order ID]) AS Qty
 FROM Orders
 where (month([Order Date]) = month(@UserStartDate) and year([Order Date]) = year(@UserStartDate) )

 --Calc Orders created this quarter
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Orders', 'ThisQtr', COUNT([Order ID]) AS Qty
 FROM Orders
 WHERE ([Order Date] BETWEEN @QuarterStartDate AND @QuarterEndDate)

 --Calc Orders created this year
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'New Orders', 'ThisYear', COUNT([Order ID]) AS Qty
 FROM Orders
 WHERE (year([Order Date]) = year(@UserstartDate) )

 --Calc order dollars created today
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'Order$','Today',SUM([Order Summary].[Order Total]) AS Total
 FROM [Order Summary] INNER JOIN
 Orders ON [Order Summary].[Order ID] = Orders.[Order ID]
 WHERE Orders.[Paid Date] = @UserStartDate

 --Calc order dollars created this week
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'Order$','ThisWeek',SUM([Order Summary].[Order Total]) AS Total
 FROM [Order Summary] INNER JOIN
 Orders ON [Order Summary].[Order ID] = Orders.[Order ID]
 WHERE (Orders.[Paid Date] BETWEEN @StartDate and @EndDate);

 --Calc order dollars created this month
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'Order$','ThisMonth',SUM([Order Summary].[Order Total]) AS Total
 FROM [Order Summary] INNER JOIN
 Orders ON [Order Summary].[Order ID] = Orders.[Order ID]
 where (month(Orders.[Paid Date]) = month(@UserStartDate) and year(Orders.[Paid Date]) = year(@UserStartDate) )

 --Calc order dollars created this quarter
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'Order$','ThisQtr',SUM([Order Summary].[Order Total]) AS Total
 FROM [Order Summary] INNER JOIN
 Orders ON [Order Summary].[Order ID] = Orders.[Order ID]
 WHERE (Orders.[Paid Date] BETWEEN @QuarterStartDate AND @QuarterEndDate)

 --Calc order dollars created this year
 Insert Into @Dashboard(myMetric, myTimeFrame, Qty)
 SELECT 'Order$','ThisYear',SUM([Order Summary].[Order Total]) AS Total
 FROM [Order Summary] INNER JOIN
 Orders ON [Order Summary].[Order ID] = Orders.[Order ID]
 WHERE (year(orders.[Paid Date]) = year(@UserstartDate) )

 Select * from @Dashboard

End

“Using SQL Server data sets and stored procedures to create dashboards in Microsoft Access”

TUESDAY NOVEMBER 11th @ 6.30pm CDT

Juan Soto, Microsoft Access MVP,  will be talking about how Dashboards are a wonderful way to convey a wide range of data points into a consolidated view, that will provide a quick glance of the operations of a firm. In other words, clients can look at the dashboard and quickly see if there are issues that require additional research.

This session will do a deep dive on creating a simple dashboard using a SQL Server stored procedure, data sets and a temporary table, allowing participants to learn more about these concepts and use them with Access.

To join our meeting, please use the following link and phone number or use your computer’s speakers with audio:

Join the meeting: https://join.me/accessusergroups

On a computer, use any browser with Flash. Nothing to download.
On a phone or tablet, launch the join.me app and enter meeting code: 842-599-774

Join the audio conference: 
Dial a phone number and enter access code, or connect via internet.

By phone: 

United States   +1.213.226.1066
Access Code   842-599-774#

Other international numbers available

By computer via internet: 
Join the meeting, click the phone icon and select ‘Call via internet’. A small download might be required.

Tuesday October 14th @ 6.30pm CDT – Working with XML data efficiently

Ben Clothier, Access MVP and co-founder of the AccessUserGroups.org, will be presenting the meeting discussing  working with XML data with an SQL Server backend.

Earlier this year Ben posted a 2 part blog on this topic and this will be an update/improved version of his blog.  Here are the links: Part 1 & Part 2

In the talk we will cover:

  • How to use XPath and XQuery to query a XML document.
  • How to use XML like a relational data source.
  • How to build a valid XML document in VBA with minimum code and especially without knowing the schema
.
  • How to post a XML document to a stored procedure in VBA‏.

Please add this to your calendar and tell your friends!

This is a free online meeting, please join us at:

Join the meeting: https://join.me/accessusergroups

On a computer, use any browser with Flash. Nothing to download.
On a phone or tablet, launch the join.me app and enter meeting code: 842-599-774

Join the audio conference: 
Dial a phone number and enter access code, or connect via internet.

By phone: 
United States   +1.213.226.1066
Access Code   842-599-774#