Posted by
Andrew Gould
on 31 March 2014
How to write macros in VBA to work with SQL Server, Access and other tables
Posted by
Andrew Gould
on 01 April 2014
How to use VBA macros to amend records in SQL Server and Access tables
Posted by
Andrew Gould
on 02 April 2014
How to construct and manipulate command objects in ADO to talk to databases using VBA
Posted by
Andrew Gould
on 24 June 2019
This video explains the current Microsoft recommendations for connecting to SQL Server using ADO and the new OLE DB Driver. You'll see how to install the new driver and create a connection string which references it. You'll also see how to create a basic recordset to retrieve data from a SQL Server database and write the results into an Excel worksheet.
Posted by
Andrew Gould
on 24 June 2019
This video shows you how to extract data from a SQL Server database using the ActiveX Data Objects (ADO) library for VBA and the Microsoft OLE DB Driver for SQL Server. You'll learn how to write a connection string to connect to a SQL Server databse and how to use a recordset to retrieve data. You'll learn how to populate the recordset using SQL Server tables, views, stored procedures and select statements and how to write the data you've retrieved into an Excel Worksheet.
Posted by
Andrew Gould
on 24 June 2019
In this video you'll learn how to open an SQL Query file, read its contents and use the result to populate an ADO recordset. You'll learn how to use file picker and folder picker dialog boxes to allow the user to pick which queries to run. You'll also learn a little about the Scripting Runtime object library and how to work with FileSystemObjects and TextStreams.
Posted by
Andrew Gould
on 24 June 2019
This video briefly explains how to return multiple sets of results to a single ADO recordset object. You'll learn how to use the NextRecordset method to retrieve the next set of results and continue doing this until all the recordsets have been processed.
Posted by
Andrew Gould
on 26 June 2019
This video shows you how to use ADO commands to modify data in a SQL Server database. You'll learn how to use the INSERT INTO, UPDATE and DELETE statements with an ADO command object to affect records.
Posted by
Andrew Gould
on 26 June 2019
Learn how to add parameters to an SQL query using ADO and allow users to enter search criteria. This video teaches you how to parameterise a query string and how to pass values to the parameter list using the Array function. You'll also learn how to create ADO parameter objects to make it easy to reuse a parameter multiple times in the same procedure.
Posted by
Andrew Gould
on 05 July 2019
This video shows you how to call and pass values to stored procedures from a SQL Server database using ADO commands. You'll learn how to reference parameters by name, how to pass values to parameters and how to retrieve a result set using a recordset object. You'll also see how to extract information from a stored procedure using output parameters.
Posted by
Andrew Gould
on 16 May 2021
The first video in our tutorial on writing SQL for Excel files with ADODB shows you how to write basic Select statements to retrieve data from a separate Excel file. You'll learn how to refer to worksheets, cell references and range names and how to deal with worksheets with no column headers. You'll also see how to write a Select list to pick a specific set of columns from the Excel source and how to rename source columns by using aliases in your query. The final part of the video discusses different ways to lay out a longer SQL query, including the controversial topic of where to put your commas!
Posted by
Andrew Gould
on 16 May 2021
This video explains how to add an Order By clause to sort the results of your SQL queries when selecting data from an Excel file. You'll learn how to sort single and multiple columns by name in both ascending and descending order. You'll also see how to sort a query when the source data has no column headers and how to sort by a column without displaying it in the query results.
Posted by
Andrew Gould
on 16 May 2021
Learn how to select the top rows from an Excel table an SQL query. You'll learn how to select a specific number of rows from the top of a list and how to use the Order By clause to influence which rows appear at the top. You'll see how to deal with tied rows by including a tie-breaker field and how to select a percentage of the rows from a table.
Posted by
Andrew Gould
on 17 May 2021
In this video you'll learn how to use the All and Distinct keywords in your SQL query to return either every row from a data source or just the rows with unique values. You'll see how to affect the sorting of the query results using the Order By clause and how to use unique row values to provide users with a choice using drop down lists on a user form.
Posted by
Andrew Gould
on 19 May 2021
This video explains how to add the Where clause to your SQL query to extract only the rows you want from an Excel file. You'll learn about the basics of adding criteria to the Where clause, including how to write basic logical tests and the range of available comparison operators. You'll also learn how to enter numbers, text and date values in your conditions, including the best date format to use. You'll learn how to combine conditions using the And and Or operators, and how to reverse the logic using the Not operator, including how to ensure multiple conditions are evaluated in the correct order by using brackets. You'll see how to use the special Like operator with wildcard characters for working with text columns. You'll also learn how to use the special In and Between operators to shorten longer criteria and make your SQL code more succinct. Finally, you'll learn how to use the Is Null and Is Not Null operators to find or ignore rows with missing values.
Posted by
Andrew Gould
on 23 May 2021
In this video you'll learn how to write criteria for text fields in SQL queries for Excel. You'll learn about the Like operator and the percentage and underscore wildcard characters. You'll see how to use a CharList to search for a range of characters in a string. You'll also learn how to use a variety of string functions such as Len, Left, Right, Mid, UCase, LCase InStr and StrComp to create more complex criteria, including making your string comparisons case-sensitive.
Posted by
Andrew Gould
on 26 May 2021
This video explains the basics of adding calculations to an SQL query for Excel. You'll learn how to add columns to the Select list and assign aliases to them. You'll learn the basic arithmetic operators, what BODMAS and PEDMAS are and how to control the order of evaluation in a complex expression. You'll see how to add calculations to the Where and Order By clauses. You'll learn two different ways to divide numbers and how to use the Mod operator to return the remainder. You'll learn the basics of concatenating values and see what happens when your expressions generate an error. You'll also learn some basic ways to format the results of your expressions using functions such as Round and Format.
Posted by
Andrew Gould
on 01 June 2021
Learn how to use IIf and Switch functions to test conditions and produce different results depending on the answer. You'll see how to create calculated columns using the IIf and Switch functions, as well as how to use the functions to control the sort order of your query results. You'll learn how to use nested IIf and Switch functions and how to write more complex logical tests using the AND, OR, IN and BETWEEN operators.
Posted by
Andrew Gould
on 03 June 2021
In this video you'll learn how to work with nulls in your SQL queries. You'll see how to use the IS NULL expression and IsNull function to check for the existence of nulls and how to use the IIf and Switch functions to replace nulls with other values. You'll also see what happens when a null is involved in a calculation and how to make sure that the calculation returns a non-null result.
Posted by
Andrew Gould
on 06 June 2021
This video teaches you how to write calculations with dates in your SQL queries for Excel files. You'll learn how to format dates using the FormatDateTime and Format functions, how to extract parts of dates using the Year, Month, MonthName, Day, Weekday, WeekdayName and DatePart functions and how to create a date from other values using the DateSerial and DateValue functions. You'll also learn how to create calculations based on the current date using the Date and Now functions, how to calculate past or future dates using the DateAdd function and how to calculate the difference between dates using the DateDiff function.
Posted by
Andrew Gould
on 08 June 2021
Learn how to write expressions to manipulate text in your SQL queries for Excel files. You'll learn how to concatenate text and other values and how to convert the data types of text and numbers using the CStr, CInt and Val functions. You'll see how to use the Replace and Trim functions to clean text values and how to calculate the length of a string with the Len function. You'll also learn how to find a character within a string using the InStr and InStrRev functions and use these in combination with the Left and Mid functions to split a string into multiple parts.
Posted by
Andrew Gould
on 11 June 2021
This video teaches you how to use aggregation functions in SQL queries for Excel to summarise data in a column. You'll learn how to use the Sum, Count, Min, Max, Avg, StDev and Var functions to generate aggregated values for all the rows in a dataset. You'll also see how to use the results of aggregation functions to create new calculations and how to deal with Nulls in the columns you're aggregating.
Posted by
Andrew Gould
on 11 June 2021
This video explains how to group rows in your SQL queries for Excel files by using the Group By clause. You'll learn how to group data using the values in a column and how to create aggregations using functions such as Sum, Avg and Count. You'll also see how to group data using the results of calculations and how to create multiple row groups.
Posted by
Andrew Gould
on 11 June 2021
This video shows you how to use the Having clause in an SQL query to apply criteria to the results of aggregation functions. You'll learn why you can't add these criteria to the Where clause, how to add the Having clause to a query and how to write criteria based on aggregation functions such as Sum, Avg and Count. As an added bonus, you'll also learn the importance of the phrase Sweaty Feet Will Give Horrible Odours!
Posted by
Andrew Gould
on 11 June 2021
This video explains how to create crosstab queries in your SQL queries for Excel files. You'll learn how to prepare the base data using a normal Select statement and how to add the Transform statement and Pivot clause to create column groups and aggregations.
Posted by
Andrew Gould
on 20 June 2021
This video shows you how to write basic Union queries in SQL for Excel. You'll learn how to merge two or more tables into a single list and the difference between Union and Union All. You'll understand the importance of selecting the same number of columns and how to map columns with different names. You'll also learn how to sort the results of a Union query, how to add calculated columns and how to add criteria.
Posted by
Andrew Gould
on 22 June 2021
This video explains how to use Union queries to combine detail rows with subtotals and grand totals in a single list.
Posted by
Andrew Gould
on 22 June 2021
This video demonstrates how to use the IN clause (not to be confused with the IN operator!) in an ADODB SQL query to select data from multiple files in the same query. You'll learn how to union tables from multiple Excel workbooks, both with and without header rows. As a bonus, you'll also see how to union data from a CSV file.
Posted by
Andrew Gould
on 27 June 2021
This video explains how to write inner joins in your SQL queries for Excel files to look up related information in different worksheets. You'll learn a bit about relational database design including what primary keys, foreign keys and one-to-many relationships are. You'll see how to join two tables using an inner join to match rows based on a common value. You'll see an alternative way to do the same thing without using an inner join. You'll also learn the syntax required to include multiple tables in the same query and finally how to apply criteria and sorting to the results.
Posted by
Andrew Gould
on 28 June 2021
This video explains how to use left and right outer joins in your SQL queries for Excel files. You'll learn why inner joins don't always return all the results you expect and how to use an outer join to ensure that you always return all of the rows from one of the tables involved in the join. You'll see how to use criteria to find the unmatched rows from a table and find all the invalid entries in a joined table.
Posted by
Andrew Gould
on 29 June 2021
This video shows how to simulate a full outer join in your SQL queries for Excel files. You'll learn how to combine the results of a left outer and a right outer join while removing the duplicated rows. You'll see how to use Union and Union All queries to control the duplication of rows in the output. You'll also learn how to combine multiple tables in the same query ensuring that you see every row from every table included.
Posted by
Andrew Gould
on 04 July 2021
This video explains how to join worksheets from multiple workbooks in a single SQL query. You'll learn about the IN clause and how to reference other workbooks, using a derived table subquery which can be joined to other tables in a query.
Posted by
Andrew Gould
on 04 July 2021
This video shows you how to merge multiple worksheets using a combination of union queries and outer joins. You'll use a union query to create a master list of values to join on and use this query to populate a derived table subquery. You'll join other worksheets to the results of the subquery to produce a side-by-side merged set of results.
Posted by
Andrew Gould
on 07 July 2021
This video demonstrates how to use derived table subqueries to consolidate multiple worksheets into a single list using the Group By clause and aggregation functions. You'll learn how to nest a Select statement in the From clause of an outer Select statement and how to reference columns from the subquery in the main query. You'll also learn how to apply the Group By clause to the rows returned by the subquery and how to add aggregated values, including grand totals.
Posted by
Andrew Gould
on 10 July 2021
This video explains how to nest one Select statement inside another Select statement to create a subquery. You'll learn how to use subqueries to return different levels of aggregation in the same Select statement and use this to compare rows against the average value of a dataset and to calculate the contribution of one row to the total. You'll see how to add criteria to a subqery and also how to use a subquery to set criteria for the main query. You'll also learn how to handle multiple rows returned by a subquery using the IN, ALL and ANY keywords
Posted by
Andrew Gould
on 10 July 2021
This video shows you how to nest multiple levels of query to answer more complicated questions about your data. You'll build on what you learnt in the previous lesson on basic subqueries to nest queries up to four levels (and up to fifty if you really want to!).
Posted by
Andrew Gould
on 11 July 2021
This video explains how to use correlated subqueries to create a variety of useful techniques such as ranking values in a column, calculating running totals and creating year to date totals.
Posted by
Andrew Gould
on 16 July 2021
This video teaches you how to use INSERT INTO statements in SQL to insert data into an existing Excel worksheet. You'll learn how to insert a single row of specific values, how to insert rows selected from a different worksheet and how to insert rows from a different Excel file. You'll also see how to use outer joins to avoid inserting duplicate rows and how to use union select statements to insert rows from multiple worksheets, ranges and workbooks at the same time.
Posted by
Andrew Gould
on 19 July 2021
This video teaches you how to use SELECT INTO statements in SQL to insert data into a new Excel worksheet. You'll learn how to insert a data into a new worksheet in the same workbook as the selected data, how to insert into a new worksheet in a different existing workbook and how to insert data into a new worksheet in a new workbook.
Posted by
Andrew Gould
on 21 July 2021
This video explains how to update existing data in an Excel workbook using SQL UPDATE statements. You'll learn how to assign a simple value to a column, how to calculate a new value to produce a different result for each row, and how to update information in one worksheet using information stored in a different worksheet. The final part of the video discusses how to import data from a separate workbook using a SELECT INTO statement and then use the imported data to update existing data.
Posted by
Andrew Gould
on 25 July 2021
This video shows you how to split a single Eacel table into multiple worksheets using the values in a column of the table. You'll learn how to create a recordset and populate it with unique values from a column and how to create a SELECT INTO command to create new worksheets. You'll see how to create new worksheets in the same workbook as the source data, in a single different workbook and how to create a separate workbook for each value in the column.
Posted by
Andrew Gould
on 03 April 2021
Learn how to pass Japanese characters from an Excel worksheet into a SQL Server stored procedure using the ADODB library in VBA. You'll learn the basics of character encoding systems, how to use the varchar and nvarchar data types in SQL Server, how to execute stored procedures from VBA and how to use the merge statement in SQL. As a bonus, you'll also learn how to write the characters for dogs, cats, puppies and kittens in Japanese!
Posted by
Andrew Gould
on 08 April 2021
One way to get data from a closed Excel workbook is to open it first, but did you know that in VBA you can connect to a workbook and extract data from it without needing to open the file? This video explains how to do this using the ActiveX Data Objects library. You'll learn how to create a connection object and construct a connection string to talk to the closed Excel file. You'll also see how to create a recordset object and load a set of data into it using an SQL Select statement. As a bonus, the video shows you how to add some basic criteria to the SQL query to control which rows of data you return from the workbook.
Posted by
Andrew Gould
on 09 April 2021
Looping through the collection of worksheets in a workbook is a standard technique in Excel VBA and easy to accomplish when the workbook is open. Did you know that you can do the same thing when the workbook is closed? This video explains how to do this using the ActiveX Data Objects library. You'll learn how to create a connection object and construct a connection string to talk to the closed Excel file. You'll learn how to query the schema of the workbook to return a list of the worksheets and loop through this list to read the name of each sheet. You'll also learn how to create a recordset object and populate it with data using an SQL Select statement. As a bonus you'll learn about the SQL Union All statement to select data from multiple tables at the same time and how to add criteria to a query using the Where clause.
Posted by
Andrew Gould
on 12 April 2021
One way to get data from closed Excel workbooks is to open each file before extracting the information, but did you know that in VBA you can connect to a workbook and extract data from it without needing to open the file? This video explains how to do this using the ActiveX Data Objects library. The video shows how to loop through a collection of Excel files in a folder. You'll learn how to create a connection object and construct a connection string to talk to each closed Excel file. You'll also see how to create a recordset object and load a set of data into it using an SQL Select statement. As a bonus, the video shows you how to add some basic criteria to the SQL query to control which rows of data you return from the closed workbooks.
Posted by
Andrew Gould
on 13 April 2021
One way to get data from closed Excel workbooks is to open each file before extracting the information, but did you know that in VBA you can connect to a workbook and extract data from it without needing to open the file? Even better, if the files you're connecting to have the same structure, you can write a single SQL SELECT statement to select the contents of all the files in one go! This video explains how to do this using the ActiveX Data Objects library. The video shows how to loop through a collection of Excel files in a folder. You'll learn how to create a connection object and build a SQL UNION ALL query to get the data from each closed Excel file. You'll also see how to create a recordset object and load a set of data into it using the SQL statement that you've constructed. As a bonus, the video shows you how to add some basic criteria to the SQL query to control which rows of data you return from the closed workbooks.
Posted by
Andrew Gould
on 14 April 2021
Looping through the collection of worksheets in a workbook is a standard technique in Excel VBA and easy to accomplish when the workbook is open. Did you know that you can do the same thing when the workbook is closed? This video explains how to do this using the ActiveX Data Objects library. You'll learn how to loop through the Excel files in a folder, creating a connection to each file using an ActiveX Data Objects Connection object. You'll learn how to query the schema of each workbook to return a list of the worksheets and loop through this list to read the name of each sheet. You'll also learn how to create a recordset object and populate it with data using an SQL Select statement. As a bonus you'll learn about the SQL Union All statment to select data from multiple tables at the same time and how to add criteria to a query using the Where clause.
Posted by
Andrew Gould
on 21 April 2021
This video shows you how to extract the results of an ADO recordset using the GetRows method and use it to populate a Listbox on a user form in VBA. You'll learn about ActiveX Data Objects connections and recordsets, how to add values to a listbox using both the AddItem method and the List property and how to transpose an array in VBA.
Posted by
Andrew Gould
on 26 April 2021
When you copy data from a recordset into an Excel worksheet you don't get the column headings by default. This video shows you two ways to loop through the Fields collection and write out the Name property of each Field object into the worksheet.
Posted by
Andrew Gould
on 28 April 2021
Learn how to extract the contents of a CSV file into an Excel workbook without opening the CSV file. You'll learn how to use ActiveX Data Objects connections and recordsets to connect to a closed file and how to write basic SQL queries to return the information from the file.
Posted by
Andrew Gould
on 30 April 2021
When you're extracting information from multiple files using ADO it can be helpful to include the filename for each row of data. This video shows you how to include a new column in the select list of your query so that you can identify which file a row of data belongs to.
Posted by
Andrew Gould
on 07 May 2021
This video explains how to use the GetRows method of an ADODB recordset to write data into an array.
Posted by
Andrew Gould
on 10 May 2021
This video explains how to use the RecordCount property of an ADODB recordset to find out how many rows your query has returned. You'll learn how to set the cursor type and the basic differences between ForwardOnly, Static, Keyset and Dynamic cursors.
Posted by
Andrew Gould
on 14 May 2021
This video explains how to check the Type property of a field in an ADODB recordset to determine its data type. You'll learn about the ADODB Data Type enumeration and how to translate the code numbers into meaningful descriptions. You'll also learn how to apply specific date or number formatting to a column based on its data type. The video also covers a bug with the CopyFromRecordset method which results in cells on the wrong worksheet being formatted as dates when you insert new worksheets.
Posted by
Andrew Gould
on 08 August 2021
This video explains what to do if your Excel column name contains a dot and you need to reference it in an SQL query using ADODB. As an added bonus you'll also learn how to deal with exclamation mark characters!
Posted by
Andrew Gould
on 23 December 2021
This video shows you how to use a schema.ini file to control the import of text files using Active Data Objects in Excel VBA. You'll learn how to use the schema file to change the delimiter from commas to tabs, how to specify column names and data types and how to control the detection of date formats
Posted by
Andrew Gould
on 04 January 2022
This video show you how to apply sorting to all the Excel tables in a workbook. You'll learn how to loop through the ListObjects collection on each worksheet in the workbook and how to use the Sort and SortField objects to apply sorting to each table. You'll also see how to use data validation to create a basic user interface which allows the user to control which column to sort the tables by.
Posted by
Andrew Gould
on 29 July 2022
This video explains how to get around the 255 column limit when importing data using ADODB in Excel VBA.
