Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Moving a table to the backend (97-2002)

    In distributing a new front end I need to move a new table from the front end to the backend ( with a different name), delete it from the front end then create a link how do I perform these three steps in code?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving a table to the backend (97-2002)

    Something like this:

    Sub MoveAndLink()
    Const strBackendDatabase = "F:AccessBackend.mdb"
    Dim strFrontendTable As String
    Dim strBackendTable As String
    strFrontendTable = "tblThis"
    strBackendTable = "tblThat"

    ' Copy to backend
    DoCmd.TransferDatabase acExport, "Microsoft Access", _
    strBackendDatabase, acTable, strFrontendTable, strBackendTable

    ' Delete from frontend
    DoCmd.DeleteObject acTable, strFrontendTable

    ' Link in frontend
    DoCmd.TransferDatabase acLink, "Microsoft Access", _
    strBackendDatabase, acTable, strBackendTable, strFrontendTable
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving a table to the backend (97-2002)

    Thanksn Hans - That works fine but if it is run twice the backend table is deleted. What is the best way to check if the table already exists in the backend and as a linked table in case it was run from another copy of the front end?
    Regards
    Kent

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving a table to the backend (97-2002)

    Since you mention Access 97-2002 in the subject, it is probably safest to use DAO. Make sure that you have checked the reference to the Microsoft DAO 3.5n Object Library (Access 97) or Microsoft DAO 3.6 Object Library (Access 2000 and up) in Tools | References...

    Put this function in a module:

    Public Function TableExists(strTable As String, Optional strDatabase As String) As Boolean
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef

    On Error Resume Next
    If strDatabase = "" Then
    Set dbs = CurrentDb
    Else
    Set dbs = OpenDatabase(strDatabase)
    End If

    Set tdf = dbs.TableDefs(strTable)
    TableExists = Not (tdf Is Nothing)

    Set tdf = Nothing
    If Not (strDatabase = "") Then
    dbs.Close
    End If
    Set dbs = Nothing
    End Function

    Use like this: to test if a table tblTest exists in the current database:

    If TableExists("tblTest") = True Then
    ...
    End If

    and to test if a table tblTest exists in the database C:AccessBackend.mdb, use

    If TableExists("tblTest", "C:AccessBackend.mdb") = True Then
    ...
    End If

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving a table to the backend (97-2002)

    Thanks Hans that's great and yes I do need to use DAO.
    Regards
    Kent

Posting Permissions

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