Access and SQL Server ADP+ Access Data Project

how Access and SQL Server can work together, ACC FE SQL BE

how to get a free trial version please go to the end of this article

by Joakim Dalby from Denmark, Made in Denmark.

 

1. Introduction to ADP+ for Access 2013 – 2016 to SQL Server

ADP+ is a Data Access Layer for updatable form and subform and for report and subreport to have a client-server rapid ap­pli­ca­tion deve­lop­ment framework in Ac­cess 2013 and Access 2016 connected to a SQL Server da­ta­ba­se 7.0, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 and SQL Server 2016 on your network or SQL Server Express on your PC or SQL Azure in the cloud over the internet.

 

ADP+ is an object-oriented framework in VBA programming for an Access front-end ap­pli­ca­tion with a back-end SQL Server database, so an updatable form and a report can use a parameterized stored procedure as criteria for data records and rows. The idea with ADP+ is to bring back in Access the brilliant Access Data Project we known from Access 2000 to 2010 to provide an efficient and native-mode access to a SQL Ser­ver database that handle all data, query and business logic inside the database.

 

An example of using ADP+ in VBA behind an up­datab­le form that is fetching data from a stored procedure that has a join between tables therefore the ADP proper­ties Input­Parameters, UniqueTable and ResyncCommand are included in ADP+. A user can update data rows in a form in Access, delete a row with a yes/no confirm mes­sa­ge­box and insert a new row that will be stored automatically in the SQL Server database table:

 

Private dalForm As DataAccessLayerForm

Private Sub Form_Load()

  Set dalForm = New_DataAccessLayerForm

  With dalForm

   .Language = English

   .ObjectType = StoredProcedure

   .Object = "dbo.Form_Suppliers"

   .InputParameters Me![SearchSupplierID]

   .UniqueTable = "Suppliers"

   .ResyncCommand = "dbo.Form_Suppliers_Resync ?"

   .Bind Me

  End With

End Sub

 

An example of using ADP+ in VBA behind a Access report where the criteria is gi­ving from an open form and the stored procedure provides data rows to the report to be shown in screen and printed out on paper or saved to a pdf file:

 

Private dalReport As DataAccessLayerReport

Private Sub Report_Open(Cancel As Integer)

  Set dalReport = New_DataAccessLayerReport

  With dalReport

   .Language = English

   .ObjectType = StoredProcedure

   .Object = "dbo.Report_Employees_By_Title"

   .InputParameters Forms![Employees_By_Title_Criteria]![Title]

   .Bind Me

  End With

End Sub

 

ADP+ provides data to a ListBox and a ComboBox or a DropdownBox in a form.

ADP+ provides extra key press in a form and extra buttons in a ribbon bar menu.

ADP+ provides easy calling a stored procedure with both input and output para­me­ters to execute or to return a recordset.

 

A screenshot from a danish Access application using ADP+ to show and up­da­te data from a SQL Server database in a form with datasheets and dropdown bo­xes. The ribbon bar is a part of ADP+ and is translated to english and german and you can make your own translation and change the ribbon bar.

 

 

Many english form and report examples will be shown later.

 

ADP+ is made for 32 bit platform of Access for Windows 32 bit or 64 bit, therefore ADP+ is not working in a 64 bit platform of Access. Microsoft says: 32-bit Office is recommended for most users.

 

I use the term linked table to a SQL Server database table, other term is Attached remote table. I use the term main-sub multiform with a mainform and subform, other terms are Master-Detail, Master-Child or Parent-Child. Likewise for main-sub multireport with a mainreport and subreport. A control in a form is a textbox, check­box, op­tion­­group, list­box or com­bo­­box (dropdown). A table contains columns also known as attribu­tes or fields. Me operator is used to refer to the form or the report and to the controls on the form or the report. I’m using the ! notation and can also use . in an object-oriented way. Parent operator is used in subform or subreport to refer to controls in the mainform or the mainreport like the primary key as a Link­Mas­ter­Field. Microsoft terms: The Requery method updates the data underlying a form or control to reflect records that are new to or deleted from the record source since it was last queried. The Refresh method shows only changes that have been made to the current set of records; it doesn't reflect new or deleted records in the record source. The Repaint method simply repaints the specified form and its controls.

 

Microsoft has stopped supporting the Access Data Project adp file in an Ac­cess desktop database and deprecated ADP with this message when I open an adp file in Access 2013: »Ac­cess Data Projects are no longer supported in this version of Access.« Microsoft continue with this statement: »ADPs continue to work in earlier versions of Access. You can continue to develop your ADP applications and we will continue to support earlier versions of Access under the standard support lifecycle. We will not update older versions of Access to support new versions of SQL Server or SQL Azure. Therefore, you may encounter issues if you use SQL Server 2012 or later versions with your ADP. ADPs will continue to support SQL 2008 R2 and earlier.« at link Changes in Access 2013, more at link Discontinued features and modified functionality in Access 2013.

 

With ADP+ it is easy for migrate from an adp file to an accdb file.

 

2. Access models for using a SQL Server database

From Access 2013 there is provided four models of SQL Server data into an Access desktop data­ba­se application in fileformat accdb, but there is also some disadvantages and de­fects that ADP+ has solved and will be described in the next chapter.

 

  1. Linked table is updatable to SQL Server table. Linked tables can be used in an Access made query and Access will try the best to make sure that the data­base server don’t send all records to the client, else there can be a per­for­man­ce issue. Every linked table must has a primary key and in a SQL Server database it is common to use an unique sequence number identity column (autonumber, increment using Int datatype, remark that Access is unable to use BigInt as a primary key). LinkMasterFields and Link­Child­Fields properties are wor­king in a multiform and multireport. Linked table is using an ODBC driver to connect to SQL Server database with a DSN file made at the user’s computer in ODBC Data Source Administrator on File DSN tab. ODBC stands for Open Database Con­nec­ti­vi­ty and DSN for Data Source Name saved to a file to be copied to another computer. DSN less means no file is needed.
  2. Embedding ODBC connectionstring for a DSN less into a SQL statement for updatable of SQL Server table like this:

SELECT * FROM Suppliers IN '' [ODBC;Driver={SQL Server Native Client 11.0};

Server=<name>; Database=Northwind;Trusted_Connection=Yes];

  1. Pass-through query is non updatable and read-only snapshot to SQL Ser­ver tables, views and stored procedures with parameters where the query is processed entirely on the SQL Server. Pass-through query can be used for showing data in a combobox and listbox in a form and for a report to be printed on paper or saved in a pdf or Excel file. LinkMasterFields and LinkChildFields properties are not working in a multiform and multireport, an error message is given: »You can't use a pass-through query or a non-fixed column crosstab query as a record source for a subform or subreport.« Pass-through query is using either ODBC with DSN file or embedding DSN less. Using DAO Data Access Objects recordset is not supported in a report recordset an error message is given: »Run-time error 32585 This feature is only available in an ADP.«
  2. ActiveX Data Objects ADO recordset is updatable to SQL Server tables, vi­ews and stored procedures with parameters so the query will be done at the server. The technique is to bind an ADO recordset to the form’s re­cord­set (not recordsource or rowsource). Link­MasterFields and LinkChildFields properties are not working in a multiform. Delete a re­cord gives an Yes/No messagebox but the record is already de­le­ted in the SQL Server da­ta­base before I click No, so deleting re­cords using ADO doesn't trigger the deletion confirmation dialog box. In a form the key press Ctrl ' (apostrophe, duplicate above data) is not working well in a new row, and sor­ting, filtering, refreshing (Sort, Filter, Refresh All) is not working and an error message is given: »Data provider could not be initialized.« ADO recordset is not supported in a report recordset an error message is given: »Run-time error 32585 This feature is only available in an ADP.« ADO recordset is DSN less and has special components for ADO connection and ADO command to connect to a SQL Server database and calling stored procedure with parameters. A ADO re­cordset can’t get the default value from a column, therefore the default va­lue has to be typed in the form by the control Default Value property.

 

Have you already upsizing an Access database to a SQL Server database and it is using sto­red pro­­cedures in an adp file, model A is like to downsizing select state­ments back to Access made query, and remember a join with three tables Access wants parentheses like this:

 

FROM (Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID)

      INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID

 

For me, when I have a SQL Server running I better use parameterized stored pro­ce­dure to represent the data access layer and sometimes the business logic layer inside the database. Transact-SQL got the Access’ IIF and CHOOSE in SQL Server 2012 and a scalar-valued function can almost do the same as a function in Access called from an Access made query. Sometimes I build a stored procedure with a parameter and inside the T-SQL will do different things depends of the value of the parameter. When a user later request me to change the business logic I can do that inside the stored procedure at the SQL Server database without updating the Access application and therefore I avoid a new deployment to the users.

 

3. ADP+ model in Access with a SQL Server database

The ADP+ model takes the best from two of the Access models and add the func­tio­na­lity that is not working in Access. ADP+ is using ADO to a SQL Ser­ver table, view or stored procedure with parameter to bind a recordset to an Access form. ADP+ is using pass-through query to SQL Server table, view or stored procedure with pa­ra­meter to bind to an Access report recordsource.

 

3.1. Advantages of ADP+

  • Using ADO recordset to provide updatable data in a form based on a SQL Ser­ver database table, view or sto­red procedure with parameters for data cri­te­ria by using Input Para­meters.
  • Using ADO recordset to provide data to combobox and listbox based on SQL Server database table, view or stored procedure with parameters.
  • Using pass-through query to provide data to combobox and listbox based on SQL Server database table, view or stored procedure with parameters.
  • Multiform is using a tech­ni­que to simulate LinkMasterFields and LinkChildFields pro­­per­ti­es so a subform can use stored procedure to receive the re­cords that belong to the current record in the mainform or the current row in a data­she­et.
  • Support updatable the many-table in a many-to-one join by using Unique Table and Resync­ Com­mand.
  • Support using BigInt datatype as a primary key.
  • Delete a record in a form or delete several selected rows in a da­ta­she­et with an Yes/No messagebox and first after the user has confirmed with an Yes, the re­cord and the rows will be deleted at the screen and in the database. Key press Delete or Ctrl - (minus) or select Delete in ribbon bar menu.
  • Key press Ctrl ' (apostrophe, duplicate above data) working normally.
  • A new key press Alt ' (apostrophe) do a duplicate of the current re­cord in a form or the current row in a datasheet and insert it as a new record/row.
  • A new key press for sort of the current column in a data­she­et or in a form:

Shift + F12 Ascending, Ctrl + F12 Descending and Alt + F12 Remove sort.

  • A new key press for filter by selection in a datasheet or in a form:

F3 Filter by selection and Ctrl + F3 Remove filter.

  • A new key press Alt + F3 for paste append, that duplicate record of a se­lec­ted record in a form, duplicate many selected records in a sub­form to a­no­ther record in the mainform or copy many rows from a Excel spread­sheet to a data­sheet in Access.
  • F5 Refresh all records in a form or all rows in a datasheet and keep focus to current record/row.
  • F9 Recalc all calculated controls in a form.
  • Shift + F9 Requery all records in a form or all rows in a datasheet and display any new or changed records and remove deleted records
  • Ctrl + F open Find dialog box independent of your Access language because in danish language of Office it is Ctrl + B. Ctrl + H open Replace dialog box and Shift + F4 find the next occurrence of the text specified in the Find and Replace dialog box when the dialog box is closed.
  • Arrow Down to next record and Up to previous record in a Continuous Form.
  • Using pass-through query to provide data to a report based on a SQL Server database table, view or sto­red procedure with parameters for data criteria.
  • Multireport is using a technique to simulate LinkMasterFields and Link­Child­Fields properties so a subreport can use a pass-through query and show the subset of data that belong to the current record in the mainreport.
  • Ribbon bar with buttons using ADP+ functions like filter and sort.
  • ADP+ don’t use linked table and Access made query. All sql select statements are in views and stored procedures inside the SQL Server database. But ADP+ gives extra smart methods to create linked table or linked view with an ODBC connectionstring as DSN less, so no DSN file is made at the user’s computer.

 

3.2. ADP+ framework is a reference in a front-end application

The ADP+ framework is in a file called ADPX.accde that provide a Data Access Layer DAL in Access with classes and factory new methods to instantiate object of class DataAccessLayerForm in the form OnLoad event procedure and to instantiate object of class DataAccessLayerReport in the report OnOpen event procedure.

 

The file ADPX.accde can be pla­ced in a new folder called ADPX in the ProgramData folder e.g. C:\ProgramData\ADPX and reused by multiple Access front-end ap­pli­ca­tion accdb files. Folder C:\ProgramData\ADPX or whatever folder you choose must be a Trusted Location and with read/write access for the user include the folder for your own application accdb file.

 

The folder ProgramData is normally hidden in File Explorer but at the tab View set a checkmark in 'Hidden Items' and close and start File Explorer again.

 

In an Access front-end application the developer will make a new module called my­Data­Access­LayerFactory that can be used to create pass-through query in a fast way. Inside the module the developer will add a new reference to the ADPX.accde file to get access to the ADP+ DataAccessLayer Object Library by menu {Tools} and {Re­fe­ren­ces} and in the dialogbox click [Browse] and find the file ADPX.accde.

 

3.3. ADP+ Connectionstring and Install sql provider at client PC

The Microsoft OLE DB Provider is SQL Server Native Client called SQLNCLI11 in ADO connection and SQL Server Native Client 11.0 in ODBC connection by DSN-less, meaning no need for making an ODBC Data Source Name file in Windows. Normally a PC u­ser account login belong to a Windows AD-group that is added to the SQL Server security and the database with read and write access. Ask your SQL Server DBA for more help. When the client connection tool is not installed ADP+ gives an error e.g. Error 3706 Provider cannot be found. It may not be pro­perly installed.

 

For installing software at user client PC to connect to a SQL Server using ADO and ODBC read about SQL Server Native Client at links e.g.:

https://msdn.microsoft.com/en-us/library/ms131321.aspx

http://www.connectionstrings.com/sql-server-native-client-11-0-oledb-provider/

 

