Training videos for learning VBA - WORKING WITH DATA

VBA - working with data - 10 videos

Excel VBA Part 30 - Querying a Database with ADO

Posted by Andrew Gould on 31 March 2014

How to write macros in VBA to work with SQL Server, Access and other tables

Excel VBA Part 31 - Modifying Data with ADO Recordsets

Posted by Andrew Gould on 01 April 2014

How to use VBA macros to amend records in SQL Server and Access tables

Excel VBA Part 32 - Executing SQL Commands with ADO

Posted by Andrew Gould on 02 April 2014

How to construct and manipulate command objects in ADO to talk to databases using VBA

Excel VBA Part 56.1 - Connect to SQL Server using ADO

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.

Excel VBA Part 56.2 - Get Data from SQL Server with ADO Recordsets

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.

Excel VBA Part 56.3 - Read and Execute SQL Query Files

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.

Excel VBA Part 56.4 - Return Multiple Result Sets from SQL Queries

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.

Excel VBA Part 56.5 - Basic ADO Commands with SQL Server

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.

Excel VBA Part 56.6 - ADO Commands and Parameters

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.

Excel VBA Part 56.7 - ADO Commands and Stored Procedures

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.

This page has 0 threads Add post