Deleting and replacing linked tables in MS Access with VBA

[JeffBarker]

Hey guys - me again!!

We have a number of databases that use MS Access for both front and back ends, and I need to write a script that deletes a linked table from the front end and replace it with the relevant table in a seperate SQL backend.

I have no clue on where to start with this, so if anyone can pass any suggestions my way I'd be most greatful!

Thanks,

Jeff.

 

[smig]

this will link your table to the new db

Dim dbs As DAO.Database
 
Set dbs = CurrentDb
 
dbs.TableDefs(YourTableName).Connect = "MS Access;PWD=YourDBPassword;DATABASE=" & YourNewdb
dbs.TableDefs(YourTableName).RefreshLink         ' Relink the table.

 

[JeffBarker]

this will link your table to the new db

Dim dbs As DAO.Database
 
Set dbs = CurrentDb
 
dbs.TableDefs(YourTableName).Connect = "MS Access;PWD=YourDBPassword;DATABASE=" & YourNewdb
dbs.TableDefs(YourTableName).RefreshLink         ' Relink the table.

Hi Smig, thanks for this!

 

[smig]

you'r welcome
hope it will help

 

[JeffBarker]

I've got some code together that deletes a table by name:

Sub mysub()
DeleteTable ("Site Information")
End Sub


Public Sub DeleteTable(TableName As String)
On Error GoTo PROC_ERR

Dim db As DAO.Database
Set db = CurrentDb

If IsTableQuery("Site Information", CurrentDb) Then
db.TableDefs.Delete "Site Information"
End If

PROC_EXIT:
Set db = Nothing
Exit Sub

PROC_ERR:

MsgBox "An error has occured in basTables.DeleteTable while attmpting to delete the table " & _
TableName & ". Error details below:" & vbCrLf & vbCrLf & Err.Number & ": " & Err.Description
Resume Next
Resume

End Sub

But I need to do it "in bulk" - has anyone got any suggestions on the best way to build a list of table names within the db and then run it into my delete code please??

Thanks,

J.

 

[DCrake]

Function RefreshLinks(MyDbName As String) As Boolean
' Refresh links to the supplied database. Return True if successful.
' Attempts to connect to mdb named in the jetdatapath setting
' Application fails if mdb not found
' This demo assumes that all the linked tabled appear in the same back end
' Code will need to be manipulated if using side ends.
' Also need to ensure that Miscrosoft DAO 3.6 Object library is referenced

'/Display message on status bar
DoCmd.Echo True, "Refreshing table links, please wait..."
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    
    '/Does the path exist
    If Dir(MyDbName) = "" Then
        MsgBox "Cannot find source database. Please contact system support", vbCritical + vbOKOnly, "Application Failed"
        DoCmd.Quit
    End If
    
    ' Loop through all tables in the database.
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
        ' If the table has a connect string, it's a linked table.
        If Len(tdf.Connect) > 0 Then
            If tdf.Connect <> ";DATABASE=" & MyDbName Then
                tdf.Connect = ";DATABASE=" & MyDbName
                Err = 0
                On Error Resume Next
                tdf.RefreshLink         ' Relink the table.
                If Err <> 0 Then
                    RefreshLinks = False
                    Exit Function
                End If
            End If           
        End If
    Next tdf
DoCmd.Echo True, "Done"

    RefreshLinks = True        ' Relinking complete.
    
End Function

Usage:

Call RefreshLinks("C:\Temp\MyDb.mdb")

Public Function DropLinks()
    For Each tdf In CurrentDb.TableDefs
        ' If the table has a connect string, it's a linked table.
        If Len(tdf.Connect) > 0 Then
            DoCmd.DeleteObject acTable, tdf.Name
            
        End If
    Next tdf

End Function

Usage:

Call DropLinks()

 

[winshent]

It sounds like you need a list of all the Access tables that are to be replaced by SQL tables, with their equivalent table names in the SQL Server Database that you will connect to..

Build the list in Access.. then iterate thru the table using a recordset..

Build a function to add the SQL Table with parameters Table Name and Connection string.. then with your recordset do

Call DeleteTable rs!AccessTableName
Call AttachSQLTable rs!SQLTableName, ConnectionString

Simples

 

[JeffBarker]

It sounds like you need a list of all the Access tables that are to be replaced by SQL tables, with their equivalent table names in the SQL Server Database that you will connect to..

Build the list in Access.. then iterate thru the table using a recordset..

Build a function to add the SQL Table with parameters Table Name and Connection string.. then with your recordset do

Call DeleteTable rs!AccessTableName
Call AttachSQLTable rs!SQLTableName, ConnectionString

Simples

Thanks, Winshent!