Install at client PC using your SQL Server DVD and install 'Client Tools Connec­tivity' or download SQLNCLI11 directly from:

 

http://go.microsoft.com/fwlink/?LinkID=239648&clcid=0x409

 

and save the file sqlncli.msi in Downloads folder and run the file at the client PC.

Remember to do Windows Update until there is no more updates because Microsoft comes with updates to database, ado and odbc drives inside Windows Update. The version to install should match the bitness of your Windows e.g. 64-bit with the 32-bit of Office/Access, choose the x64 (64-bit) version.

 

ADP+ is using a file called DataAccessLayerConnection.sql that must be placed in the same folder as the front-end application accdb file. The file contains two con­nec­tion­­strings to a SQL Server and a database:

 

  • The first line is for ADO connection to make ADO recordset.
  • The second line is for pass-through query by embedding ODBC DSN less.

 

Connectionstring to SQL Server 2014 / 2012 by <name> to a Northwind database:

 

Provider=SQLNCLI11;Server=<name>;Database=Northwind;Trusted_Connection=Yes

ODBC;Driver={SQL Server Native Client 11.0};Server=<name>;Database=Northwind;Trusted_Connection=Yes

 

An alternative:

Provider=SQLNCLI11;Server=<name>;Database=Northwind;Integrated Security=SSPI

ODBC;Driver={SQL Server Native Client 11.0};Server=<name>;Database=Northwind;Trusted_Connection=Yes

 

Connectionstring for a username and password to a SQL Server 2014 and 2012:

 

Provider=SQLNCLI11;Server=<name>;Database=Northwind;Uid=<user>;Pwd=<password>

ODBC;Driver={SQL Server Native Client 11.0};Server=<name>;Database=Northwind;

Uid=<user>;Pwd=<password>

 

With instance:

 

Provider=SQLNCLI11;Server=<name>\<instance name>;Database=<database name>;Trusted_Connection=Yes

ODBC;Driver={SQL Server Native Client 11.0};Server=<name>\<instance name>;

Database=<database name>;Trusted_Connection=Yes

 

With instance and portnumber:

 

Provider=SQLNCLI11;Server=<name>\<instance name>,<portnumber>;

Database=<database name>;Trusted_Connection=Yes

ODBC;Driver={SQL Server Native Client 11.0};Server=<name>\<instance name>,<portnumber>;

Database=<database name>;Trusted_Connection=Yes

 

With ip-address, user and password:

 

Provider=SQLNCLI11;Data Source=123.45.6.78,4987;Network Library=DBMSSOCN;

Initial Catalog=Northwind;User ID=TheBest;Password=YouNeverKnow;

 

 

In SQL Server 2008 use SQLNCLI10 and {Server Native Client 10.0} and in SQL Server 2005 use SQLNCLI and {SQL Native Client}.

 

Connectionstring to SQL Server 2000 and in case SQLNCLI is not working in 2005:

 

Provider=SQLOLEDB;Server=<name>;Database=Northwind;Integrated Security=SSPI

ODBC;Driver=SQL Server;Server=<name>;Database=Northwind;Trusted_Connection=Yes

 

In case you can’t install provider SQLNCLI11 at client PC then you can try to use the sql server 2000 connectionstring because the old provider SQLOLEDB is part of the Windows OS since Windows XP.

 

In case you like to link your Access application to a MySql server you need to down­load and install the ODBC connector from Oracle:

https://dev.mysql.com/downloads/connector/odbc/

 

Many connectionstrings in this link

 

Your IP address is listed in SQL Azure's administration tools and by default it will include your current IP address and it needs to specify the range of IP addresses of other users.

 

ADP+ makes only one ADO connection to the SQL Server database and keep the con­nec­tion open until the front-end application is closed. ADP+ don’t use disconnected recordset where the connection is set to Nothing. When I update a record in a form or a row in a datasheet and leave the record/row the data is saved in the SQL Server database. If I don’t want to leave the record/row I can click Save in ribbon bar menu or key press Shift + Enter to have the record/row saved in the database.

 

Without file DataAccessLayerConnection.sql add instead your own file

If you do not like the file DataAccessLayerConnection.sql with the connection string, you can use your own file and insert a function in your Access application VBA code and ADP+ will instead using the connection strings from the Data­Access­Layer­Connec­tion­File function:

 

Public Function DataAccessLayerConnectionFile() As String

  DataAccessLayerConnectionFile = "NorthwindApp.txt"

End Function

 

Without file DataAccessLayerConnection.sql add instead to your VBA code

If you do not like the file DataAccessLayerConnection.sql with the connection string, you can drop the file and insert a function in your Access application VBA code and ADP+ will instead using the connection strings from the Data­Access­Layer­Connec­tion­String function:

 

Public Function DataAccessLayerConnectionString() As String

  DataAccessLayerConnectionString = "Provider=SQLNCLI11;Server=<name>;Database=Northwind;Trusted_Connection=Yes" + "¤" + _

                  "ODBC;Driver={SQL Server Native Client 11.0};Server=<name>;Database=Northwind;Trusted_Connection=Yes"

End Function

 

3.4. ADP+ is using ADO 6.1 Library

ADPX.accde is using Microsoft ActiveX Data Objects 6.1 Library for Windows 7 SP1, Windows 8, Windows 8.1, Windows 10 and from Windows Server 2008 R2 SP1 from 32-bit file: C:\Program Files (x86)\Common Files\System\ado\msado15.dll.

 

ADPX.accde is using Microsoft Office 15.0 - 16.0 Access database engine Object Library. Access 2007 (12), Access 2010 (14), Access 2013 (15), Access 2016 (16) is repla­ce­ring DAO 3.6 Data Access Object from Access 2002 and Access 2003 to access pass-through query.

 

3.5. Example Access front-end application and SQL database

I will in the next chapters show the use of ADP+ in an Access Northwind front-end application with a SQL Server 2014 Northwind back-end database with stored procedures to provide data to form and combobox in chapter 4, main-sub multiform in chapter 5 and main-sub multireport in chapter 6.

 

In folder C:\ProgramData\ADPX I have placed the file ADPX.accde for Access.

In folder C:\ProgramData\Northwind I have placed two files for Access:

- DataAccessLayerConnection.sql with connectionstring to SQL Server database.

- NorthwindApp.accdb that is the front-end application file with a new reference to the file C:\ProgramData\ADPX\ADPX.accde.

 

Shortcut at Windows desktop for easy to start the Nortwind application:

 

Access 2013

"C:\Program Files\Microsoft Office 15\root\office15\MSACCESS.EXE" "C:\ProgramData\Northwind\NorthwindApp.accdb"

 

Access 2016

"C:\Program Files\Microsoft Office 16\root\office16\MSACCESS.EXE" "C:\ProgramData\Northwind\NorthwindApp.accdb"

 

NorthwindApp.laccdb is the lock file while you using NorthwindApp.accdb.

 

NorthwindApp.accdr is a copy of the NorthwindApp.accdb and copy is renamed to accdr for simulate run­ning an app like you were using the runtime version of Access when you have a full license installed. A runtime version needs a startup form and don't show navigation pane or ribbon bar menu and File button has few options and Shift key press to startup without autoexec macro is disabled. Access Runtime is free for a user to install and to use your Access application. Runtime for Access 2013 remember to use the 32 bit version in file Access­Run­time_x86_en-us.exe and the language your users prefer.

 

NorthwindApp.accde is a save as NorthwindApp.accdb for take away the possibi­li­ty for users to change design of form and report, code behind form or report, query and module, and an accde is com­piled to give a faster performance. In old days a mde file. I deploy and distribute an Access desk­top appli­cation to users as an accde file. I start the application at the users PC with this shortcut at Windows desktop:

 

"C:\Program Files\Microsoft Office 15\root\office15\MSACCESS.EXE" "C:\ProgramData\Northwind\NorthwindApp.accde"

 

When you like to simulate a runtime version of Access on the users PC:

 

"C:\Program Files\Microsoft Office 15\root\office15\MSACCESS.EXE" "C:\ProgramData\Northwind\NorthwindApp.accdr" /runtime

 

accdt is a Micro­soft Access Template file.

 

4. ADP+ in action with a form and datasheet

I will show how easy it is with ADP+ and DataAccessLayerForm object to provide da­ta for an updatable form with an Yes/No confirm messagebox for deleting a record.

 

4.1. Updatable form for Employee

Inside the SQL Server database Northwind I make a stored procedure (later it will have a parameter) where I have selected the primary key and other columns:

 

CREATE PROCEDURE [dbo].[Form_Employee]

AS

BEGIN

 SET NOCOUNT ON

 SELECT   EmployeeID, FirstName, LastName, Title, BirthDate, Photo

 FROM     dbo.Employees

 ORDER BY FirstName, LastName, EmployeeID

END

 

To make a form in the Access front-end application by using the form guide, I make a pass-through query in the module myDataAccessLayerFactory where I already have add the reference to the ADPX.accde file:

 

Private Sub CreatePassThroughQuery()

 DataAccessLayerConnectionFactory.CreatePassThroughQuery "Form_Employee", "EXEC dbo.Form_Employee"

End Sub

 

The first parameter is the name of the pass-through query and the second parameter is the sql statement to execute the stored procedure. Connection to the database is automatically done by ADP+.

 

I design a single form based on the pass-through query "Form_Employee" so the Record Source property will be the  pass-through query, and I will set the form properties as I like them and finish the design. I set form property "Key Preview" to Yes, so ADP+ can take over some of the special keys. (Key Preview in danish is called "Tast gennemsyn"). All controls in the form have properties Name and Control Source filled out with the column name from the select part of the stored procedure as a bound textbox.

 

Inside the form design I open the Visual Basic for Applications VBA Code Editor by click at the icon in design ribbon bar menu or key press Alt+F11, and I type in a private object variable called dalForm from the ADP+ class DataAccessLayerForm to provide data to the form. OnLoad event procedure of the form do the instantiation of the object dalForm by calling a New factory me­thod and the properties of dalForm is assigned with values and some are using enumerating so I only have to choose a value by a prompt intellisense, like property ObjectType gives SQL, StoredProcedure, Table, View. The form property Record Source will be taken over by ADP+ that will provide the data for the form through code property Me.Recordset and therefore override the pass-through query:

 

Option Compare Database

Option Explicit

Private dalForm As DataAccessLayerForm  'Declare an object as the form’s private class member.

Private Sub Form_Load()

  On Error Resume Next

  DoCmd.Echo False

  Set dalForm = New_DataAccessLayerForm 'Set object to new instance by calling a factory procedure.

  With dalForm                          'Set properties with prompt intellisense values.

   .Language = English                  'Default is danish therefore I set language to english here.

   .ObjectType = StoredProcedure        'Data come from a table, view or here a stored procedure.

   .Object = "dbo.Form_Employee"        'Name of table, view or here the stored procedure.

   .InputParameters                     'This sp don't have a parameter else here a list of controls.

   .Bind Me                             'Binding Me to object dalForm and it will override the form

  End With                              'Record Source pass-through query with data from stored proc.

  DoCmd.Echo True

  On Error GoTo 0

End Sub

 

Private Sub Form_Unload(Cancel As Integer)

  On Error Resume Next

  dalForm.Form_Unload   'This method has the same name as the form event for ADP+ garbage collector.

  Set dalForm = Nothing

  On Error GoTo 0

End Sub

 

Save the form and open it from the Navigation pane and update and insert em­ploy­ees with a photo bmp file by right click at the photo frame:

 

 

The primary key EmployeeID is an Identity and it must be in a form but can be hidden for users with same foreground and background color as the detail band color.

 

The deletion of a record is not yet been taken care of by ADP+ because I need to add more code where the form event procedures are calling methods of the same name from the dalForm object:

 

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)

  dalForm.Form_BeforeDelConfirm Cancel, Response

End Sub

 

Private Sub Form_Delete(Cancel As Integer)

  dalForm.Form_Delete Cancel

End Sub

 

Private Sub Form_AfterDelConfirm(Status As Integer)

  dalForm.Form_AfterDelConfirm Status

End Sub

 

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

  dalForm.Form_KeyDown KeyCode, Shift , Me![EmployeeID]   ' last parameter for the primary key is

End Sub                                                   ' optional for F5 Refresh functionality.

 

Private Sub Form_Error(DataErr As Integer, Response As Integer)

  On Error Resume Next

  dalForm.Form_Error DataErr, Response

  On Error GoTo 0

End Sub

 

Save the form, open it and delete the current employee by key press Ctrl - (minus) or Delete in ribbon bar menu and an ADP+ Yes/No messagebox will appear:

 

 

When a form is using datasheet I can deleting several rows at once by first se­lec­ting (highlighting) several rows and then key press Delete or Ctrl – and ADP+ will show an Yes/No deleting mes­sage­box. The rows are first deleted in the database table when I choose Yes and I get only the messagebox one time even though I have selected multiple rows:

 

 

All rows in a datasheet can be selected by click in the upper left corner or key press Ctrl + A and then key press Delete or Ctrl - for deletion with confirm messagebox.

 

The Form_KeyDown event procedure before takes also care of the special keys:

Ctrl ' (apostrophe) do a duplicate above data from the previous row.

Alt ' (apostrophe) do a duplicate of the current row and insert it as a new row.

Shift + F12 ascending sort of the current column.

Ctrl + F12 descending sort of the current column.

Alt + F12 remove sort.

Ctrl + (plus) for inserting a new record after the last row.

Ctrl - (minus) for deleting the current record with a Yes/No confirm messagebox.

 

If I like to delete the pass-through query "Form_Employee" I also have to delete it from the form Record Source property and leave it blank. ADP+ will not use the pass-through que­ry because ADP+ provide an updatable ADO re­cordset to the form Recordset property and will replace the form Record Source property by the dalForm property Bind Me.

 

When the form property Record Source is based on a pass-through query, I don’t need to add all columns as controls in the form, because I can in the code behind the form refer to all columns of the pass-through query like this:

  MsgBox Me![BirthDate]

  MsgBox Me.BirthDate

