Access and SQL Server ADP+ Access Data Project

How Access and SQL Server can work together

How to convert adp to accdb by reuse stored procedures and views

Access front end SQL Server back end - 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, 2019, Office 365 and Microsoft 365 to a SQL Server

ADP+ is a Data Access Layer for updatable form and subform and for report and subreport to have a client-server rapid application development framework in Access 2013, Access 2016, Access 2019, Office 365 and Microsoft 365 connected to a SQL Server database 7.0, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019 and SQL Server 2022 on your network or SQL Server Express on your PC or Azure SQL Server in the cloud over the internet.

ADP+ is an object-oriented framework in VBA programming for an Access front-end application 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 Server database that handle all data, query and business logic inside the database. ADP+ also applies Table, View, Table-valued function, Scalar function and SQL statement.

With ADP+ your Access become an Integrated Development Environment (IDE).

An example of using ADP+ in VBA behind an updatable form that is fetching data from a stored procedure that has a join between tables therefore the ADP properties InputParameters, 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 messagebox and insert a new row that will be stored automatically in the SQL Server database table, where variable object has name dalForm for referring to data access layer for a form instantiated by class DataAccessLayerForm.

A form for Employee has a dropdown box for filter employees after their title that is a parameter to the stored procedure by property InputParameters:

Private dalForm As DataAccessLayerForm

Private Sub Form_Load()

Set dalForm = New_DataAccessLayerForm

With dalForm

.Language = English

.ObjectType = StoredProcedure

.Object = "dbo.Form_Employee"

.InputParameters Me![FilterTitle]

.Bind Me

End With

End Sub

A form for Supplier has a dropdown box for move to the row for a supplier or to show all suppliers, and stored procedure has a join to another table therefore property UniqueTable and ResyncCommand makes sure that form shows the right row after a user has updated data in the row:

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 an Access report where the criteria is giving from an open form and a stored procedure provides data rows to the report to be shown in screen and printed out on paper or saved to a pdf file, where variable object has name dalReport for referring to data access layer for a report instantiated by class DataAccessLayerReport:

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 parameters and return value or a recordset and to execute an insert, update, delete.

An example of using ADP+ in VBA to call a stored procedure with input and output parameters in a sub procedure:

Private Sub ExecuteStoredProcedureWithOutput()

Dim EmployeeID As Long

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

Dim dalExec As DataAccessLayerForm

EmployeeID = 8 'a search criteria value for the stored procedure.

Set dalExec = New_DataAccessLayerForm

With dalExec

.ObjectType = StoredProcedure

.Object = "dbo.Get_Employee_Data"

.InputParameters EmployeeID

.ExecuteOutputParameters FirstName, LastName, BirthDate

End With

If dalExec.Status Then

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

Else

MsgBox dalExec.ErrorMessage

End If

Set dalExec = Nothing

End Sub

An example of using ADP+ in VBA to send a SQL statement to SQL Server database and get data rows back in a recordset and show the data in a messagebox:

Private Sub ShowDataFromSQLstatement()

Dim rs As ADODB.Recordset

Dim dalRS As DataAccessLayerForm

Dim list As String

Set dalRS = New_DataAccessLayerForm

With dalRS

.ObjectType = SQL

.Object = "SELECT * FROM dbo.Employees ORDER BY LastName, FirstName"

.InputParameters

Set rs = .Recordset

End With

If dalRS.Status Then

list = ""

While Not rs.EOF

list = list + rs("LastName") + vbTab + rs("FirstName") + vbNewLine

rs.MoveNext

Wend

MsgBox list

rs.Close

Else

MsgBox dalRS.ErrorMessage

End If

Set rs = Nothing

Set dalRS = Nothing

End Sub

A screenshot from a Access danish language application in Denmark using ADP+ to show and update data from a SQL Server database in a form with datasheets and dropdown boxes. 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 and for 64 bit platform of Access for Windows 32 bit or Windows 64 bit. Microsoft 365 is 64 bit by default.

For Access 2013 Microsoft said: 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, checkbox, optiongroup, listbox or combobox (dropdown). A table contains columns also known as attributes 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 ! bang operator to refer a control e.g. a textbox (field, column). It is okay to use same name in ControlSource Property and Name Property. When I’m using the . dot operator in an object-oriented way to refer to a control like a textbox, I will use two different names for ControlSource as CustomerName and Name as txtCustomerName to avoid trouble. Parent operator is used in subform or subreport to refer to controls in the mainform or the mainreport like the primary key as a LinkMasterField. 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 Recalc method updates calculations and conditional formatting. The Repaint method simply repaints the specified form and its controls. Read more about bang versus dot in forms

Concatenation string operator I do either an ampersand (&) or an addition (+).

I have seen some developers using Forms(0) instead of Me.

From outside a form I am using like this to go through form and subforms:

Forms![Suppliers].SetFocus

Forms![Suppliers].Form![Products_Subform].SetFocus

Forms![Suppliers].Form![Products_Subform].Form![Categories_Subform].SetFocus

Microsoft has stopped supporting the Access Data Project adp file in an Access desktop database and deprecated ADP with this message when I open an adp file in Access 2013: »Access 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. Access 2010 is the last version supporting ADP.

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

With ADP+ it is easy after a migration from accdb database to SQL Server database to connect Access forms and reports to tables, views and stored procedures in the database.

Read more about creating and using CRUD stored procedures.

Office 365 is being renamed to Microsoft 365.

Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise.

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 database application in fileformat accdb, but there is also some disadvantages and defects 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 database server don’t send all records to the client, else there can be a performance issue. Every linked table must have 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 LinkChildFields properties are working 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 Connectivity 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={ODBC Driver 18 for SQL Server};

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

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

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

3. Pass-through query is non updatable and read-only snapshot to SQL Server 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.«
4. ActiveX Data Objects ADO recordset (Dim rs As ADODB.Recordset) is updatable to SQL Server tables, views 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 recordset (not recordsource or rowsource). LinkMasterFields and LinkChildFields properties are not working in a multiform. Delete a record gives an Yes/No messagebox but the record is already deleted in the SQL Server database before I click No, so deleting records 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 sorting, 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 recordset can’t get the default value from a column, therefore the default value 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 stored procedures in an adp file, model A is like to downsizing select statements 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 procedure 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.

Making a pass-through query connected to a SQL Server from an Access database: Create a query, add no table, right click in query window and select SQL-specific and Pass-through. In property sheet at ODBC connctionstring write:

ODBC;Driver={ODBC Driver 18 for SQL Server};Server=<name>;Database=Northwind;Trusted_Connection=Yes

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

In query window write: SELECT * FROM dbo.Employees

Under Design tab click Run and you have fetch data from your SQL server if your Windows authentication PC login account has been made inside the SQL Server or through an AD group.

