Sub CopyPNP()
Const LocalTableName = "Master"
Const ExternalTableName = "Master"
Const ExternalDbPath = "H:\PNP\Strategic Plan.mdb"
Const ExternalDbPassword = "password" ' <<< replace with your password
'If tblTest exists, delete it.
DeleteTable LocalTableName
'Create link to the PNP Master table
LinkAccessTable ExternalTableName, ExternalDbPath, LocalTableName, _
ExternalDbPassword
MsgBox "Property network Plan data uppdated successfully."
End Sub
Public Sub DeleteTable(TableName As String)
' Deletes the specified table from the current database, if it exists.
On Error GoTo ErrorHandler
CurrentDb.TableDefs.Delete TableName
ErrorExit:
Exit Sub
ErrorHandler:
If Err.Number = 3265 Then ' Item not found in this collection
Resume Next
End If
Err.Raise Err.Number
End Sub
Public Sub LinkAccessTable(TableName As String, InDatabase As String, _
Optional LocalName As String, _
Optional Password As String)
' Creates a link to a table in an external Jet database.
' If LocalName is not specified, it defaults to TableName.
' If the external database is password protected, the password must be specified
Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strConnect As String
On Error GoTo ErrorHandler
If Len(LocalName) = 0 Then LocalName = TableName
strConnect = ";DATABASE=" & InDatabase
If Len(Password) > 0 Then strConnect = strConnect & ";PWD=" & Password
Set dbs = CurrentDb()
Set tdf = dbs.CreateTableDef(LocalName, 0, TableName, strConnect)
dbs.TableDefs.Append tdf
dbs.TableDefs.Refresh
RefreshDatabaseWindow
ErrorExit:
Set tdf = Nothing
Set dbs = Nothing
Exit Sub
ErrorHandler:
Set tdf = Nothing
Set dbs = Nothing
Err.Raise Err.Number
End Sub
.Connect).