and when ADP+ override the Record Source the code will still be working.

 

dalForm.Requery instead of Me.Requery and Me.Refresh.

dalForm.RequeryForm requery the form like Shift + F9 for Requery all.

dalForm.RefreshForm Me![EmployeeID] requery the form with the primary key control in the form like F5 for Refresh all.

 

Access can sometimes after saving an open form instead of saving in form design mode change the form Record Source property to this expression:

{ ? = call dbo.Form_Employee }

which gives an error after opening the form again. I have to delete the Record Source in form design mode. If I keep the pass-through query the Record Source property will not change and the pass-through query will be replaced by the stored procedure in dalForm. Else to avoid form changes I recommend to distribute an application saved as an accde file. { call } no­ta­tion is from the model ODBCDirect that was stopped supporting in Access 2007, but is still working behind Access.

 

4.2. Updatable form for Employee with a filter combobox

This example extend the Employee form with a filter combobox that select a title and the form will only show employees with that title. It is a replacement of:

 

Me.ServerFilter = "Title = 'Sales Representative'"

Me.Refresh

 

 

 

The stored procedure Form_Employee will get a parameter as title criteria with de­fault value null, so open the form will start with all employees:

 

CREATE PROCEDURE [dbo].[Form_Employee](@Title nvarchar(30) = NULL)

AS

BEGIN

 SET NOCOUNT ON

 SELECT   EmployeeID, FirstName, LastName, Title, BirthDate, Photo

 FROM     dbo.Employees

 WHERE    Title = @Title OR @Title IS NULL

 ORDER BY FirstName, LastName, EmployeeID

END

 

A new stored procedure will distinctly get the title from all the employees and will be used to provide data to the combobox:

 

CREATE PROCEDURE [dbo].[List_Title]

AS

BEGIN

 SET NOCOUNT ON

 SELECT   DISTINCT Title

 FROM     dbo.Employees

 ORDER BY Title

END

 

I add a combobox to the Employee form in the header band and call it FilterTitle and to get data from a new DataAccessLayerForm object I also add in the code:

 

Private dalFilter As DataAccessLayerForm

 

I instantiate in OnLoad event procedure the dalFilter object with properties and bind it to the combobox FilterTitle. The combobox property Row Source will be ta­ken over by ADP+ that will provide the data for the combobox through code pro­perty Recordset, so no sql statement in RowSource property but a stored procedure assigned into the dalFilter object:

 

  Set dalFilter = New_DataAccessLayerForm

  With dalFilter

   .Language = English

   .ObjectType = StoredProcedure

   .Object = "dbo.List_Title"

   .InputParameters

   .Bind Me![FilterTitle]

  End With

 

ADP+ gives combobox and listbox an InputParameters like in a form, so a value can be passed by to the stored procedure parameters that provide the data for the box.

 

The combobox FilterTitle need to update the form with the selected title, therefore I add an AfterUpdate event procedure where dalForm’s InputParameters will take the selec­ted value from the combobox into the dalForm object. I requery the stored procedure so it will be executed with the value from InputParameters and the form will requery too and showing the employees with the selected title from the combobox:

 

Private Sub FilterTitle_AfterUpdate()

  With dalForm

   .InputParameters Me![FilterTitle] ' the value from combobox is parameter value to stored procedure

   .Requery                          ' that will return a new recordset with employess of the title.

   .SetFocus                         ' set focus to the first control in the form.

  End With

End Sub

 

In case an user change or type in a new title for an employee, the combobox need to be requeried with new data when the user enter the combobox again, like this:

 

Private Sub FilterTitle_Enter()

  DoCmd.RunCommand acCmdSaveRecord  ' save the record so changes will be added to the combobox.

  dalFilter.Requery       ' update the combobox for FilterTitle in case a new Title has been type-in.

End Sub

 

In ADP I wrote:

 

Me.ServerFilter = "Title = 'Sales Representative'"

Me.Refresh

 

In ADP+ I write:

 

With dalForm

 .InputParameters "Sales Representative"

 .Requery

End With

 

In ADP I wrote:

 

Me.ServerFilter = Me.OpenArgs

 

In ADP+ I write inside Sub Form_Load(), OnLoad event:

 

With dalForm

 .Language = English

 .ObjectType = StoredProcedure

 .Object = "dbo.Form_Employee"

 .InputParameters Me.OpenArgs

 .Bind Me

End With

 

4.3. Updatable form for Employee with buttons for insert and delete

I add two Access buttons [Insert new] and [Delete current] with VBA code and the Delete button will use ADP+ deleting procedure with an Yes/No confirm messagebox:

 

Private Sub InsertEmployee_Click()

  On Error GoTo InsertEmployee_Error ' If SaveRecord is failed because LastName is missing then

  DoCmd.RunCommand acCmdSaveRecord ' Form_BeforeUpdate is called therefore goto InsertEmployee_Error.

  DoCmd.GoToControl "FirstName"

  DoCmd.GoToRecord , , acNewRec

  On Error GoTo 0

  Exit Sub

InsertEmployee_Error:

  On Error GoTo 0

  Exit Sub

End Sub

 

Private Sub DeleteEmployee_Click()

  On Error Resume Next

  DoCmd.RunCommand acCmdDeleteRecord

  On Error GoTo 0

End Sub

 

Constraint validation so names always have a value where I use a ADP+ function called IsNullOrEmpty and a control for that birthday is a good date for a living person:

 

Private Sub Form_BeforeUpdate(Cancel As Integer)

  ' To avoid breaking the table Employees constraints

  If dalForm.IsNullOrEmpty(Me![FirstName]) Or dalForm.IsNullOrEmpty(Me![LastName]) Then

     MsgBox "FirstName and LastName must have a text value.", vbOKOnly, "Missing values"

     Cancel = True

  End If

  If Not dalForm.IsNullOrEmpty(Me![BirthDate]) And

     (Me![BirthDate] < DateAdd("yyyy", -120, Date) Or Me![BirthDate] > Date) Then

     MsgBox "BirthDate must be between 120 years ago and today.", vbOKOnly, "Wrong value"

     Cancel = True

  End If

End Sub

 

 

5. ADP+ in action with a mainform and subform datasheet

I’m a big fan of an user interface to show and type in data based on an one-to-many-to-many relationship between three tables and comboboxes to give data from other tables to make it easy for the users to type in or choose the right values, like a multi­form with one mainform and two subforms:

 

 

The database ER diagram:

 

 

I have made three stored procedures for the mainform and the two subforms:

 

CREATE PROCEDURE [dbo].[Form_Suppliers]

AS

BEGIN

 SET NOCOUNT ON

 SELECT   SupplierID, CompanyName, ContactName, Region

 FROM     dbo.Suppliers

 ORDER BY CompanyName

END

 

CREATE PROCEDURE [dbo].[Form_Suppliers_Products](@SupplierID int)

AS

BEGIN

 SET NOCOUNT ON

 SELECT   ProductID, ProductName, CategoryID, UnitPrice, UnitsInStock, SupplierID

 FROM     dbo.Products

 WHERE    SupplierID = @SupplierID

 ORDER BY ProductName

END

 

CREATE PROCEDURE [dbo].[Form_Suppliers_Products_Orders](@ProductID int)

AS

BEGIN

 SET NOCOUNT ON

 SELECT   dbo.[Order Details].OrderID, dbo.Customers.CompanyName, dbo.Orders.OrderDate,

          dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity, dbo.[Order Details].Discount,

          dbo.[Order Details].ProductID, dbo.Orders.OrderID AS OrderID_Dummy

 FROM     dbo.[Order Details]

          INNER JOIN dbo.Orders ON dbo.Orders.OrderID = dbo.[Order Details].OrderID

          INNER JOIN dbo.Customers ON dbo.Customers.CustomerID = dbo.Orders.CustomerID

 WHERE    dbo.[Order Details].ProductID = @ProductID

 ORDER BY dbo.[Order Details].OrderID

END

 

More about the last stored procedure in 7.2. I have made three simple stored procedures for the three comboboxes: List_Suppliers, List_Categories and List_Orders.

 

I didn’t made pass-through queries for form guide design so the forms have blank Record Source and all controls have the Name and Control Source filled out by hand with the same column names from the stored procedures, except SearchSupplierID combobox in the mainform header band has blank Control Source, unbound.

 

Behind the multiform there is three objects of DataAccessLayerForm to provide data for mainform and two subforms and other three objects of DataAccessLayerForm to provide data for three comboboxes.

 

In the last subform there is a many-to-one relationship where many Order details each has a Company name and an Order date from other tables and the co­lumns are non updatable because I set the Locked properties to Yes else Alt ' (apostrophe) will duplicate Company name and Order date which gives an insert error because the columns are a kind of lookup columns from other tables.

 

The many-table Order Details has an Unique­ Table so ADO will only create one de­le­te sql statement for Order Details table. There is also a Resync ­Com­mand that return the updated record by the primary key so Company name and Order date shows the right values from the one-tables, more in 7.3.

 

ADP+ simulate Link­Child­Fields pro­per­ty by giving the for­eign key in subforms a de­fault value when an user insert a new row in the subform. In Products subform the hidden column Suppli­er­ID has default value =[Parent]![SupplierID] and in Order subform the hid­den column Pro­duct­ID has default value =[Parent]![Suppliers_Pro­ducts_Subform].[Form]! [Pro­­duct­­Id].

 

ADP+ simulate LinkMasterFields property by OnCurrent event procedure in Supplier main­form and in Products subform, where the current record/row primary key is the In­put­Parameters. In Supplier form I use OnCurrent to call a public sub procedu­re me­thod RequerySubform in Products subform to do requery with current value:

 

Private Sub Form_Current()  'In Supplier mainform.

  On Error Resume Next      'Is needed because Access is loading a subform before its mainform.

  Me![Suppliers_Products_Subform].Form.RequerySubform  'Call a public method in Products subform.

  On Error GoTo 0

End Sub

 

Public Sub RequerySubform()  'In Products subform is the called public methods.

  With dalForm

   .InputParameters Parent![SupplierID]  'Refer to current supplier in mainform.

   .Requery

  End With

End Sub

 

In Products subform I use OnCurrent event procedure to call a public sub procedure method in Or­ders subform. The design pattern is the same for all mainforms and sub­forms also when a sub like Products subform become a mainform for Or­ders sub­form:

 

Private Sub Form_Current()

  On Error Resume Next

  Parent![Suppliers_Products_Orders_Subform].Form.RequerySubform  'Call a method in Orders subform

  On Error GoTo 0

End Sub

 

Public Sub RequerySubform()

  With dalForm

   .InputParameters Parent![Suppliers_Products_Subform].Form![ProductID] 'Refer to current product

   .Requery  

  End With

End Sub

 

Based on =Forms![Mainformname]![Subformname].Form![SubformControlname].

ADP+ makes it easy to use Unique ­Table and Resync­ Com­mand in the Orders sub­form which refer back to the Products subform current row:

 

  Set dalForm = New_DataAccessLayerForm   'Set object to new instance by calling a factory procedure.

  With dalForm

   .Language = English

   .ObjectType = StoredProcedure

   .Object = "dbo.Form_Suppliers_Products_Orders"  'Stored procedure provide data to the subform.

   .InputParameters Parent![Suppliers_Products_Subform].Form![ProductID]  'Refer to current product

   .UniqueTable = "Order Details"  'Make sure delete records only from Order Details table.

   .ResyncCommand = "dbo.Form_Suppliers_Products_Orders_Resync ?,?" 'Stored proc return update record

   .Bind Me               'Binding Me to object dalForm in the subform and it will override the form

  End With                'Record Source pass-through query with data from stored procedure.

 

 

6. ADP+ in action with a mainreport and subreport and Excel

A multireport shows one-to-many relationship among data like for each employee it will include the latest month’s orders of the employee (Northwind has old data):

 

 

A report normally has a criteria form to limit data and I use the title of the employ­ees that I already have used as a filter in the employee form:

 

 

Print button has code to open a report and later I show code inside the report that will query data to provide for the report:

 

Private Sub Print_Click()

  On Error Resume Next

  If Not IsNull(Me![Title]) Then

     DoCmd.OpenReport "Employees_By_Title", acViewPreview

     DoCmd.RunCommand acCmdFitToWindow

  End If

  On Error GoTo 0

End Sub

 

I make a stored procedure for the mainreport with title as a parameter and another stored procedure for the subreport also with the title as a parameter, so both sto­red procedures provide all data at once to the multireport:

 

CREATE PROCEDURE [dbo].[Report_Employees_By_Title](@Title nvarchar(30))

AS

BEGIN

 SET NOCOUNT ON

 SELECT   EmployeeID, FirstName + ' ' + LastName AS Name, BirthDate, Title, Photo

 FROM     dbo.Employees

 WHERE    Title = @Title

 ORDER BY FirstName + ' ' + LastName, BirthDate, EmployeeID

END

 

For subreport to get the latest month’s orders of each employee, I have calculated an yyymm in­te­ger of OrderDate and find the maximum value:

 

CREATE PROCEDURE [dbo].[Report_Employees_Orders_By_Title](@Title nvarchar(30))

AS

BEGIN

 SET NOCOUNT ON

 SELECT   o.EmployeeID, o.OrderDate, c.CompanyName

 FROM     dbo.Orders o

          INNER JOIN dbo.Customers c ON c.CustomerID = o.CustomerID

          INNER JOIN

          (SELECT ee.EmployeeID, LatestYearMonth = MAX(YEAR(oo.OrderDate)*100+MONTH(oo.OrderDate))

           FROM dbo.Orders oo

                INNER JOIN dbo.Employees ee ON ee.EmployeeID = oo.EmployeeID

           WHERE ee.Title = @Title

           GROUP BY ee.EmployeeID

          ) t ON t.EmployeeID = o.EmployeeID AND

                                        t.LatestYearMonth = YEAR(o.OrderDate)*100+MONTH(o.OrderDate)

 ORDER BY o.OrderDate, o.OrderID

END

 

I make two pass-through queries with default text value for title parameter in the module myDataAccessLayerFactory:

 

