Creating a BE Switch to change Linked Tables 1

[Isadore]

I have had for the longest time problems with the University Server being slow - so what I have done is to have the Server Tables brought to the local machine for time intensive updating routines, etc.. This has resulted in 2 separate Front Ends, one which is linked locally, and the second which is linked to the Server.

It would be ideal if I could put an Event on the Opening form in which the user can choose with a simple click which BE they would like to be "linked" to. I have seen mentioned DoCmd.TransferDatabase, Environ() and an API() call for login but not sure how these may work.

If, in code, I have to "de-link" and "re-link" each table during the event, that is ok so long as the job is done.

Any ideas? Thanks in advance. The code would appear as follows (if it exists), e.g., in a frame with 2 option buttons:

If frmBE = 1 Then
  ' Link to Path A
  MsgBox "You are now linked to the SERVER S: BE Tables"
Else
  ' Link to Path B
  MsgBox "You are now linked to the LOCAL C: BE Tables"
End If

The database is in Access 2000 and both OLE and DAO are in use in various forms.

 

[dRahme]

Hi, you can do this with a couple batch files. One of my remote servers is 'S' drive. When I am working on forms, reports, etc I use the Subst command to add an 'S' drive to my local hard drive:

echo off
Subst /d S:
Subst S: C:\
echo on

When I wish to connect to the server I use another batch file:

echo off
net use S: /delete
net use S: \\100.00.000.0\share passwd /userep\admin
echo on

That way you do not have to refresh any links because the BE's are referenced the same. Have to be in the same directories, of course.

Hope that Helps

dRahme

 

[Isadore]

dRahme - excellent solution; howerver these guys at the University a bit paranoid when it comes to anything unique, new, etc... Couple of quick questions.

Should I create the S:\ in batch 1 each time the database is open; i.e., when the computer is turned off I suppose this directory disappears, and does the creation of this new S: cause the harddrive to take up additional space (I don't think it does, it changed it so fast my impression was that it was just another drive referencing the same files).

Finally what exactly are the variables in the second batch file:

net use S: \\100.00.000.0\share passwd /userep\admin

Using made up values how would this appear?

I like the solution; looks great. My impression is that it would work as follows:

1) Database opens, Batch file creates an "S" drive, and the database is linked to this Drive.

2) Like you my Server is an "S" drive. So I suppose when I want to change the BE I just hyperlink in the properties window to the second batch file and you're good to go.

3) To change back from the Server "S" drive to the local "S" drive would I use yet another batch file or return to the original batch file?

 

[Isadore]

The switch back from the Server "S" to the original "S" on the local drive might appear as:

echo off
net use S: \\100.00.000.0\share passwd /userep\admin /delete
net use S:
echo on

 

[dRahme]

Hi,

1. Yes - The batchfiles I save in a directory that I access on the windows toolbar. Right Click on the windows toolbar and you can add the file path there so it's always available.

2. Yes - Use the second batch file. Deletes the 'S' drive on the hard drive "C" of there is one and then creates the link to the server 'S' Drive.

3. You know, I'd have to check that. I use Tools, Disconnect Network drive and delete and do it that way. I'm not at work so perhaps someone else can answer that.

It might simply be this but I would have to check:

echo off
net use S: /delete
Subst /d S:
Subst S: C:\
echo on



4. Net Use:

Go to Start / Run / and type "Net Use ?". That shows the available switches.

Net Use - use this
\\100.00.000.0 - this is the IP address
\share - use this directory
passwd - the logon password
/user - your username

 

[Isadore]

Thanks for your time, appreciate that.

 

[dRahme]

#3 - Deleting the server connection and going back to desktop - this is correct. I checked this morning.

echo off
net use S: /delete
subst /d S:
Subst S: C:\
echo on

 

[Isadore]

dRahme - just a final word; works like a charm here, and all of it is done in code so the user never even realizes that during a code driven event they disconnect from one set of tables, connect to another, and then run the routine, finally re-connecting the originally linked tables. Appreciate your response -- thanks.

 

[dRahme]

Glad it works for you and thanks for the star.

dRahme