alter table to add a column in a linked table

[markjkubicki]

I need to check if a field exists in a linked table (this could be done just before the table is attached, or after it has already been attached -although I do not think I'd be allowed to do that...) and if it is missing, add it (alternately, if the field already exists, and re-adding it will have no effect on any existing data, I'd skip the checking and just default to adding the field). The table will never have more than (1) record



I've gotten to a point that this code is generated, but it results in an error (3293 syntax error)

ALTER TABLE tbeFixtureSchedulePrintOptions IN 'F:\Jobs\test\TEST Project - Copy.mdb' INSERT COLUMN AIASectionNumber Text(15);

or its generic, unpopulated version:
ALTER TABLE <table name> IN '<database with file path>' INSERT COLUMN <field name> <data type>(<size>);

also,
I actually haven't figured out how to check if a field exists in a linked table - if it hasn't be already attached... so I have this ungracious snippet that attaches the table, checks for the field, then if it is missing, breaks the link and attemps (SEE ABOVE) to add it...


the reason for all this is that updated versions of the projects front-ends are continually distributed to project managers who each have their own backends, and occassionally, an update requires that a new field be added to an existing table; hence, the ALTER 'linked' TABLE, ADD COLUMN.
This may be the totally wrong way of approaching thisw and if so... just let me know

many thanks in advance,
mark

[June7]

Managers have their own backends instead of a common BE for all on a server? DB manager nightmare!

Try ADD COLUMN instead of INSERT COLUMN.

Check this thread http://forums.devarticles.com/micros...vba-58559.html

[markjkubicki]

< you're right, multiple back-ends!!! -it's what I inherited, oh well! >

back to the task...
just tried changing INSERT COLUMN to be ADD COLUMN...
still got the same error


about checking to see if a field exists, I previously found this snippet, which seems to me, and what do I know, to be elegantly concise....


Dim exists As Boolean
exists = False
On Error Resume Next
exists = CurrentDb.TableDefs(tablename).Fields(newfieldname ).Name = newfieldname
fieldexists = exists 'If FALSE, field does not exist
'Add a field to the table.
If fieldexists = False Then
'Initialize
Set Db = CurrentDb()
Set tdf = Db.TableDefs(tablename)
tdf.Fields.Append tdf.CreateField(newfieldname, newfieldtype, newfieldoption)
MsgBox "Field added."
'Clean up
Set fld = Nothing
Set tdf = Nothing
Set Db = Nothing
End If

thoughts ?

[June7]

I tested and also get the syntax error on the ALTER TABLE.

It is modifying TableDefs instead of using an SQL action. Here is example of code from my project that adds a new table to backend and alters some properties. Thought your error would be a simple fix and would not need my example but maybe you will find it helpful. It opens a connection to the backend to create and alter table.

Private Sub btnBuild_Click()
'On Error GoTo Err_proc
Dim tdf As TableDef
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim db As DAO.Database
 
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
 
If IsNull(Me.tbxTestNum) Then
    MsgBox "Must enter test number.", vbCritical, "Error"
 
Else
 
    'Create the Table
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='\\dotatufs02\CRM\Lab\Database\Data\LabData.accdb'"
    cn.Execute "CREATE TABLE " & Me.tbxTestNum & " (LabNum text(12) PRIMARY KEY Not Null, method text(30) Not Null);"
    With CurrentDb
        Set tdf = .CreateTableDef(Me.tbxTestNum)
        tdf.SourceTableName = Me.tbxTestNum
        tdf.Connect = "; DATABASE=\\dotatufs02\CRM\Lab\Database\Data\LabData.accdb"
        .TableDefs.Append tdf
    End With
    Me.tbxTestNum.SetFocus
    rs.Open "SELECT * FROM BuildTable;", CurrentProject.Connection, adOpenStatic, adLockPessimistic
 
    'must use DAO to set AllowZeroLength property
    Set db = DBEngine.OpenDatabase("\\dotatufs02\CRM\Lab\Database\Data\LabData.accdb")
    While Not rs.EOF
        If rs!DataField <> "LabNum" And rs!DataField <> "method" Then
            cn.Execute "ALTER TABLE " & Me.tbxTestNum & " ADD COLUMN " & _
            rs!DataField & " " & IIf(rs!DataType = "Boolean", "Bit", rs!DataType) & _
            IIf(rs!DataType = "Text", "(" & rs!FieldSize & ")", "") & ";"
        End If
        If rs!DataType = "Text" Then
            db.TableDefs(Me.tbxTestNum).Fields(rs!DataField).AllowZeroLength = False
        End If
        rs.MoveNext
    Wend
 
    rs.Close
    cn.Close
 
End If
 
Exit_proc:
    Exit Sub
 
Err_Proc:
    MsgBox "Error encountered in AddDataTable procedure btnBuild_Click - " & Err & " : " & Err.Description
    Resume Exit_proc
End Sub

[markjkubicki]

thanks! (it's a bit over my head, but, hey... learn something new every day -right?)

I'm going to need a bit of tie to chew on it... but 1st question:
in the line:
rs.Open "SELECT * FROM BuildTable;", CurrentProject...
what does < BuildTable > refer to? (I've run a couple a web searches, but nothing relevant is coming up)

[June7]

This module was designed to create a specific category of table for this database. The db is used to manage laboratory test data. If we implement a new test, would need a new table for the data. BuildTable is the name of table in my project that is used as a 'map' of fields and field properties to create a new table. A form is used to enter field names and select some property parameters as records in BuildTable. Then the procedure refers to BuildTable as source of info to build the new table. Where it refers to recordset to get table info, you can put your variables and you won't need the loop structure. What is important to note is the connection to backend that is established to create and alter the table. I just didn't take time to make the code more generic, athough I actually did remove 5 lines. I could have just posted the lines that show declaring and setting the connection but thought you might find it informative to see them in the context of a procedure.

[markjkubicki]

<you've done great ! thnx>