Advices for your table structure in a SQL Server database (more reading)

• Table has a primary key.
• Table has a bit column let it has a devault value 0

[Discontinued] [bit] NOT NULL CONSTRAINT [DF_Products_Discontinued] DEFAULT ((0)),

• Table has an extra column with data type Rowversion before called Timestamp, but it never had anything to do with »time of day«, because it is a unique value stamped into the column whenever the row is changed. ODBC driver uses the Rowversion value to quickly determine whether someone else has changed the row, otherwise it must look at all the column values, which is slower and prone to mistakenly determining that the row has changed, when it hasn't (e.g. with null bit column and floating point numbers.) It is only for SQL Server and ODBC driver to see before updating a row, if this has changed on the server since the last reading by the driver. If it hasn't, the update is safe. If it has, the row is re-read and the update is cancelled. Rowversion saves ODBC from having to examine each column individually when doing updates to see if someone else has changed a column in the row. The Rowversion column does not need to be included in an Access query, form, or report. The only downside, and it's an edge case, is that Rowversion eliminates the possibility of partial row concurrency, that is, the ability to update columns A and B while another process is updating columns C and D. This is so rare that I don't even consider it unless there's a clear need for it. Example of a bug fix with rowversion

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 functionality that is not working in Access. ADP+ is using ADO to a SQL Server 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 parameter 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 Server database table, view or stored procedure with parameters for data criteria by using Input Parameters.
• 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 technique to simulate LinkMasterFields and LinkChildFields properties so a subform can use stored procedure to receive the records that belong to the current record in the mainform or the current row in a datasheet.
• Support updatable the many-table in a many-to-one join by using Unique Table and Resync Command.
• Support using BigInt datatype as a primary key.
• Delete a record in a form or delete several selected rows in a datasheet with an Yes/No messagebox and first after the user has confirmed with an Yes, the record 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 record 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 datasheet 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 selected record in a form, duplicate many selected records in a subform to another record in the mainform or copy many rows from a Excel spreadsheet to a datasheet 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 stored procedure with parameters for data criteria.
• Multireport is using a technique to simulate LinkMasterFields and LinkChildFields 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. Linked table and linked view with primary key is easy to refresh with ADP+ when connection string is changed from a developer server to a test server and later to a production server.

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 placed in a new folder called ADPX in the ProgramData folder e.g. C:\ProgramData\ADPX and reused by multiple Access front-end application 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. How to make a folder as a Trusted_Location

The folder ProgramData is normally hidden in File Explorer but at 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 myDataAccessLayerFactory 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 {References} 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 user 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 properly installed.

Download the latest ODBC driver 18 for SQL Server:

https://aka.ms/downloadmsodbcsql

Download the latest OLE DB driver called Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)

https://aka.ms/downloadmsoledbsql

Read introductory text about:

Microsoft OLE DB Driver for SQL Server

Install the bitness flavor that matches Windows, not VBA. In most cases that is the 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 connectionstrings 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 by <name> to a Northwind database is like this:

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

ODBC;Driver={ODBC Driver 18 for SQL Server};Server=<name>;Database=Northwind;Trusted_Connection=Yes

Driver history for Microsoft SQL Server

More readings and link for your information

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 Connectivity' or download SQLNCLI11 - Native OLE DB\SQL Server Native Client 11.0 – 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.

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

Portnumber 1433 is default but sometimes you need to add it, read more.

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}

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

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

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;Trusted_Connection=Yes

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 Microsoft OLE DB Provider for SQL Server (SQLOLEDB) is part of the Windows OS since Windows XP:

Provider=SQLOLEDB;Data Source=<server>;Initial Catalog=Northwind;Integrated Security=SSPI

ODBC;Driver=SQL Server;Data Source=<name>;Initial Catalog=Northwind;Integrated Security=SSPI

SQL Server 2016 and 2017 is using:

Microsoft OLE DB Driver for SQL Server and Microsoft ODBC Driver for SQL Server.

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

In 2018 Microsoft released a new driver for Microsoft OLE DB Driver for SQL Server and OLE DB Driver for SQL Server and Released: Microsoft OLE DB Driver for SQL Server and Installing OLE DB Driver for SQL Server.

Download MSOLEDBSQL directly from: Microsoft OLE DB Driver for SQL Server.

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

SQL Server 2019 and 2022 is using:

Microsoft OLE DB Driver for SQL Server and Microsoft ODBC Driver for SQL Server.

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

ODBC;Driver={ODBC Driver 18 for SQL Server};Server=<name>;Database=Northwind;Trusted_Connection=Yes

SQL Server Express LocalDB at your PC is using this server name:

Server=(localdb)\mssqllocaldb

Provider and ODBC Driver depends of the version of SQL Server Express read more.

From your SSMS Management Studio you can connect to: (localdb)\mssqllocaldb.

Azure Active Directory authentication methods directly into the driver embedded Active Directory Authentication Library (ADAL). With the latest release of the OLE DB driver Access developers can now implement Azure multi factor security into their applications, previously the latest version of SQL Server ODBDC was the only supporting the technology, allowing them to run code in VBA that is compatible:

Microsoft OLE DB Driver for SQL Server and Microsoft ODBC Driver for SQL Server.

When you are running 32-bit Access on 64-bit Windows, you install 64-bit drivers and it will include the 32-bit components needed for the 32-bit Access to run with.

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

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

No need for using DataTypeCompatibility=80 but read more

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

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

Access 32 bit is using ODBC 32 at Windows er 64 bit, 32 bit same as x86.

1. Downloading Visual C++ Redistributable for Visual Studio 2015

https://www.microsoft.com/en-us/download/confirmation.aspx?id=48145

Download: vc_redist.x86.exe

Run: vc_redist.x86.exe

2. Downloading mySQL ODBC Connector

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

Click Download: Windows (x86, 32-bit), MSI Installer.

Run: mysql-connector-odbc-8.0.14-win32.msi

3. Setting up ODBC 32 bit

Help: https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-dsn-windows-5-2.html

4. In Windows menu select System and search for ODBC select ODBC data sources 32 bit.

5. Select System DSN. Click Add and select MySQL ODBC 8.0 Unicode Driver.

6. Data Source Name: <name of the connection>. TCP/IP server: <name of my sql server>.

7. In an Access query set property ODBC: ODBC;DSN=<name of the connection>;

8. Direct to a table: ODBC;DSN=<name of the connection>;;TABLE=Customer

Many connectionstrings in this link and Microsoft ODBC Driver 17 for SQL Server.

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 connection 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 DataAccessLayerConnectionFile 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 DataAccessLayerConnectionString 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

This is handy when you are using a SQL Server login Uid and password Pwd and do not like to save the credentials inside a Pass-through query. This technique is called cached connection:

Public Function DataAccessLayerConnectionString() As String

