Map Drives

[Vassago]

At my workplace we have an ancient networking system with brand new computers. The computers boot so fast that sometimes they boot before the network can properly load their profile when employees move to a new computer. This sometimes causes problems with one of my front end/back end databases. The backend database is on one of the shared network drives that sometimes don't load with the user's profile. As a result, they get streams of error messages about not being able to load certain linked tables. I was wondering if there was some code that would map a network drive to a specified drive letter. I know how to do this with batch files, but was wondering if this could be done in Access as well. Thanks for any replies.

Vassago

 

[chewy]

why dont you run the batch file from inside Access?

 

[Vassago]

I thought of that. Was just curious if there was a way to do it inside Access. Would save me having to recode some things.

 

[ghudson]

Why don't you use the UNC for the location of the files instead of mapping to a fix drive letter? That will prevent having to know where each person is mapping to specific location on the server.

You will have to relink all of your tables with the UNC location of the backend. You will have to type (copy/paste) the UNC into the "File Name" field when you are relinking the tables. Then click the Open button to display the db's in the UNC location.

UNC = \\Server\Partition\folder\

HTH

 

[Vassago]

I thought of that as well. Just trying to save time.

Okay, then I'll probably do the latter. I really don't want to make a batch file to run from within Access. Who knows the damage that could be caused if someone got ahold of that! (the guys who will be using the database are none too smart)

Thanks for the advice!

Vassago

 

[Tim K.]

You can check this Map Network Drive out.

 

[Vassago]

Thanks for the reply. I was trying to do this in Access and avoid the batch file route, as that's what I currently have set up. Looke like I'm stuck with it though. Thanks for your help though.

 

[Vassago]

Thanks for the reply Pat. The reason why I need to map the drives is because the drives are different for each site that will be using the database. For example:

In Jacksonville we have drives:

G:\ = \\jcksn1a1\common
H:\ = \\jcksn1b\department$

and in Tulsa we have:

G:\ = \\okfile01\common
H:\ = \\okfile02\department$

I was going to have the database check an id table to see where the user is located and map the drives accordingly. If I try to use the UNC name with the linked tables, then I'll have to create a separate database for each site (8 sites total) and then every update would have to be done to each database, which would take way too much time.

I may just have to use the batch file way. I just thought there might be a way within Access to do this. Thanks to everyone that replied, I appreciate everyone's ideas and input in the matter.

 

[arvindn]

Please, tell me how to map a drive using a batch file to map drives? it would be a great help.

 

[ghudson]

No warranties but this is something I used years ago in a batch file...

setdrive X: "XXX@YYY@ZZZ"

You really should use the UNC \\Server\Partition\Directory\File.mdb when linking your db's. Also use the UNC in your custom shortcuts.

 

[bach]

Hi.

Hello,

'ghudson' is the UNC method as simple as

UNC \\<servername>\<foldername>\<filename>

or do you not need the UNC ?

 

[Sergeant]

I have handled a case just like yours. Here's what I did:
I made a 'tables' table...with fields that would identify what site each entry was for, the table name, and the table path. I shipped the front end with no linked tables, and using some logic at startup, the "Where are you" form would pop up. Once the user selected his location, a sub would run, looping through appropriate entries in the 'tables' table and creating a link to each one in accordance with the UNCTablePath field for each.
You could even do the location sensing automatically, testing the UNC paths using FolderExists in a function.

 

[bach]

Sergeant,

Thanks for your reply, I am still a bit on this. Any chance for some demo coding.

I have a table that will hold the servername and folder structure, as the server are being renamed in future and this would mean that the change could be done quite easily.

Regards.

 

[Sergeant]

I finally found a copy of the app I described. I laughed when I finally realized how I went about this task 2 yrs ago...it is so juvenile!
It uses a 'tables' table and a 'LinkPath' table.
Basically, the startup form has OnLoad code that checks to see if all the linked tables are OK (using a very, very crude function)
Here is the function:

Function CKTBL(TBLNM As String)
    On Error GoTo er
    TBLPATH = CurrentDb.TableDefs(TBLNM).Connect
    CKTBL = Right(TBLPATH, (Len(TBLPATH) - 10))
    Exit Function
er:
    Err.Clear
    CKTBL = "X"
End Function

...if they are OK, that form closes and the main form opens. If a table comes back 'X', the ReLink form stays open, and the user selects his location and hits the 'Relink' button, which loops through the 'tables' table and creates a link to each table. Here is the relink function...(also very crude)...

Sub RELINK(SHPATH As String)
    On Error Resume Next
    Dim DB As Database
    Set DB = CurrentDb
    Set TDF = DB.TableDefs

    With Me.Recordset
        .MoveFirst
        Do While Not .EOF
            Set NEWTBL = DB.CreateTableDef(Me.TBLNAM)
            NEWTBL.Connect = ";DATABASE=" & SHPATH
            NEWTBL.SourceTableName = Me.TBLNAM
            TDF.Append NEWTBL
            
            .MoveNext
        Loop
    End With
    TDF.Refresh
    DoCmd.Close acForm, "FMRELINK"
    DoCmd.OpenForm "USERFORM"
End Sub

If it seems confusing still, don't feel bad. I am not sure exactly what I was thinking at the time, but it does work!
I would do it quite differently today...if you really can't make any headway, post back here and we'll work through it.