VBA to Delete & Add a backend table and then refresh links

[neelsfer]

I need to delete an old table in a backend DB and then add a new table by the same name. Does the links need to be "refreshed" after doing this?
My problem is to get to the backend DB (c:\pisprg\Pisd ataV2.accdb) and use DeleteTable "Fis_itemsT " in Line 3

I use the following code.

Dim strMsg As String
DoCmd.SetWarnings False
DeleteTable "Fis_itemsT"
'i add this same table from the FE DB again after deletion in BE.
DoCmd.TransferDatabase acExport, "Microsoft Access", "c:\pisprg\PisdataV2.accdb", acTable, "Fis_ItemsT1", "Fis_ItemsT", False
DoCmd.SetWarnings True
'I refresh links now
strMsg = RefreshTableLinks()
If Len(strMsg & "") = 0 Then
Debug.Print "All Tables were successfully relinked."
Else
MsgBox strMsg, vbCritical
End If
 MsgBox "Update completed succesfully"

I will call the following code to refresh links afterwards

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strCon As String
Dim strBackEnd As String
Dim strMsg As String
Dim intErrorCount As Integer
Set db = CurrentDb
For Each tdf In db.TableDefs
If Left$(tdf.Connect, 10) = ";DATABASE=" Then
strCon = Nz(tdf.Connect, "")
strBackEnd = Right$(strCon, (Len(strCon) - (InStrRev(strCon, "\") - 1)))
If Len(strBackEnd & "") > 0 Then
Set tdf = db.TableDefs(tdf.Name)
tdf.Connect = ";DATABASE=" & CurrentProject.Path & strBackEnd
tdf.RefreshLink
Else
intErrorCount = intErrorCount + 1
strMsg = strMsg & "Error getting back-end database name." & vbNewLine
strMsg = strMsg & "Table Name: " & tdf.Name & vbNewLine
strMsg = strMsg & "Connect = " & strCon & vbNewLine
End If
End If
Next tdf
ExitHere:
On Error Resume Next
If intErrorCount > 0 Then
strMsg = "There were errors refreshing the table links: " _
& vbNewLine & strMsg & "In Procedure RefreshTableLinks"
RefreshTableLinks = strMsg
End If
Set tdf = Nothing
Set db = Nothing
Exit Function

Any suggestions and am i on the right track?

[TheSmileyCoder]

You declare a database object, and use it to open the backend.

Untested air code:

Dim oBackend as Dao.DataBase
  Set oBackend=OpenDatabase("C:\TestFolder\Backend.mdb"
  oBackend.Execute "Drop table YourTableName"
  oBackEnd.Close
  set oBackend=nothing

dim oFrontend as dao.Database
  set oFrontend=CurrentdB
  ofrontend.execute "SELECT Table1.* INTO YourTableName IN 'C:\TestFolder\Backend.mdb' FROM Table1;
set OFrontend=nothing

I have no idea whether the table links need to be refreshed. I imagine it might depend on whether field names has changed during the process, but it should be easy enough for you to test.

[Stewart Ross]

A link to a table does not need to be refreshed upon replacement of that table if the name of the table remains the same.

I use SELECT .. INTO statements to update hundreds of tables every day, replacing the current versions with updated ones. The links to those tables work perfectly without any form of refreshment.

-Stewart