DataAccessLayerConnectionString = "Provider=SQLNCLI11;Server=<name>;Database=Northwind;Uid=<user>;Pwd=<pwd>" + "¤" + _

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

End Function

A pass-through query property ODBC Connect Str do not include Uid and Pwd in the connectionstring. Via VBA code we make an ODBC connection including Uid and Pwd and it will be cached in memory and it will be reused for pass-through query, linked table and linked view. NorthwindApp.accdb includes an example in sub MakeCachedConnection, but it is not a part of ADP+. Read more at these two links:

Cached connection and Using DSN-Less Connections.

3.4. ADP+ is using ADO 6.1 Library

ADPX.accde is using Microsoft ActiveX Data Objects 6.1 Library for Windows 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-2019/365 (16) is replacing 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 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"

Access 365

"C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE"

"C:\ProgramData\Northwind\NorthwindApp.accdb"

NorthwindApp.laccdb is the lock file while you using NorthwindApp.accdb, and when you are using a native Access database Dat.accdb file it gives also a Dat.laccdb.

NorthwindApp.accdr is a copy of the NorthwindApp.accdb and copy is renamed to accdr for simulate running 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 AccessRuntime_x86_en-us.exe and the language your users prefer.

NorthwindApp.accde is a save as NorthwindApp.accdb for take away the possibility for users to change design of form and report, code behind form or report, query and module, and an accde is compiled to give a faster performance. In old days a mde file. I deploy and distribute an Access desktop application 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 Microsoft Access Template file.

3.6. System Resources Exceeded error message

Change the task affinity to a single core for the Access. Open the Task manager and click the Processes tab, right-click at MSACCESS.EXE and click "Set Affinity" and check the box next to "CPU 0" or "CPU 1" etc. to assign only that CPU core to that program.

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

%windir%\System32\cmd.exe /c start "" /High /Affinity 1

"C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE"

"C:\ProgramData\Northwind\NorthwindApp.accdb"

Or calling a batch file or a PowerShell script doing: Read more at Microsoft

cd "C:\Program Files\Microsoft Office\root\Office16\"

start /Affinity 1 MSACCESS.EXE means CPU 0 using a single core of CPU.

start /Affinity 3 MSACCESS.EXE means CPU 0+1.

start /Affinity 7 MSACCESS.EXE means CPU 0+1+2.

start /Affinity F MSACCESS.EXE means CPU 0+1+2+3.

4. ADP+ in action with a form and datasheet

I will show how easy it is with ADP+ and DataAccessLayerForm object to provide data 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 method 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 employees 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 selecting (highlighting) several rows and then key press Delete or Ctrl – and ADP+ will show an Yes/No deleting messagebox. 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 query because ADP+ provide an updatable ADO recordset 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 } notation 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 default 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 taken over by ADP+ that will provide the data for the combobox through code property 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 selected 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 a 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

In Access I wrote:

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

Me.FilterOn = True

Me![Subform].Form.Filter = "Quantity = 2"

Me![Subform].Form.FilterOn = True

In ADP+ I write when I want to use a filter criteria on a sql server recordset:

dalForm.Filter "Title = 'Sales Representative'"

dalForm.Filter "Quantity = 2"

dalForm.Filter "IsActive = 1"

Drop the filter and remove the criteria:

dalForm.Filter "Remove"

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 an ADP+ function called IsNullOrEmpty and a control for that birthday is a good date for a living person (vbNullString):

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 a 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 multiform with one mainform and two subforms called cascading 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 columns 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 delete sql statement for Order Details table. There is also a Resync Command 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 LinkChildFields property by giving the foreign key in subforms a default value when a user insert a new row in the subform. In Products subform the hidden column SupplierID has default value =[Parent]![SupplierID] and in Order subform the hidden column ProductID has default value =[Parent]![Suppliers_Products_Subform].[Form]! [ProductId].

ADP+ simulate LinkMasterFields property by OnCurrent event procedure in Supplier mainform and in Products subform, where the current record/row primary key is the InputParameters. In Supplier form I use OnCurrent to call a public sub procedure method 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 Orders subform. The design pattern is the same for all mainforms and subforms also when a sub like Products subform become a mainform for Orders subform:

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 Command in the Orders subform 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 employees 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 stored 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 integer 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 EmployeeID textbox with Visible set to No. I add ADP+ code that will simulate LinkMasterFields and LinkChildFields properties in the subreport at OnOpen and OnClose 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 mainreport 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 import 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 some extra formatting 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, ExcelCell As Object

'Dim ExcelFile As Excel.Workbook, ExcelApp As Excel.Application, ExcelCell As Excel.Range

'Reference to Microsoft Excel Object Library from a module at menu {Tools} and {References}.

On Error GoTo MakeExcelXLSX_ErrorHandler

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.ActiveSheet.Range("A1").Select

ExcelFile.Application.Selection.Style = "Normal"

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

' Gets rid of green triangle for numbers when a column has rows with numeric value and letters.

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

For Each ExcelCell In ExcelFile.ActiveSheet.UsedRange.Cells

ExcelCell.Errors(3).Ignore = True ' ExcelCell.Errors.Item(3).Ignore = True

Next

' Example of a numeric 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.Range("A2").Select

ExcelFile.Application.ActiveWindow.FreezePanes = True

ExcelFile.ActiveSheet.Name = Me![Title]

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

Else

MsgBox dalExcel.ErrorMessage

MakeExcelXLSX = False

End If

MakeExcelXLSX_ExitRoutine:

On Error Resume Next

rs.Close

Set rs = Nothing

On Error GoTo 0

Exit Function

MakeExcelXLSX_ErrorHandler:

MsgBox Err.Description

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

Resume MakeExcelXLSX_ExitRoutine

End Function

Alternative for loop:

Dim i As Integer

For i = 0 To rs.Fields.Count - 1

ExcelFile.ActiveSheet.Cells(1, i + 1).Value = rs.Fields(i).Name

Next i

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

Predeclared instantiation

When I have a reference to Microsoft Excel Object Library from a module at menu {Tools} and {References}, I can call a function in Excel e.g. for a sleep/pause:

Call Excel.Application.Wait(Time:=DateAdd("s", 5, Now()))

Excel.Application.Wait Time:=DateAdd("s", 5, Now())

Excel.Application.Wait DateAdd("s", 5, Now())

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

Save report as a PDF file with filter criteria

Public Sub ExportFilteredReportToPDF()

Dim reportname As String

Dim filename As String

Dim criteria As String

reportname = "YourReportName"

filename = "C:\tmp\report_export_file.pdf"

criteria = "SomeTextField = 'ABC' AND SomeNumberField = 123"

DoCmd.OpenReport reportname, acViewPreview, , criteria, acHidden

DoCmd.OutputTo acOutputReport, reportname, acFormatPDF, filename

