Create Table with DAO and make records

Logic: CreateTable_Numberz

Set up the error handler and dimension variables. Set database object to current database Use CreateTableDef to create a new table

Create a table to store numbers with CreateTableDef. Make a field called Num using CreateField. Add a field description using CreateProperty. Append the field to the table's field collection. Append the table to the database's table collection.

Create an index named 'PrimaryKey'. Append a field to the index, and set the Primary property to true. Append the index to the table.

Make the table show up in the Navigation Pane using RefreshDatabaseWindow.

Give the user a message when it is done.

Logic: MakeRecords_Numberz

Set up the error handler and dimension variables. If there is not a table called Numberz, then give the user an error message and exit.

Dimension the database variable and open a recordset for the Numberz table.

Loop from the specified (or default) start and stop numbers, and add a record for the value of the loop counter (i). If the value already exists, Err.Number will be 3022 so the next statement will be executed.

Give the user a message when it is done.

Parameters

Optional

pnStart is the number to start with (default=1)
pnStop is the number to end with (default=1440)

Logic: DoesTableExist

Return True if table exists and False if it does not. Called by CreateTable_Numberz.

Dimension a string and resume the next statement if there is an error.

See if a string can be assigned to the name property of the specified table. If there is an error, then the table does not exist.

Parameter

psTablename is the name of the table to check for existence

Code

' Module Name: mod_CreateTable_Numberz
'*************** Code Start *****************************************************
' Purpose  : create a table called Numberz withb long Num that is PK   
' Author   : crystal (strive4peace) 
' License  : below code
' Code List: www.MsAccessGurus.com/code.htm
'--------------------------------------------------------------------------------
'                              CreateTable_Numberz'--------------------------------------------------------------------------------
'
Sub CreateTable_Numberz()
'... 181221 strive4peace, consider all
'
'create a table to store sequential numbers
   'CREATE table:
   '  Numberz
   '     field: Num, Long, PK
   '
   ' CALLED BY
   '  MakeRecords_Numberz, if necessary
   '
   'set up error handler
   On Error GoTo Proc_Err
   
   'dimension variables
   Dim db As DAO.Database _
      , tdf As DAO.TableDef _
      , fld As DAO.Field _
      , idx As DAO.Index

  'set database object to current database
   Set db = CurrentDb
   
   'create a new table
   Set tdf = db.CreateTableDef("Numberz")
   
   'create a field
   Set fld = tdf.CreateField("Num", dbLong)
   
   'set the description to the field name
   fld.CreateProperty "Description", dbText, "Num"
   
   'append the field to the table
   tdf.Fields.Append fld
   
   'append the table to the collection of tables
   db.TableDefs.Append tdf
   
   'create an index and name it Primary Key
   Set idx = tdf.CreateIndex("PrimaryKey")
   
   With idx
      'add the Num field to the index
      .Fields.Append idx.CreateField("Num")
      'set this to BE the Primary index
      .Primary = True
   End With
   
   'append the index to the table
   tdf.Indexes.Append idx
   
   'refresh the tables in the database
   db.TableDefs.Refresh      
    
   'make the table show up in the Navigation Pane ...
   Application.RefreshDatabaseWindow
   
   '... do it now
   DoEvents
   
   MsgBox "Done creating Numberz table", , "Done"   
   
Proc_Exit:
   On Error Resume Next
   'release object variables
   Set idx = Nothing
   Set fld = Nothing
   Set tdf = Nothing
   Set db = Nothing
   Exit Sub
  
Proc_Err:
   'error handler
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   CreateTable_Numberz "
   Resume Proc_Exit
   Resume
End Sub

'--------------------------------------------------------------------------------
'                              MakeRecords_Numberz'--------------------------------------------------------------------------------
'
Sub MakeRecords_Numberz( _
   Optional pnStart As Long = 1 _
   , Optional pnStop As Long = 1440 _
   )
'... 181221 strive4peace
'make records in a table to store sequential numbers
' tablename --> Numberz
' fieldname --> Num (Primary Key, Long Integer, no Default Value)
' if the Numberz table does not exist, it will be created
   
' use this table to to get all records when data is missing
'  for instance, to construct particular years, months, days, ...
'                to print a copies of a report
'                to find missing Numberz
'you can also use a Numberz table to print out copies of a report and more
'
   ' click HERE
   ' press F5 to Run
      '~~~~~~~~~~~~~~~~~~
      'NEEDS reference to Microsoft DAO Library
      'or
      'Microsoft Office ##.0 Access Database Engine Object Library
      '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   'CALLS
   '  DoesTableExist_Numberz
   '
   'PARAMETER
   ' every now and then, you might need to run this again
   ' to change the max number created
   '  pnStop is the last number to create
   '     1440 is the number of minutes in one day   

   On Error GoTo Proc_Err
   
   Dim db As DAO.Database _
      , rs As DAO.Recordset
      
   Dim i As Long _
      , iCount As Long     
      
   '190316 if Numberz table doesn't exist, create it
   If Not DoesTableExist("Numberz") Then
      Call CreateTable_Numberz          
   End If
   
   Set db = CurrentDb
   Set rs = db.OpenRecordset("Numberz", dbOpenDynaset)

   iCount = 0   
   With rs
      For i = pnStart To pnStop
         .AddNew
         !num = i
         .Update
         iCount = iCount + 1
      Next i
   End With 'rs
   
   MsgBox "Done creating " & iCount & " records in Numberz", , "Done"
 
