1

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
Erik A
  • 31,639
  • 12
  • 42
  • 67
Joel Adrian Simbahan
  • 25
  • 3

1 Answers1

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

Andre
  • 26,751
  • 7
  • 36
  • 80
  • 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)? – Joel Adrian Simbahan Jul 25 '18 at 09:06