In this video you’ll find out how to create a table with SQL, and insert data into a table, with SQL insert syntax, with data from a dynamic array. Also how to update a table, with SQL update syntax, and select data from a table using ms access sql.

'========================================================
'BY LOEBLCOM SERVICES 2012
'ERIK LOEBL(713)409-7041
'EMAIL: erik@loeblcomservices.com
'WEB:  http://loeblcomservices.com
'========================================================


'========================================================
'frmLogin
'========================================================
Option Compare Database


Private Sub btnOK_Click()

    Dim strUser As String

    Dim strSQL As String
    Dim rst As Object
    Dim intCount As Integer
    
    strUser = Nz(Me.txtUser, 0)

    strSQL = "SELECT UserName FROM Users WHERE UserName='" & strUser & "'"
    
    Set rst = CurrentDb.OpenRecordset(strSQL)
    
    intCount = rst.RecordCount
    
    Select Case intCount
        Case 0
            MsgBox "incorrect credentials, try again"
        Case 1
            MsgBox "correct"
            Me.Visible = False
    End Select
    
End Sub

Private Sub lblRegister_Click()

    Dim strNewUser As String
    Dim strSQLInsert As String
    
    strNewUser = InputBox("Enter your name to register.")
    
    strSQLInsert = "INSERT INTO Users (UserName) VALUES ('" & strNewUser & "')"
    
    CurrentDb.Execute strSQLInsert
    
    MsgBox "Great! Now enter '" & strNewUser & "' in the user name box above."
    

End Sub


'========================================================
'frmEntries
'========================================================

'========================================================
'BY LOEBLCOM SERVICES 2012
'ERIK LOEBL(713)409-7041
'EMAIL: erik@loeblcomservices.com
'WEB:  http://loeblcomservices.com
'========================================================

Option Compare Database

Dim m_dblTotalScore As Double

Dim m_strUser As String
Dim m_intAnswers As Integer
Dim m_strAnswers() As String

Private Sub Form_Load()

    Me.btnCheck.Enabled = True
    m_strUser = Forms("frmLogin").Controls("txtUser")
    Me.lblNameBox.Caption = "Welcome " & m_strUser
    
    'create a new table
    CreateTable (m_strUser)
    
    m_intAnswers = 0
    
End Sub

Private Sub Form_Open(Cancel As Integer)

    DoCmd.OpenForm "frmLogin", , , , , acDialog

    Me.btnCheck.Enabled = True 'initialize this
    m_dblTotalScore = 0 'initialize this
    
End Sub

Private Sub btnCheck_Click()

    Dim dblResult As Double
    
    'check the entry then add it the
    If (IsNumeric(txtAnswer.Value)) Then
        dblResult = txtAnswer.Value
        
        m_dblTotalScore = m_dblTotalScore + dblResult
        
        'un-rem this to format the value as currency
        'Me.txtTotal = Format(m_dblTotalScore, "$##.00")
        Me.txtTotal = m_dblTotalScore
        
        'add the result to the listbox
        lstResults.ColumnCount = 1
        lstResults.ColumnHeads = True
        lstResults.ColumnWidths = "1in"
        If (lstResults.ListCount = 0) Then
            'add column entry
            lstResults.AddItem "Entry"
        End If
        
        'add answer and score to the array - 12/15/12
        ReDim Preserve m_strAnswers(m_intAnswers)
    
        lstResults.AddItem dblResult
        m_strAnswers(m_intAnswers) = dblResult
        
        m_intAnswers = m_intAnswers + 1
    
    Else
        MsgBox "Numbers only please."
        
    End If

End Sub


Private Sub btnEnd_Click()
  
    Dim strMsg As String
    
    Dim intAnswers As Integer
    Dim intAnswer As Integer
    Dim intEnd As Integer
    Dim strInsert() As String
    Dim strInsertSQL As String
    Dim strUpdateWagesSQL As String
    
    strMsg = "Are you sure you want to quit?"
    intEnd = MsgBox(strMsg, vbYesNo)
    
   
    Select Case intEnd
        Case vbYes
        
            'insert the values in the table
            intAnswers = UBound(m_strAnswers)
            
            For intAnswer = 0 To intAnswers
                'Debug.Print m_strAnswers(intAnswer)
                strInsert = Split(m_strAnswers(intAnswer), ";")
                
                strInsertSQL = "INSERT INTO " & m_strUser & " (Entry)" & _
                    " VALUES ('" & strInsert(0) & "')"
                Debug.Print strInsertSQL
                
                CurrentDb.Execute strInsertSQL
                
                'insert TotalWages Earned
                strUpdateWagesSQL = "UPDATE Users SET TotalWages =" & m_dblTotalScore & " WHERE UserName = '" & m_strUser & "'"
                
                CurrentDb.Execute strUpdateWagesSQL
                
            Next
            Me.txtAnswer.SetFocus
            Me.btnEnd.Enabled = False
            
            MsgBox "Thanks for working with us!"
        Case vbNo
            Exit Sub
            
    End Select
    
    
End Sub

Public Function CreateTable(strTableName As String) As Boolean
    Dim strCreateTable As String
    Dim strTableMonths As String
    Dim intCount As Integer
    
    Dim strValues As String
    
    On Error GoTo errHandler
    
    
    'CREATE A NEW ONE BASED ON THE UNIQUE VALUES IN THE TABLE..
    
    strCreateTable = "CREATE TABLE " & strTableName & " (Entry double)"
       
        
    If Right(strCreateTable, 1) = "," Then
        strCreateTable = Left(strCreateTable, Len(strCreateTable) - 1)
        strCreateTable = strCreateTable & ")"
    End If
    
    CurrentProject.Connection.Execute strCreateTable
    
    If Err.Number = 0 Then
        CreateTable = True
    End If
    
    
    Exit Function
errHandler:
    CreateTable = False
    MsgBox Err.Description
    
End Function


Private Sub Form_Close()
    DoCmd.Close acForm, "frmLogin"
End Sub

Private Sub btnClose_Click()
    DoCmd.Close acForm, "frmEntries"
End Sub

You can download the database file, by clicking here.