VBA - working with data videos | 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

This video has the following accompanying files:

File name Type Description
GenerateUpdatedMoviesDatabase.sql SQL query
Movies 2007.accdb Access database (new format)
Top Movies 2012 ADO Commands.xlsm Excel workbook with macros

Click to download a zipped copy of the above files.

There are no exercises for this video.

Making a  video bigger

You can increase the size of your video to make it fill the screen like this:

View full screen

Play your video (the icons shown won't appear until you do), then click on the full screen icon which appears as shown at its bottom right-hand corner.

 

When you've finished viewing a video in full screen mode, just press the Esc key to return to normal view.

Improving the quality of a video

To improve the quality of a video, first click on the Settings icon:

Settings icon

Make sure you're playing your video so that the icons shown appear, then click on this gear icon at the bottom right-hand corner.

 

Choose to change the video quality:

Video quality

Click on Quality as shown to bring up the submenu.

 

The higher the number you choose, the better will be your video quality (but the slower the connection speed):

Connection speed

Don't choose the HD option unless you have a fast enough connection speed to support it!

 

Is your Wise Owl speaking too slowly (or too quickly)?  You can also use the Settings menu above to change your playback speed.

This page has 2 threads Add post
09 Mar 20 at 12:26

This video shows how to use commandtext, whereas the previous video concentrates on recordsets.

Can you please explain the merits of using commandtexts over recordsets or is it just an alternative?

 

10 Mar 20 at 09:40

Hi, you can use Commands to execute SQL statements which don't return results to a Recordset, such as CREATE and DROP statements.

You can also use a Command to modify data without returning a Recordset which may give better performance, for example, using an UPDATE statement in a Command vs. retrieving a Recordset and altering its data.

I hope that helps!

10 Mar 20 at 09:48

Thanks for the explanation.

 

 

Andrew G  
11 Mar 20 at 07:11

You're welcome!

02 Jun 19 at 11:49

Dear Andrew,

I'm having a problem with this lesson

I'm using SQL Server 2016 (installed by your instractions) and in this video you're using SQL Server 2012.

The problem is ConnectionString. Examples from "https://www.connectionstrings.com/sql-server-2016/" doesn't work at all

Using ConnectionString from Excel data Connection does do some work but useless to add anydata to database. 

Here is my current ConnectionString "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=ma\new_sql2016;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MA;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Movies"

Would you please excuse my ignorance and drop a small hint how to fix this problem.

Sincerely yours

Alexander

 

03 Jun 19 at 07:33

Hi there, here's some basic code which will work with the new version of the Movies database:

Const SQLConStr As String = _
    "Provider=SQLNCLI11;Server=.\SQL2016;DataBase=Movies;Trusted_Connection=yes"
 
Sub ConnectToDB()
 
    Dim MoviesConn As ADODB.Connection
    Dim MoviesData As ADODB.Recordset
    Dim r As Range
    
    Set MoviesConn = New ADODB.Connection
    Set MoviesData = New ADODB.Recordset
    
    MoviesConn.ConnectionString = SQLConStr
    MoviesConn.Open
    
    On Error GoTo CloseConnection
    
    With MoviesData
       .ActiveConnection = MoviesConn
       .Source = "Film"
       .LockType = adLockOptimistic
       .CursorType = adOpenKeyset
       .Open
               
        On Error GoTo CloseRecordset
        
        Sheet1.Activate
        For Each r In Range("A3", Range("A3").End(xlDown))
        
            .AddNew
            .Fields("Title").Value = r.Offset(0, 1).Value
            .Fields("ReleaseDate").Value = r.Offset(0, 2).Value
            .Fields("RunTimeMinutes").Value = r.Offset(0, 3).Value
            .Update
            
        Next r
    End With
    
CloseRecordset:
    MoviesData.CancelUpdate
    MoviesData.Close
    
CloseConnection:
    MoviesConn.Close
    
    Set MoviesData = Nothing
    Set MoviesConn = Nothing
    
End Sub
 

I hope that helps!