Microsoft KB Archive/88157

{|
 * width="100%"|

ACC1x: How to Create a Table with Access Basic Code

 * }

Q88157

-

The information in this article applies to:


 * 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 &quot;template table&quot; 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

This template can be used as a generic template for using the CreateTable procedure. Once you have defined your data types in a template table, you can use the CreateTable procedure in the following manner:

Definition
CreateTable, ,

Where:

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.

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

defines the structure of the columns in the table.

Where:

"= &quot; As ,...&quot;"

NOTE: There are quotation marks around the entire parameter.

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

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

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 = &quot;Select &quot;

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

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

HomePos = InStr(HomePos, StructureDef, &quot;,&quot;) + 1 Loop

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

Set CrtTblDB = CurrentDB Set CrtTblQry = CrtTblDB.CreateQueryDef(&quot;TempQuery&quot;, SelectStmt)

CrtTblQry.Execute CrtTblQry.Close CrtTblDB.DeleteQueryDef (&quot;TempQuery&quot;)

End Sub Keywords : kbusage

Issue type : kbhowto

Technology :