hello,i have problem with running really slow when it is retreaving records from table to display only the records I need. I am using access (.mdb) for my database and I use ADO to connect to the database on visual basic .most of the time my computer will hang when i retreaving data and my computer will become extremely slow until i need to restart computer.Does anyone know how to go about making data retreaval more efficient or faster, either through tweaking my code or tweaking the db itself using MS Access ?please help Thanks!


VB Code:
  1. Private m_rs As ADODB.Recordset  ' Recordset tool

  2.  
  3. Private Sub CmdAdd_Click(Index As Integer)

  4.     On Error Resume Next

  5.     With m_rs

  6.         .Fields("Location") = ComboLocation.Text

  7.         .Fields("DatePurchase") = DTPpurchase

  8.         .Fields("Item") = ComboItem.Text

  9.         .Fields("Detail") = Txtitem2.Text

  10.         .Fields("IDNumber") = Txtidnumber.Text

  11.         .Fields("Price") = Txtprice.Text

  12.         .Fields("Status") = Combostatus.Text

  13.         .Fields("DateScrap") = DTPScrap

  14.         .Update

  15.         Call clear

  16.         MsgBox "Record Saved", vbInformation, "Record is Saved"

  17.         'End If

  18.     End With

  19. End Sub

  20. 'Delete Record

  21. Private Sub cmddelete_Click(Index As Integer)

  22.     On Error Resume Next

  23.     With m_rs

  24.         .Fields("Location") = ComboLocation.Text

  25.         .Fields("DatePurchase") = DTPpurchase

  26.         .Fields("Item") = ComboItem.Text

  27.         .Fields("Detail") = Txtitem2.Text

  28.         .Fields("IDNumber") = Txtidnumber.Text

  29.         .Fields("Price") = Txtprice.Text

  30.         .Fields("Status") = Combostatus.Text

  31.         .Fields("DateScrap") = DTPScrap

  32.         .Fields("Deletedate") = Txtdeldate.Text

  33.         Call clear

  34.         If MsgBox("Are you sure you wan't to delete record", vbOKCancel + vbExclamation, "Deleting Record") = vbOK Then

  35.             .Update

  36.             Call display1

  37.         End If

  38.     End With

  39. End Sub

  40.  
  41. Private Sub Cmdnew_Click(Index As Integer)

  42.     On Error Resume Next

  43.     Call clear

  44.     m_rs.AddNew

  45.     Txtlocation.SetFocus

  46.  
  47. End Sub

  48. Private Sub ComboItem_Click()

  49. Dim imageName As String

  50.     Picture1.Picture = LoadPicture()

  51.     imageName = "D:\Tool Inventory v8.6\photo\" & ComboItem.Text & ".jpg"

  52.     If Dir$(imageName) <> "" Then

  53.         Picture1.Picture = LoadPicture(imageName)

  54.     Else

  55.         Picture1.Picture = LoadPicture()

  56.     End If

  57. End Sub

  58.  
  59. Private Sub Datagrid1_Click()

  60.  Dim imageName As String

  61.      DataGrid1.SetFocus

  62.     DataGrid1.MarqueeStyle = dbgHighlightRow

  63.     If Len(DataGrid1.Columns(1)) = 0 Then

  64.         DTPpurchase.Enabled = False

  65.     Else

  66.         DTPpurchase.Enabled = True

  67.         DTPpurchase.Value = DataGrid1.Columns(1)

  68.     End If

  69.     If Len(DataGrid1.Columns(7)) = 0 Then

  70.         DTPScrap.Enabled = False

  71.     Else

  72.         DTPScrap.Enabled = True

  73.         DTPScrap.Value = DataGrid1.Columns(7)

  74.     End If

  75.     ComboLocation.Text = DataGrid1.Columns(0)

  76.     ComboItem.Text = DataGrid1.Columns(2)

  77.     Txtitem2.Text = DataGrid1.Columns(3)

  78.     Txtidnumber.Text = DataGrid1.Columns(4)

  79.     Txtprice.Text = DataGrid1.Columns(5)

  80.     Combostatus.Text = DataGrid1.Columns(6)

  81.     DataGrid1.Refresh

  82.     

  83.     Picture1.Picture = LoadPicture()

  84.     imageName = App.Path & "\photo\" & ComboItem.Text & ".jpg"

  85.     If Dir$(imageName) <> "" Then

  86.         Picture1.Picture = LoadPicture(imageName)

  87.     Else

  88.         Picture1.Picture = LoadPicture()

  89.     End If

  90.  
  91. End Sub

  92. Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)

  93. 'row down

  94.   Dim imageName As String

  95.      DataGrid1.SetFocus

  96.     DataGrid1.MarqueeStyle = dbgHighlightRow

  97.     If Len(DataGrid1.Columns(1)) = 0 Then

  98.         DTPpurchase.Enabled = False

  99.     Else

  100.         DTPpurchase.Enabled = True

  101.         DTPpurchase.Value = DataGrid1.Columns(1)

  102.     End If

  103.     If Len(DataGrid1.Columns(7)) = 0 Then

  104.         DTPScrap.Enabled = False

  105.     Else

  106.         DTPScrap.Enabled = True

  107.         DTPScrap.Value = DataGrid1.Columns(7)

  108.     End If

  109.     ComboLocation.Text = DataGrid1.Columns(0)

  110.     ComboItem.Text = DataGrid1.Columns(2)

  111.     Txtitem2.Text = DataGrid1.Columns(3)

  112.     Txtidnumber.Text = DataGrid1.Columns(4)

  113.     Txtprice.Text = DataGrid1.Columns(5)

  114.     Combostatus.Text = DataGrid1.Columns(6)

  115.     DataGrid1.Refresh

  116.     

  117.     Picture1.Picture = LoadPicture()

  118.     imageName = App.Path & "\photo\" & ComboItem.Text & ".jpg"

  119.     If Dir$(imageName) <> "" Then

  120.         Picture1.Picture = LoadPicture(imageName)

  121.     Else

  122.         Picture1.Picture = LoadPicture()

  123.     End If

  124.  
  125.  
  126. End Sub

  127. Private Sub Form_Load()

  128.     Dim rs1 As ADODB.Recordset

  129.     Dim rs2 As ADODB.Recordset

  130.  
  131.     'sql query login user department

  132.    Set m_rs = OpenRecordset("select * from [Inventory]" & vbCrLf & _

  133.                             "where ([Location] in (" & vbCrLf & _

  134.                             "  select [DepartmentName] from [Department]" & vbCrLf & _

  135.                             "  where [Id] in (" & vbCrLf & _

  136.                             "    select [DepartmentID] from [UserDepartment]" & vbCrLf & _

  137.                             "    where [UserId] = '" & gUserId & "'" & vbCrLf & _

  138.                             "  )" & vbCrLf & _

  139.                             ") or exists(SELECT * FROM UserRoles WHERE [UserId] = " & gUserId & _

  140.                             " and [RoleId] in (select [Id] from [Roles] where [RoleName] = 'admin')))" & _

  141.                             " and Deletedate is null", adOpenStatic, adLockOptimistic)

  142.     Set DataGrid1.DataSource = m_rs

  143.     Debug.Print "Connection Object Created"

  144.     

  145.     'stutus list

  146.     Combostatus.AddItem "Active"

  147.     Combostatus.AddItem "Relocate"

  148.     Combostatus.AddItem "Spoilt"

  149.     

  150. 'If Not IsNull(m_rs!Location) Then

  151.  ' ComboLocation.Text = m_rs!Location

  152. 'End If

  153.  
  154. 'If Not IsNull(m_rs!Item) Then

  155.  ' ComboItem.Text = m_rs!Item

  156. 'End If

  157.  
  158. Set rs1 = OpenRecordset("select distinct Location From Inventory", adOpenStatic, adLockReadOnly)

  159.    While Not rs1.EOF

  160.             ComboLocation.AddItem rs1!Location

  161.             rs1.MoveNext

  162.         Wend

  163.         rs1.Close

  164. Set rs1 = Nothing

  165.  
  166. Set rs2 = OpenRecordset("select distinct Item From Inventory", adOpenStatic, adLockReadOnly)

  167.    While Not rs2.EOF

  168.             ComboItem.AddItem rs2!Item

  169.             rs2.MoveNext

  170.         Wend

  171.         rs2.Close

  172. Set rs2 = Nothing

  173.     

  174.     'Text1.Text = "Database Record " & "_" & m_rs.RecordCount

  175.     Txtdeldate.Text = Format(Now, "dd/mm/yyyy")

  176. End Sub

  177.  
  178. Private Sub CmdExit_Click()

  179.     Unload Me

  180. End Sub

  181. Private Sub cmdSave_Click()

  182.     ' This button will save the changes and additions

  183.     With m_rs

  184.         ' If our Textboxes not empty

  185.         If Txtlocation.Text <> "" And Txtdateofpurchase.Text <> "" _

  186.                 And Txtitem1.Text <> "" And Txtitem2.Text And Txidnumber.Text <> "" _

  187.                 And Txtprice.Text <> "" And Txstatus.Text <> "" And Txtdateofscrap.Text Then

  188.             ' Put the content of our textboxes in the current

  189.             ' recordset

  190.             .Fields("Location") = ComboLocation.Text

  191.             .Fields("Date Of Purchase") = Txtdateofpurchase.Text

  192.             .Fields("Item") = ComboItem.Text

  193.             .Fields("Detail") = Txtitem2.Text

  194.             .Fields("ID Number") = Txtidnumber.Text

  195.             .Fields("Price") = Txtprice.Text

  196.             .Fields("Status") = Txtstatus.Text

  197.             .Fields("Date Of Scrap") = Txtdateofscrap.Text

  198.             .Update

  199.         End If

  200.     End With

  201.     ' Ready, now we're able to change our data

  202. End Sub