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

You could delete the table (using the DROP TABLE command), then create a new one with one column:

vConnection.Execute "DROP TABLE MyTable"

vConnection.Execute "CREATE TABLE MyTable ([Record Number] Integer CONSTRAINT PrimaryKey PRIMARY KEY)"

The CONSTRAINT part makes the Record Number field the primary key. If you don't need that, you can omit it.

Answer 3

Hans,

No need to be sorry.  You have been a tremendous help!!  If you happen to think of a cleaner or more direct way (the goal is to end up with an empty database with a single column labeled ("Record Number") then please post back.

Thanks again.

---

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

Answer 4

Ah yes, stupid of me, opening a recordset on the table locks it. Sorry about that!

Answer 5

Hans,

No joy.  At least not here.  I had tried something very similar before going the route I did.  With my earlier attempt and with your code I get the same error on the statement in the For ... Next loop:

The database engine could not lock 'myTable' because it is already in use by another person or process.

Relevent code up to the point of the error is shown below:

Dim vConnection As New ADODB.Connection

Dim vRecordSet As New ADODB.Recordset

Dim pCmdLind As String

'.......

'Unrelated code.

'.......

'Provide connection string for data using Jet Provider for Access database

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

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

vConnection.Open

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

The code I used opens the recordset, gets the column names, closes the recordset then deletes the columns.  That error makes it appear the the record set must be closed before the table can be altered?  I don't see anything in the code that relates to "locking" the table so if your method is supposed to work then perhaps I have an issue with the database setup itself.

Thanks for the suggestions and any other ideas you may have.

---

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