| So far, this chapter has focused on opening a database using ADO. If you don't have access to ADO, or if you're opening a database that uses the Jet database engine (as a practical matter, this means Microsoft Access databases), you should consider using DAO instead. As mentioned earlier in this chapter, DAO is optimized for Jet and you'll get better performance using DAO on Jet databases than using ADO. (Admittedly, the delta isn't large.) And DAO syntax is identical to ADO syntax in many ways, so when it comes time to shift your code from using DAO to using ADO, the change will not be as painful as it might have been to shift from, say, Lotus 1-2-3 to Excel. Declaring a DAO Database You begin using DAO to access a Jet database by establishing a reference to DAO, just as you did for ADO. With the VBE active, select References from the Tools menu. Scroll down until you locate the DAO object library, fill its check box, and click OK (see Figure 8.8). Figure 8.8. The latest version of DAO depends on which version of Office you have installed. 
With the reference established, you declare an object variable to represent the database. For example Dim dbGLDatabase As Database
You would not normally use the New keyword in the declaration, as you often would when declaring an ADO connection. The reason is that in ADO, a connection really does start out as a new one: It doesn't exist before your code begins to run (although its data source usually does). In DAO, there is no object that directly corresponds to an ADO connection. (A DAO Connection object is a reference to a database; in other words, it's a Database object.) You point your code at an existing database and open it. Here's a typical example: Dim dbGLDatabase As DAO.Database Dim strDBPath As String strDBPath = "C:\Documents and Settings\Owner\My Documents\GL.mdb" Set dbGLDatabase = OpenDatabase (strDBPath)
The full syntax of the statement that sets the database object is Set dbObject = Workspace.OpenDatabase (Name, Options, ReadOnly, Connect)
where dbObject is an object variable declared as a DAO Database. Workspace is an object that defines how your code interacts with the data source. It uses either the Jet database engine or ODBC Direct a method of interacting with ODBC data sources that bypasses the Jet engine. In general, if you're using DAO, it's because you want to use an Access database, and the Workspace object will therefore use Jet. Workspace is an optional argument and in most cases you won't need to use it. The Name argument to the OpenDatabase method specifies the path to, and name of, the database that's being opened. The Options argument, for Jet workspaces, is either True (which means that the database is opened in exclusive mode) or False (the default, meaning that the database is opened in shared mode). In exclusive mode, no other user can subsequently open the database. If you want to modify, say, a table's structure, you won't get a message that you can't because another user has already opened it: No one else can get in. But if another user has the database open before you attempt to open it in exclusive mode, you'll get a runtime error message to that effect. The ReadOnly argument also takes a True or False value. If False, the database is opened for read-write access. If True, it is opened read-only. Opening a database as read-only is occasionally useful in special circumstances. However, setting permissions for databases or password-protecting Excel projects and workbooks is usually a better strategy. The Connect argument is almost always used in conjunction with non-Jet, ODBC data sources.
Letting the User Locate the Database At times, you don't know which database your code should open. This happens most often when your code is intended to support a user whose familiarity with database usage and data management is limited to knowing where the database is stored. VBA provides a method, GetOpenFilename, that enables you to let the user browse to the database that he wants to open. The method is a member of the Application object. NOTE The GetOpenFilename method is not limited to use with databases you can use it in your VBA code to let the user identify Excel workbooks, Word documents, PowerPoint presentations, even executables any file whose path and name your code needs access to. Furthermore, you can use it in VBA code regardless of the host application; for example, you can use it in VBA code that you're writing for Access or Word.
In its simplest form, the GetOpenFilename usage is merely Dim strFileToOpen As String strFileToOpen = Application.GetOpenFilename
Figure 8.9 shows what the user sees when the second statement runs. Figure 8.9. The user sees the contents of the folder from which he last opened a file. 
If, in Figure 8.9, the user chose to select the Access database named Compact Indicators.mdb and then clicked OK, the variable strFileToOpen would take something like this value: C:\Technical Documentation\Compact Indicators.mdb
The value returned by the GetOpenFilename method includes its path. Bear in mind that when your code confronts the user with a GetOpenFilename dialog box, no file is actually opened when he clicks OK. All that happens is that the name of and path to the file selected by the user are captured and stored in a variable. If the file is to be opened, it's up to your code to do it. What if the user clicks the Cancel button instead? In that case, the variable that the method's result is assigned to takes on the value False. To keep your code from trying to open a file named False, you need to provide a way for it to recover. Looking Out for Your User If you want, you can exert more control over what the GetOpenFilename method displays to the user. Suppose that you know the user will always want to open one of the databases found in a particular folder. In that case, you can save the user some steps by means of code like this: Dim strFileToOpen As String Dim strOldFolder As String Dim dbGLDatabase As Database strOldFolder = CurDir ChDir "C:\" strFileToOpen = Application.GetOpenFilename If strFileToOpen = "False" Then MsgBox "No file was selected." Exit Sub End If Set dbGLDatabase = OpenDatabase (strFileToOpen) ChDir strOldFolder
Two string variables are declared: one to store the path to and name of the file to open, and one to store the current directory's path. The CurDir function is used to store the current directory. Then, because you know that the user will want to open a file in the C: drive's root, you use the ChDir statement to change the default directory to C:\. The GetOpenFilename method then returns the name of the file the user wants to open. Should the user click the Cancel button, the If block is entered: The user sees a message to confirm that no file was selected, and the subroutine is exited. If the user clicks OK, the path and name of the selected file are stored in strFileToOpen, which is used by the OpenDatabase method to assign the file to the object variable dbGLDatabase. Finally, the default directory is reset to the location in use before it was changed to C:\. This is just a matter of good coding manners. TIP It's a good idea to put this sort of code in its own subroutine or function. If a function, you could then set its value either to the name of the file selected or to False if the user cancelled. If a subroutine, it could accept a string variable as an argument; the code assigns the filename or False to the variable, which is then (by default) returned to the calling procedure. Either way, the calling procedure knows what to do: continue normally in response to a valid path and filename, or tie up loose ends and stop processing in response to False.
Filtering File Types for Your User You can tidy things up even more if you want, by specifying the type of file that the user can select in the GetOpenFilename dialog box. strFileToOpen = Application.GetOpenFilename _ ("Access databases (*.mdb), *.mdb")
This statement uses the FileFilter argument of the GetOpenFilename method. It limits the files shown in the main window of the GetOpenFilename dialog box. As used earlier, it shows only those files with the .mdb extension. It also restricts to MDB files the file types shown in the Files of Type dropdown. If you want to direct the user's attention to MDB files, but to allow for the possibility that the user might want to specify another type of file, you can put two (or more) file types in the FileFilter argument. The following example gives the user access to MDB files, and secondarily to any type of file, through use of the Files of Type dropdown. strFileToOpen = Application.GetOpenFilename _ ("Access databases (*.mdb), *.mdb,All files (*.*),*.*")
Figure 8.10 shows the effect of this specification of the FileFilter argument. Figure 8.10. Notice that only MDB files are visible. All files become visible when the user chooses All Files (*.*). 
The GetOpenFilename method has three other arguments that you might find useful: FilterIndex. If you've specified more than one filter in the FileFilter argument, FilterIndex identifies the one to use. The default is 1, meaning the first filter is used. A value of 2 would use the second filter first; the user can still select the first filter from the Files of Type dropdown: strFileToOpen = Application.GetOpenFilename _ ("Access databases (*.mdb), *.mdb,All files (*.*),*.*",2)
Title. Use this argument to provide text to display in the dialog box's title bar. The following shows MDB files by default, and puts Select a database in the title bar: strFileToOpen = Application.GetOpenFilename _ ("Access databases (*.mdb), *.mdb,All files (*.*),*.*",1, _ "Select a database")
MultiSelect. This argument enables the user to select more than one file. The variable to which the result is assigned must be declared as Variant because GetOpenFilename will return an array (even if the array contains only one element). You can then loop through the array to get the filenames: Dim varFileArray As Variant, i As Integer varFileArray = Application.GetOpenFilename _ ("Access databases (*.mdb), *.mdb,All files (*.*),*.*",1, _ "Select one or more databases", ,True) For i = 1 To Ubound(varFileArray) ActiveSheet.Cells(i,1) = varFileArray(i) Next i
|
