Deep into Access

Introduction to Access Programming

2012-01-06T15:06:00.000-08:00

I get a lot of questions from people who are new to Access development and have never programmed before. Here's an introduction to the subject:

Introduction to Access Programming

(Disclaimer: Please realize that getting involved in programming can be life-changing - not necessarily for the better!)

Map Network Drives with VBScript

2011-12-29T10:46:00.000-08:00

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. "

How to Set Trusted Locations for a Runtime Application

2011-10-01T20:41:00.000-07:00

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

Copy & Design Action in Navigation Pane Relief v2.0

2011-09-28T16:08:00.000-07:00

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.

Help Tab

2011-09-20T16:03:00.002-07:00

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

2011-09-20T16:03:00.001-07:00

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

2011-09-20T16:02:00.001-07:00

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

2011-09-20T16:01:00.001-07:00

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

2011-09-20T15:59:00.002-07:00

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

2011-09-20T15:59:00.001-07:00

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

2011-09-20T15:57:00.002-07:00

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

http://office.microsoft.com/en-us/access-help/learn-the-structure-of-an-access-database-HA010341808.aspx

Thank you, Microsoft.

Windows 7 and Mapped Network Drives

2011-09-20T15:57:00.001-07:00

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

2011-09-20T15:56:00.001-07:00

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

2011-09-20T15:53:00.000-07:00

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

Colorific

2010-09-23T10:57:00.000-07:00

Can a form be too colorful?

We'll find out. I'm about to release a new product with a very colorful interface. With this application the colors are substituting for icons. Here's a look at the main form:

Can you guess what it does yet?

Understanding Access 2007 Layout Grouping

2007-11-20T20:10:00.000-08:00

Here's a look in design mode at one of the new Access 2007 features - Stacked Layout. The highlighted controls are grouped together in a table-like structure in such a way that when you change the width of one control, the other controls in the same column change width as well. Same for controls in rows. This is a nice feature for helping to keep controls organized on a form.

But what if you want to manipulate these controls at runtime? Say change the size and/or position of these control? Like so (from a command button OnClick event procedure):

Text0.Left = Text0.Left + 100

You'll find that these controls are quite a different animal. First, moving one control will move ALL of the controls in the group - well, depending on which of the controls in the layout group you move, that is. In my experimentation, if you attempt to move any control in the right column, nothing happens. But moving the control in the upper left moves all the controls in the group.

But what if you change a font in one of the controls, does it change for all the controls in the layout group? No.

Weird. More on layout groups later.

First Post

2007-11-20T18:26:00.000-08:00

Hi. This is intended to be a blog where I post up some musings, code and or examples of what I'm working on or what I've discovered about Microsoft Access. I'm an Access developer who finds himself digging deep under the hood of Access at times, and sometimes I find something interesting to share with the Access developer community. We'll see how this goes. I've tried blogging before and it just seems to be hard to keep up with. Please bear with any large gaps between posts.

Thanks,

Peter De Baets