title: Slow database query: Using VB.net connecting to Access
body: <p>I have a program which will only allow a certain amount of concurrent users to use the program at one time. To do this I have a single table in an access database which holds each user that is using the program. Now although this does work the query seems to be running very slowly, I am certain it is something to do with the database functions as it was running fine before I implemented them. </p>
  
  <p>Here are my functions:</p>
  
  <pre><code>Public Function openDB() As Boolean
      cnn.Open()
      Return True
  End Function
  
  Public Function closeDB() As Boolean
      cnn.Close()
      Return True
  End Function
  </code></pre>
  
  <p>Then there is the function for checking the database. This is where I think it may be tripping up because I have 2 queries running here:</p>
  
  <pre><code>Public Function CheckLicence() As Boolean
      Dim result As Boolean = HandleRegistry()
      If result = True Then
          Dim _table As String = "Users"
          Dim query As String = "SELECT * FROM " &amp; _table &amp; " WHERE Machine_ID='" &amp; CpuId() &amp; "'"
          Dim sizeQuery As String = "SELECT COUNT(*) FROM " &amp; _table
          Dim NoUsers As Integer = 0
          Dim ds As New DataSet
  
          Dim dr As OleDbDataReader
          Dim cmd As New OleDbCommand(query, cnn)
          dr = cmd.ExecuteReader
  
  
          Dim sizeCdm As New OleDbCommand(sizeQuery, cnn)
          NoUsers = sizeCdm.ExecuteScalar()
  
          If dr.Read() Then
              Return True
          Else
              If NoUsers &lt; My.Settings.NoUsers Then
  
                  addToDB()
              Else
                  MsgBox("Too many users are currently using this program. Clear a user and try again.")
  
                  Return False
              End If
          End If
  
      Else
          MsgBox("Your licence has expired, contact support to purchase a new licence.")
          Return False
      End If
      Return True
  End Function
  </code></pre>
  
  <p>And to add and remove I have to get the cpu id, I found the code for that on here somewhere it does work but maybe that could be the slow part, I dont actually know if this is the correct way of getting it.</p>
  
  <pre><code> Public Sub addToDB()
      Dim _table As String = "Users"
      Dim query As String = "INSERT INTO " &amp; _table &amp; " ([User], [Machine_ID]) VALUES (?,?)"
      Dim ds As New DataSet
  
      Dim cmd As New OleDbCommand(query, cnn)
      cmd.Parameters.AddWithValue("?", Environment.UserName)
      cmd.Parameters.AddWithValue("?", CpuId())
      cmd.ExecuteNonQuery()
  
  
  End Sub
  Public Sub RemoveFromDB()
      Dim _table As String = "Users"
  
      Dim query As String = "DELETE * FROM " &amp; _table &amp; " WHERE Machine_ID='" &amp; CpuId() &amp; "'"
      Dim ds As New DataSet
  
      Dim cmd As New OleDbCommand(query, cnn)
      cmd.ExecuteNonQuery()
  
  End Sub
  Private Function CpuId() As String
      Dim computer As String = "."
      Dim wmi As Object = GetObject("winmgmts:" &amp;
          "{impersonationLevel=impersonate}!\\" &amp;
          computer &amp; "\root\cimv2")
      Dim processors As Object = wmi.ExecQuery("Select * from " &amp;
          "Win32_Processor")
  
      Dim cpu_ids As String = ""
      For Each cpu As Object In processors
          cpu_ids = cpu_ids &amp; ", " &amp; cpu.ProcessorId
      Next cpu
      If cpu_ids.Length &gt; 0 Then cpu_ids =
          cpu_ids.Substring(2)
  
      Return cpu_ids
  End Function
  </code></pre>