DoCmd.Close acReport, reportname, acSaveNo

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 variable to make sure that there is only one ADOconnection. ADP+ use ADOrecordset with properties:

LockType = adLockOptimistic; CursorType = adOpenKeyset;

CursorLocation = adUseClient; MarshalOptions = adMarshalModifiedOnly;

ADP+ is using ADOcommand.Parameters.Refresh to get stored procedure parameters meta data by sp_procedure_params_rowset.

This chapter presents some advanced stuff from ADP+.

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 Server Profiler 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 different stored procedures. Each stored procedure is only called one time:

ADP+ prevent subforms to be fired off several times by loading and there is no need 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"

ADP+ is using a caching when the InputParameters don't change value and therefore the dalForm.Requery is skipped. When you like to force the Requery for the same value at InputParameters i.e. from a criteria textbox for search or filter, because you like to refresh data from the database that has been updated by another user or process, you must use dalForm.RequeryForm.

You can also use dalForm.RefreshForm <primary key of the table>

e.g. dalForm.RefreshForm Me![OrderID]

See example in form Orders_Date in the NorthwindApp.accdb example application.

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 means that the foreign key can't be updatable in a form or in a 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 column e.g. [Order Details].OrderID is updatable in the form. I have study the recordset and field properties but I didn’t found out why [Order Details].OrderID act like an identity column, even the field property ISAUTOINCREMENT is 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, it will only be working when the select part refer to the primary key from the one-table, e.g. CustomerID from one-table Customers because one customer has many orders, is working:

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

The select below is not working because CustomerID is taking from the many-table Orders where it is the foreign key:

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 values shown in the form or datasheet. An example was the Suppliers multiform in the last subform where columns Company name and Order date don’t belong to the many-table Order Details 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 values 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 ResyncCommand 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 combobox list in other column like =[OrderID].[Column](1) and therefore I don’t need resynchronization. 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 error message »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 instead 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 together with LimitToList = Yes, I can type in an integer value, that is another workaround.

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 message. ADP+ has an ExecuteReturnValue method for an integer return value from a stored 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 a 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, therefore 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 programming:

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:

Me![SupplierID].ColumnOrder = 14

Me![SupplierID].FrozenColumn = True

Set Datasheet Caption property in VBA examples:

Me![CustomerName].Properties("DatasheetCaption").Value = "Name"

Me![SubForm].Form![SubSubForm].Form![WorkingHours].Properties

("DatasheetCaption").Value = "Hours"

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.

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 Advanced 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

HDR refers to Header of column names in the file as YES or NO.

IMEX refers to Import Export mode where 2 = Linked, 1 = Import enables sql select, 0 = Export enables sql insert and update.

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.

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

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. 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.10. 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"

Using link to a SQL view that is identical to the pass-through query but linkview to SQL View has much faster consistent performance.

7.11. 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 with EmployeeID as primary key:

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

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

MsgBox DataAccessLayerConnectionFactory.ErrorMessage

End If

I make a linked view in the module myDataAccessLayerFactory with OrderID and ProductID as composite primary key:

DataAccessLayerConnectionFactory.CreateLinkView "View_Order_Details", "dbo", "OrderID, ProductID"

If Not DataAccessLayerConnectionFactory.Status Then

MsgBox DataAccessLayerConnectionFactory.ErrorMessage

End If

The linked view can be used in an updatable form like a linked table. In SQL Server Profiler I can see strange sql statements from Access to SQL Server, the same pattern as using linked table.

RefreshLinks must be run as the first thing to do in starting you app typical in a AutoExec macro by calling a vba Public Function RefreshLinks:

Private Sub RefreshLinks()

DataAccessLayerConnectionFactory.RefreshLinks

If DataAccessLayerConnectionFactory.Status = False Then

MsgBox DataAccessLayerConnectionFactory.ErrorMessage

End If

End Sub

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).

rs.Close

Else

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

End If

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

rs.Close

Else

MsgBox dalRS.ErrorMessage

End If

Set rs = Nothing

Set dalRS = Nothing

End Sub

ADO data types

7.13. Execute a sql statement and fetch the result in a recordset

Examples for insert a row and fetch the identity numer, delete and update rows.

Private Sub Execute_Insert()

Dim dalRS As DataAccessLayerForm, rs As ADODB.Recordset

Set dalRS = New_DataAccessLayerForm

With dalRS

.ObjectType = sql

.Object = "SET NOCOUNT ON; INSERT INTO dbo.Suppliers (CompanyName) VALUES ('Dalby');

SELECT SCOPE_IDENTITY();"

.InputParameters

Set rs = .Recordset

End With

If dalRS.Status Then

MsgBox "Inserting is done with new SupplierID = " + CStr(rs(0))

rs.Close

Else

MsgBox dalRS.ErrorMessage

End If

Set rs = Nothing

Set dalRS = Nothing

End Sub

Private Sub Execute_Delete()

Dim dalExec As DataAccessLayerForm

Set dalExec = New_DataAccessLayerForm

With dalExec

.ObjectType = sql

.Object = "DELETE dbo.Suppliers FROM dbo.Suppliers WHERE CompanyName = 'Dalby'"

.InputParameters

.Execute

End With

If Not dalExec.Status Then

MsgBox dalExec.ErrorMessage

Else

MsgBox "Deletion is done"

End If

Set dalExec = Nothing

End Sub

Private Sub Execute_Update()

Dim dalExec As DataAccessLayerForm

Set dalExec = New_DataAccessLayerForm

With dalExec

.ObjectType = sql

.Object = "UPDATE dbo.Customers SET dbo.Customers.Country = 'United Kingdom'

WHERE dbo.Customers.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, also works when there is no row/record in a form:

MsgBox dalForm.Recordset.RecordCount

The current row number in a form:RecordCount:

MsgBox Me.CurrentRecord

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 value 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

From form Employee button Orders.

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 ControlSource formula like this, where IsError is looking for #Error (in danish #Fejl):

=Sum([Orders])

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

Adding two columns called 3 and 4 in detail band in a form or in a report where columns 3 or 4 or both can have a Null value, we need to convert Null to Zero before adding them, but if the result is zero I like to show Null which IIf takes care of:

=IIf(Nz(![3])+Nz(![4])=0;Null;Nz(![3])+Nz(![4]))

In the form footer or report footer to show the sum of all rows, no use of ! here:

=IIf(Sum(Nz([3])+Nz([4]))=0;Null;Sum(Nz([3])+Nz([4])))

