How to find current backend file and rename it with VBA

[neelsfer]

Some of clients have an backend linked file that needs to be renamed. This file is not the same for my different clients.
What i have in mind is to use VBA to find the current backend file name (only one file), and then rename it to something else.
These backends files all start with "RacetimeDataV6.1Lic....accdb"(the dots is a licencenumber).It must now be called "RacetimeDatav6.4.accdb".Some of my newer clients already have the renamed file, and the code must then ignore it.
This "wrong" file will be in the C:\RT directory.

My new plan
After opening the startupform, they press a button which then searches for any "Racetimedatav6.4Lic....accdb" file in the c:\RT directory, and rename it to RacetimeDatav6.4.accdb.
The frontend will then have to be closed and re-opened, to link to new "re-named" BE file.
Is it possible?

[nico5038]

I've made a "general" re-link function, as all my back-ends are stored in the same folder as the front-end.

Function fncRelink()
'function to relink tables to the "_be" database
'It's assumed that the "_be" database is in the same folder as the frontend !
 
Dim td As DAO.TableDef
 
For Each td In CurrentDb.TableDefs
  If Len(td.Connect) > 0 Then
     td.Connect = ";DATABASE=" & Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")) & Mid(td.Connect, InStrRev(td.Connect, "\") + 1)
     td.RefreshLink
  End If
Next
 
MsgBox "Ready, tables relinked"
 
End Function
 

Al you need is "hard-coding" the new location in the .connect and you have solved the re-link. A test for the present .connect value being the new location can be used to skip the re-link.

Getting the idea?

Nic;o)

[NeoPa]

Why wait for a button to be pressed? Trigger the process on startup of the database.

Find the file first. Then rename it.

When that's been done, use the logic in Nico's code to redo all the matching links and refresh them in place. Once that has been accomplished (and you can include an informative MessageBox call in there wherever you want) the database will continue forward working exactly as you intend (and the user hasn't even been disturbed by the process).

[neelsfer]

Good idea but how do i find and rename the existing file with VBA? I was stupid enough to customize the BE file for my first few clients, and now its a pain having to customize updates of the FE for them, as they are technologically disadvantaged and struggle with the Linked table manager!(i also feel like that some days)
The BE is in the same directory as the FE = c:\RT
Every one of those client's files start with "RacetimeDatav6.1Lic..." Only the last part differs and it must become "RacetimeDataV6.4.accdb". Any suggestions how to accomplich that?

[NeoPa]

Sorry. I expected that to be the easy bit.

You either have a known linked table, or all linked tables, that are linked to the BE database. The .Connect property of a linked table contains the full path to that database. That's where the file is that needs to be renamed. The rename command in VBA is the Name statement. You can use a For Each loop in your code to process through the TableDefs collection for all matching linked tables.

Does that make it all clearer? Please say if there's anything still confusing.

[neelsfer]

Neopa i still have to learn what you have forgotten!

If it was a straightforward rename, then its easy

Sub DoRename()
Name "c:\RT\RacetimeDatav6.1Lic105.accdb" As "c:\RT\RacetimeDatav6.4.accdb"
End Sub

but now i have to identify the unknown "RacetimeDatav6.1Lic" without the "105" (as above) and then rename it to "RacetimeDatav6.4.accdb"

This part "RacetimeDatav6.1Lic" is the same for all these clients.
This is why i am stuck!

[NeoPa]

The answer's there Neels. In post #5 already.

If you post the name of one of your linked tables that you know is linked to the original file then I'll knock up some code for you to enable you to determine the full name of the original file. Actually, as the name is just one element of it I'll just use a Constant in the code :

Option Explicit
Option Compare Database
 
Private Const conTblName As String = "tblLinked"  'Enter your table name here
 
Private Function BEName() As String
    Dim varX As Variant
 
    With CurrentDb.TableDefs(conTblName)
        For Each varX In Split(.Connect, ";")
            If varX Like "DATABASE=*" Then
                BEName = Split(varX, "=")(1)
                Exit For
            End If
        Next varX
    End With
End Function

[pdebaets]

I don't think you're going to be able to rename the back-end file if it is open (I.e.: if someone is connected to it from a front-end). You may need a utility application that does the renaming.

You can find the back-end full path by getting the connect string from a table that you know is connected:

dim MyConnectString as string
dim MyFullPath as string
MyConnectString = CurrentDB().TableDefs("MyTableName").connect
MyFullPath = right(MyConnectString,len(MyConnectString) - len("Database;"))

[NeoPa]

PDEBaets:
I don't think you're going to be able to rename the back-end file if it is open (I.e.: if someone is connected to it from a front-end).

That's a good point, worth making, but not entirely true. Only if a linked table is actually open will the BE database be locked to a rename (Hence the code posted in post #7 actually works).