DataAccessLayerConnectionFactory.CreatePassThroughQuery "Report_Employees_By_Title",

                              "EXEC dbo.Report_Employees_By_Title 'Inside Sales Coordinator'"

 

DataAccessLayerConnectionFactory.CreatePassThroughQuery "Report_Employees_Orders_By_Title",

                              "EXEC dbo.Report_Employees_Orders_By_Title 'Inside Sales Coordinator'"

 

I design mainreport and subreport upon the pass-through queries together with Em­­ploy­ee­­ID textbox with Visible set to No. I add ADP+ code that will simulate Link­Master­Fields and LinkChildFields pro­per­ti­es in the subreport at OnOpen and On­Clo­se event procedures and at detail band OnFormat event procedure where the code refer to control Me![EmployeeID] so I have a textbox with Visible set to No and I place the textbox in a small place. I can always find the textbox again from the Property sheet by choosing textbox EmployeeID in the combobox / dropdown box:

 

 

Code behind the subreport will be like this:

 

Private dalReport As DataAccessLayerReport   'Declare an object as the report’s private class member.

 

Private Sub Report_Open(Cancel As Integer)

  On Error Resume Next

  Set dalReport = New_DataAccessLayerReport  'Set object to new instance by calling a factory proc.

  With dalReport

   .Language = English

   .ObjectType = StoredProcedure

   .Object = "dbo.Report_Employees_Orders_By_Title"

   .InputParameters Forms![Employees_By_Title_Criteria]![Title]

   .Bind Me                             'Binding Me to object dalReport and it will override the

  End With                              'Record Source pass-through query with data from stored proc.

  On Error GoTo 0

End Sub

 

Private Sub Report_Close() 'This event gives back the default text value for parameter in p-t query.

  On Error Resume Next

  With dalReport

   .InputParameters "Inside Sales Coordinator"  'No need for SQL text ' ADP+ takes care of that.

   .Requery                                     'Reset pass-through query with the default value.

  End With

  Set dalReport = Nothing

  On Error GoTo 0

End Sub

 

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

  On Error Resume Next

  dalReport.Detail_Format Cancel, FormatCount, Parent![EmployeeID], Me![EmployeeID]

  On Error GoTo 0

End Sub

 

I can test the report directly from the Navigation pane where I right-click at main­report and click Print Preview so it look like paper layout with the default title. The Report View has some limitations such as its lack of support for code that adjusts displays and calculations in the Detail event so multireport is not working there. Therefore it is best to test the multireport from the form I made before that use a filter dropdown for selection of the wanted title for the report to show at screen, to print at paper or to save as pdf file in the ribbon bar menu and send the report by email like by some Outlook programming in the Access application and avoid run-time error 287 and works for outgoing smtp and exchange server:

 

Dim oApp As Outlook.Application

Dim oFld As Outlook.MAPIFolder

Dim oMail As Outlook.MailItem

Set oApp = New Outlook.Application

Set oFld = oApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)

Set oMail = oFld.Items.Add

With oMail

 .Recipients.Add "mail@xyz.com"

 .Subject = "Subject"

 .Body = "Body"

 .Importance = olImportanceHigh

 .Attachments.Add "C:\ProgramData\xyz.pdf"

 .DeleteAfterSubmit = True

 .Send

End With

Set oMail = Nothing

Set oFld = Nothing

' oApp.Quit   ' Not needed so when Outlook is open before, it will then remain open after this.

Set oApp = Nothing

 

More Outlook VBA tips at this link

 

Export to Excel

Almost the same design pattern for Export to Excel where I make a stored procedure for the data columns to the spreadsheet, create a pass-through query with default text value for the title parameter:

 

Private Sub ExportExcel_Click()

  Dim dalExport As DataAccessLayerReport     'Declare an object as the Sub’s locale variable.

  On Error Resume Next

  Set dalExport = New_DataAccessLayerReport  'Set object to new instance by calling a factory proc.

  With dalExport

   .Language = English

   .ObjectType = StoredProcedure

   .Object = "dbo.Export_Employees_Orders_By_Title"

   .InputParameters Forms![Employees_By_Title_Criteria]![Title]

   .PassThroughQueryName = "Export_Employees_Orders_By_Title"  'No Bind here instead p-t query name.

   .Requery                                                    'Make the p-t query with param value.

  End With

  If Dir(Me![FileName]) <> "" Then

     Kill Me![FileName]

  End If

  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml,

                            "Export_Employees_Orders_By_Title", Me![FileName], True

  With dalExport  'Set back the default text value for parameter value in p-t query.

   .InputParameters "Inside Sales Coordinator"

   .Requery

  End With

  Set dalExport = Nothing

  On Error GoTo 0

End Sub

 

In case TransferSpreadsheet xlsx file gives an error use instead OutputTo:

 

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PassThroughQuery", FileName, True

DoCmd.OutputTo acOutputTable, "PassThroughQuery", acFormatXLSX, Filename, False  ' 65535 rows

DoCmd.OutputTo acOutputTable, "PassThroughQuery", "Excel Workbook (*.xlsx)", Filename, False

 

In case you like to save as xls file up to 65535 rows:

 

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "PassThroughQuery", FileName, True

DoCmd.OutputTo acOutputTable, "PassThroughQuery", acFormatXLS, Filename, False  ' 16383 rows

DoCmd.OutputTo acOutputTable, "PassThroughQuery", "Excel 97 - Excel 2003 Workbook (*.xls)", Filename, False

 

Import to Excel

Instead of doing an export to a new Excel spreadsheet file, you can from Access vba create an Excel workbook file with a sheet and im­port data from a DAO or a ADO recordset directly into an open Excel sheet with vba commando:

 

ActiveSheet.Range("A2").CopyFromRecordset rs

 

and afterwards do so­me ex­tra for­mat­ting to make the sheet looking nicer for the user when he/she open the Excel file.

 

Private Function MakeExcelXLSX(ExcelFilename As String) As Boolean

  Dim dalExcel As DataAccessLayerForm

  Dim rs As ADODB.Recordset, column As ADODB.Field

  Dim ExcelFile As Object, ExcelApp As Object

  'Dim ExcelFile As Excel.Workbook, ExcelApp As Excel.Application 'Ref to Microsoft Excel Object Lib

   

  On Error GoTo MakeExcelXLSX

  If Dir(ExcelFilename) <> "" Then

     Kill ExcelFilename

  End If 

  Set dalExcel = New_DataAccessLayerForm

  With dalExcel

   .Language = English

   .MarshalModifiedOnly = No ' because Excel CopyFromRecordset do not accept use of Marshal.

   .ObjectType = StoredProcedure

   .Object = "dbo.Export_Employees_Orders_By_Title"

   .InputParameters Forms![Employees_By_Title_Criteria]![Title]

   Set rs = .Recordset

  End With

  If dalExcel.Status Then

   If Not rs.EOF Then

     Set ExcelApp = CreateObject("Excel.Application")

     'Set ExcelApp = New Excel.Application

     ExcelApp.Visible = False

     Set ExcelFile = ExcelApp.Workbooks.Add(1) ' ExcelFile.Application.SheetsInNewWorkbook = 1

     ExcelFile.Windows(1).Visible = True

     ExcelFile.Application.DisplayAlerts = False

     ExcelFile.Application.Selection.Style = "Normal"

     ExcelFile.ActiveSheet.Range("A1").Select

     With ExcelFile.Application.ActiveWorkbook.Styles("Normal")

          .IncludeNumber = True

          .IncludeFont = True

          .IncludeAlignment = True

          .IncludeBorder = True

          .IncludePatterns = True

          .IncludeProtection = True

     End With

     With ExcelFile.Application.ActiveWorkbook.Styles("Normal").Font

          .Name = "Calibri"  '.Name = "Arial"

          .Size = 11         '.Size = 8

          .Bold = False

          .Italic = False

          .Underline = xlUnderlineStyleNone

          .StrikeThrough = False

          .ColorIndex = xlAutomatic

     End With

    

     ExcelFile.ActiveSheet.Range("A1").Select

     For Each column In rs.Fields

         ExcelApp.ActiveCell = column.Name

         ExcelApp.ActiveCell.Offset(0, 1).Select

     Next

 

     ExcelFile.ActiveSheet.Range("A2").CopyFromRecordset rs

 

     ' Example of a number value format column to display

     ' ExcelFile.ActiveSheet.Columns("H:H").Select

     ' ExcelFile.Application.Selection.NumberFormat = "#,##0.00"

     ' ExcelFile.ActiveSheet.Columns("M:O").Select

     ' ExcelFile.Application.Selection.NumberFormat =

     ' "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"

 

     ' ExcelFile.ActiveSheet.Columns("P:P").Select

     ' ExcelFile.Application.Selection.ColumnWidth = 17

 

     ' ExcelFile.ActiveSheet.Range("A1").Select

     ' If ExcelFile.ActiveSheet.AutoFilter Is Nothing Then

     '    ExcelFile.Application.Selection.AutoFilter

     ' End If

    

     ExcelFile.ActiveSheet.Cells.Select

     ExcelFile.ActiveSheet.Cells.EntireColumn.AutoFit

                   

     ExcelFile.ActiveSheet.Name = Me![Title]

     ExcelFile.ActiveSheet.Range("A2").Select

     ExcelFile.Application.ActiveWindow.FreezePanes = True

     ExcelFile.ActiveSheet.Range("A1").Select

              

     ExcelFile.SaveAs ExcelFilename

     ExcelFile.Application.DisplayAlerts = True

     ExcelFile.Close SaveChanges:=False

     ExcelApp.Quit

     Set ExcelFile = Nothing

     Set ExcelApp = Nothing

     MakeExcelXLSX = True

   Else

     MsgBox "No data match the criteria.", vbInformation + vbOKOnly, "No Excel file"

     MakeExcelXLSX = False

   End If

   rs.Close

   Set rs = Nothing

  Else

     MsgBox dalExcel.ErrorMessage

     MakeExcelXLSX = False

  End If

  On Error GoTo 0

  Exit Function

MakeExcelXLSX:

  MsgBox Err.Description

  On Error GoTo 0

  On Error Resume Next

  If Not ExcelFile Is Nothing Then

     ExcelFile.Application.DisplayAlerts = True

     ExcelFile.Close SaveChanges:=False

     ExcelApp.Quit

     Set ExcelFile = Nothing

     Set ExcelApp = Nothing

  End If

  MakeExcelXLSX = False

  On Error GoTo 0

  Exit Function

End Function

 

How to clean up an Excel workbook so that it uses less memory.

 

Save report as a PDF file without open the report on the screen

I can add a new button with two different kind of code behind, where the first code save a pdf file in the same folder as the application accdb file and the second code shows a save-as file dialogbox where a user can select folder and pdf file name:

 

Private Sub PDFSaveDialog_Click()

  Dim pdfFileName As String

  If Not IsNull(Me![Title]) Then

     DoCmd.Hourglass True

     pdfFileName = Application.CurrentProject.Path & "\" & Me![Title] & ".pdf"

     If Dir(pdfFileName) <> "" Then

        Kill pdfFileName

     End If

     DoCmd.OutputTo acOutputReport, "Employees_By_Title", acFormatPDF, pdfFileName, False

                    ' False for not to start a pdf application to show the file content.

     DoCmd.Hourglass False

     MsgBox pdfFileName + " is saved.", vbInformation + vbOKOnly, "PDF"

  End If

End Sub

 

Private Sub PDFSaveDialog_Click()

  Dim pdfFileName As String

  Dim fDialog As Office.FileDialog ' Requires reference to Microsoft Office 15.0 Object Library.

  If Not IsNull(Me![Title]) Then

     Set fDialog = Application.FileDialog(msoFileDialogSaveAs)

     With fDialog

      .AllowMultiSelect = False

      .Title = "Save to a PDF file"

      .InitialFileName = Application.CodeProject.Path & "\*.pdf"

      If .Show = True Then

         DoCmd.Hourglass True

         pdfFileName = .SelectedItems(1)

         If Dir(pdfFileName) <> "" Then

            Kill pdfFileName

         End If

         DoCmd.OutputTo acOutputReport, "Employees_By_Title", acFormatPDF, pdfFileName, False

                        ' False for not to start a pdf application to show the file content.

         DoCmd.Hourglass False

         MsgBox pdfFileName + " is saved.", vbInformation + vbOKOnly, "PDF"

      Else

        MsgBox "You clicked Cancel in the file dialog box."

      End If

     End With

  End If

End Sub

 

7. ADP+ technique

The beauty of ADO model is the automatic creating of SQL statements as INSERT, UPDATE and DELETE with the use of Unique Table and Resync Command. ADP+ use form properties Me.Recordset = rs, Me.UniqueTable and Me.ResyncCommand but don’t use Me.InputParameters, Me.RecordSource or Me.RowSource as we known from adp file. ADP+ use form property Recordset Type as Updatable snaphot but user can change it in the form design to make it read-only as a Snapshot form. ADP+ use a static va­ria­ble to ma­ke sure that there is only one ADOconnection. ADP+ use ADOrecord­set with proper­ties:

 

Lock­Type = adLockOptimistic;  CursorType = adOpenKeyset;

Cur­sor­Location = adUse­Client; MarshalOptions = adMarshal­Modi­fiedOnly;

 

ADP+ is using ADOcommand.Parameters.Refresh to get stored procedure parameters meta data by sp_procedure_pa­rams_row­set.

This chapter presents some advanced stuff from ADP+.

 

In Access 2010 and before versions it was fine to write: Set rst = Me.Recordset.

But from Access 2013 it is better to write: Set rst = Me.RecordsetClone.

 

7.1. Subform stored procedure fired only one time

When I open a multiform in Access the subform will make an OnLoad event two times, but ADP+ will make sure that the stored procedure is only called one time to provide data for the form record source by an ADO recordset. From SQL Ser­ver Pro­filer trace I can monitoring the calls of stored procedures when I am open a multiform with three subforms and three combobox which is getting data from six diffe­rent stored pro­ce­dures. Each stored procedure is only called one time:

 

 