But above formula 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 object (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:

An alternative is shown in form "Employee SQL statement" button Orders that is open form "Employee_Orders Pass-through query" where recordsource is using a pass-through query called Form_Employee_Orders. It makes the form read-only, but it is okay for showing the orders statistic list and then the calculated textbox Total can use fomula: =Sum([Orders]).

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, there 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.

#Name? (#Navn? in danish) shown in a control e.g. a textbox in a form or a report means that the RecordSource is missing. ADP+ for a report must have a pass-through query in the RecordSource.

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 value 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 value 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 or a sort order by on a column, the requery 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 Or Me![Data].Form.OrderByOn = 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

' RecordsetClone in a subform gets the value of the previous record/row therefore

' RecordsetClone.Bookmark point at the record before I did the Me![Data].Requery.

End If

DoCmd.GoToControl CurrentControlName

Me![Data].Form.Painting = True

On Error GoTo 0

End Sub

7.21. Continuous form (tabular 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 combobox (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 mouse 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, Edit cursor and SetFocus

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 dialogbox, because 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 (Caret cursor, | cursor, Text cursor or the Insertion point, not same as I-Beam mouse pointer) 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 working 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

A mainform has two subforms, and from inside one of them, I like to make Ctrl+ArrowUp jump to the other subform, same as Shift+Tab:

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

If (Shift = acCtrlMask) Then

Select Case KeyCode

Case vbKeyUp

On Error Resume Next

Parent![Sub_Person].SetFocus

Parent![Sub_Person].Form![TextboxFirstName].SetFocus

KeyCode = 0

On Error GoTo 0

End Select

End If

End Sub

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 contextual 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 Access 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 an Access system table and is hidden in navigation pane but rightclick at navigation pane title like "All Access objects" and choose {Navigation Options} and set a checkmark at "Show system 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 RibbonGetVisible 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> <qat> </qat> </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"/> <tab idMso="TabOfficeFeedback" 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 Access 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 Access danish version I have to use danish words instead of Forms and Form, there are 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 automatic 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 Access danish version 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")

A format in a textbox for USA: #,##0;0;0;0; and for Europe: #.##0;0;0;0;

for positive value; negative value; zero value; null value.

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"))

In a sql query:

UPDATE CUSTOMER

SET NumberOfEmployees =

IIf(Nz(NumberOfEmployees) <= 1, Null, NumberOfEmployees - 1)

WHERE CustomerId = 1966

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')")

Me![StatusName].ForeColor = Eval("RGB(0, 0, 0)")

Me![StatusName].ForeColor = Eval(Me![RGBcodeForegroundColor])

7.26. Access canvas, Access desktop, Size of Access Client Area, MDIClient

The blank space area to the right side of the navigation pane and below the ribbon bar menu I am calling canvas of Access. Microsoft calling it MDIClient, Multiple Document Interface. I like to know the size of the canvas in Access. Different Access versions have different heights of the ribbon bar menu and therefore different height size of the canvas. Windows taskbar has normal or small buttons, and screen resolution can be like 1920 x 1080 or 1920 x 1200, they all determine the height of the canvas in Access. When I know the size of the canvas, I can place a form in the »overlapping windows« mode, not maximize a form, inside the canvas as in the center or as a full size form window (maybe without border) using the Me.Move who apply twips, therefore I convert pixels to twips.

Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr,

ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr

Private Declare PtrSafe Function GetClientRect Lib "user32" (ByVal hwnd As LongPtr, lpRect As RECT) As Long

Private Declare PtrSafe Function GetDesktopWindow Lib " user32" () As LongPtr

Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hwnd As LongPtr) As LongPtr

Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hdc As LongPtr, ByVal nIndex As Long) As Long

Private Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hwnd As LongPtr, ByVal hdc As LongPtr) As Long

Private Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long

Private Const LOGPIXELSX As Integer = 88

Private Const LOGPIXELSY As Integer = 90

Private Const DIRECTION_VERTICAL As Integer = 1

Private Const DIRECTION_HORIZONTAL As Integer = 0

Private Type RECT

Left As Long

Top As Long

Right As Long

Bottom As Long

End Type

Private Function PixelsToTwips(lPixels As Long, iDirection As Integer) As Long

Dim lDesktopHWnd As LongPtr

Dim lDeviceHandle As LongPtr

Dim lPixelsPerInch As Long

Const lTwipsPerInch As Long = 1440

lDesktopHWnd = GetDesktopWindow() ' for Windows. 'lDeviceHandle = GetDC(0) for Linux.

lDeviceHandle = GetDC(lDesktopHWnd)

Select Case iDirection

Case DIRECTION_HORIZONTAL:

lPixelsPerInch = GetDeviceCaps(lDeviceHandle, LOGPIXELSX)

Case DIRECTION_VERTICAL:

lPixelsPerInch = GetDeviceCaps(lDeviceHandle, LOGPIXELSY)

Case Else:

lPixelsPerInch = 96 ' screen density of 96 dpi.

End Select

lDeviceHandle = ReleaseDC(0, lDeviceHandle)

PixelsToTwips = (lPixels / lPixelsPerInch) * lTwipsPerInch

End Function

Private Function TwipsToPixels(lTwips As Long, iDirection As Integer) As Long

Dim lDesktopHWnd As LongPtr

Dim lDeviceHandle As LongPtr

Dim lPixelsPerInch As Long

Const lTwipsPerInch As Long = 1440

lDesktopHWnd = GetDesktopWindow()

lDeviceHandle = GetDC(lDesktopHWnd)

Select Case iDirection

Case DIRECTION_HORIZONTAL:

lPixelsPerInch = GetDeviceCaps(lDeviceHandle, LOGPIXELSX)

Case DIRECTION_VERTICAL:

lPixelsPerInch = GetDeviceCaps(lDeviceHandle, LOGPIXELSY)

Case Else:

lPixelsPerInch = 96 ' screen density of 96 dpi

End Select

lDeviceHandle = ReleaseDC(0, lDeviceHandle)

TwipsToPixels = (lTwips / lTwipsPerInch) * lPixelsPerInch

End Function

Public Function ShowCanvasSize()

Dim MDIRect As RECT

Dim lWidthPixels As Long

Dim lWidthTwips As Long

Dim lHeightPixels As Long

Dim lHeightTwips As Long

Dim lDock As LongPtr

DoEvents ' when you already have hide navigation pane or ribbon bar menu for bigger formsize.

lDock = FindWindowEx(Application.hWndAccessApp, 0&, "MDIClient", vbNullString)

GetClientRect lDock, MDIRect ' GetWindowRect includes borders of right/buttom side so form size becomes too big.

lWidthPixels = MDIRect.Right - MDIRect.Left

lWidthTwips = PixelsToTwips(lWidthPixels, DIRECTION_HORIZONTAL)

lHeightPixels = MDIRect.Bottom - MDIRect.Top

lHeightTwips = PixelsToTwips(lHeightPixels, DIRECTION_VERTICAL)

MsgBox "Width (pixels) = " & lWidthPixels & " Width (twips) = " & lWidthTwips & vbNewLine &

