Variables in SQL (4 Viewers)

[John Sh]

I have a table, H2, with a number of seldom used fields.
I want to create a separate table for each of the fields with table and field name the same as these near empty fields.
I.E. if the field name is "Murgetroid" then I want a table "Murgetroid" with a text field "Murgetroid"
If I use a literal string for the table and field names the code below works but that means changing the string value for every new table.
How can I do this using variables for the table and field names.
This code produces a "Create Table syntax error"

Private Sub Make_Tables()
    Dim RS   As Recordset
    Dim lNum1 As Integer
    Dim INum2 As Integer
    Set RS = oDB.OpenRecordset("H2", dbOpenSnapShot)
    With RS
        lNum1 = .Fields.Count - 1
        INum2 = 1
        Do Until INum2 = lNum1
            TempVars!Name = .Fields(INum2).Name
            DB.Execute "CREATE TABLE tempvars!name ( tempvars!name CHAR);", dbFailOnError
            INum2 =INum2 + 1
        Loop
    End With
End Sub

 

[cheekybuddha]

Happy New Year!

I'm loathe to tell you how to do this because I feel that I will be loading the gun with which you will shoot yourself in the foot further down the road!

You need to concatenate your Tempvar values into the SQL string:

' ...
            TempVars!Name = .Fields(INum2).Name
            DB.Execute "CREATE TABLE " & tempvars!name & " (" & tempvars!name & " CHAR);", dbFailOnError
' ...

 

[CJ_London]

Which brings into question- why use a tempvar?

Curious to know what the purpose of exercise is