ADP+ prevent subforms to be fired off several times by loading and there is no ne­ed for old Access tricks with global or static variables and unbound subforms like:

 

Me![Suppliers_Products_Subform].SourceObject = "Suppliers_Products_Subform"

Me![Suppliers_Products_Orders_Subform].SourceObject = "Suppliers_Products_Orders_Subform"

 

7.2. Foreign key workaround

Since Access 2007 ADP there has been an error in an one-to-many relationship where the form will show data from the many-table and data from the one-table and the one-table primary key is an identity column with the same name as the foreign key in the many-table (I never like naming as PK_OrderId and FK_OrderId). The identity column property from one-table will be passed to the Access form even though it is only the many-table in the sql select statement. That strange thing me­ans that the foreign key can't be updatable a form or subform, e.g. the multiform in the last subform for column OrderId. My workaround in ADP before and in ADP+ is an alias-rename of the one-table primary key identity column, OrderID_Dummy:

 

SELECT    dbo.[Order Details].OrderID, dbo.Customers.CompanyName, dbo.Orders.OrderDate,

          dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity, dbo.[Order Details].Discount,

          dbo.[Order Details].ProductID, dbo.Orders.OrderID AS OrderID_Dummy

FROM      dbo.[Order Details]

          INNER JOIN dbo.Orders ON dbo.Orders.OrderID = dbo.[Order Details].OrderID

          INNER JOIN dbo.Customers ON dbo.Customers.CustomerID = dbo.Orders.CustomerID

WHERE     dbo.[Order Details].ProductID = @ProductID

ORDER BY dbo.[Order Details].OrderID

 

The OrderID_Dummy column is not included in the form. Now the foreign key co­lumn e.g. [Order Details].OrderID is updatable in the form. I have study the re­cord­set and field properties but I didn’t found out why [Order Details].OrderID act like an identity column, even the field property ISAUTOINCREMENT was False.

 

I have noticed a simular problem when a combobox or listbox receive data from a sql join with columns from two or more tables, then it will only be working when the select part refer to column from the one-table instead of using the same co­lumn from the many-table, e.g.:

 

SELECT    dbo.Orders.OrderID, dbo.Customers.CustomerID, dbo.Orders.OrderDate

FROM      dbo.Customers

          INNER JOIN dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID

ORDER BY dbo.Orders.OrderID

 

instead of this that can gives problem for combobox and listbox:

 

SELECT    dbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Orders.OrderDate

 

7.3. Resynchronization

»The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source« is an error message from Access after updating data in a form based on a join between tables.

 

Resynchronization a record after it had been updated or inserted is relevant in a many-to-one relationship form where I update the columns from the many-table and I want the one-table columns to be refreshed automatically with correctly va­lu­es shown in the form or datasheet. An example was the Suppliers multiform in the last subform where co­lumns Company name and Order date don’t belong to the many-table Order De­tails but belong to two one-tables Customers and Orders. ADP+ is using Resync Command through an extra stored procedure that has same select and join as the stored procedure that provide data for the subform but the parameters and where-part is the primary key of the many-table here Order Details and no order by:

 

CREATE PROCEDURE [dbo].[Form_Suppliers_Products_Orders_Resync](@OrderID int, @ProductID int)

AS

BEGIN

 SET NOCOUNT ON

 SELECT   dbo.[Order Details].OrderID, dbo.Customers.CompanyName, dbo.Orders.OrderDate,

          dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity, dbo.[Order Details].Discount,

          dbo.[Order Details].ProductID,

          dbo.Orders.OrderID AS OrderID_Dummy

 FROM     dbo.[Order Details]

          INNER JOIN dbo.Orders ON dbo.Orders.OrderID = dbo.[Order Details].OrderID

          INNER JOIN dbo.Customers ON dbo.Customers.CustomerID = dbo.Orders.CustomerID

 WHERE    dbo.[Order Details].OrderID = @OrderID AND dbo.[Order Details].ProductID = @ProductID

END

 

When the form has send an update of a record or added a new record to the table and the record has been saved in the table, this stored procedure will return one record from the table with correctly data from the one-table columns and update the recordset and the form. When I change or type in an OrderId then the Company name and Order date columns will automatically be refreshed and show the va­lu­es at the screen for users information.

 

When I use ResyncCommand the stored procedure is parameterized with the primary key showned with a ? parameter, that is normally the primary key of the table that is specified in Unique Table. I have used the same technique in adp files where the form had a property for Unique Table and for Re­sync­Command which also could use a sql statement with ? in the where-part, but ADP+ wants to use a stored procedure and with parameters represented by ?, like this:

 

.UniqueTable = "Order Details"

 

.ResyncCommand = "dbo.Form_Suppliers_Products_Orders_Resync ?,?"

 

Sometimes I have add a textbox in a datasheet showing extra data from the com­bo­box list in other column like =[OrderID].[Column](1) and therefore I don’t need re­syn­chro­ni­za­tion. But it takes a little second to update this calculated column when I scroll through a datasheet and that gives the screen a kind of ghosting.

 

In case you need a left outer join, I have good result of using a case-when to avoid this errormessage »Incorrect syntax near 'Resync'« since Access 2002, like this:

 

SELECT A.*, CASE WHEN B.Remark IS NOT NULL THEN B.Remark ELSE '' END AS Remark

FROM dbo.House AS A

     LEFT OUTER JOIN dbo.Buyer AS B ON A.Id = B.Id

WHERE A.Id = @Id

 

7.4. Integer values in a combobox workaround

When a combobox contains integer values and I like to type in an integer value in­ste­ad of selecting it from the list, like OrderId combobox in the last subform in the multiform, I can’t leave the column because Access tells me that the integer value is not in the list. My workaround is to set combobox property LimitToList to No and add this code to combobox BeforeUpdate for validation of the type in value:

 

Private Sub OrderID_BeforeUpdate(Cancel As Integer)

  dalOrderID.Combobox_BeforeUpdate Cancel  'Cancel will be true if combobox value is not in the list.

End Sub

 

In chapter 8 I use a pass-through query as Row Source to a combobox and to­ge­ther with LimitToList = Yes, I can type in an integer value, that is another work­around.

 

7.5. Primary key validation with a friendly message to the user

The multiform has an extra object of DataAccessLayerForm to provide an exist data validation when I have typed in an OrderId because same OrderId can’t exist in two rows within the same product and to avoid a SQL Server primary key validation mes­sage. ADP+ has an ExecuteReturnValue method for an integer return value from a sto­red procedure. I use a stored procedure to do the primary key validation and it will return 1 when it is true the parameters already exist and return 0 for false, no exist. After an OrderId has been typed in or selected in the combobox but before it is saved in the database, I do an exist validation and when value already is in the table, Cancel will be true so cursor don’t leave the combobox and the user will get an user friendly message:

 

CREATE PROCEDURE [dbo].[Form_Suppliers_Products_Orders_Exist](@OrderID int, @ProductID int)

AS

BEGIN

 SET NOCOUNT ON

 SET ROWCOUNT 1

 SELECT   1

 FROM     dbo.[Order Details]

 WHERE    dbo.[Order Details].OrderID = @OrderID AND dbo.[Order Details].ProductID = @ProductID

 RETURN @@ROWCOUNT

END

 

Private dalExist As DataAccessLayerForm  'Declare an object as the form’s private class member.

  'and inside the Form_Load() event procedure the object gets its stored procedure I made above.

  Set dalExist = New_DataAccessLayerForm 'Set object to new instance by calling a factory procedure.

  With dalExist

   .Language = English

   .ObjectType = StoredProcedure

   .Object = "dbo.Form_Suppliers_Products_Orders_Exist"

  End With

 

Private Sub OrderID_BeforeUpdate(Cancel As Integer)

  dalOrderID.Combobox_BeforeUpdate Cancel  'See explanation in the section for integer values.

  On Error Resume Next

  If Not Cancel Then

     With dalExist

      .InputParameters Me![OrderID], Me![ProductID]  'primary key of Order Details table.

      Cancel = .ExecuteReturnValue

     End With

     If Cancel Then

        MsgBox "The OrderId is already used and can't be used two times within the same product.",

               vbCritical, "OrderId is unique"

     End If

  End If

  On Error GoTo 0

End Sub

 

7.6. Datasheet column width

Sometimes a subform don’t want to save the width of columns in a datasheet, the­re­fore it can be faster to code them and ADP+ makes it a little more easy in the Form_Load event procedure. Below the values 20, 35, 8 and 11 is from the form datasheet column width in ribbon bar. They need recalc to be pixels in the program­ming:

 

Me![CategoryID].ColumnWidth = 20 * DataAccessLayerColumnWidth

Me![ProductName].ColumnWidth = 35 * DataAccessLayerColumnWidth

Me![UnitPrice].ColumnWidth = 8 * DataAccessLayerColumnWidth

Me![UnitsInStock].ColumnWidth = 11 * DataAccessLayerColumnWidth

Me![ProductID].ColumnHidden = True

Me![SupplierID].ColumnHidden = True

 

Can also use special values: -1 sizes the column to the default width and -2 sizes the column to autofit the text, like Me![ProductName].ColumnWidth = -2. Normally I use form’s TabOrder to set the order of the columns in the datasheet, but I can also use property ColumnOrder. Also property FrozenColumn.

 

7.7. Form Record Source

I have noticed that Access set the form’s property Record Source when the Recordset get an ADO recordset. In the multiform with one mainform and two subforms, the form’s Record Source is this while the form is open:

 

{ ? = call dbo.Form_Suppliers }

{ ? = call dbo.Form_Suppliers_Products(?) }

{ ? = call dbo.Form_Suppliers_Products_Orders(?) }

 

In the code there is also a Me.RecordSelectors which was a form property in adp file with a schema of SQL Server database tables like dbo. I have not yet found out the use of Me.InputParameters in a form in a desktop application in Access.

 

7.8. Import a file directly to a table in SQL Server database

  1. I have a file Data.csv in a folder C:\Data.
  2. I make a normal test import of the file into Access and under button Advan­ced I set the file specifications and change Fields1… to better column names  and datatypes. I save the specification with a name Data_csv. I cancel the import or I can do the import to see it is working and then delete the table.
  3. In SQL Server database I create a new table ImportData with same column names and datatype plus and extra column as first column with name Login where I will store user’s login code in case of two current users at the same time do an import of two different Data.csv files into table ImportData.
  4. In Access Visual Basic Application VBA Editor I make new module and I make two sql statements that will be running from Access, delete old imported data and transfer data from the file to the SQL Server table without saving any data in the Access accdb file to prevent the file size to bloat:

 

DoCmd.SetWarnings False

DoCmd.RunSQL "DELETE * FROM ImportData IN '' [ODBC;Driver={SQL Server Native Client 11.0};" + _

             "Server=<name>; Database=Northwind;Trusted_Connection=Yes]" + _

             "WHERE Login = '" & DataAccessLayerConnectionFactory.UserId & "'"

 

DoCmd.RunSQL "INSERT INTO ImportData IN '' [ODBC;Driver={SQL Server Native Client 11.0};" + _

             "Server=<name>; Database=Northwind;Trusted_Connection=Yes]" + _

             "SELECT '" & DataAccessLayerConnectionFactory.UserId & "' AS Login, * " + _

             "FROM [Data.csv] IN '' [Text;DSN=Data_csv;FMT=Delimited;HDR=NO;IMEX=2;" +_

                                    "CharacterSet=1252;ACCDB=YES;DATABASE=C:\Data]"

DoCmd.SetWarnings True

 

This technique is using embedding ODBC connectionstring into SQL statement and therefore no linked table or linked import file to an Access application file. The saved import specification I think is better than the old shema.ini file for import.

ADP+ provide the login code of the current user with this code command:

DataAccessLayerConnectionFactory.UserId.

The saved Import Advanced Specification is used in DSN=Data_csv.

HDR = Header of column names in the file, YES or NO.

The path C:\Data become the database and the file name [Data.csv] become table.

Yeah you can always built this into a form that ask for the file placement and so on and use it to call an ADP+ method to do the above task like this:

 

DataAccessLayerConnectionFactory.ImportCSVWithLoginColumn "C:\Data", "Data.csv", "Data_csv",

                                                       "No", "ImportData"

 

If Not DataAccessLayerConnectionFactory.Status Then ' false when file don’t exists or import go down

   MsgBox DataAccessLayerConnectionFactory.ErrorMessage

End If

 

An Excel file Data.xlsx with first row header of column names same as in table:

 

DataAccessLayerConnectionFactory.ImportExcelWithLoginColumn "C:\Data", "Data.xlsx", "Sheet1$",

                                                       "Yes", "ImportData" ' in danish Ark1$

 

If Not DataAccessLayerConnectionFactory.Status Then ' false when file don't exists or import go down

   MsgBox DataAccessLayerConnectionFactory.ErrorMessage

End If

 

7.9. View Order By tip

A view in SQL Server database with sortorder to be a pass-through query to provide data to a form, combobox, listbox or report need this TOP(99.99) to work:

 

CREATE VIEW [dbo].[List_Region]

AS

SELECT TOP (99.99) PERCENT RegionID, RegionDescription

FROM   dbo.Region

ORDER BY RegionDescription DESC

 

I make a pass-through query in the module myDataAccessLayerFactory:

 

DataAccessLayerConnectionFactory.CreatePassThroughQuery "List_Region","SELECT * FROM dbo.List_Region"

 

7.10. Linked view can be updatable

Updatability for a linked view I need to create a pseudo index of the primary key inside the Access application and ADP+ takes care of that:

 

CREATE VIEW [dbo].[View_Employees]

AS

SELECT TOP (99.99) PERCENT EmployeeID, LastName, FirstName, Title, BirthDate

FROM   dbo.Employees

ORDER BY LastName, FirstName

 

I make a linked view in the module myDataAccessLayerFactory:

 

DataAccessLayerConnectionFactory.CreateLinkView "View_Employees", "dbo", "EmployeeID"

 