"Height (pixels) = " & lHeightPixels & " Height (twips) = " & lHeightTwips

End Function

Public Function WindowsScreenWidthPixels()

WindowsScreenWidthPixels = GetSystemMetrics(0)

End Function

Public Function WindowsScreenHeightPixels()

WindowsScreenHeightPixels = GetSystemMetrics(1)

End Function

You can call =ShowCanvasSize() at a button in a form to see the size values.

The default dpi (Dots per inch) for most Windows computers is 96 with a 100% scale level. Increase the scale level to 125% (120 dpi) or 150% (144 dpi) makes text and other items appear larger on the computer screen together with scroll bars. The MDI hierarchy tree looks like: MDI Parent, MDI Client, MDI Child.

In a form or a in report the following applies:

1 pixel = 0,0264583333333333 cm. 1 cm = 37,79527559055118 pixel.

7.27. Data macro on a table – a table trigger

The Northwind example has a table Temp_Employees to hold temporary data from a stored procedure, please see the two VBA ADP+ code examples in module myDataAccessLayerFactory. The table has a column InsertTime_Temp with default value Now() to tell us when a row has been inserted. And a column InsertUser_Temp to tell us which user (Windows login) has been inserted the row, but DefaultValue property can’t call a function, therefore I am using a Data Macro for Before Change:

If [IsInsert] Then

SetField

Name InsertUser_Temp

Value LoginUser()

When table Temp_Employees is used for example to a report and the application has concurrent users, the report must only shows the rows that belong to a specific user and here is column InsertUser_Temp helpful in an Access query with search criteria: WHERE InsertUser_Temp=LoginUser().

Before I insert rows into the table Temp_Employees, I have to delete the old previous inserted rows by the user, therefore the sql delete statement use the same criteria:

CurrentDb.Execute "DELETE * FROM [Temp_Employees] WHERE [InsertUser_Temp] = '" &

LoginUser() & "'", dbFailOnError

On an Access table how create a data macro attached to the Before Change event:

A youtube video presentation Description

Another example of a data macro before change to set a date/time for a column UpdateTime when data in a row has been changed. UpdateTime has default value Now():

If [IsInsert]=False Then

If Not Updated("UpdateTime") Then

SetField

Name UpdateTime

Value Now()

7.28. ADO stream

The Northwind example shows an example of how to use ADO stream together with ADP+ DataAccessLayerConnectionFactory.ADODBConnection:

Private Sub ADOstream_Example()

Dim ADOcmdStream As ADODB.Command

Dim ADOstream As ADODB.Stream

Dim sql As String

Dim streamText As String

Set ADOcmdStream = New ADODB.Command

Set ADOcmdStream.ActiveConnection = DataAccessLayerConnectionFactory.ADODBConnection

ADOcmdStream.CommandTimeout = 5000

sql = "SET NOCOUNT ON SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS " + _

"WHERE TABLE_NAME = 'List_Region'"

Set ADOstream = New ADODB.Stream

ADOstream.Open

With ADOcmdStream

.CommandType = adCmdText

.CommandText = sql

.Properties("Output Stream") = ADOstream

.Execute , , adExecuteStream

End With

streamText = ADOstream.ReadText

ADOstream.Close

Set ADOstream = Nothing

Set ADOcmdStream = Nothing

MsgBox streamText

End Sub

7.29. TempVars

A global variable that can be used inside a query and replace the query parameter:

Public Sub SetTempVars()

TempVars.Add "ReportsTo", 2

TempVars.Add "LastName", "Fuller"

End Sub

Public Sub ShowTempVars()

MsgBox TempVars!ReportsTo

MsgBox TempVars!LastName

End Sub

Public Sub UseInQueryTempVars()

Dim qd As QueryDef, strQueryName As String

strQueryName = "temp"

On Error Resume Next

CurrentDb.QueryDefs.Delete strQueryName

On Error GoTo 0

