Database Design – Video 8

In this part of the video series you’ll learn the following:

Database Design in Access
building tables in Access
field properties
field datatypes
relating tables in Access

<< Free Access Programming tutorial Video 7

Microsoft Access Form Design – Video 9>>

Now A Few Tidbits on ADO and SQL before your quiz:

SQL stands for “Structured Query Language”.

Databases like Access, MySQL, SQL Server, Oracle… (etc), understand the SQL language, there are some dialects in each of the platforms, but it essentially the same.

To select a field from a table using SQL, you will be using the following statement:

SELECT {fieldname} FROM {tablename}

The above statement can be executed in this raw form inside a query editor or something in the database platform that can interpret and run SQL statements.

ADO SQL

ADO on the other hand is done through is done in the VBA environment.

Here we are updating a recordset with ADO

Sub UpdateLastLogin()
    Dim dteLogin As Date
    Dim rst As New ADODB.Recordset
    Dim cnn As ADODB.Connection
    
    Set cnn = CurrentProject.Connection
    
    dteLogin = Now()
    
    'use these recordset attributes or you'll _
        get the "Current Recordset does not support updating" error msg _
            >>> adOpenStatic, adLockOptimistic, adCmdText
    
    rst.Open "SELECT Login FROM Employees", cnn, adOpenStatic, adLockOptimistic, adCmdText

    'example only: You should probably just use this on the user _
        that just logged in (Environ("UserName")), and not everyone!
    Do Until rst.EOF

        'reference the recordset field with the exclamation (bang) symbol
        rst!Login = dteLogin  'this field is a Data/Time datatype
        rst.Update
        
        rst.MoveNext
    Loop
    
    'garbage cleanup
    rst.Close
    Set rst = Nothing
    
    cnn.Close
    Set cnn = Nothing
    
End Sub

That brings us to our final example folks. The SQL DELETE statement:

Sub DeleteMe()
    Dim strSQL As String
    Dim cnn As ADODB.Connection
    
    Set cnn = CurrentProject.Connection
    
    strSQL = "DELETE * FROM Employees WHERE EmployeeID= 11"
    
    'Execute the ADO/SQL statement
    cnn.Execute strSQL
    
    'garbage cleanup
    cnn.Close
    Set cnn = Nothing
    
End Sub



Check for understanding – Video 8


Offsite Related Information:

“Go Fund Me” Page


($5 suggested amount)

(…10% of your gift amount will go to charity)

Free! Subscribe To Our YouTube Channel!

Free MS Access VBA Programming Course

Facebooktwitterredditpinterestlinkedinmailby feather
Tags: , , , , , , , , , ,