If Not DataAccessLayerConnectionFactory.Status Then     ' false if an error occurs

   MsgBox DataAccessLayerConnectionFactory.ErrorMessage

End If

 

The linked view can be used in an updatable form like a linked table. In SQL Ser­ver Pro­fi­ler I can see strange sql statements from Access to SQL Server, the same pattern as using linked table, so I will not recommend linked table or linked view to an Access desktop database application front-end.

 

7.11. Linked table without DSN file

I make a linked table in the module myDataAccessLayerFactory with ADP+:

 

DataAccessLayerConnectionFactory.CreateLinkTable "Employees"

 

If Not DataAccessLayerConnectionFactory.Status Then     ' false if an error occurs

   MsgBox DataAccessLayerConnectionFactory.ErrorMessage

End If

 

7.12. Get a recordset

ADP+ method Recordset gives an ADO recordset based on a stored procedure or here on a sql statement. Don’t use Recordset when dal object is binding to a form instead use RecordsetClone, see section 7.16.

 

Private Sub SQLVersion()

  Dim dalRS As DataAccessLayerForm, rs As ADODB.Recordset  ' declaring object from two classes.

  Set dalRS = New_DataAccessLayerForm  ' early binding to new instance calling a factory procedure.

  With dalRS

   .ObjectType = SQL                   ' when using a sql statement in next line.

   .Object = "SELECT @@VERSION"

   .InputParameters                    ' have to be here also when no parameters are used.

   Set rs = .Recordset                 ' the method return an ADODB.Recordset.

  End With

  If dalRS.Status Then                 ' to validate dalRS where Status = True is good.

     MsgBox rs(0)                      ' first row and first column (0).

  Else

     MsgBox dalRS.ErrorMessage         ' getting the error that had happen inside dalRS.

  End If

  rs.Close

  Set rs = Nothing

  Set dalRS = Nothing

End Sub

 

Show the rows from a sql server table through a ADO recordset:

 

Private Sub ShowTable()

  Dim dalRS As DataAccessLayerForm, rs As ADODB.Recordset, list As String

  Set dalRS = New_DataAccessLayerForm

  With dalRS

   .ObjectType = Table

   .Object = "Employees"

   .InputParameters

   Set rs = .Recordset

  End With

  If dalRS.Status Then

     While Not rs.EOF

       list = list + rs("LastName") + Chr(13) & Chr(10)

       rs.MoveNext

     Wend

     MsgBox list

  Else

     MsgBox dalRS.ErrorMessage

  End If

  rs.Close

  Set rs = Nothing

  Set dalRS = Nothing

End Sub

 

7.13. Execute a sql statement

ADP+ method Execute will execute a stored procedure or here a sql statement that change data in a table:

 

Private Sub Execute()

  Dim dalExec As DataAccessLayerForm

  Set dalExec = New_DataAccessLayerForm

  With dalExec

   .ObjectType = SQL

   .Object = "UPDATE Customers SET Country = 'United Kingdom' WHERE Country = 'UK'"

   .InputParameters

   .Execute

  End With

  If Not dalExec.Status Then

     MsgBox dalExec.ErrorMessage

  Else

     MsgBox "Updating is done"

  End If

  Set dalExec = Nothing

End Sub

 

7.14. Execute a stored procedure with return value

ADP+ method ExecuteReturnValue gives a integer (int = long) value from a stored procedure RETURN:

 

CREATE PROCEDURE [dbo].[Get_Employees_NumberOf_By_Title](@Title nvarchar(30))

AS

BEGIN

  SET NOCOUNT ON

  DECLARE @NumberOf int

  SELECT  @NumberOf = COUNT(*)

  FROM    dbo.Employees

  WHERE   Title = @Title

  RETURN @NumberOf

END

 

Private Sub ExecuteReturn()

  Dim Title As String, NumberOfEmployees As Long

  Dim dalExec As DataAccessLayerForm

  Title = "Sales Representative"

  Set dalExec = New_DataAccessLayerForm

  With dalExec

   .ObjectType = StoredProcedure

   .Object = "dbo.Get_Employees_NumberOf_By_Title"

   .InputParameters Title

   NumberOfEmployees = .ExecuteReturnValue

  End With

  If dalExec.Status Then

     MsgBox NumberOfEmployees

  Else

    MsgBox dalExec.ErrorMessage

  End If

  Set dalExec = Nothing

End Sub

 

7.15. Execute a stored procedure with output parameters

ADP+ method ExecuteOutputParameters has variable or control as parameter and assign them the value from a stored procedure OUTPUT parameter like this:

 

CREATE PROCEDURE [dbo].[Get_Employee_Data]

   @EmployeeID int,

   @FirstName nvarchar(10) OUTPUT, @LastName nvarchar(20) OUTPUT,

   @BirthDate datetime OUTPUT, @ReportsTo int OUTPUT

AS

BEGIN

  SET NOCOUNT ON

  SELECT  @FirstName = FirstName, @LastName = LastName, @BirthDate = BirthDate, @ReportsTo = ReportsTo

  FROM    dbo.Employees

  WHERE   EmployeeID = @EmployeeID

END

 

Private Sub ExecuteOutput()

  Dim EmployeeID As Long

  Dim FirstName As String, LastName As String, BirthDate As Date, ReportsTo As Long

  Dim dalExec As DataAccessLayerForm

  EmployeeID = 8

  Set dalExec = New_DataAccessLayerForm

  With dalExec

   .ObjectType = StoredProcedure

   .Object = "dbo.Get_Employee_Data"

   .InputParameters EmployeeID

   .ExecuteOutputParameters FirstName, LastName, BirthDate, ReportsTo  ' get values from the sp.

  End With

  If dalExec.Status Then

     MsgBox FirstName & " " & LastName & " " & BirthDate & " " & ReportsTo

  Else

    MsgBox dalExec.ErrorMessage

  End If

  Set dalExec = Nothing

End Sub

 

7.16. Get a recordsetclone from the form

ADP+ method RecordsetClone gives an ADO recordset from the dalForm object that is already bind to a form.

 

Show data from RecordsetClone:

 

  Dim rs As ADODB.Recordset

  Set rs = dalForm.RecordsetClone

  rs.MoveFirst

  While Not rs.EOF

    MsgBox rs("LastName")

    rs.MoveNext

  Wend

  rs.Close

  Set rs = Nothing

 

Find data and Bookmark the form:

 

  Dim rs As ADODB.Recordset

  Set rs = dalForm.RecordsetClone

  rs.Find "LastName = 'Peacock'", 0, adSearchForward  ' rs.Find "LastName Null" finding null value.

  If Not rs.BOF And Not rs.EOF Then

     Me.Bookmark = rs.Bookmark

  Else

     MsgBox "Not found"

  End If

  rs.Close

  Set rs = Nothing

 

Filter data with multiple criteria and Bookmark the form:

 

  Dim rs As ADODB.Recordset

  Set rs = dalForm.RecordsetClone

  rs.Filter = "LastName = 'Peacock' and FirstName = 'Margaret'"

  If Not rs.BOF And Not rs.EOF Then

     Me.Bookmark = rs.Bookmark

  Else

     MsgBox "Not found"

  End If

  rs.Close

  Set rs = Nothing

 

Filter data and show data:

 

  Dim rs As ADODB.Recordset

  Set rs = dalForm.RecordsetClone

  rs.Filter = "Title = 'Sales Representative'"

  While Not rs.EOF

    MsgBox rs("LastName")

    rs.MoveNext

  Wend

  rs.Close

  Set rs = Nothing

 

RecordCount:

 

MsgBox dalForm.RecordsetClone.RecordCount

 

Value from a column that is not bound and shown in the form, example that LastName is not a control in the form, I can still access it by using the RecordsetClone:

 

If Not Me.NewRecord Then

   MsgBox dalForm.RecordsetClone("LastName")

Else: Beep

End If

 

7.17. Row validation text

When a table has a row / column constraint defined in SQL Server like this:

 

ALTER TABLE [dbo].[SALES]

  ADD CONSTRAINT [CK_SALES] CHECK ([Amount] <> 0)

GO

 

I could in an adp file see the Constraint expression and type in a Validation Text for the user in case he type a 0 in a Amount control in a form.

In Access I need to programming the validation text in the form like this:

 

Private Sub Form_BeforeUpdate(Cancel As Integer)

  If Me![Amount]) = 0 Then

     MsgBox "Amount cannot be 0.", vbOKOnly, "Error in Amount"

     Cancel = True

  End If

End Sub

 

7.18. OpenArgs and Sum

With Me.OpenArgs you can open a form with a where-criteria like this:

 

DoCmd.OpenForm "Employee_Orders", acNormal, , , , , Me![EmployeeID]

 

The form Employee_Orders is using Me.OpenArgs at InputParameters to the stored procedure parameter:

 

Set dalForm = New_DataAccessLayerForm

With dalForm

 .Language = English

 .ObjectType = StoredProcedure

 .Object = "dbo.Form_Employee_Orders"

 .InputParameters Me.OpenArgs

 .Bind Me

 .RequeryPassThroughQuery "Form_Employee_Orders"  ' for the summary total textbox

End With

 

Together with this stored procedure that make a little statistic:

 

CREATE PROCEDURE [dbo].[Form_Employee_Orders](@EmployeeID int = NULL)

AS

BEGIN

  SET NOCOUNT ON

  SELECT Year = YEAR(OrderDate), Orders = COUNT(*)

  FROM    dbo.Orders

  WHERE   EmployeeID = @EmployeeID OR @EmployeeID IS NULL

  GROUP BY YEAR(OrderDate)

  ORDER BY YEAR(OrderDate)

END

 

Making a sum calculated textbox in a form to show a total sum of rows with Con­trol­­Source formula like this, where IsError is looking for #Error (in danish #Fejl):

 

=Sum([Orders])   or   =IIf(IsError(Sum(![Orders]));0;Sum(![Orders]))

 

is not working in Access together with a ADO recordset.

Therefore I will use DLookup, DCount and DSum like this formula:

 

=DSum("Orders";"Form_Employee_Orders")

 

where "Form_Employee_Orders" is a pass-through query that is using the same o­bject (the stored procedure with parameter from OpenArgs) that dalForm is using, and I only need to tell the dalForm to requery the pass-through query as shown above and the calculated textbox with =DSum will be recalculated automatically.

 

In the Employee form I add a Orders button that will open a popup form with the above programming together with the Total sum:

 

 

The bug was fixed in July 2015 and automatic updated by Windows Update

If I change Orders button property UseTheme to Yes for using Windows theme, the­re is a bug in Access before July 2015 because a control with property Use theme = Yes will be changed to No when I copy/paste a form or import/export a form. Therefore I add few lines of settings and color #code in the Form_Load method:

 

Me![Orders].UseTheme = True

Me![Orders].HoverColor       = RGB(&HDE, &HEB, &HF7) ' #DEEBF7

Me![Orders].PressedColor     = RGB(&HD8, &HD8, &HD8) ' #D8D8D8

Me![Orders].HoverForeColor   = RGB(&H40, &H40, &H40) ' #404040

Me![Orders].PressedForeColor = RGB(&H40, &H40, &H40) ' #404040

 

But I think the old bug in all Access versions is still there, when a form or a report is using a query as RecordSource and I change a lot of the criterias in the query, the form will return no rows, until I in property RecordSource delete the query, put cursor focus at property Caption, and then I go back to RecordSource and select the same query, so the changed query will be hug up with the form and it will work fine.

 

7.19. Sum of subform data and show it in a textbox in the multiform

 

 

"Sum of Quantity" is calculated by calling a stored procedure with a parameter va­lue from the subform "Products for the supplier" hidden column ProductID because ProductID is the foreign key in the table "Order Details" where I want to summary the Quantity column:

 

CREATE PROCEDURE [dbo].[Form_Suppliers_Products_Orders_SumQuantity]

       (@ProductID int, @Quantity int OUTPUT)

AS

BEGIN

  SET NOCOUNT ON

  SELECT  @Quantity = SUM(Quantity)

  FROM    dbo.[Order Details]

  WHERE   ProductID = @ProductID

END

 

In the mainform "Suppliers" I add a new dal object in the Form_Load event:

 

Private dalSuppliers_Products_Orders_SumQuantity As DataAccessLayerForm

Set dalSuppliers_Products_Orders_SumQuantity = New_DataAccessLayerForm

With dalSuppliers_Products_Orders_SumQuantity

 .ObjectType = StoredProcedure

 .Object = "dbo.Form_Suppliers_Products_Orders_SumQuantity"

 .InputParameters [Suppliers_Products_Subform].Form![ProductID]

End With

 

I make a form behind code private function that will get the current parameter va­lue of ProductID by InputParameters and will return the current sum of quantity:

 

Private Function SumQuantity()

  Dim sum As Long

  With dalSuppliers_Products_Orders_SumQuantity

   .InputParameters [Suppliers_Products_Subform].Form![ProductID]

   .ExecuteOutputParameters sum

  End With

  SumQuantity = sum

End Function

 

I add a textbox control with a Control Source formula that will call the function and assign the return value to the textbox and show it in the form to the user:

 

=IIf(Not IsNull([SupplierID]) And Not IsNull([Suppliers_Products_Subform].[Form]![ProductID] And Nz([Suppliers_Products_Orders_Subform].[Form]![OrderID])<>0);SumQuantity();"")

 

I make sure that the formula use controls from the mainform as SupplierID and from both subforms as ProductID and OrderID, because then Access will automatic recalc the textbox when one of the controls change value.

 

7.20. Requery subform and how to keep row position

To update a subform using Me.Refresh or Me.Requery makes the cursor jump back to the first row. Sometimes using a bookmark helps to bring back the cursor to the current row position like this:

 

Dim Bookmark As Variant

Bookmark = Me.Bookmark

Me.Requery

Me.Bookmark = Bookmark

 

Another way is to make a sub procedure in the mainform to do the requery of the subform because that will not change the cursor position of the current row, like this with a public sub, so it can be called from outside the form:

 

Public Sub Form_Subform_Requery()

  Me![<subform>].Requery

End Sub

 

