Solved Variables in SQL (1 Viewer)

[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

 

[The_Doc_Man]

if the field name is "Murgetroid" then I want a table "Murgetroid" with a text field "Murgetroid"

Inadvisable. Access searches for things by name. Because you would have duplicate names for different objects, you would find it necessary to qualify every field reference with the table reference. If you built a form that had a control named Murgetroid for that field, it would only get worse, since bound forms recognize the name of a control AND the name of a field in its .RecordSource, and we have fielded many cases where object name confusion became critical. In the end, it is your database, but my advice is, "don't invite confusion - it's a guest who never wants to leave."

 

[MarkK]

I have a table, H2, with a number of seldom used fields.

Table design should be determined by what fields belong to the object the table represents, not how frequently those fields are used.

If you have a table tShip that stores data about ships in your navy, and maybe that table has a field called "ReactorMegawatts." And maybe your navy has 1000 ships, but only 2 are nuclear, so the ReactorMegawatts field is 'rarely used.' It is, however, logically and inextricably an attribute of a ship and so, regardless of its frequency of use, it belongs in the tShip table.

A field is an attribute of a type. A type is stored in a table. This field/table pattern you create represents a state of reality, not a pattern of frequency.

 

[John Sh]

Thank you all.
Obviously "Murgetroid" was used for demonstration purposes and does not indicate a valid field name.
Nor does the code snippet represent the final subroutine
I also need other fields to link existing data, so this is a bare bones question.
That said, the question was answered and it now works.
John

Mark.
>Table design should be determined by what fields belong to the object the table represents, not how frequently those fields are used.<
Doesn't this go against normalisation, where a common attribute is held in a separate table and related to the main table?

What I am doing is, in a table of 12,500 records, removing a field with, say 400 entries, and creating a related table with 400 records, for each of the fields in question.
I agree that this will increase the complexity of the form, but it will decrease the overall size of the database.
John

 

[The_Doc_Man]

>Table design should be determined by what fields belong to the object the table represents, not how frequently those fields are used.<
Doesn't this go against normalisation, where a common attribute is held in a separate table and related to the main table?

If the attribute is common enough to warrant a "translation table" approach, that separate table makes sense - but the code associating to that table, if it is to remain normalized, has to itself be a foreign key in the main table and the PK in the translation table in order to maintain proper normalization. If the translation code is very short and the translated equivalent text is very long, this makes sense. I.e. USA postal abbreviations for state names - 2-letter codes for much longer names. But if the code in question and its translation are BOTH very short, it is of little or no concern to just keep the raw field in the proper table.

And I remember Sylvester the cat, "Heavens to Murgetroid"! I didn't take that usage as anything other than a dummy name.
"Thufferin' thuccotash"!

 

[CJ_London]

but it will decrease the overall size of the database.

12500 is a tiny number of records and if indexing is involved might even increase the overall size

 

[John Sh]

"Thufferin' thuccotash"!
Thanks Doc Man.
I've often pondered the "advantage" of normalisation where the overhead defeats the purpose.
Your explanation puts it all into context.

This may well be one of those cases but the exercise is a learning experience.
John

 

[The_Doc_Man]

but the exercise is a learning experience.

LIFE is a learning experience.