Linked tables with relative path? 4

[VictoryHighway]

Hello,
I need to use a relative path for a linked table. How can I do that? Everytime I try to link in a table, it uses an absolute path, which will not work for the application that I am using. I appreciate any help you can offer.

--Geoffrey

 

[willir]

Geoffrey

If you find a solution to this #%$#%! issue, let me know. It would make development, deployment and support a heck of a lot easier.

As a work around, you may want to consdier using an admin table that stores the location of the backend database. Kind of logic, if this file exist, then use this link; if not try the other link.

Rather than hard coing the location, using the table makes it more manageable.

Richard

 

[jksmi]

Hi,

you need to add a reference to Microsoft Scripting Runtime for this to work as it creates a file system object to get a parent folder and check the file exists...

Public Function RefreshLinkedTables() As Boolean
Dim td As TableDef
Dim fso As FileSystemObject
Dim sField As String
Dim sConnect As String

On Error Resume Next

RefreshLinkedTables = True

[COLOR=green]' get current database parent folder[/color]
Set fso = CreateObject("Scripting.FileSystemObject")
sConnect = fso.GetParentFolderName(Application.CurrentDb.Name)

[COLOR=green]' add your database name to link to...[/color]
sConnect = sConnect & "\myOtherDatabase.mdb"

[COLOR=green]' check other database exists[/color]
fso.GetFile (sConnect)
If Err.Number <> 0 Then     [COLOR=green]' file doesnt exist here![/color]
    RefreshLinkedTables = False
    Exit Function       [COLOR=green]' no point in continuing[/color]
End If

For Each td In CurrentDb.TableDefs
    [COLOR=green]' see if its a linked table...[/color]
    If Len(td.Connect) > 0 Then
        [COLOR=green]' if so then see if we can access the
        ' name property of the first field[/color]
        sField = td(0).Name
        If Err.Number <> 0 Then     ' couldnt access field
            ' so try and reattach
            Err.Number = 0     [COLOR=green]' clear error[/color]
            td.Connect = ";DATABASE=" & sConnect
            td.RefreshLink
            If Err.Number <> 0 Then
                RefreshLinkedTables = False
                Err.Number = 0
            End If
        End If
    End If
    
Next td

End Function

HTH, Jamie
FAQ219-2884

 

[willir]

I got to learn FSO. This is the third useful post that made use of this library that I came across within the last week. Have a star.

 

[VictoryHighway]

I will give that a try myself. It looks like that will do what I want.

Thanks!

 

[scriverb]

Okay guys, I am confused here as to what the need is. I have used the Admin table technique to relink a front-end to a back-end when distributed across different network servers. This technique relinks by replacing the local server name into the connect string. Works great and I have no problem understanding.

But, after looking over the code above I understand what it is doing but it seems to refresh a link that already exists. What is meant by the term "relative path" and how does that relate to this process? It may just be late here and I am being dense but I just am not seeing it. I have googled fso.GetParentFolderName and it looks like it returns the path to the back-end folder that houses the linked tables for the front end. I guess I will have to read up on FSO but maybe you could clear this up for me.

Thanks

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[jksmi]

Hi Bob,

I understood it to be a path relative to the database, i.e. when you have an HTML page in "myfolder", rather than writing <A HREF ="//myserver/myfolder/images/image1.gif"> you could write a relative path <A HREF ="/images/image1.gif"> - or if the HTML page was in a sub folder of "myfolder" the relative path would be <A HREF ="../images/image1.gif"> - so if you moved the HTML page the image and images folder have to move with it. e.g. if the db is to be installed on a pc but the user gets to choose which folder it gets saved in. Supporting files could also be saved in this folder (or subfolder) because your db can find them by looking up the parent folder of the db (which becomes your base path).

The code above just checks to see if any links to tables are broken, and if so tries to fix them according to their relative position to the database's parent folder (above assumes that they're in the same folder but you could, for example, add a sConnect & "\support files\myOtherDatabase.mdb".

I'm not too good at explaining things so if this doesn't make sense let me know and I'll try again!



HTH, Jamie
FAQ219-2884

 

[scriverb]

Jamie, thanks for the fine explantion. I do appreciate you taking the time on this one. I will be studying up on FSO and see if I can make use of the scripting.

Question please. I noticed an animated .gif file displayed in your signature. How did you accomplish this? Can't seem to figure that one out. I have seen it on different signatures and have been fooling with it for a while. yours may come from the available smiles and such but I have also seen some person artwork. Any ideas??

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[jksmi]

Hi Bob,

The FileSystemObject can pretty much do all of the stuff you might do in the microsoft explorer window, from checking a file exists (as in this example) to getting the file extension - stuff that you could probably replicate with functions but some stuff you would have to call API's to deal with.

I quite like the idea of using a relative path as many of the databases that I create end up being installed on a server or on the local pc, so I can let it find its own support files (i.e. startup icon, movies, log files, etc...).

One of the most useful things about the fso is you can read and write to files, i.e. you could use it to save a log to a txt file (more useful if your in another vba app) or you could read in an xml file to parse, etc...

with regards the icons, I've not tried it but you can use your own , i.e. [ignore]