And in the subform where I need the update to be done, I am calling the sub procedure in the mainform using the Parent operator like this:

 

DoCmd.RunCommand acCmdSaveRecord

Parent.Form_Subform_Requery

 

When a subform is using a filter it will make the cursor jump back to the first row, but extra programming can bring back cursor to the original row in the subform Data:

 

Public Sub Form_Subform_Requery()

  Dim SelTop As Integer

  Dim CurrentSectionTop As Integer

  Dim RowsFromTop As Integer

  Dim CurrentControlName As String  ' sometimes needed to keep cursor in same column

  On Error Resume Next

  CurrentControlName = Application.Screen.ActiveControl.Name

  Me![Data].Form.Painting = False

  SelTop = Me![Data].Form.SelTop

  CurrentSectionTop = Me![Data].Form.CurrentSectionTop

  Me![Data].Requery

  If Me![Data].Form.FilterOn = True Then

     If Me![Data].Form.Section(acHeader).Visible = True Then

        RowsFromTop = (CurrentSectionTop - Me![Data].Form.Section(acHeader).Height) /

                                  Me![Data].Form.Section(acDetail).Height

     Else

        RowsFromTop = CurrentSectionTop / Me![Data].Form.Section(acDetail).Height

     End If

     Me![Data].Form.SelTop = Me![Data].Form.RecordsetClone.RecordCount

     Me![Data].Form.SelTop = SelTop – RowsFromTop

     Me![Data].Form.RecordsetClone.AbsolutePosition = Me![Data].Form.CurrentRecord +

                                                                                RowsFromTop - 1

     Me![Data].Form.Bookmark = Me![Data].Form.RecordsetClone.Bookmark

  End If

  DoCmd.GoToControl CurrentControlName

  Me![Data].Form.Painting = True

  On Error GoTo 0

End Sub

 

7.21. Continuous form

When you like a fancy layout you can use continuous form instead of datasheet but Access do not support Arrow Down and Up to move around the records or rows. ADP+ gives you Arrow Down to next record and Arrow Up to previous record in a continuous form and still respect the com­bo­­box (dropdown) where Arrow Down and Arrow Up is working as normally together with F4 for open the combobox.

 

 

In datasheet Access stopped showing the black triangle as record selector but in continuous form it is still there to indicate which record is currently selected or has focus. If you like highlight of the current record in an continuous form look this Tip.

 

In a subform you can drop vertical scrollbar and add this method to make the mou­se wheel scroll up or scroll down among the rows, here with a step of five rows.

For ADP+

Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)

  On Error Resume Next

  If Count > 0 And Me.CurrentRecord < dalForm.RecordsetClone.RecordCount Then

     DoCmd.RunCommand acCmdRecordsGoToNext

     DoCmd.RunCommand acCmdRecordsGoToNext

     DoCmd.RunCommand acCmdRecordsGoToNext

     DoCmd.RunCommand acCmdRecordsGoToNext

     DoCmd.RunCommand acCmdRecordsGoToNext

  ElseIf Count < 0 And Me.CurrentRecord > 1 Then

     DoCmd.RunCommand acCmdRecordsGoToPrevious

     DoCmd.RunCommand acCmdRecordsGoToPrevious

     DoCmd.RunCommand acCmdRecordsGoToPrevious

     DoCmd.RunCommand acCmdRecordsGoToPrevious

     DoCmd.RunCommand acCmdRecordsGoToPrevious

  End If

  On Error GoTo 0

End Sub

For Access

Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)

  On Error Resume Next

  If Count > 0 And Me.CurrentRecord < Me.RecordsetClone.RecordCount Then

     Me.Recordset.MoveNext

     Me.Recordset.MoveNext

     Me.Recordset.MoveNext

     Me.Recordset.MoveNext

     Me.Recordset.MoveNext

  ElseIf Count < 0 And Me.CurrentRecord > 1 Then

     Me.Recordset.MovePrevious

     Me.Recordset.MovePrevious

     Me.Recordset.MovePrevious

     Me.Recordset.MovePrevious

     Me.Recordset.MovePrevious

  End If

  On Error GoTo 0

End Sub

 

7.22. SendKeys

When using the Sendkeys command in Access vba programming it will interferes with the Lock keys as Num Lock, Caps Lock, Scroll Lock and turn them off.

ADP+ has its own version of SendKeys without any change to Lock keys at the keyboard light or at the Access status line, it is called SendKeys_ like this example:

 

DoCmd.GoToControl "FilterTitle"

SendKeys_ "{F4}", True

 

But you can also use an Access command: Me![FilterTitle].Dropdown

 

No need to use SendKeys_ "+{F2}", True for Shift + F2 for showing the zoom dia­log­box, be­cau­se there is an Access command for open the zoom dialogbox when the cursor is into a textbox control in a form, example:

 

DoCmd.GoToControl "LastName"

DoCmd.RunCommand acCmdZoomBox

 

No need to use SendKeys_ "{F2}", True for edit a textbox control, because there is a way for getting edit cursor mode when the textbox control has focus in a form, example:

 

Me![LastName].SetFocus

Me![LastName].SelStart = Len(Me![LastName] & "")

Me![LastName].SelLength = 0

 

Or use an ADP+ functionality like this: dalForm.EditCursor Me![LastName]

 

In a mainform set focus to a column in a subform like this:

 

On Error Resume Next

Me![Suppliers_Products_Subform].SetFocus

Me![Suppliers_Products_Subform].Form![CategoryID].SetFocus

On Error GoTo 0

 

On Error is needed in case of no row in the subform if adding a row is not allowed.

 

When cursor has focus in a subform the GoToControl is not working anymore to go to mainform control like SupplierID as this DoCmd.GoToControl "SupplierID"

It is better to use Me![SupplierID].SetFocus

 

Parent![SupplierID].SetFocus is not working because Parent is only wor­king inside a subform as a reference to the mainform in a multiform. When a mainform has a subform and that subform has another subform, the mainform becomes the grand-parent of the last subform and that subform can refer to a control in the mainform: Parent.Parent![SupplierId]

If the first subform has a value in property Tag, the last subform refering: Parent.Parent![<subform name in mainform>].Tag

 

7.23. Contextual tab in Ribbon bar

In a multiform with subform as datasheet and the cursor is placed in the subform for type-in new data or update data, the Access Form Datasheet contextual tab will been shown in the Ribbon bar as an extra colorful tab wih some options that users normally don’t want to use. Here is an example from a danish version of Access where the danish word dataark is datasheet. I find the this form datasheet con­tex­tual tab annoying and I wish there was a form design property to switch off but the only way is to make a new ribbon bar that inherit all ribbons from Ac­cess and then do the switch off.

To make a new ribbon bar I have to create a new table in the accdb file with three columns:

·     RibbonId as Autonumber and set as primary key

·     RibbonName as Text

·     RibbonXml as Memo

and save the new table by the name UsysRibbons.

It is a Access system table and is hidden in navigation pane but rightclick at navigation pane title like "All Ac­cess objects" and choose {Navigation Options} and set a checkmark at "Show sy­stem objects" and UsysRibbons will be shown.

I open UsysRibbons and type in a new row with a RibbonName called ADP+ and this XML text in column RibbonXml:

 

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

   <ribbon startFromScratch="false">

      <contextualTabs>

         <tabSet idMso="TabSetFormDatasheet" getVisible="RibbonGetVisible" />

      </contextualTabs>

   </ribbon>

</customUI>

 

 

TabSetFormDatasheet refer to the Form Datasheet contextual tab and Ribbon­GetVisible refer to a sub procedure I will make in a new module called myRibbon where I can change False to True in case I want the contextual tab back again:

 

Public Sub RibbonGetVisible(control As IRibbonControl, ByRef returnedVal)

  returnedVal = False ' False = hide the Contextual Tab of Datasheet

End Sub            ' Requires reference to Microsoft Office 15.0 Object Library

 

The last thing I have to do is goto File, Access Options, Current Database and in Ribbon name choose the new ribbon bar called ADP+. Then I exit Access, I start Access again and I open the Suppliers form and I set the cursor into a subform and the Form Datasheet contextual tab is hidden, not shown, disabled or gone.

 

In case you want to hide other tabs and groups at home tab the RibbonXml could be like this:

 

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

   <ribbon startFromScratch="false">

      <contextualTabs>

         <tabSet idMso="TabSetFormDatasheet" getVisible="RibbonGetVisible" />

      </contextualTabs>

      <tabs>

         <tab idMso="TabHomeAccess">

            <group idMso="GroupTextFormatting" getVisible="RibbonGetVisible" />

            <group idMso="GroupSortAndFilter" getVisible="RibbonGetVisible" />

            <group idMso="GroupWindowAccess" getVisible="RibbonGetVisible" />

            <group idMso="GroupViews" getVisible="RibbonGetVisible" />

            <group idMso="GroupRecords" getVisible="RibbonGetVisible" />

         </tab>

         <tab idMso="TabCreate" getVisible="RibbonGetVisible" />

         <tab idMso="TabExternalData" getVisible="RibbonGetVisible" />

         <tab idMso="TabDatabaseTools" getVisible="RibbonGetVisible" />

      </tabs>

   </ribbon>

</customUI>

 

In case you want to hide all tabs and all buttons in File:

 

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

   <commands>

      <command idMso="Help" enabled="false"/>

      <command idMso="WindowClose" enabled="false"/>

      <command idMso="ApplicationOptionsDialog" enabled="false"/>

      <command idMso="FileExit" enabled="false"/>

   </commands>

   <ribbon startFromScratch="true">

      <contextualTabs>

         <tabSet idMso="TabSetFormDatasheet" getVisible="RibbonGetVisible" />

      </contextualTabs>

      <tabs>

        <tab idMso="TabHomeAccess" visible="false"/>

        <tab idMso="TabCreate" visible="false"/>

        <tab idMso="TabExternalData" visible="false"/>

        <tab idMso="TabDatabaseTools" visible="false"/>

        <tab idMso="TabSourceControl" visible="false"/>

        <tab idMso="TabAddIns" visible="false"/>

        <tab idMso="TabPrintPreviewAccess" visible="false"/>

        <tab id="PrintPreview" visible="true"/>

      </tabs>

   </ribbon>

   <backstage>

      <button idMso="FileSave" visible="false"/>

      <button idMso="SaveObjectAs" visible="false"/>

      <button idMso="FileSaveAsCurrentFileFormat" visible="false"/>

      <button idMso="FileOpen" visible="false"/>

      <button idMso="FileCloseDatabase" visible="false"/>

      <button idMso="ApplicationOptionsDialog" visible="false"/>

      <button idMso="FileExit" visible="false"/>

      <tab idMso="TabInfo" visible="false"/> 

      <tab idMso="TabOfficeStart" visible="false"/>

      <tab idMso="TabRecent" visible="false"/>

      <tab idMso="TabSave" visible="false"/>

      <tab idMso="TabNew" visible="false"/>

      <tab idMso="TabPrint" visible="false"/>

      <tab idMso="TabShare" visible="false"/>

      <tab idMso="TabHelp" visible="false"/>

   </backstage>

</customUI>

 

7.24. Ribbon bar menu

I have in NorthwindApp.accdb made a ribbon bar menu to a form with the functions that ADP+ is supporting like filter and sort with an easy click navigation of records:

 

 

The above is made in a danish version af Access and in Denmark the last letter of the alphabet is Ĺ compared to Z in english. In the RibbonXml property Label, I have made an english and german translation of the buttons text display in the ribbon bar. Two other properties for tool tips are Screentip and Supertip.

 

7.25. Non english version of Access – Form become Formular

I have noticed that Me and Parent works fine in danish version of Access, but use of Forms and Form in a textbox control source gives #Name? (#Navn? in danish).

In english version I can make textbox Control Source formula or Default Value property like this formula:

 

=[Forms]![Employee]![Title]

=[Forms]![Suppliers]![Suppliers_Products_Subform].[Form]![ProductName]

=[Suppliers_Products_Subform].[Form]![ProductName]

 

In danish version I have to use danish words instead of Forms and Form, that is Formularer and Formular like this formula:

 

=[Formularer]![Employee]![Title]

=[Formularer]![Suppliers]![Suppliers_Products_Subform].[Formular]![ProductName]

=[Suppliers_Products_Subform].[Formular]![ProductName]

 

When cursor leaves the Control Source property, the danish Access version au­to­matic change danish Formular to english Form. Later if I copy-paste the formula to another textbox, I get an error like #Navn?. Therefore I need after the copy-paste to rewrite Forms to Formularer and Form to Formular, leave the property and see it switch back to the english.

This formula =[Parent]![SupplierID] works also in danish version.

 

Function DLookup in textbox Control Source formula is also using danish Formularer for the english Forms, like this formula:

 

=DLookUp("[RegionName]";"REGION";"[RegionId]='" & [Formularer]![Menu]![RegionId] & "'")

 

When control is inside the form, no need for form reference like this formula:

 

=IIf(![Id] Is Null;"";DLookUp("[Name]";"Customer";"[Id]=" & ![Id]))

 

=DCount("*";"REGION";"[RegionId]=" & ![RegionId])

 

Parent has not a danish word, so like in a subreport of a multireport a sum textbox:

 

=DSum("[Quantity]";"Report_Production";"[Projektno]=" & [Parent]![Projektno] &

" AND [Department]=" & [Parent]![Department] & " AND [Team]='" & [Parent]![Team] & "'")

 

Inside a query when you want the query to be updatable:

 

NumberOfRegion: DCount("*";"REGION";"[RegionId]=" & [REGION].[RegionId])

 

In danish Access a weekday name format for a textbox:

=WeekdayName(Weekday(Date();2))+" den "+Format(Date();"d. mmmm yyyy")

become instead because there is a danish function for weekday name:

=NavnPĺUgedag(Weekday(Date();2))+" den "+Format(Date();"d"". ""mmmm yyyy")

When you get a #Navn #Name it could be something with a function or like [Date].

In VBA the danish function NavnPĺUgedag is not working.

More danish date function

 

