VBA - Update Linked Table of an Access file from Excel

Question

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

Andre · Accepted Answer · 2018-07-25 09:27:24Z

1

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

edited Jul 25, 2018 at 9:27

answered Jul 25, 2018 at 9:00

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joel Adrian Simbahan Over a year ago

Hi Andre! This works. However, it creates a duplicate table. Is there anyway I could just update the existing table's linkage (without using delete table then transferdatabase)?

Collectives™ on Stack Overflow

VBA - Update Linked Table of an Access file from Excel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related