[/ignore]
or you can browse through the tektips smileys, there are about 4 catagories I think -
[URL unfurl="true"]http://www.tipmaster.com/includes/smileys.cfm[/url] you just need to type in the name, i.e. [ignore][/ignore] is


HTH, Jamie
FAQ219-2884

 

[scriverb]

Thanks Jamie. Appreciate your explanation and encouragement. I am headed to the bookstore to pickup a manual.

Thanks for headsup on the icon thing.

Nice posting here. Much appreciated.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[willir]

Jamie Thanks for the information. As soon as I saw your post using FSO, I speculated much of what you later confirmed (which why I gave you a star). I am puzzled why I never saw FSO usage in the Access reference books I use.

Bob Well done on TipMaster award - you deserve it.

Richard

 

[scriverb]

Thanks Richard. The real reward here is solving problems and helping others learn the secrets of ACCESS. As well as learning a few new things myself. It still amazes me how much I don't know yet.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[jksmi]

Hi,

well done to Bob from me too!

with regards the use of fso in Access books, I think that it was new to vba/vb in version 6 (access 2k) and I think that the Script Runtime library is actually borrowed from VBScript... here's some more info in the MSDN Library or I tend to just use the object browser and hit f1

HTH, Jamie
FAQ219-2884

 

[RoyVidar]

It still amazes me how much I don't know yet."
- Then you must be rather good at hiding it, scriverb! Congrats - and deserved!

There's a lot of information on FileSystemObject here, too (hint hint - keyword search;-)). For instance this faq from Bowers74 in the Office VBA forum faq707-4116.

Some other threads demonstrating at least some of the properties and methods of the filesystemobject:
thread705-733508 (good joke at the end;-)), thread705-778800, thread181-734953

In this thread thread705-840886, there's a couple of links to other recourses too.

I can't find much about the FileSystemObject in my Access references either, found some in an Excel book when browsing thru them just now, but jksmi's method is what I've used to learn, too.

(Scripting Runtime is available, and works on my a97 setup)

BTW - seen some real nice threads involving your work too, jksmi, including this (beat me there with the MSDN reference;-)) and willir. Keep up the good work!

Roy-Vidar

 

[scriverb]

Thanks Roy-Vidar for the nice comments, especially from you and others like jksmi(jamie, somewhere in Michigan??). The knowledge that is flowing around these threads at times is incredible. I have also seem many great ideas coming from others that don't necessarily have the time to post as often and I am sure they are deserving of recognition here at TT also. Their qualifications are quite obvious from some of the great code and teaching skills that I have seen.

I certainly will use TT search and your Thread links to readup on FSO. Have to keep remembering that most of what I have learned has been through this site and others previous to finding TT. It seems I can learn more from F1 on some code and someone else's moments of creation than I ever have in my manuals.

Thanks again.





Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[NorthNone]

The original post didn't specify doing this in code, so here is the 'manual' method that results in a UNC link (//networkname/dir1) MY NETWORK PLACES is the key.

No code UNC links: (you may need to set up the
appropriate network place first)

New tables:
File
Get External Data
Link Tables
MY NETWORK PLACES icon (last in list on left)

Relink tables
Tools
Database Utilities
Select tables to relink
Click checkbox "Always prompt for new location" at bottom of screen
OK
MY NETWORK PLACES icon (last in list on left)

BTW, hats off to all the code postings - great work!


---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[jksmi]

Hi NorthNone,

thats a good point - it was the relative bit that made me jump to a coded solution 'cos I was working on something to look for support files quite recently.

I've not tried the network places bit before, I did something similar by adding the network folder to my favourites and editing the shortcut to include the unc (i.e. instead of G:\..."). This works well if you have a lot of databases in the same server folder 'cos you just have to hit favourites and there's your folder, but if its a pain if your just doing it the once...

One thing the code is good for is if you're database path (inc unc) is too long, access doesnt like that and starts to raise errors...

Hi Bob, no - not Michigan, UK - thats why I'm usually only around 9-5 (that'll be while I should be working then hmmmmm). jksmi's an old tao id - just something that came to mind when tt asked me for a handle...

HTH, Jamie
FAQ219-2884

 

[rbertram]

This code does almost everything I need it to do. I am in a situation where our network will not let me link tables through UNC paths. It tells me that I do not have access rights to the path, even though I do. This may be because of the extremely long paths and the use of many spaces. There is nothing I can do about it, so I tried this solution, which works now, but I am about to be in a situation where I think it will not work.

I will be linking to tables from another Access database, but it is located on a completely different server. This code appears that it just assigns the path of the current database to sConnect. What can I do if the linked database in on another server? I cannot use drive letters, since there are about 1500 servers on the network, and users are allowed to assign any drive letter they want to mapped shared drives.

Also, Access does not recognize "FileSystemObject" as a data type. I have just eliminated it from the variable declaration, and it still works. I have tried the code in Access 97 and Access 2003. Am I missing something? Does it make a difference if it is there or not?

 

[PHV]

Access does not recognize "FileSystemObject" as a data type
Have you referenced the Microsoft Scripting Runtime ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rbertram]

I thought I had. I had referenced it in another DB on a different workstation. It works now, thanks.

Any ideas about my other issue?