In a textbox: =IIf(IsNull([SumMarketValue]);"Sold";[SumMarketValue])

With format in the formula:

Format([SumMarketValue], "#,##0.00")

Format([SumMarketValue], "#.##0,00")

Format([SumMarketValue], "Standard")

Format([SumMarketValue], "Currency")

In a query with new column SumMarketValue2: MarketValue as a Sum of records,

SumMarketValue in case of Null value and make the format instead of in the form:

IIf(IsNull([SumMarketValue2]);"Sold";Format([SumMarketValue2];"Standard"))

 

Eval function in Access to execute an expression like this:

 

Dim State As Boolean

State = Eval("[Forms]![Orders]![ShipRegion] IN ('AK', 'CA', 'ID', 'WA', 'MT', 'NM', 'OR')")

 

8. Combobox and listbox with data from Pass-through query

Pass-through query returns a non updatable and read-only dataset and therefore can be used as Row Source to combobox and listbox instead of ADO recordset. ADP+ provides this feature from requests of users and I will show a little example:

 

 

I have made two stored procedures that I will make to pass-through queries in module myDataAccessLayerFactory, where List_Suppliers shows all suppliers and List_Products has a supplier as parameter value for showing products connected to that supplier, and I use a dummy value 18:

 

DataAccessLayerConnectionFactory.CreatePassThroughQuery "List_Suppliers", "EXEC dbo.List_Suppliers"

DataAccessLayerConnectionFactory.CreatePassThroughQuery "List_Products", "EXEC dbo.List_Products 18"

 

I run the code and the two pass-through queries is made with the connectionstring from the file DataAccessLayerConnection.sql second line. I open the two news pass-through queries for testing them.

 

I design a form with a combobox for Supplier that will use List_Suppliers as Row Source and a listbox Products that will use List_Products as Row Source. When combobox has select a value, it has to requery listbox with the right supplier parameter value, therefore I do a ADP+ programming behind the form so after update Supplier combobox it will requery Products listbox:

 

Private dalProducts As DataAccessLayerComboListbox

 

Private Sub Form_Load()

  Set dalProducts = New_DataAccessLayerComboListbox

  With dalProducts ' Pass-through query List_Products is selected to the listbox Row Source property.

   .Language = English

   .ObjectType = StoredProcedure

   .Object = "dbo.List_Products"

   .InputParameters Me![Supplier]

   .Bind Me![Products]

  End With

End Sub

 

Private Sub Supplier_AfterUpdate()

  With dalProducts

   .InputParameters Me![Supplier]  ' gets the current value of the combobox of Supplier.

   .Requery

  End With

End Sub

 

9. Migration of an ADP to an Access front-end application

From Access 2013 it can not open an adp file therefore you need your older Access version (2000-2010) where you made the adp file, to convert the adp file either to a mdb file (2000-2003) or to an accdb file (2007-2010) like this:

 

ADP in Access 2000-2003:

  1. Make a new mdb file with the same name as your adp file.
  2. Import forms, reports, macros and modules from the adp file to your new mdb file.
  3. Exit Access.
  4. Start Access 2013 or 2016, open the mdb file and select »File« and »Save as« the mdb file to an accdb file and exit Access.
  5. Make a new DataAccessLayerConnection.sql file in Notepad and save it in same folder as your new accdb file. Make sure the connectionstring fit to your SQL Server version, see above section 3.3.
  6. Start Access 2013 or 2016 and open the accdb file and start to add ADP+ to the accdb file by reference to the ADPX.accde file as described in section 3.2.

 

ADP in Access 2007-2010:

  1. Make a new accdb file with the same name as your adp file.
  2. Go to ribbon bar External data and click button Import Access database.
  3. Find and select your adp file.
  4. Import forms, reports, macros and modules from the adp file to your new accdb file.
  5. Exit Access.
  6. Make a new DataAccessLayerConnection.sql file in Notepad and save it in same folder as your new accdb file. Make sure the connectionstring fit to your SQL Server version, see above section 3.3.
  7. Start Access 2013 or 2016 and open the accdb file and start to add ADP+ to the accdb file by reference to the ADPX.accde file as described in section 3.2.

 

I will loose the settings in form and report properties Record Source, Input Parameters, Uni­que Table and Resync­ Com­mand. It is best to call a stored procedure instead of adding a sql statement to the dal object in the code behind your form and report.

 

Before I do the import, I have tried to built in ADP+ in an existing adp file, so I have access to the settings in forms and report and can use them in the ADP+ properties and methods to the dal objects. But I am not able to run everything in an adp file by using ADP+ because there is no pass-through query for report, and a form can not call a stored procedure so I need a work around in the adp file to make it run and shown data in a form:

 

Set dalForm = New_DataAccessLayerForm

With dalForm

 .Language = English

 ' Work around for adp file

 .ObjectType = SQL

 .Object = "EXEC dbo.[Orders at stock] " & Forms![Menu]![Year] & ", '" & Forms![Menu]![Region] & "'"

 .InputParameters

 ' Normal ADP+ for in an accdb file

 '.ObjectType = StoredProcedure

 '.Object = "dbo.[Orders at stock]"

 '.InputParameters Forms![Menu]![Year], Forms![Menu]![Region]

 .Bind Me

End With

 

For combobox (dropdown) and listbox a stored procedure with parameter is wor­king in the normal ADP+ way in an adp file and they are easy to convert from property Row Source to ADP+ way and empty the Row Source of the combobox and listbox. In case of programmering the data rows for a combobox Type in a subform of a multiform via a stored procedure with parameters in adp file, I have to replace Me![Type].RowSource with the ADP+ dal object like this example:

 

In an adp file:

 

Private Sub Type_Enter()

  Me![Type].RowSource = "EXEC dbo.List_Type(" & Parent![Year] & ", '" & Me![Code] & "')"

End Sub

 

In an accdb file with ADP+:

 

Private Sub Type_Enter()

  With dalType

   .InputParameters Parent![Year], Me![Code] ' gets the current values of the other controls.

   .Requery

  End With

End Sub

 

And in Form_Load is the declaring already made for the combobox in ADP+ way:

 

Private dalType As DataAccessLayerForm

Set dalType = New_DataAccessLayerForm

With dalType

 .Language = English

 .ObjectType = StoredProcedure

 .Object = "dbo.List_Type"

 .InputParameters Parent![Year], Me![Code]

 .Bind Me![Type]

End With

 

10. ADP+ in Object Browser

A complete list of classes and properties and methods in ADP+ framework:

 

In Access 2013 is the reference version 15.0 and in Access 2016 it is 16.0.

 

ADP+ messagebox are in four languages: Danish, English, German and Italian. If you like your own language to be included in ADP+ please feel free to contact me.

 

11. About me

I have been working with Microsoft Access since I got version 1.0 at February 4, 1993. Before that I worked with Borland Paradox for Dos and Access multiform with subform was a true copy from Paradox for Dos, but Paradox for Windows en­cap­su­lated its subforms inside the mainform. Access never changed multiform design.

 

I have developed ADP applications since Access 2000 and I recommend this article from Microsoft for learning more about ADP:

Developing Client/Server Solutions with Microsoft Access Data Project

 

Create an Access project in Access 2010

»This form or report contains changes that are incompatible with the current database format.« You must avoid using any form wizards or any layouts when designing a form in an ADP using Access 2010. Design only in Design view, not Layout view. Access 2010 creates some hidden controls (aptly named »empty cell controls«) when you do this that can't be saved in 2002-2003 format that adp file is using in Access 2007 and Access 2010. Remember you can under File, Access Options and Current database uncheck 'E­na­ble Layout View for this database' and after this you will never be able to choose Layout view for form or report.

Microsoft Access Database Engine 2010 Redistributable 32 bit same as your Access.

 

In case you like to use linked table I recommend this article from Microsoft:

Optimizing Access application linked to a SQL Server

 

Pass-through query was in Access 1.0 to SQL Server 1.1 (U­ser’s guide page 660) for calling stored procedures. With Access 97 I have used pass-through query to access SQL Server 7.0 database, and from Access 2000 I only used ADP.

 

A lot of Access VBA programming  and more.

 

I will never understand why Microsoft had to stop supporting Access Data Project to access a SQL Server database for tables, views, stored pro­ce­dures and user defined functions in Record Source and Row Source together with Link­Master­Fields and Link­Child­Fields. For sure I have no need to imple­ment a SQL Ser­ver database, tables or stored procedures through Access UI because I am using SQL Ser­ver Enterprise Manager or Management Studio for development and test independently of the front-end application. A minor and annoying feature of Access ADP is when I edit the Record Source property Access clears the Input Parameters property therefore I always keep a copy of Input Parameters property in the Tag property :)

 

I like all my sql statements inside the back-end database and not in the front-end application file, so I will never be a fan of LINQ with sql statements to a database, ok maybe LINQ in an application to access a special datastructure so I can query it in a sql way.

 

This Microsoft explanation make me laugh: »Issue that this hotfix package fixes: You may be unable to calculate some fields in Access 2010 if the computer has not been restarted for a long time (24 days, for example).« More.

 

SQL Server Migration Assistant for Access (AccessToSQL)

Microsoft SQL Server Migration Assistant v5.3 for Access

Access to SQL Server Migration: How to Use SSMA

Last link installation gives to connect to MS Access database using this driver: "Microsoft Office 12.0 Access Database Engine OLE DB Provider" instead of "Micro­soft Office 15.0 Access Database Engine OLE DB Provider"

 

You can instead mark a Access table, select External Data, Export, More, ODBC Database. When you have an accdb database file, create first a new database in "SQL Server 2014 Management Studio", then start your Access and select File and Save As to a mdb file, start "SQL Server 2014 Import and Export Data (32-bit)" and select the mdb file and connect to your sql server and to the new database. Select Access ta­bles and maybe do some edit mappings, mostly data types is converted with not null or null, but after the package has been running you need to make primary key and index and foreign key in the diagram in your sql server database.

 

If you get this message »Expression too complex« do a Compact and repair.

If you get error message when you start your application, your VBA code need to be reset by making a shortcut at Windows desktop like this:

 

"C:\Program Files\Microsoft Office 15\root\office15\MSACCESS.EXE" /decompile "C:\ProgramData\Northwind\NorthwindApp.accdb"

 

Do a Compact and repair, Compile your VBA code and again Compact and repair.

 

Three ways to set cursor in first row:

 

DoCmd.GoToRecord , , acFirst

DoCmd.RunCommand acCmdRecordsGoToFirst

Me.Recordset.MoveFirst

 

Access DDL to reset an Autonumber (Identity/Sequence) column in the linked data­base called Dat.accdb and placed in same folder as the application App.accdb file:

 

Dim db As Database

Set db = OpenDatabase(CurrentProject.Path & "\Dat.accdb")

db.Execute "ALTER TABLE CUSTOMER ALTER COLUMN CustomerId COUNTER(1,1)"

db.Close

 

How to fetch the new generated autonumber and go to the record with the new id:

 

Public Function Retrieve_New_Autonumber(sql As String) As Long

  Dim db As DAO.Database

  Dim rs As DAO.Recordset

  Dim id As Long

  Set db = CurrentDb

  db.Execute sql

  Set rs = db.OpenRecordset("SELECT @@IDENTITY")

  id = rs.Fields(0) ' Debug.Print rs.Fields(0) ' see med Ctrl + G or show MsgBox rs.Fields(0)

  rs.Close

  Set rs = Nothing

  db.Close

  Set db = Nothing

  Retrieve_New_Autonumber = id

End Sub

Private Sub Do_Retrieve_New_Autonumber()

  Dim sql As String

  Dim CreatedDate As Date

  Dim CustomerId As Long

  CreatedDate = Format(Now(), "mm/dd/yyyy hh:mm:ss")

  sql = "INSERT INTO CUSTOMER (CreatedDate, CustomerName) " + _

          "VALUES(#" + CStr(CreatedDate) + "#, 'New Customer Name')"

  CustomerId = Retrieve_New_Autonumber(sql)

  Me.Requery

  Me.Recordset.FindFirst "CustomerId = " + CStr(CustomerId)

  Me![CustomerName].SetFocus

End Sub

 

A SQL Delete statement is not working because it is join on a non-primary column:

 

DELETE T1.*

FROM T1 INNER JOIN T2 ON T2.Name = T1.Name

This works:

DELETE DISTINCTROW T1.*

FROM T1 INNER JOIN T2 ON T2.Name = T1.Name

I prefer:

DELETE T1.*

FROM T1

WHERE EXISTS(SELECT 1 FROM T2 WHERE T2.Name = T1.Name)

 

Page header and footer for page 1 in a report is hidden by report property Page Header changed from All Pages to Not with Rpt Hdr/Ftr.

 

Back in 1998, I put an Access front-end application with a SQL Server database at internet through Citrix software for multiple users and its really works great. Windows Terminal Server or Remote Desktop Protocol RDP works well on both IOS and Android, Microsoft provide an RDP client. Only issue I had is users not understan­ding the difference between Mouse mode and Touch mode. Make sure your front end interface works for the Touch mode and buttons etc. are big enough. Here is another approach to put your Access application into the cloud Accesshosting.

 

Access without ADP has really make me reuse old techniques and working with ADO recordset binding to a form and simulate LinkMasterFields and LinkChildFields pro­per­ti­es and give Delete key press an Yes/No messagebox. In 1990 I developed a macro to WordPerfect 5.1 for making it easy to merge data from a Paradox database to a document like a letter to customers. In 1992 I made extra functionality in Paradox Utilities and in 1994 I made an Access 2.0 Utilities, look here for more information but it is in danish:

 

Access Utilities   WordPerfect and Paradox Utilities.htm

 

Please visit my homepage for the english part:

 

JoakimDalby.dk

 

Purchase price of an ADP+ license is perpetual for unlimited time and applications in your company and for unlimited number of users in your organization is US dollar $199 or euro €185 in­clu­ding 25% danish vat.

 

A free trial version is available for download here ADPX.zip

 

© Copyright of ADP+ belongs to Joakim Dalby, Denmark.

 

Any comments and suggestions are welcome