Thursday, December 29, 2011

Map Network Drives with VBScript

When setting up that Access database on a local area network, you'll likely need to map network drives on each end user's PC. This means you'll be creating a psuedo-drive "Z:" or whatnot, which opens a folder on the server where your Access application back-end resides.

As new computers are added to the network, you'll need to map the network drives on the new computers. This process can be eased by running a VBScript to automatically do the drive mapping. Below is an example script that I use to do this. You can modify the "Actions" to add or remove mapped network drives, in case you have multiple drives mapped, or need to do some mapping clean up.

Just save this code in a file with a .vbs extension, modify it for your needs, then double-click it from Windows Explorer to run it.

' Map network drives 

' Usage
'    Set the size of the ADrive, ARemoteShare, and AAction arrays to
'    be the number of drive mapping actions you want to take. Modify
'    the "Actions" in the "Fill Actions Array" section below, save, 
'    then double-click on this file from Windows Explorer to run it.
'
' "Actions" can be "Remove" (removes/disconnects a mapped network 
' drive), or "Add" (adds a mapped network drive, replacing whatever 
' mapping exists for the same drive letter).
'
' For multiple "Actions", modify the array sizes below, then fill
' the array entries as shown in the "Fill Actions Array" section below.
'
' This script will remove any existing drive map to the same drive letter
' including persistent or remembered connections (Q303209)
'
' from: http://ss64.com/vb/syntax-drivemap.html
' modified by Peter De Baets 12/28/2011

Option Explicit
Dim objNetwork, objDrives, objReg, i, objDrive, fileSys
Dim strReplaceDrive, strLocalDrive, strRemoteShare, strShareConnected, strMessage
Dim bolFoundExisting, bolFoundRemembered
Const HKCU = &H80000001
Dim J
'******************************************
'* Set the size of these arrays to the number
'* of drive mapping actions you want to take.
'*
Dim ADrive(2)
Dim ARemoteShare(2) 
Dim AAction(2)
'*
'****************************************** 


'******************************************
'* Fill Actions Array 
'* (make your changes here)
'*
' Example
'ADrive(1) = "X:"
'ARemoteShare(1) = "\\Servername\Share"
'AAction(1) = "Add"

' Action #1
ADrive(1) = "X:"
ARemoteShare(1) = "\\MyServer\MyShare"
AAction(1) = "Remove"

' Action #2
ADrive(2) = "Z:"
ARemoteShare(2) = "\\MyServer\MyShare"
AAction(2) = "Add"
'*
'******************************************


Set filesys = CreateObject("Scripting.FileSystemObject") 
for j = 1 to ubound(AAction)
  ' Check parameters passed make sense
  If Right(ADrive(j), 1) <> ":" OR Left(ARemoteShare(j), 2) <> "\\" Then
    wscript.echo "INvalid Action #" & j & " //NoLogo"
    WScript.Quit(1)
  End If
  if AAction(j) = "Add" then
    wscript.echo " - Mapping: " + ADrive(j) + " to " + ARemoteShare(j)
  Else
    wscript.echo " - Disconnecting: " + ADrive(j) + " from " + ARemoteShare(j)
  End If
  Set objNetwork = WScript.CreateObject("WScript.Network")
  ' Loop through the network drive connections and disconnect any that match strLocalDrive
  bolFoundExisting = False
  Set objDrives = objNetwork.EnumNetworkDrives
  If objDrives.Count > 0 Then
    For i = 0 To objDrives.Count-1 Step 2
      If objDrives.Item(i) = ADrive(j) Then
     if AAction(j) = "Remove" Then
    set objDrive = fileSys.GetDrive(objDrives.Item(i))
       if objDrive.ShareName = ARemoteShare(j) then
            strShareConnected = objDrives.Item(i+1)
            objNetwork.RemoveNetworkDrive ADrive(j), True, True
            i=objDrives.Count-1
            bolFoundExisting = True
    Else
      wscript.echo " - Drive " + ADrive(j) + " connected to " + ARemoteShare(j) + " not found. Continuing..."
    End if
  Else
       'wscript.echo "  sharename=" + objDrive.ShareName
          strShareConnected = objDrives.Item(i+1)
          objNetwork.RemoveNetworkDrive ADrive(j), True, True
          i=objDrives.Count-1
          bolFoundExisting = True
  End if
      End If
    Next
  End If

  ' If there's a remembered location (persistent mapping) delete the associated HKCU registry key
  If bolFoundExisting <> True Then
    Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    objReg.GetStringValue HKCU, "Network\" & Left(ADrive(j), 1), "RemotePath", strShareConnected
    If strShareConnected <> "" Then
      objReg.DeleteKey HKCU, "Network\" & Left(ADrive(j), 1)
      Set objReg = Nothing
      bolFoundRemembered = True
    End If
  End If

  if AAction(j) = "Add" then
    'Now actually do the drive map (persistent)
    Err.Clear
    On Error Resume Next
    objNetwork.MapNetworkDrive ADrive(j), ARemoteShare(j), True
  End If
Next 

