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.
[sourcecode language=”vb”]
‘========================================================
‘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
[/sourcecode]
You can download the database file, by clicking here.






