Moving a table to the backend (97-2002)

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote