Create tables from VBA

[twgonder]

I've been researching the whole day, trying to find a complete way to create tables.


These two Microsoft helps come up very short.
CREATE TABLE statement (Microsoft Access SQL) | Microsoft Docs
Microsoft Access Data Types - Open Database Connectivity (ODBC) | Microsoft Docs

This code works, but I can't find what to use to get a decimal.

Public Sub CreateTableX2()
  Dim wrkDb As Database
  Set wrkDb = CurrentDb
  wrkDb.Execute "CREATE TABLE SqlCrTableX2 " _
    & "(aShortText varchar(23), " _
    & "aLongText LONGTEXT, " _
    & "aNumberByte BYTE, " _
    & "aNumberInteger SHORT, " _
    & "aNumberLongInteger LONG, " _
    & "aNumberSingle SINGLE, " _
    & "aNumberDouble DOUBLE, " _
    & "aNumberReplication GUID, " _
    & "aNumberDecimal NUMERIC, " _
    & "aDateTime DATETIME, " _
    & "aCurrency CURRENCY, " _
    & "aAutoNumber COUNTER, " _
    & "aYesNo BINARY, " _
    & "CONSTRAINT SqlCrTableX2Constraint PRIMARY KEY " _
    & "(aShortText)" _
    & ");"
  wrkDb.Close
End Sub

What I also can't find is if there is a way to add all the rest of the properties to each field, like format, caption, default value, etc. to CREATE TABLE.

[June7]

Review
https://www.tek-tips.com/viewthread.cfm?qid=1172710
https://stackoverflow.com/questions/...th-alter-table

Some properties cannot be edited with SQL and requires VBA using DAO TableDefs. Example:

        If rs!DataType = "Text" Then
            'change the AllowZeroLength default Yes to No
            db.TableDefs(Me.tbxTestNum).Fields(rs!DataField).AllowZeroLength = False
        ElseIf rs!DataType = "Number" Then
            'make sure number field does not have 0 as DefaultValue
            db.TableDefs(Me.tbxTestNum).Fields(rs!DataField).DefaultValue = ""
        End If

Those properties you do reference, I would advise not to set in table.

[Minty]

This thread has some thoughts on this issue.
https://www.access-programmers.co.uk.../#post-1797089

[ranman256]

there shouldnt be any reason to create tables in code.

[orange]

You may get some insight and examples from Allen Browne's site re DAO.

Some other relevant links from Allen Browne.

http://allenbrowne.com/func-ADOX.html

http://allenbrowne.com/func-ADO.html

[orange]

Also, here are a couple of subs I retrieved from some older testing

'---------------------------------------------------------------------------------------
' Procedure : AlterTable
' Author    : Jack
' Created   : 7/10/2011
' Purpose   : To alter a table with a field of Long Integer datatype to Decimal.
' Must use ADO based on a lot of Google work.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Private Sub AlterTable()
      Dim cnn As New ADODB.Connection
10       On Error GoTo AlterTable_Error

20    Set cnn = CurrentProject.Connection
30    cnn.Execute ("Alter TABLE [TEST] ALTER COLUMN [ID] Decimal(7,3)")

40    Set cnn = Nothing

50       On Error GoTo 0
60       Exit Sub

AlterTable_Error:

70        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure AlterTable of Module AWF_Related"

End Sub

'---------------------------------------------------------------------------------------
' Procedure : CreateTable
' Author    : Jack
' Created   : 7/10/2011
' Purpose   : Create a table using ADO and SQL
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Private Sub CreateTable()
      Dim cnn As New ADODB.Connection

10       On Error GoTo CreateTable_Error

20    Set cnn = CurrentProject.Connection
30    cnn.Execute ("CREATE TABLE [TEST] ([ID] Long NOT NULL, [MyTextField] Text (2), [MyDecimalField] Decimal(10,6))")

40    Set cnn = Nothing

50       On Error GoTo 0
60       Exit Sub

CreateTable_Error:

70        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CreateTable of Module AWF_Related"

End Sub

[twgonder]

Review
https://www.tek-tips.com/viewthread.cfm?qid=1172710
https://stackoverflow.com/questions/...th-alter-table
...
Those properties you do reference, I would advise not to set in table.

I tried the sample in the first link. I was getting this compile error in another accdb yesterday, in code that had previously run.
Is there something that needs to be enabled for this to work, or do I have to look for corruption in Access, or should it not work on Access 2021?


I'm not sure what you mean by the last sentence.

[twgonder]

there shouldnt be any reason to create tables in code.

If you remember the old Table Wizard from Access 1.0, even though it's been removed, it did what I want to do, only on steroids (mine, not the one from M$).
Building tables by hand is an invitation for inconsistency in naming, design and errors (I like my errors to be consistent).

[Welshgasman]

You likely need the ADO reference?
You appear to be obsessed with access corruption?
Why use a product you do not appear to have any faith in?

[twgonder]

@ Orange Oh yes, those are very good examples. Why can't I find those in my searches? Gotta know the correct lingo I guess. Thanks!

[twgonder]

@ Welshgasman answered in post #8 and created forty-plus years ago in another dbs. Served me very well over the years when dealing with dozens of new enterprise clients wanting mods to things like captions in a db with 100+ tables. Don't they teach to write programs to write programs anymore? Or does everyone want to twitch with a mouse for weeks and call that productivity? See this for a small example of how to write programs to write programs: https://www.accessforums.net/showthr...728#post498728