I can create a new field in a local table using a data definition query. 

How can I do the same in a linked table please?

I'm guessing here, but I would try something like:



(warning--air code!)

Dim WS As DAO.Workspace

Dim dbLinked As DAO.Database



Set WS = DBEngine.Workspaces(0)

Set dbLinked = WS.OpenDatabase("c:\MyLinkedDatabase.mdb")



   With dbLinked

      .Execute ("ALTER TABLE blah blah blah....")

      .Close

   End With



   Set dbLinked = Nothing

   Set WS = Nothing



You might need to call .RefreshLink on the local tabledef object in order to

see the changes...



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

(xxx) xxx-xxxx



-----Original Message-----

From: Clive Astley [mailto:clive.astley@p...]

Sent: Friday, October 12, 2001 11:15 AM

To: Access

Subject: [access] Create a field





I can create a new field in a local table using a data definition query. 

How can I do the same in a linked table please?

Question 1 - Using DDL to create a new field in a local table and Question 2

-

Using DDL to create a new field in a Linked table are answered below. 

This function should have error trapping added so we don't duplicate fields,

open bad databases or unavailable versions.  

Also note that for this to work, DDL must have exclusive access to the

database since it is manipulating MSysObjects.



Usage:

      ? fAddFld("tbl_DBLog","\\fs_dha03\User_data\CMS\myDatabase.mdb")



Parameter 1 is the table name to append

Parameter 2 is optional and can reference either mapped or UNC path

information.

You could add a third parameter as a Table or ParamArray of field names...



Here's the starter code:



Function fAddFld(sTable As String, Optional sRemoteDB As String) As Long

Dim dbs As Database, tdf As TableDef

Dim wsp As Workspace



  Set wsp = DBEngine.Workspaces(0)

  If IsMissing(sRemoteDB) Then

    Set dbs = DBEngine(0)(0)                        ' Faster call syntax

than

CurrentDB()

  Else

    Set dbs = wsp.OpenDatabase(sRemoteDB, False)

  End If

  

  Set tdf = dbs.TableDefs(sTable)

  With tdf

      .Fields.Append .CreateField("ModType", dbText, 24)  ' Append Field

[Module Type]

      .Fields.Append .CreateField("Lines", dbInteger)     ' Append Field

[Lines]

      .Fields.Refresh

  End With

  dbs.TableDefs.Refresh                                   ' Refresh the list

  fAddFld = Err.Number                                        ' How did it

go

  

  Set dbs = Nothing

  Set wsp = Nothing

  Set tdf = Nothing

  

End Function



Tom Hamilton

T_Systems, Inc

Database Programmer

(xxx) xxx-xxxx



>>> Clive Astley 10/12/01 11:14AM >>>

I can create a new field in a local table using a data definition query. 

How can I do the same in a linked table please?