Backend File Swap/Rename Utility Required

[PaulO]

Namliam

I really appreciate the efforts and interest of both David and yourself in this thread ... though I recognise in this instance I have leant towards the content in David's postings. I'm equally confident that if David hadn't committed himself to this thread that you and I together would have reached a successful outcome which, right now, we're a bare fraction away from.

I must admit that my incoming expectation was that I would not find a solution ... that fact that I have has completely blown me away!

I have gained many great tips and/or solutions from this site, and I have posted a few back from within my own (limited) knowledge base of experience. I maybe can't give back as much as you Guys, as my VB knowledge is poor, but like you I'll give all I know to those who need help.

 

[PaulO]

Hmmm ... here's where my poor knowledge of VB coding lets me down big time and I have to confess that I wouldn't know what to do with that code ...

I wonder whether I should consider the following options:-

1) Prefixing the names of the TXT and XLS files (there are only six in total) with "ZZZ" in that way the existing code will only fail when it gets to the links I don't want refreshed anyway?

2) Link the TXT and XLS files to the backend file and THEN through to the frontend file? In which instance, with all the linked files derived from the backend file, the routine would work as currently written?

Update:

Option 1 worked ... quite painless actually ... renaming the TXT and XLS files effectively moved them right to the end of the linked table list meaning that the routine didn't crash! All I have to do from now is ensure that any future linked TXT and XLS are named with the same 'ZZZ' prefix.

David ... I think you can probably imagine how pleased I am with this outcome. Thank you!

 

[DCrake]

So have you got the issue resolved now?

 

[PaulO]

Yep! Very happy with the outcome!

 

[namliam]

Option 1 worked ...

Nice, but its a shyte option IMNSHO...


Why not use my suggestion of looking for the MDB extension? Much cleaner !

 

[PaulO]

Well perhaps one man's happiness is another man's misery ...

If you can clean/insert the relevant code and post it back here I'd be more than happy to try it out.

However what I do at least have a working solution, something I never thought I'd get.

 

[namliam]

Which I already did ! ! !

HERE: http://www.access-programmers.co.uk/forums/showpost.php?p=936899&postcount=39

 

[PaulO]

Namliam ... like I say I'm happy enough with the existing solution ... but here's the code section you're referring to ... please edit for the enhancement you're referring to:-

Set dbs = OpenDatabase(HostMDB)

For Each tdf In dbs.TableDefs
' If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & StrDatabaseToOpen
Err = 0
On Error Resume Next
tdf.RefreshLink ' Relink the table.
If Err <> 0 Then
RefreshLinks = False
Exit Function
End If

Is it possible to vary the command to specific text within the linked tables i.e. if table name begins with specified text and ends in .mdb? e.g. "linked*.mdb"

 

[namliam]

I would suggest instead of
If Len(tdf.Connect) > 0 Then
to use
If tdf.Attributes = dbAttachedTable Then

And to only relink MDB files, perhaps something like
IF right(tdf.connect,3) = "MDB" then
??

How hard is that to understand???

If tdf.Attributes = dbAttachedTable Then
    IF right(tdf.connect,3) = "MDB" then
        tdf.Connect = ";DATABASE=" & StrDatabaseToOpen
    end if
end if
Err = 0
On Error Resume Next
...

You can add simular stuff to find "specific" databases or leave some alone..