Access: strSQL Alter Table (Field Types)

[arcaidius]

Hello,

Would greatly appreciate some help with VBA. I am using the records on form1 (from Table1) to enter the data type either Text or Double in "DataType" column. There will only be 10-30 records.

When complete, I want to click a macro button that updates Table2 data types, "Field1" with ([Table1], [DataType], [ID=1] ; Field2 with ([Table1], [DataType], [ID=2]) and so on.
I have the below code that works if the data type is in the code. Can anyone help me alter this to lookup the datatype from records in Table1? (also need something to stop when there's no more records, or a loop? The Field names will be "Field" and 1 thru whatever, so is there a way to do something

IF(RIGHT(1)=[Table1], [DataType], [ID#]) Then

??? and keep looping?

Private Sub Command9_Click()
    
    Dim strSQL As String
    
    strSQL = "ALTER TABLE [Table2] ALTER COLUMN Field1 double"
    DoCmd.RunSQL strSQL

    strSQL = "ALTER TABLE [Table2] ALTER COLUMN Field2 double"
    DoCmd.RunSQL strSQL

End Sub

 

[arcaidius]

If someone could just please help me get this code to recognize "Fname" as the field name to be altered?

Private Sub DataType_AfterUpdate()

Dim strSQL As String

Fname = Forms!Form1.Recordset![Field]

 strSQL = "ALTER TABLE [Table2] ALTER COLUMN Fname double"
    DoCmd.RunSQL strSQL
End Sub

 

[jackd]

This works for me. I changed field id from short text to number and it ends up as double.

Sub jd()
10      On Error GoTo jd_Error
          Dim db As DAO.Database
20        Set db = CurrentDb
          Dim s As String
30        s = "ALTER TABLE tbOne Alter COLUMN id number primary key"
40     DoCmd.RunSQL s
50      On Error GoTo 0
jd_Exit:
60      Exit Sub
jd_Error:
70      MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure jd" _
           & "  Module  ADO_Etc "
80      GoTo jd_Exit
End Sub

 

[Micron]

seems like this is cross posted

Access: VBA to create table fields from record values

I need some help with VBA code to create new fields in a Table based on the records from a different table. So Table1 will have 2 fields, a name field and data type field. Example: FieldName FieldType Required Length Decimal Yes/No My goal here is that using a form for Table1 to...

www.mrexcel.com