Adding Variable Columns to Access DataBase table

Question

I have a very simply access database( .mdb format) that contains one table with one column.  There is no data in the database.

I am trying to use it to collect the text value of an undetermined number of fields in Word document.  Working toward that goal right now I am only trying to figure out how to add a number of columns to the table.  I will need a column for each uniquely named field in the document.

The code below adds one new column with the fixed heading "Column2" to the table.  The rest of the iterations of the loop are skipped due to the Error handling. 

vConnection.ConnectionString = "data source=D:\Batch\Tally Data Forms\Extracted Data.mdb;" & _

                                "Provider=Microsoft.Jet.OLEDB.4.0;"

vConnection.Open

On Error Resume Next

For i = 2 To 10

  vConnection.Execute "ALTER TABLE MyTable ADD COLUMN Column2 Text;"

Next i

I need to figure out how to make:  "ALTER TABLE MyTable ADD COLUMN Column2 Text;" a varialbe value something like

Dim pStr as String

For i = 2 To 10

  pStr = "ALTER TABLE MyTable ADD COLUMN Column " & i & "Text;"

  vConnection.Execute pStr

Next i

So that I end up with 10 columns "Column 2", "Column 3", etc.

When I tried the technique above I keep getting a syntax error.

My ultimate goal is to create something like this:

Dim oCC as ContentControl

vConnection.ConnectionString = "data source=D:\Batch\Tally Data Forms\Extracted Data.mdb;" & _

                                "Provider=Microsoft.Jet.OLEDB.4.0;"

vConnection.Open

On Error Resume Next

For Each oCC in ActiveDocument.ContentControls

  'Make a column for each titled Content Control

  pStr = "ALTER TABLE MyTable ADD COLUMN " & oCC.Title & "Text;"

  vConnection.Execute pStr

Next oCC

'Delete previous data

vConnection.Execute "DELETE * FROM MyTable"

For Each oCC in ActiveDocument.ContentControls

vRecordSet(oCC.Title) = oCC.Range.Text

Next oCC

Thanks.

---

Greg Maxey --- Visit my website at: http://gregmaxey.mvps.org/word\_tips.htm

Answer 1

The problem is that you introduced a space in the field name, but no space before the field type Text. Field names with spaces should be enclosed in square brackets.

This should work:

  pStr = "ALTER TABLE MyTable ADD COLUMN Column" & i & " Text;"

or

  pStr = "ALTER TABLE MyTable ADD COLUMN [Column " & i & "] Text;"

In your "ultimate goal":

 pStr = "ALTER TABLE MyTable ADD COLUMN [" & oCC.Title & "] Text;"

Answer 2

Try this:

'Clear database records

vConnection.Execute "DELETE * FROM MyTable"

Set vRecordSet = vConnection.Execute("select * from myTable")

For i = vRecordset.Fields.Count - 1 To 1 Step -1

    vConnection.Execute "ALTER TABLE myTable DROP COLUMN [" & vRecordset.Fields(i).Name & "]"

Next i

Answer 3

Hans,

While probably lacking elegance the following seems to clear the data base of all records and columns except column 1:

'Clear database records

vConnection.Execute "DELETE * FROM MyTable"

Set vRecordSet = vConnection.Execute("select * from myTable")

ReDim arrCols(vRecordSet.Fields.Count)

i = 0

For Each Column In vRecordSet.Fields

  arrCols(i) = Column.Name

  i = i + 1

Next Column

vRecordSet.Close

For i = 1 To UBound(arrCols) - 1

  vConnection.Execute "ALTER TABLE myTable DROP COLUMN [" & arrCols(i) & "]"

Next i

Do you know if there is a more direct or elegant approach?

---

Greg Maxey --- Visit my website at: http://gregmaxey.mvps.org/word\_tips.htm

Answer 4

Hans,

That was really helpful.  Thanks again.

Hopefully you can help with a follow on question.  I want to ensure that my database is stripped down to the bare bone basic (e.g., 1 column with no data) each time my procedure runs. I can clear the data using:

'Clear database records

vConnection.Execute "DELETE * FROM MyTable"

I can drop a known column using:

vConnection.Execute "ALTER TABLE myTable DROP COLUMN FavColor"

I won't always know the column names.  How do I drop all columns except column 1?

Thanks.

---

Greg Maxey --- Visit my website at: http://gregmaxey.mvps.org/word\_tips.htm

Answer 5

Hans,

Thank you.  When you (or rather I) are close to ignorant on a subject then even small steps forward feel like leaps!!   

---

Greg Maxey --- Visit my website at: http://gregmaxey.mvps.org/word\_tips.htm