Add column/field to linked database?

[jimbrooking]

I have what I thought would be a pretty simple problem: add a new field to a linked table. The application is a large for-sale database installed at a number of client sites. I was intending to sneak into an update code to check if the field was in the table already using something like DCount("newFieldName","LinkedTableName"), which will give an error if the field doesn't exist, a number of records if it does, including 0 if the field exists but there aren't any records. That seems to work OK, but now what? I had indended to just run a SQL statement to ALTER TABLE LinkedTableName ADD COLUMN... but Access won't let me do that with a linked table.

I've been been beating my head against a wall for a few hours, and wonder if anyone here might have a code snippet they could share that would (a) add the column to the linked table directly, or (b) replace the linked table by a local table I construct by adding all the linked table's data to a new local table which has the extra column in it.

Oh, I've been trying to stick with ADO/X, but any solution - DAO or ADO/X will be appreciated.

Thanks - this is probably easy and I'm missing something fundamental.

Jim

 

[FLabrecque]

Have you tried to create a DAO.Database variable pointing to the other DB? Here's some code:

Set dbOther = DBEngine.Workspaces(0).OpenDatabase("C:\TEST.mdb")
Set myTABLE = dbOTHER.TableDefs("myTable")
For Each newFIELD In myTABLE.Fields
  IF myTABLE.Name = "newColumn" THEN
    blnHasNewColumn = TRUE
  END IF
Next

IF not blnHasNewColumn THEN
  dbOTHER.Execute "Whatever SQL creates the column"
END IF

 

[jimbrooking]

ADO Solution

Thanks to Pat and FLabrecque for the suggestions. The code Pat posted from Help came close, but I was unable to get it to compile - kept getting a compile error on the .CreateField statement. I finally managed to find an ADO help file on my computer, installed as part of the Access Runtime Version that comes with Access 2000 Professional. It pointed the way to the solution below, which adds the column to the linked table. I tried to set a default value for the added column, but kept getting an error 3421 when the statement (commented out in the code below) was executed. That's OK, I can deal with that. Anyway, for anyone else who has this problem, I'm posting the code that works for me, and thanks again to all who looked at this.

<rant>Why are examples of ADO code so hard to come by? Stuff like this isn't that uncommon, I imagine, but every time I try to do something new with ADO it's like pulling teeth to find decent documentation and relevent examples.</rant>

Public Function MakeNewDD() As Boolean
Const DDname  As String = "Due_Diligence"
Const NewCol As String = "dd_impact"
Const NewColDefault As Variant = 1
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim strDDPath As String
Dim strErrMsg As String
Dim intImpact As Integer
Dim strSQL As String

On Error Resume Next
intImpact = DCount(NewCol, DDname)
If Err.Number = 0 Then
    MakeNewDD = True
    Exit Function
End If
    
On Error GoTo DDError

MakeNewDD = True
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
strDDPath = cat.Tables(DDname).Properties("Jet OLEDB:Link Datasource").Value
Debug.Print "Back-end database: " & strDDPath

Set cat = New ADOX.Catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDDPath & ";"
Set tbl = cat.Tables(DDname)
tbl.columns.Append NewCol, adInteger
' tbl.columns(NewCol).Properties("Default") = NewColDefault

Set tbl = Nothing
Set cat = Nothing

strSQL = "UPDATE " & DDname & " SET " & NewCol & " = " & NewColDefault & ";"
DoSQL strSQL

Exit Function

DDError:
strErrMsg = "Error " & Err.Number & ": " & Err.Description & vbCrLf & vbCrLf & _
 "Please report this problem to [email]xxx@zzz.com[/email]"
Debug.Print strErrMsg
MsgBox strErrMsg, vbExclamation, "DD Table Restructure Failed"
MakeNewDD = False
End Function

Jim

 

[jimbrooking]

Access 2000 help, in my view, was broken in ACC2000 (compared to ACC97). It's much easier for me to find answers on the web than by trying to use online help except when I know exactly what I'm looking for, and the incantation needed to evoke it. For example, VB functions. That's a pretty sad comment about Access help.

When I use ADO in searches I may see ADO topics in the result list, but when I click them, nothing happens. Somehow, the topic titles are accessible to help, but not the body of the help.

The lengthy topics lists that appear when one searches for something appear to be in random order. I would prefer relevence order (well, Google knows how to do that with a semi-infinite universe of topics! ), or an alphabetical order (with an on-screen option to switch).

Delighted you're interfacing with MS. Can't think of anyone I'd rather have speaking on behalf of the developer community!