Automatically linking database tables with VBA

[DRH192]

Hi,

I have been looking for the answer to this question for some time and have never found a real solution.

I have a split database, I keep the front end and the back end on a shared area and encourage the users to copy down the front end to their machine to use it.

The problem I have is that, although all have access to the shared drive, the drive letter is often different for different users.

I am constantly asked to go to users machines just to link the tables to the shared back end, its getting tedious.

I am thinking that I could use code similar to this

For drive letters A to X
if len(path to db)<>0 then
link to this path
end if
next drive letter

this code would only ever find 1 database that would match the file path so could work. The problem is I just dont know how to complete the linking procedure as part of a VBA procedure.

Many Thanks

 

[Crowley16]

why not link your back end using absolute paths, e.g.
\\server\folder\database...
instead of
x:\database...

--------------------
Procrastinate Now!

 

[Crowley16]

oh, forgot to say, to do this, when you link your tables, instead of going to x:\database... navigate through network places and the server\folder\database...

--------------------
Procrastinate Now!

 

[DRH192]

That method would have been nice. For some reason though it is not possible because of the way our servers seem to be set up.

does anyone know a way of building the links the way I described in my first post

 

[Crowley16]

docmd.deleteobject actable, "tblName"
docmd.transferdatabase aclink, "Microsoft Access", "path", acTable, "tblName", "tblName"

F1 for explanations...

--------------------
Procrastinate Now!

 

[MajP]

That method would have been nice. For some reason though it is not possible because of the way our servers seem to be set up

I am not a network guy, but I can not believe that you can not type in the UNC instead of a relative path. Did you try? Can you explain why you can not use a UNC?

 

[DRH192]

Of course!!!!!!!

Thats so flippin simple, god |I get so annoyed when I see a simple solution like that to something I try and make much harder.

Thanks

 

[Crowley16]

it's still better to link directly with the full network path, so if you can, do it that way...

--------------------
Procrastinate Now!

 

[Binnit]

DRH192
I too have been seeking this particular holy grail so far without much success.

I have read in other posts that you can create and run the Autoexec macro so that when the db is opened it uses the macro to check & "refresh" the links.

I would also like to be able to do this so that it would look for the name of the BE db to relink rather than hardcode a particular path which may be different.

I will mark this post and hope we can get some further insight.



If IT ain’t working Binnit and Reboot

 

[Crowley16]

if you want to search for the backend, then you will need to get a fileSystemObject and start searching through it...

there's already quite a lot of posts about the FileSystemObject plus google brings up a LOT of helpful links.

--------------------
Procrastinate Now!

 

[MajP]

If you can get a copy of the the Access Developers Handbook (Litwin, Getz, and Gunderloy) it includes the code to do this and more (and it comes with a disk).

rather than hardcode a particular path which may be different

Maybe I am missing something, but still do not understand how the UNC could be different? I could see if the back end is moved. However, unless I am wrong, the UNC is the same for all users and rarely changes.

 

[Crowley16]

he meant hard code a mapped path, i.e. x:\blah rather than a UNC

--------------------
Procrastinate Now!

 

[MajP]

The code is not in the Desktop Developers Handbook, but in the Enterprise Developers Handbook (page 13 Managing Linked Tables).
Both these books are great resources, and worth investing in.

 

[PizMac]

I have code for this that I have used successfully for many years but have just converted to Access 2003 and get an error message on my old code

Dim jdbs As Database, jrst As Recordset
- see my latest post

If we can sort that then I'll gladly send you the code

Hopefully
Judith

 

[JerryKlmns]

Judith

In Access97 this was perfectly understandable :

Dim jdbs As Database, jrst As Recordset

but it should be

Dim jdbs As DAO.Database, jrst As DAO.Recordset

For Access2000+ to work, just use the latest and add a reference to Microsoft DAO 3.6 Object Library. The default library (Access2000+) is Microsoft ActiveX Data Object 2.x library, which is ADODB. So the conflict rises when access sees Recordset understanding that it should be in the first in order selected library (= ADODB) but you meant ... DAO! Those two, they don't share all methods and there you get your error.

Do use this DAO.Recordset for all your recordset coming from back old days...

 

[PizMac]

Thanks Jerry - I had done all that and it still gave me an error but on going back in this morning it had suddenly fixed itself - I have no idea why it didn't compile OK yesterday but - there we go! (it was previously on Access 2000 with no problems)

if anyone still wants the automatic re-linking code let me know (even though it's antique it works!)

 

[PizMac]

I've just realised - I removed an OLE library reference and that must have cleared the problem! - though why - who knows!

 

[kwestor]

hi PizMac

since you mentioned about the code in Automatic linking of Access databases, can you send it through this address? --- gmelinas@yahoo.com.

thanks a lot. i really need this code.