DoCmd.Transferdatabase fails trying to link new table

[TOPSie]

I have frontend .accdb and a backend .mdb. I want to add a new "Vehicle" and create an "orders" table in the backend, then link to the new table into the frontend. I use this code

wOrderVehicle = "tblOrdersGC33"
DoCmd.CopyObject "N:\Database2012\MyBE.mdb", wOrderVehicle, acTable, "tblOrders"
DoCmd.TransferDatabase acLink, "Microsoft Access", "N:\Database2012\MyBE.mdb", acTable, wOrderVehicle, wOrderVehicle, True

The CopyObject works and a new table is created in the back end but the Transferdatabase line fails:



I have tried separating the two lines with a 2 second timer and tried to default the penultimate parameter as it seems superfluous, but always the same result

[June7]

Why are you continuing to use mdb backend?
Why are you programmatically creating new table? Is this a routine occurrence - why?

I tested your code and it works without error for me.

[TOPSie]

I am using a backend because this app is about 20 years old and I am only maintaining and enhancing it as requirements change (Access 2016 at the moment)

I have a "template" table in the backend tblOrders. All existing vehicles have an "orders" table - tblOrders(reg). Vehicles get replaced so I want to dynamically add a new tblorder(new reg).

I have discovered that though apparently working the code #DoCmd.CopyObject "N:\Database2012\MyBE.mdb", wOrderVehicle, acTable, "tblOrders"# creates a linked table in the backend and it is linked ti itself?

So I have tried variants of #DoCmd.TransferDatabase# using Import and Export.
Import creates a table in the Front End and Export creates a linked table in the backend like CopyObject
In all cases, attempting to link the backend table to the font end using TransferDatabase fails as before

[June7]

This is not an optimal schema.

Each vehicle should not have its own table. Should be one table with a field for vehicle code.

Suggest you convert be to accdb sooner than later.

Confused by your statement that CopyObject creates a linked table linked to itself. I do not understand this.

Since I cannot replicate error, can't really help further.

[TOPSie]

This is not an optimal schema.

I appreciate this but please enlighten me. This app has only two users. They download a copy of the accdb front end individually at start of day and access the same backend (.mdb) on a shared server. The mdb is the main business tables and the accdb is code and some temporary tables. The .accdb is stored on the same server where updated copies can be placed as changes are required. As the developer I upload changed versions of the accdb as required and the users can then reload the updated copy. This separates the changleable code from the (relatively) fixed data structure in the mdb.
I only took on this task a few years ago, with limited experience of Access - this was how the system was set up back then - I have been too busy with a) learning Access and b) coping with a stream of change requests as the business evolves to look to deeply at the "architecture"

[June7]

My second line in previous post expands on the first line.

"Each vehicle should not have its own table. Should be one table with a field for vehicle code."

Routinely modifying a database structure indicates bad design.

[Welshgasman]

DB structure aside, I do not know why it is not working for you.

This works for me.

Sub LinkTable()
Dim wOrderVehicle As String, strDBBE As String
wOrderVehicle = "tblOrdersGC33"
strDBBE = "F:\Users\Paul\documents\Commissions_be.accdb"
DoCmd.CopyObject strDBBE, wOrderVehicle, acTable, "tblTemp"
DoCmd.TransferDatabase acLink, "Microsoft Access", strDBBE, acTable, wOrderVehicle, wOrderVehicle, True
End Sub