Posts Categorized: DATABASE

Over 100 Tips for Faster Microsoft Access Databases

This paper outlines specific performance tips you can use to improve the speed of your Microsoft Access applications. These tips apply to all versions of Access including Access 2016, 2013, 2010, 2007, …

Summary

Sometimes, you may have to perform maintenance tasks on a Microsoft Access database, such as compacting or repairing, making backup copies, or making design modifications. Many of these operations require that all users exit the database. However, there is …

Occasionally we come across project requirements that include the ability to do sequential numbering in Access on a set of data. We cannot just use the auto-number data type because there is no promise of sequentiality but only uniqueness. In …

Put in where condition as below:

Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String

   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"

   Set conn = New ADODB.Connection
   conn.Open 

1. Combo box : control souce -unique column, but set first column to the column you want to show(Other Column)
2. Other column: set hide control and set the value when combo box change

One of the most peculiar aspects of Visual Basic for Applications in the ! operator, called the “bang”. There’s a lot of confusion about the bang operator, and nowhere have I seen the correct whole story about it. So, I …

SELECT StudentID, Year, Subject,  AVG(TestScore) AS AvgScore
  FROM
(
  SELECT StudentID, Year, Subject, TestScore
   FROM MyTable t
   WHERE TestID IN
  (
   SELECT TOP 3 TestID 
     FROM MyTable
    WHERE StudentID = t.StudentID
      AND Year = t.Year
      AND Subject = t.Subject
    ORDER