In most cases what you show is all that is needed, and I've used that for years also.
But I have a development situation where we need to frequently relink between a backend Access database using Jet and a backend SQL Server via ODBC. Because of a quirks in how Jet handles the switch, and I don't remember the details, simply resetting the connection string does not work.
In my code, which has also been in use for years elsewhere, the essential lines are:
' Rename the old table
db.TableDefs(originalname).Name = tempname
' create a new table
Set tb = db.CreateTableDef(cleanName)
' Set the new connection string
tb.Connect = newConnectionStringdb.TableDefs.Append tb
' Delete the old table
DoCmd.DeleteObject acTable, tempname
I found the problem.
It turns out I'm hitting the Access Cached connection problem that I thought the above code fixed. The new connection string is to the same server and database, but other parameters are changed. As a result the db.Append tb line throws away the new connection string and reuses the original. To work around it, I have to open the relinking form BEFORE any of the existing connections are used. Then it does what is expected. From what I can tell, there is not a good solution.