I asked ChatGPT, this is the response. Until someone on here can provide a "cleaner" answer, this might get you going

========================================

Yes, it is possible to create a linked table via VBA without using the Access prompts. You can use the "TableDef" object in VBA to create a linked table. Here is an example code that demonstrates how to create a linked table via VBA:

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb
Set tdf = db.CreateTableDef("LinkedTable")

tdf.Connect = "ODBC;Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;"
tdf.SourceTableName = "dbo.MyTable"

db.TableDefs.Append tdf

In this example, a new TableDef object is created with the name "LinkedTable". The Connect property is set to the connection string for the external database, and the SourceTableName property is set to the name of the table in the external database that you want to link to. Finally, the TableDef object is appended to the TableDefs collection of the current database.

You will need to modify the connection string and table name to match your specific requirements.