Proc_Exit:
   On Error Resume Next
   If Not rs Is Nothing Then
      rs.Close
   Set rs = Nothing
   End If
   Set db = Nothing
   Exit Sub
  
Proc_Err:
   If Err.Number = 3022 Then
      'value already exists -- skip it
      iCount = iCount - 1
      Resume Next
   End If
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   CreateNumberzRecords"

   Resume Proc_Exit
   Resume
End Sub
'--------------------------------------------------------------------------------
'                              MakeRecords_Numberz_launch'--------------------------------------------------------------------------------
'
Sub MakeRecords_Numberz_launch()
'   CLICK HERE and press F5 to Run!
   Call MakeRecords_Numberz
End Sub
'--------------------------------------------------------------------------------
'                              DoesTableExist'--------------------------------------------------------------------------------
'
Function DoesTableExist(psTablename As String) As Boolean
'strive4peace
'return True if table exists in the current database,
'  or false if it does not
'
'  PARAMETER
'     psTablename is the name of the table to check for existence of
'
   ' dimension variable
   Dim sName As String
   
   ' don't stop code if an error happens
   On Error Resume Next
   
   ' see if the table is there
   sName = CurrentDb.TableDefs(psTablename).Name
   
   ' if tablename couldn't be assigned, it does not exist
   DoesTableExist = IIf(Err.Number > 0, False, True)

End Function
'
' LICENSE
'   You may freely use and share this code 
'     provided this license notice and comment lines are not changed;
'     code may be modified provided you clearly note your changes.
'   You may not sell this code alone, or as part of a collection,
'     without my handwritten permission.
'   All ownership rights reserved. Use at your own risk.
'   ~ crystal (strive4peace)  www.MsAccessGurus.com
'*************** Code End *******************************************************

Scenarios

-- Make multiple copies of a report

What do you do when you need three (3) copies of something? Easy with a numbers table.

In the record source for your report (or display form), add a numbers table with criteria under the field with the number for specifying 1 to 3 copies ... whatever you want -- maybe an expression to calculate it.

Don't make any link lines to the numbers table — let it free-float, so to speak; not connected to anything. Whatever rows your query gets will be tripled for 3.

Cartesian Query

A cartesian query is when data sets are included without a join. This causes a multiplication of records. If A has 100 records and B has 3 records, then a cartesian of A and B is 100*3=300 records in the resulting data set.

You could then apply criteria and sort.

HowTo

Make a query to get what you want to see. Add the numbers table and these 2 columns. Customize names and expressions as it suits you.

Note the calculated field using the built-in Choose function to get different text for each of the 3 copies of the information.

TitleOfCopy: Choose([Num],"Shop","Office","Customer")

Here is the designer view and the results:

DAO_CreateTable_qNumberz_Choose_MyTitle

-- Find Missing Data

You can use a numbers table to find where data is missing by constructing values and joining in your other data.

The query screenshot below creates dates (expression below) and shows, day-by-day, the status of records for the month of December 2018, to see if records are missing.

When the day of the week is displayed, we see it is no surprise that data is missing for Saturdays and Sundays since the shop is not normally open. On December 8, which was a Saturday, the status does not say 'Missing', so there must be one or more records. That might bear exploring.

Missing records for days that are not the weekend

It is not suprising that today, Friday, December 21, does not have records because the data has not yet been transferred from the collection systems.

However, there are also no records for December 12, which was a Wednesday. Business was done that day, so data is probably missing, and needs to be entered or converted.

DAO_CreateTable_qNumberz_CreateDates_FindMissing

Calculated Field

TheDate was calculated using this alias and expression:

TheDate: DateSerial(Year(Date()),Month(Date()),[num])

Criteria

To get all the days in the current month up to today (whatever that is) we want 1 (one) to whatever is the day number of the month. Criteria under Num (from the Numberz table) is:

Between 1 And Day(Date())

If you absolutely know the records in the numbers table start at 1, you could simply use:

<=Day(Date())

References

There are a lot of references for this example! Even though a simple table with only one field is created, there is a primary key to avoid duplicates and provide a unique index, and then records are made.

This simple set of actions brings in a lot of basic knowledge about how objects are created and stored. Spending time with these links will give you a solid foundation for Access and information management that you can build strong structures upon.