Set qd = CurrentDb.CreateQueryDef(strQueryName, "SELECT Employees.*

FROM Employees WHERE (((Employees.LastName)=[TempVars]![LastName])) OR

(((Employees.ReportsTo)=[TempVars]![ReportsTo]));")

DoCmd.OpenQuery strQueryName, acViewNormal, acReadOnly

CurrentDb.QueryDefs.Delete strQueryName

End Sub

7.30. Compact and Repair with VBA code

Public Sub Compact_Repair_DB()

Dim path As String, database As String, compact As String

path = "C:\Data\"

database = "NorthwindApp.accdb"

compact = "NorthwindApp_Compact.accdb" ' temporary database

Application.CompactRepair path & database, path & compact, False

'DBEngine.CompactDatabase path & database, path & compact

Kill path & database

Name path & compact As path & database ' a rename of _Compact.accdb to original.

End Sub

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

An example of Cascading Combo Box or Cascading List Box where the values in second box is dependent on the selected value in first box.

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 an 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

To make sure all rows is fetched to a listbox and scroll inside of it is working nice:

Private Sub Products_AfterUpdate()

Dim lng As Long

lng = Me![Products].ListCount

End Sub

In a Continuous Form the Cascading Combo Boxes is not working in Access because previous records is going blank or are blanking, but there is different methods to make it work, here is three links:

CascadingComboInSubform with two different sql statements

Unbound combobox with a bound textbox on top

Cascading Combos on Continuous Forms and Datasheets

Using Tab key ↹ (abbreviation of tabulator key or tabular key) to go to a combobox follow these events in vba code: _Enter() and _GotFocus().

Using mouse click to a combobox follow these events in vba code:

_Enter(), _GotFocus() and _MouseDown. Using F4 to open/close the dropdown list.

To a bound combobox to a column in a PERSON table, I like to requery when it gets focus by Tab key or mouse click to show in the dropdown list all the cities that belong to the country I have selected in previous combobox, therefore the City combobox has a sql statement in its RowSource combobox with name CityId:

SELECT CityId, CityName FROM CITY WHERE CountryId = ![CountryId]

Together with some VBA code:

Private Sub CityId_Enter()

DoCmd.RunCommand acCmdSaveRecord

Me![CityId].Requery

End Sub

Private Sub CityId_MouseDown(Button As Integer, Shift As Integer,

X As Single, Y As Single)

Me![CityId].SetFocus

Me![CityId].Dropdown

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-2019/365, 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-2019/365 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-2019/365 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, Unique Table and Resync Command. 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 from the adp file, I will try to build in ADP+ vba code in an existing adp file as comments ('), because I have in the adp file access to the settings in forms and reports and I can use them in the ADP+ properties and methods to the dal objects. But I am not able to run ADP+ in the adp file. Some examples:

In an adp file in Form_Load:

'Private dalForm As DataAccessLayerForm

'Set dalForm = New_DataAccessLayerForm

'With dalForm

' .Language = English

' .ObjectType = StoredProcedure

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

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

' .Bind Me

'End With

adp code:

Private Sub Type_Enter()

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

End Sub

become with ADP+ vba code:

'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

Maybe you have installed Access 2013-2019/365 in parallel with your Access 2010 and you are written the ADP+ code directly to your accdb file and run it.

Maybe you like to use only Access 2010 at your PC where you have the adp file open in an Access window and you have the new accdb file open in another Access window to see the settings and properties in adp and write the ADP+ code in accdb and run it.

Conversion of an ADP to an Access front-end application

Convert an old adp file to accdb or adp upgrade to Access

You have tables, views and stored procedures in your SQL Server database and for me, I like to keep them. But there will be some situations in forms or reports, where you need to use linked table in your app.accdb and move your view or stored procedure to a query in Access with reference to a form instead of a parameter. Sometimes I have used a pass-through query in my app.accdb to call a stored procedure with parameter and insert the result dataset into a table, maybe placed in a tempdb.accdb and link the temp table to the main app.accdb for making a report with subreports easier to convert. Pass-through query gives a read-only dataset used in a RowSource of a combobox or listbox. ADP+ do the assignment of a pass-through query calling a stored procedures with parameters.

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-365 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 encapsulated its subforms inside the mainform. Access never changed multiform design.

Wikipedia Microsoft Access versions Error Number and Error Description

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 'Enable 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.

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

Optimizing Microsoft Office Access Applications Linked to SQL Server

Pass-through query was in Access 1.0 to SQL Server 1.1 (User’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 Many more Access VBA programming

Access UI accessforever isladogs Many database example tips

Fast Table Lookup Allen Browne Stephen Lebans Markus G Fischer Juan Soto

Tutorial User voice site Access Form Resizing Based On Screen Resolution

Data types Functions I love Replace Round More Round

Access funtion Round is using a banking way, therefore ADP+ has a normal way:

Dim v As Double

v = 123.445

MsgBox Round(v, 2) ' 123.44 Access way

MsgBox Round_(v, 2) ' 123.45 ADP+ way

v = 0.445

MsgBox Round(v, 2) ' 0.44 Access way

MsgBox Round_(v, 2) ' 0.45 ADP+ way

Round function returns a value of the same type that is passed to it specifying the absolute value of a number, therefore Round(<number>;2) when <number> contains Null, Null is returned. If it is an uninitialized variable, zero is returned.

To calculate a KPI as PercentageSale = PaidSale / TotalSale in a query I do this:

PercentageSale: Round(IIf([TotalSale]=0;0;[PaidSale]/[TotalSale]);2)

to handle division by zero with a result as 0 I use IIf, and to handle TotalSale has a Null value with a result as Null I use Round. Using Round also avoid a mouse click inside a textbox to get | cursor and showing the decimal number, e.g. PercentageSale becomes 0.497563 and a textbox has format property: 0%;0%;0%;0% will show 50% always. Remark, Round(value;3) doesn't do the trick, so I must accept that Round(0.445; 2) becomes 0.44 because Access is using a banking way.

An alternative is using of Format function in a query but it return value as a string: Format(IIf([TotalSale]=0;0;[PaidSale]/[TotalSale]);"0%;0%;0%;0%")

therefore the sort order is not working correctly e.g.: 1, 10, 2, 20, 3.

In Access I like data type Decimal and I have seen that Double is better than Single, e.g. 16.6 as Double becomes 16.6000003814697 as Single in a DAO recordset (in danish Dobbelt reelt tal).

I will never understand why Microsoft had to stop supporting Access Data Project to access a SQL Server database for tables, views, stored procedures and user defined functions in Record Source and Row Source together with LinkMasterFields and LinkChildFields. For sure I have no need to implement a SQL Server database, tables or stored procedures through Access UI because I am using SQL Server 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.

Migrate an Access database to SQL Server and read about Link tables

Migrate Access backend to SQL server, see manual instructions at the end

SQL Server Migration Assistant (SSMA) v8.0

SQL Server Migration Assistant for Access (AccessToSQL)

Microsoft SQL Server Migration Assistant 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 "Microsoft Office 15.0 Access Database Engine OLE DB Provider"

You can instead mark an Access table, select External Data, Export, More, ODBC Database. When you have an accdb database file, create first a new database in "SQL Server Management Studio", then start your Access and select File and Save As to a mdb file, start "SQL Server Import and Export Data (32-bit)" and select the mdb file and connect to your sql server and to the new database. Select Access tables 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« then do a Compact and repair.

If you get error message when you start your application or open a form, your VBA code needs 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"

After you have decompiled your accdb file, you open the file and you open a module and you do a Compile of your VBA code and end with a Compact and repair.

»This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)« I have seen in a open/running query when a calculated/derived column shows #Error (in danish #Fejl) so I have to fix the formula. I have also seen in a open report when there is too many calculations and some of them is using a DLookup, DCount, DMax or DMin that is using another query as source, therefore time to refactor the code called Code refactoring, maybe add the result of query to a temp table and use it as recordsource of report, or find the main table of the query and move data to a new table and rename back to original table name, yes needs to delete and set back the relationships too. I also seen a query with a column: NumberSort: CLng([Number]) but Number is a text data type and can contain letters and a report do a Grouping or Sorting on NumberSort which gives the same error as above.

If you receive message error 3014 Cannot open any more tables or error 3048 Cannot open any more databases, it has nothing to do with how many distinct databases you have open. What it is actually talking about is an internal array or collection of what are called TableIDs. Every open recordset includes load of a form with comboboxes, listboxes, subforms and DLookup, DSum, DCount in VBA code use up a lot of TableIDs. A DSum in a query to a updatable form is using a TableID e.g.:

Sum_Invoice_Amount: CCur(Nz(DSum("[Amount]";"INVOICELINE";"[InvoiceId]=" & [InvoiceId]);0))

On any given form, you can reduce the number of TableIDs used, by removing comboboxes, listboxes and DLookups and hide subforms e.g.

Me![Sub_Orders].SourceObject = "".

For error message System Resource Exceeded and There isn’t enough memory to perform this operation, read The /LARGEADDRESSAWARE (LAA) flag demystified.

Re-enable macro Autokeys when keystrokes is not working by making a module and run the sub:

Global Const voKEY_ASSIGNMENT = "Key Assignment Macro"

Public Sub ResetAutoKeys()

Application.SetOption voKEY_ASSIGNMENT, "Autokeys"

End Sub

In macro Autokeys can't be used to capture or trapping Alt keystrokes like Alt+F4 is not permitted. To disable Alt+F4 you need to use the KeyDown event in a form.

Avoid error message with DataErr = 7878:

»The data has been changed

Another User edited this record and saved the changes before you attempted to save your changes.

Re-edit the record.«

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

If DataErr = 7878 Then

Response = acDataErrContinue

'Else

' MsgBox DataErr & " " & Response

End If

End Sub

Three ways to set cursor in first row:

DoCmd.GoToRecord , , acFirst

DoCmd.RunCommand acCmdRecordsGoToFirst

Me.Recordset.MoveFirst

To check for at least one row in the form e.g. in a button_click method:

If Me.Recordset.RecordCount >= 1 Then

If Me![Subform].Form.Recordset.RecordCount >= 1 Then

If Me![Subform].Form![SubformInsideSubform].Form.Recordset.RecordCount >= 1 Then

Access DDL to reset an Autonumber (Identity/Sequence) column in the linked database 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

Read more about alter a table.

For an empty table I want the first inserted row to start CategoryId with 0 when I type in »Missing« in the CategoryName column. I do this in a Sub in Dat.accdb:

CurrentDb.Execute "ALTER TABLE Category ALTER COLUMN CategoryId COUNTER(0,1)", dbFailOnError

Add a table validation rule or a table constraint, an example for your inspiration:

CurrentProject.Connection.Execute "ALTER TABLE Category ADD CONSTRAINT GroupRule CHECK (CategoryGroup IN ('Fine', 'Good', 'Nice'));"

Read much more about alter a table.

Access show the constraints through a sql statement:

SELECT ForeignName AS TableName, Name AS ConstraintName,

Connect AS CheckExpression, DateCreate, DateUpdate

FROM MSysObjects WHERE Type = 9;

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

Id = 0

Set db = CurrentDb

db.Execute sql

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

Id = rs.Fields(0) ' Debug.Print rs.Fields(0) ' see with 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

(Me.SubFormName.Form.Recordset.Find "FieldName = " & Me.OpenArgs)

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)

Or:

DELETE T1.*

FROM T1

WHERE T1.Name IN (SELECT T2.Name FROM T2)

A mix of EXISTS and IN because based on a CLEANUP table with projects, I like to delete real projects that have no name and no supplier number and have no tasks (not exists a task):

DELETE PROJECT.*

FROM PROJECT

WHERE ((PROJECT.Name Is Null) AND (PROJECT.SupplierNo Is Null) AND

NOT EXISTS (SELECT 'x' FROM TASK

WHERE (TASK.ProjectId=PROJECT.ProjectId) HAVING (COUNT(TASK.TaskId)>=1)) AND

(PROJECT.ProjectNo IN (SELECT CLEANUP.ProjectNo FROM CLEANUP)))

A SQL Update statement is not working because operation must use an updatable query and using Min function makes the query readonly:

UPDATE InvoiceItemLine

INNER JOIN

(

SELECT ID, Min(Position) AS MinPosition

FROM InvoiceItemLine

GROUP BY ID

) AS t

ON t.ID = InvoiceItemLine.ID AND t.MinPosition = InvoiceItemLine.Position

SET InvoiceItemLine.Type = 'S';

Instead use Domain function DMin:

UPDATE InvoiceItemLine

SET InvoiceItemLine.Type = 'S'

WHERE InvoiceItemLine.Position =

DMin("[Position]","InvoiceItemLine","[ID]=" & [InvoiceItemLine].[ID])

I have learned that a union of two queries using null for a column can give a funny result, therefore I am using dummy values e.g.:

SELECT PatientId, TreatmentId, #31-12-2099# AS DateOfDepot

FROM PatientTreatmentQuery

UNION ALL

SELECT PatientId, -2147483648 AS TreatmentId, DateOfDepot

FROM PatientDepotQuery;

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.

MsgBox StrConv("hubert blaine wolf berger dorff", 3)

3 = convert the first letter of every word to upper-case.

Nice constants: vbNullString ("" empty string) and MsgBox "X" & vbNewLine & "Y".

vbCrLf do the same. vbTab or Chr(9), same as Chr(13)+Chr(10) makes new line.

Merge columns of text that allow null value to a new column in a query in Access:

Fullname: Trim(Nz([Person].[Firstname];"")+" "+Nz([Person].[Middlename];"")+

" "+Nz([Person].[Surname];""))

When you use the Nz function in an expression in a query without using the valueifnull argument, the results will be a zero-length string in the fields that contain null values, but I prefer to add "".

NameAge: Trim(Nz([Person].[Name];"")+" "+CStr(Nz([Person].[Age];""))

it needs CStr() to convert integer value of Age to a string.

Number: Max(Val(Replace(Nz(Table].[ColumnStringDatatype],",","."));0);",";".")))