'Error traps
If Err <> 0 Then
  Select Case Err.Number
    Case -2147023694
      'Persistent connection so try a second time
      On Error Goto 0
      objNetwork.RemoveNetworkDrive ADrive(j), True, True
      objNetwork.MapNetworkDrive ADrive(j), ARemoteShare(j), True
      WScript.Echo "Second attempt to " & AAction(j) & " map drive " & ADrive(j) & " to/from " & ARemoteShare(j)
    Case Else
      On Error GoTo 0
      WScript.Echo " - ERROR: Failed to " & AAction(j) & " map drive " & ADrive(j) & " to/from " & ARemoteShare(j)
  End Select
  Err.Clear
End If

Set objNetwork = Nothing
wscript.echo "Done. "

Saturday, October 1, 2011

How to Set Trusted Locations for a Runtime Application

A user at AccessForums.net asked how to set up trusted locations when the end user doesn't have Access installed. You need to have a bit of a comfort level with regedit, then it is easy:

http://www.accessforums.net/security/how-set-macro-security-distributed-app-17714.html#post81250

Wednesday, September 28, 2011

Copy & Design Action in Navigation Pane Relief v2.0


I use form and report templates when putting together Access applications. This new feature of Navigation Pane Relief v2.0 takes 4 steps out of the process of turning a template into an application object.

For me, a template is simply a form or report that I copy and use as a starting point when designing a new form or report.

I used to right click on the template, select copy, specify a new name, then find the copy in the Navigation Pane, then right click on it and select "Design", then make my changes to it. With the new "Copy & Design" feature in Navigation Pane Relief, I double click "Copy & Design", I specify a new name, and then the copy is automatically opened in design mode. A great time-saver (2 steps instead of 6) if you work from templates.

More information about the new version of Navigation Pane Relief can be found at the Navigation Pane Relief Homepage.

Tuesday, September 20, 2011

Help Tab

In my latest application, I've included an extra tab on each form with help information. I thought this would be easier than writing an entire help add-on. We'll see how my customer likes it.




Other Microsoft Access Blogs

Here are some other Access blogs I check out every now and then:

http://blogs.office.com/b/microsoft-access/

http://msmvps.com/blogs/access/default.aspx

http://blog.nkadesign.com/

http://accessblog.net/

http://rogersaccessblog.blogspot.com/

Connecting to Other Applications Using Late Binding

Here are some great examples of how to use late binding to connect to other Office applications like Excel, PowerPoint, and Word.

http://www.excelguru.ca/node/10

I especially like the way the code checks first for an existing instance of the application, and if not found, gets a new one.

Documenting Using Dia

Visio is nice if you want to create a diagram to document some process or flow in your Access application, but if you don't have Visio, there is a nice (and free) alternative called Dia. You can download it here:

http://live.gnome.org/Dia

I use a flowchart diagram to help me track where different queries are used and what they are used for.

Print Barcodes from Access

You'll need a barcode font, if you don't already have it. Once you install the barcode font on your computer, you can print using that font and your text or ID number will appear as a barcode that can be scanned. Here's the site to download the barcode font:

http://www.idautomation.com/fonts/free/

Longevity

A customer from 10 years ago just contacted me to upgrade ShrinkerStretcher to the latest version. So I would have to assume that he was using the 10 year old version all of this time. That's longevity!

We're now on ShrinkerStretcher version 11.7. You can find out more at http://www.peterssoftware.com/ss.htm

Learn the Structure of an Access Database

A client of mine has requested, as part of a training session, to provide an overview of Access. Well, I've never done this before - usually I provide training only for an application that I've developed. So I was pleased to find this on the web. It's basically an outline of a beginning Access course:
Learn the Structure of an Access Database
Thank you, Microsoft.

Windows 7 and Mapped Network Drives

Mapped network drives sometimes show a big red X in Windows 7. When you open them with Explorer, all seems to work OK. But if you have to connect to them first with a program like Access that may be trying to connect to a database back-end you'll get a nasty error message, and the back-end won't connect.

The problem may be that Windows 7 attempts to connect the network drive before the network connection is established. Sounds like it could be a bug.

The work around is a registry entry (hack?) that you can implement with this handy .reg script. Just create a .reg file and put this text in it:

REGEDIT4


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System]

"EnableLinkedConnections"=dword:00000001

... then open it ("Merge" it) on each Windows 7 computer that has the problem. Reboot and all should connect OK.

UPDATE 9/15/2011:

A user reports that the above registry fix/hack did not resolve the problem. So I'm still looking for a way to get Windows 7 to reconnect network drives AFTER the network connection has been established. Can you help? Any ideas?

DBForums Access Code

Need some free Access database code? Some samples to help you get started, or help you learn something new? Check out the DBForums Access Code bank: http://www.dbforums.com/microsoft-access/1605962-dbforums-code-bank.html

Lots of great stuff to download and incorporate into your database.

Moving Posts to This Blog

I'm moving some posts from my new blog "Deep into Access" to this blog which I started years ago, but never continued. I like the blogspot software better. So, this old blog will be my new new blog. I'll rename it to "Deep into Access" shortly