Relink access tables from excel macro

[Ndain]

Expertise needed!

I need to create an Excel macro that opens an access database (path found in cell of excel workbook) and relinks the tables in the database to the excel file (using a path found in cell of the excel workbook).

BONUS POINTS: I need code that will refresh linked SharePoint tables from the same excel macro.

GO!

[ranman256]

you cant without Access. So just use Access to do it.

[Micron]

Pretty sure you can if you use Automation, not that I disagree with the suggestion. Anyway, you can Google something like 'run access code from excel' and find code that will open a db and run code. Not sure if you can call a macro as well as a function. I would not use a sub. Just make sure the module is a standard module and has a different name from the function. Here's how to relink based on table paths in Access table. I would not attempt to use an Excel list as it wouldn't be worth the extra work - unless maybe it's a linked spreadsheet.

Function RelinkByList() As Boolean
'loops thru table tblLinkedTables (in front end) & compares current appPath to
'tblLinkedTables path value, ensuring links to BE are valid. Invalid link may
'exist if new front end is published or if db is an unauthorized copy
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim currConnect As String, trgtConnect As String, msg As String, tblName As String
Dim result As Integer
Dim Warned As Boolean

On Error GoTo errHandler

Set db = CurrentDb
Set rst = db.OpenRecordset("tblLinkedTables", dbOpenSnapshot)
rst.MoveFirst

Do Until rst.EOF
  tblName = rst.Fields("TableName")
  Set tdf = db.TableDefs(tblName)
'curConnect is current connection value, trgtConnect is path stored in tblLinkedTables
'if network structure ever changes, parameter "11" may have to be altered to display path properly
  currConnect = Nz(Mid(tdf.Connect, 11), "") 'check what the connection property is
 trgtConnect = Nz(rst!DataFilePath, "") 'reads what the tblLinkedTables says the path should be

If trgtConnect <> currConnect Then
    msg = "Current table mapping differs from stored path for '" & tblName & "'." & vbCrLf
    msg = msg & "Re-link this table? CLICK CANCEL TO STOP CHECKING ALL TABLES."
    msg = msg & vbCrLf & vbCrLf
    msg = msg & "Expected path: " & vbCrLf & trgtConnect & vbCrLf & vbCrLf
    msg = msg & "Current path: " & vbCrLf & currConnect
    result = MsgBox(msg, vbYesNoCancel, "CHECKING CONNECTIONS FOR " & tblName)
        If result = 2 Then
            Set tdf = Nothing
            Set rst = Nothing
            Set db = Nothing
            RelinkByList = False
            Exit Function
        End If
        If result = 7 Then rst.MoveNext 'user said don't change this link
        If result = 6 Then DoCmd.OpenForm "frmWait"
            tdf.Connect = ";DATABASE=" & trgtConnect
            tdf.RefreshLink
        End If
End If
LoopHere:
rst.MoveNext
Loop

exitHere:
  Set tdf = Nothing
  Set rst = Nothing
  Set db = Nothing
  DoCmd.Close acForm, "frmWait"
  RelinkByList = True 'relink function succeeded
Exit Function

errHandler:
If Err.Number = 3265 Then
    msg = "Table '" & tblName & "' not found in list of linked tables." & vbCrLf
    msg = msg & "The table may be missing from database or mis-spelled" & vbCrLf
    msg = msg & " in the list of linked tables." & vbCrLf & vbCrLf
    msg = msg & "Please call database administrator to check tables information."
    msg = msg & vbCrLf & "Exiting now..."
    MsgBox msg, vbOKOnly, "TABLE NOT FOUND"
    shutdown
End If
If Err.Number = 3321 Then
    msg = "Cannot re-link " & tblName & ": No path specified in table tblLinkedTables."
    msg = msg & vbCrLf & "Please call database administrator to check tables information."
    msg = msg & vbCrLf & "Exiting now..."
    MsgBox msg, vbOKOnly, "TABLE PATH MISSING"
    shutdown
End If
MsgBox "Error number " & Err.Number & ": " & Err.Description

End Function