in a query for small numbers as string e.g. "1.1" or "4,6" or "10,8" or "10.9" gets 10.9 as the max number. Val function with a parameter value as null will return #Error therefore I use Nz function to convert null to zero (zero in danish is nul).

IIf(IsNull(…),…,…) and IIf(IsNumeric(…),…,…) is usefull and C-functions too.

A date column allows null and we want a decreasing sorting with null showing first: Sorting: CDate(Nz([PaymentDate];#31-12-2099#))

Convert or cast a datetime to a date and a time value:

SELECT DateValue([PaymentDateTime]) AS PaymentDate,

TimeValue([PaymentDateTime]) AS PaymentTime

FROM Sales

Nice ways to update a row: Me.Requery Me.Refresh Me.Recalc for calculations and conditional formatting and Me.Repaint method completes any pending screen updates and force immediate repainting of the controls e.g. OLE object.

Invalid precision for decimal data type (Præcisionsværdien for decimaldatatype er ugyldig) use CDec(...,2).

Like to have a caption of a button or a tab page name end with a space, use an alternative to space as a invisible character as Alt+0160 just press the ALT key and type in 0160 at numeric keyboard, and then let go of the ALT key.

(To insert a non-breaking space in Word or Excel: press Ctrl + Shift + Space.)

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 understanding 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.

Roadmap for Access Update history for Microsoft 365 Apps Release notes

Access without ADP has really make me reuse old techniques and working with ADO recordset binding to a form and simulate LinkMasterFields and LinkChildFields properties 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 language:

Access Utilities WordPerfect and Paradox Utilities.htm Macro is blocked

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 $210 or euro €185 including 25% danish vat. The purchase can not be canceled.

A free trial version for year 2024 is available for download here:

32 bit: ADPX_32.zip or 64 bit: ADPX_64.zip

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

Any comment, suggestion, inquiry and purchase are welcome

IMPORTANT: Unblock any file you download before extracting files if it is a Zip file. Otherwise, even though extracted files might not have an unblock indicator, they might not work right. Here are the steps to Unblock a file (remove MOTW Remove Mark of the Web):

1. Right-click on the file in File Explorer.

2. Choose Properties from the shortcut menu.

3. If file is blocked, you will see a checkbox to Unblock at the bottom.

4. Check Unblock and choose OK or Apply.