Please Read: Significant Update Planned, Migrating Forum Software This Month

See more
See less

Creating table name from a variable

  • Filter
  • Time
  • Show
Clear All
new posts

  • Creating table name from a variable

    I can easily create a table inside a MDB database by typing the name of the table.
    How can I use a variable to pass to the program to create a table named after the variable?

    Her is the code that I use to create a Database as well as a table:

    table_name = "RRS"
    'Check if database exists
    Set objFSOA = CreateObject("Scripting.FileSystemObject")
    If objFSOA.FileExists(ScriptPath&"inventory.mdb") Then
    ' Skip, the database has already been created (use the database just found)
    'Create Database
    Set objConnectionA = CreateObject("ADOX.Catalog")
    objConnectionA.Create _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source = inventory.mdb"

    'Create table
    Set objConnectionA = CreateObject("ADODB.Connection")
    objConnectionA.Open _
    "Provider= Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source= inventory.mdb"
    objConnectionA.Execute "CREATE TABLE site1(I would like to replace site1 by the variable table_name(" & _
    "site TEXT(50) ," & _
    "Office _location TEXT(50) ," & _
    "Manufacturer TEXT(100) ," & _
    "Model TEXT(50) ," & _
    "Serial_number TEXT(50) ," & _
    "Processor TEXT(50) ," & _
    "Memory TEXT(50) ," & _
    "Computer_name TEXT(50) ," & _
    "Domain_name TEXT(50) ," & _
    "OS TEXT(50) ," & _
    "Version TEXT(50))"
    End if
    'End Function

  • #2
    Re: Creating table name from a variable

    I would expect a line similar to:
    objConnectionA.Execute "CREATE TABLE " & table_name & "("
    would work (where table_name is your variable)

    Alternatively define a string and build it up to your whole SQL statement:
    strSQL = "CREATE TABLE " & table_name & "("
    strSQL = strSQL & "site TEXT(50), "
    strSQL = strSQL & "Office_location TEXT(50), "
    objConnectionA.Execute strSQL

    You could also have an output to test strSQL is what you expect it to be
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    IT Trainer / Consultant
    Ossian Ltd

    ** Remember to give credit where credit is due and leave reputation points where appropriate **


    • #3
      Re: Creating table name from a variable

      Hi bertv,

      Can you wrap the code in your post in code tags pls (by selecting the code and click the # button. Or by entering the tag [code] befor the code and [/code] after the code)

      Howto create and use a variable for the table name see also "strTable" in this sample:


      This posting is provided "AS IS" with no warranties, and confers no rights.


      ** Remember to give credit where credit's due **
      and leave Reputation Points for meaningful posts