Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Connecticut
    Posts
    7

    Unanswered: Deleting a Linked table

    I have split dB (Access 2000) that contains a handful of linked tables. One of the linked tables periodically needs to be deleted and imported from a third database.

    My problem is how would I code a delete of the table on the backend, and then import the new table on the backend. So I can re-link it to the front end, using the attached code.

    (DoCmd.TransferDatabase acLink, "Microsoft Access", sLoc, acTable, "tblContacts", "tblContacts", False)

    Any thoughts?

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    The next SQL will return you the linked Access Tables

    SELECT MSysObjects.Name, MSysObjects.Flags, MSysObjects.Type
    FROM MSysObjects
    WHERE (((MSysObjects.Name)<>'MSysObjekts') AND ((MSysObjects.Flags)<>-2147483648 And (MSysObjects.Flags)<>2 And (MSysObjects.Flags)<>-2147483645) AND ((MSysObjects.Type) In (6)))
    ORDER BY MSysObjects.Name;


    Then you can Delete them using a For .... Each and DoCmd.DeleteObject

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Also you can make a RecordSet and delet its content using the previous SELECT

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    There may be an easier way, but here is how I would go about it, just thinking off the top of my head.

    You have a "front end," client application, an mdb.
    You have a "back end," data server, an mdb.
    The front end has linked tables to the back end.
    Some tables in that back end need to, occassionally, be completely "rebuilt" by importing data from an outside data source.

    Assuming that you are mandating that this has to be done from the client application, I would . . .

    1) in the client application, delete the link to the table in question in the data server mdb.
    2) in the client application, use your DoCmd.TransferDatabase to IMPORT the data table from the outside data source.
    3) in the client application, delete the data server mdb table using the following example:

    Code:
    Dim db as Database
    Dim tdefs as TableDefs
    
    Set db=DBEngine.Workspaces(0).OpenDatabase("PathToYourDataServerMDB")
    Set tdefs=db.TableDefs
    tdefs.Delete "NameOfDataServerMDBTableYouWantToDelete"
    
    Set tdefs=Nothing
    Set db=Nothing
    4) in the client application, use your DoCmd.TransferDatabase to EXPORT the table you imported in step 2, to the data server mdb.

    4) in the client application, use your DoCmd.TransferDatabase to LINK the table you exported in step 4.

    WARNING! Frequent use of this methodology will make your client mdb grow. So, it is important to have a repair/compact procedure in-place.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •