MS Access SQL

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