ACC1x: How to Create a Table with Access Basic Code

• Microsoft Access versions 1.0, 1.1

SUMMARY

There is no command in Microsoft Access to programmatically create a table. This article demonstrates a method and a user-defined Access Basic function you can use to accomplish this task. The user-defined function requires a "template table" that you can create.

MORE INFORMATION

Create the template table before you create the user-defined function. The template table is used to define the data types that will be used in the new table.

For example, in the template table below, the user has created a data type called Long Text, which is a 255-character Text field. The user has also created data types called Short Text, Long Int, and so on.

   Table Name: MyTemplate
   ----------------------

   Field Name     Field Type     Length
   ------------------------------------

   Long Text      Text           255
   Short Text     Text           50
   Long Int       Number         Long Integer
   Integer        Number         Integer
   Memo           Memo           N/A
   Date/Time      Date/Time      N/A
   OLE Object     OLE Object     N/A

Definition

CreateTable <target table>, <template table>, <structure definition>

 Where:

    <target table> is the name of the table to create and MUST NOT exist
    in the database. You may want to code a routine to check for the
    existence of such a table before invoking this procedure.

    <template table> contains the user-defined data types that will be
    used in creating the target table.

    <structure definition> defines the structure of the columns in the
    table.

 Where:

    <structure definition> = "<field name> As <user-defined data
    type>,..."

Below is an example of how you would invoke the CreateTable procedure to create a table based on the template table discussed earlier:

   CreateTable "NewTable",
               "MyTemplate",
               "First Name As Short Text,
                Last Name As Short Text,
                Description As Long Text,
                Amount As Integer,
                Notes As Memo"

How CreateTable Works

As mentioned earlier, there is no Access Basic command or function to create a table. Fortunately, Microsoft Access supports the SQL SELECT INTO command that can be used to create a table. However, SELECT INTO requires a table for its FROM clause, which explains why you need to have a template table.

CreateTable parses the structure definition, then builds a SELECT INTO statement based on that definition. Once the SQL statement is built, the QueryDef object is employed to invoke the command, resulting in a new table.

Procedure Listing

NOTE: In the following sample code, an underscore (_) is used as a line continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

   Sub CreateTable (TargetTbl As String, TemplateTbl As String,
      StructureDef As String)

      Dim LineChunk As String, SelectStmt As String, TempChunk As String
      Dim CharPos As Integer, HomePos As Integer, BuildLoop As Integer
      Dim CrtTblDB As Database, CrtTblQry As QueryDef

      BuildLoop = True
      HomePos = 1
      SelectStmt = "Select "

      Do While BuildLoop
         If InStr(HomePos, StructureDef, ",") <> 0 Then
            LineChunk = Trim(Mid$(StructureDef, HomePos, (InStr(HomePos,_
                        StructureDef, ",") - HomePos)))
         Else
            LineChunk = Trim(Mid$(StructureDef, HomePos))
            BuildLoop = False
         End If

         TempChunk = Trim$(Mid$(LineChunk, InStr(UCase$(LineChunk),_
                     " AS ") + 3))
         SelectStmt = SelectStmt & "[" & Trim(Mid$(TempChunk, 1))_
                      & "]" & " As [" & Trim(Mid$(LineChunk, 1, _
                      InStr(UCase$(LineChunk), " AS "))) & "],"

         HomePos = InStr(HomePos, StructureDef, ",") + 1
      Loop

      SelectStmt = Left$(Trim(SelectStmt), Len(Trim(SelectStmt)) - 1) & "_
                   Into [" & TargetTbl & "] From [" & TemplateTbl & "];"

      Set CrtTblDB = CurrentDB()
      Set CrtTblQry = CrtTblDB.CreateQueryDef("TempQuery", SelectStmt)

      CrtTblQry.Execute
      CrtTblQry.Close
      CrtTblDB.DeleteQueryDef ("TempQuery")
   End Sub

Keywords          : TblModfy kbusage
Version           : 1.0 1.1
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto