Also, here are a couple of subs I retrieved from some older testing
Code:
'---------------------------------------------------------------------------------------
' 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
Code:
'---------------------------------------------------------------------------------------
' 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