DoesTableExist, the helper function to determine if a table does indeed already exist in the current database, should be a page on its own.

Allen Browne's
DAO Programming Code Examples

Allen Browne's pages are gold for every Access developer. He documented the basics of using DAO so very well, and his site is a treasure trove of knowledge.

To build and modify Access objects, you must understand them. This page will be a good reference again and again. Here you will gain understanding about how to create Access objects-- whether they be tables, fields, queries, or other.
DAO Programming Code Examples

According to Allen,

"DAO (Data Access Objects) is the native library Microsoft designed to expose the object in Access".

Docs / Office VBA Reference / Language reference / Reference / Functions / Choose

Help: Choose function

Docs / Office client development / Access / Access desktop database reference / Microsoft Data Access Objects reference / Database / Methods / CreateTableDef method

Help: Database.CreateTableDef method (DAO)

Docs / Office client development / Access / Access desktop database reference / Microsoft Data Access Objects reference / TableDef / Methods / CreateField method

Help: TableDef.CreateField method (DAO)

Docs / Office client development / Access / Access desktop database reference / Microsoft Data Access Objects reference / Field / Methods / CreateProperty method

Help: Field.CreateProperty method (DAO)

threw this in for good measure:

Docs / Office client development / Access / Access desktop database reference / Microsoft Data Access Objects reference / TableDef / Methods / CreateProperty method

Help: TableDef.CreateProperty method (DAO)

Docs / Office client development / Access / Access desktop database reference / Microsoft Data Access Objects reference / Fields / Methods / Append method

Help: Fields.Append method (DAO)

Docs / Office client development / Access / Access desktop database reference / Microsoft Data Access Objects reference / TableDefs / Methods / Append method

Help: TableDefs.Append method (DAO)

Docs / Office client development / Access / Access desktop database reference / Microsoft Data Access Objects reference / TableDefs / Methods / CreateIndex method

Help: TableDef.CreateIndex method (DAO)

Docs / Office client development / Access / Access desktop database reference / Microsoft Data Access Objects reference / Index / Properties / Primary property

Help: Index.Primary property (DAO)

Docs / Office client development / Access / Access desktop database reference / Microsoft Data Access Objects reference / Indexes / Methods / Append method

Help: Indexes.Append method (DAO)

Docs / Office VBA Reference / Access / Object model / Application object / Methods / RefreshDatabaseWindow

Help: Application.RefreshDatabaseWindow method (Access)

Docs / Office client development / Access / Access desktop database reference / Microsoft Data Access Objects reference / Database / Methods / OpenRecordset method

Help: Database.OpenRecordset method (DAO)

Docs / Office client development / Access / Access desktop database reference / Microsoft Data Access Objects reference / TableDefs / Properties / Name property

Help: TableDef.Name property (DAO)

More Notes

Numberz table

Why not name this table 'tbl_Numbers' instead of 'Numberz'? You can if you want. You can name it anything you like. I use Numberz because it is short, easy to remember, and spelled with "z" on the end instead of "s" to avoid any possible conflict with reserved words.

How can you learn what to avoid? This is a reason to use naming conventions, or at least use them for ideas. There are a few standards such as Leszynski/Reddick. In practice, do what you are comfortable with and will remember.

Allen Browne has an excellent page to look up bad names and reserved words that, as a general rule, you shouldn't use when you create names ... table names, field names, calculated field names, procedure names, ....

I use Allen's page a lot to look up common words that might have a conflict. The general rule I use is to avoid common words is to spell them wrong (in a way I can remember) ... hence the "z" on the end of "Numberz" ~

Problem names and reserved words in Access by Allen Browne

Access can have problems when you name objects something it might interpet differently. For instance, almost every object has a "Name" property -- so you should not use 'Name' for a name that you create. Another common error is using "Date" for a field name. Date() is a built-in function, so there could be confusion.

There are more than 2,500 terms you should not use for names, for one reason or another, which Allen lists for you. There is a handy alphabet line at the top so you can quickly jump to a letter, which is nice since there are so many items in the list! There is also a useful download tool you can run on any database to check for bad words and more.

http://allenbrowne.com/AppIssueBadWord.html

Allen's DbIssueChecker

On this link, you will also find a database issue checker utility you can download (also free) to check any existing Access database for violation of rules. There are several tests you can run.

Bad name
Bad characters
No primary key
Record too wide

... and then once those things are fixed, you may want to check other issues. I don't agree with all the tests being issues — some depend on your situation. For instance, if the data is in Access, zero-length strings (ZLS, "") are okay, and perhaps even preferred, so you can see if the user really filled a value.

Analyzer

While you are developing, here is a free utility you can use to document the structure of your database: https://github.com/strive4peace/Analyzer

Screen shot

Examples

Call CreateTable_Numberz()

Call MakeRecords_Numberz()