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

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.

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!