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

 

[Pat Hartman]

Here is an example from help. It uses the tabledefs collection of the northwind db as an example so you can see how code running in one db can modify objects in a different db.

Example code rarely if ever includes error trapping. I suggest that you include some.

Append and Delete Methods Example

This example uses either the Append method or the Delete method to modify the Fields collection of a TableDef. The AppendDeleteField procedure is required for this procedure to run.

Sub AppendX()

   Dim dbsNorthwind As DAO.Database
   Dim tdfEmployees As DAO.TableDef
   Dim fldLoop As Field

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set tdfEmployees = dbsNorthwind.TableDefs!Employees

   ' Add three new fields.
   AppendDeleteField tdfEmployees, "APPEND", _
      "E-mail", dbText, 50
   AppendDeleteField tdfEmployees, "APPEND", _
      "Http", dbText, 80
   AppendDeleteField tdfEmployees, "APPEND", _
      "Quota", dbInteger, 5

   Debug.Print "Fields after Append"
   Debug.Print , "Type", "Size", "Name"

   ' Enumerate the Fields collection to show the new fields.
   For Each fldLoop In tdfEmployees.Fields
      Debug.Print , fldLoop.Type, fldLoop.Size, fldLoop.Name
   Next fldLoop

   ' Delete the newly added fields.
   AppendDeleteField tdfEmployees, "DELETE", "E-mail"
   AppendDeleteField tdfEmployees, "DELETE", "Http"
   AppendDeleteField tdfEmployees, "DELETE", "Quota"

   Debug.Print "Fields after Delete"
   Debug.Print , "Type", "Size", "Name"

   ' Enumerate the Fields collection to show that the new 
   ' fields have been deleted.
   For Each fldLoop In tdfEmployees.Fields
      Debug.Print , fldLoop.Type, fldLoop.Size, fldLoop.Name
   Next fldLoop

   dbsNorthwind.Close

End Sub

Sub AppendDeleteField(tdfTemp As TableDef, _
   strCommand As String, strName As String, _
   Optional varType, Optional varSize)

   With tdfTemp

      ' Check first to see if the TableDef object is 
      ' updatable. If it isn't, control is passed back to 
      ' the calling procedure.
      If .Updatable = False Then
         MsgBox "TableDef not Updatable! " & _
            "Unable to complete task."
         Exit Sub
      End If

      ' Depending on the passed data, append or delete a
      ' field to the Fields collection of the specified
      ' TableDef object.
      If strCommand = "APPEND" Then
         .Fields.Append .CreateField(strName, _
            varType, varSize)
      Else
         If strCommand = "DELETE" Then .Fields.Delete _
            strName
      End If

   End With

End Sub

 

[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

 

[Pat Hartman]

The example I posted was DAO. If you didn't already have a reference to the DAO library, you would have had to add one.

I'm glad you perservered and found the ADO example.

I find that if I include "ADO" in my searches, I can get some ADO help but help is really annoying. I'm going to start a wish list thread in the Water cooler so look for it. I would like to have it all together by Monday evening so I can send it to Microsoft. I'll be speaking to the Access team leader on Wednesday and I want to pass the list along before I speak to him.

 

[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!

 

[Pat Hartman]

Right. I forgot about that bug and I don't remember how to fix it. I think it has something to do with MDAC and which components were added in which order. When I find a pc with the problem, I just bypass Access help and go to the source. Here's a list of the help files that I find most helpful:
ADO210.chm ADO 2.1
ADO260.chm ADO 2.6
VBAOF10.chm VBA 10
DAO360.chm DAO 3.6
HTMLREF.chm
JETERR40.chm Jet 4.0
JETSQL40.chm Jet 4.0
wp.chm Microsoft Data Access Technical Articles

Search your system for .chm files. You'll be amazed at the number of them. Not all are useful though.