CODEWITHSUNDEEP
excelms-accessvba
Joel Adrian Simbahan
Joel Adrian Simbahan

Reputation: 25

VBA - Update Linked Table of an Access file from Excel

I am trying to change the linked table address from an Access file "Hey.accdb" using VBA coding from an Excel file.

I've coded the script below in my Excel file and it prompts the error "Object required" when I run it. Can someone please help me with this problem. I've been staring at it for too long. Thanks.

Sub RunMacroinAccesswithPara2()

Set Db = CreateObject("Access.Application")
        Db.OpenCurrentDatabase "D:\Database1\Hey.accdb"
        Db.Visible = True
        Db.AutomationSecurity = msoAutomationSecurityLow

    DoCmd.TransferDatabase TransferType:=acLink, _
        DatabaseType:="Microsoft Access", _
        DatabaseName:="V:\Reporting\Quarterly\2018Q2\JP\Data\04\Database\Valuation_Database.mdb", _
        ObjectType:=acTable, _
        Source:="Valuation_Database_Adjusted", _
        Destination:="Valuation_Database_Adjusted"

End Sub

Upvotes: 1

Views: 1218

Answers (1)

Andre
Andre

Reputation: 27634

DoCmd belongs to the Access application object.

So use

Db.DoCmd.TransferDatabase ' etc.

Edit

To update the link, you need the TableDef object, set its .Connect property and run .RefreshLink.

See Linked table ms access 2010 change connection string

Upvotes: 1

Related Questions