468,253 Members | 1,302 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,253 developers. It's quick & easy.

How to find current backend file and rename it with VBA

547 512MB
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?
Sep 11 '11 #1
8 4844
3,079 Expert 2GB
I've made a "general" re-link function, as all my back-ends are stored in the same folder as the front-end.

Expand|Select|Wrap|Line Numbers
  1. Function fncRelink()
  2. 'function to relink tables to the "_be" database
  3. 'It's assumed that the "_be" database is in the same folder as the frontend !
  5. Dim td As DAO.TableDef
  7. For Each td In CurrentDb.TableDefs
  8.   If Len(td.Connect) > 0 Then
  9.      td.Connect = ";DATABASE=" & Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")) & Mid(td.Connect, InStrRev(td.Connect, "\") + 1)
  10.      td.RefreshLink
  11.   End If
  12. Next
  14. MsgBox "Ready, tables relinked"
  16. 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?

Sep 11 '11 #2
32,056 Expert Mod 16PB
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).
Sep 11 '11 #3
547 512MB
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?
Sep 11 '11 #4
32,056 Expert Mod 16PB
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.
Sep 11 '11 #5
547 512MB
Neopa i still have to learn what you have forgotten!

If it was a straightforward rename, then its easy
Expand|Select|Wrap|Line Numbers
  1. Sub DoRename()
  2. Name "c:\RT\RacetimeDatav6.1Lic105.accdb" As "c:\RT\RacetimeDatav6.4.accdb"
  3. 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!
Sep 11 '11 #6
32,056 Expert Mod 16PB
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 :

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Database
  4. Private Const conTblName As String = "tblLinked"  'Enter your table name here
  6. Private Function BEName() As String
  7.     Dim varX As Variant
  9.     With CurrentDb.TableDefs(conTblName)
  10.         For Each varX In Split(.Connect, ";")
  11.             If varX Like "DATABASE=*" Then
  12.                 BEName = Split(varX, "=")(1)
  13.                 Exit For
  14.             End If
  15.         Next varX
  16.     End With
  17. End Function
Sep 11 '11 #7
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:

Expand|Select|Wrap|Line Numbers
  1. dim MyConnectString as string
  2. dim MyFullPath as string
  3. MyConnectString = CurrentDB().TableDefs("MyTableName").connect
  4. MyFullPath = right(MyConnectString,len(MyConnectString) - len("Database;"))
Sep 14 '11 #8
32,056 Expert Mod 16PB
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).
Sep 14 '11 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Andreas Neudecker | last post: by
1 post views Thread by Rob R. Ainscough | last post: by
reply views Thread by JM | last post: by
17 posts views Thread by fl | last post: by
13 posts views Thread by thomas.mertes | last post: by
4 posts views Thread by viktorijakup | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.