Open Database in directory Code

I need help with this code

Problem is with this line "pth = GetFolder("P:\")"

I am trying to allow user to open a directory to open a database.

Sub OpenDB()
 Dim db As DAO.Database
 Dim ws As DAO.Workspace
 Dim rst As DAO.Recordset
 Set ws = DBEngine.Workspaces(0)
pth = GetFolder("P:\")
 Set db = ws.OpenDatabase _
Who is Participating?
MarkVrenkenConnect With a Mentor Commented:
Public Function getFolder() As String
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
   Dim PathName As String

   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      ' Allow user to make multiple selections in dialog box in this case i set it to false
      .AllowMultiSelect = False
      ' Set the title of the dialog box.
      .Title = "Please select the file with the data"
      ' Clear out the current filters, and add our own.
      .Filters.Add "excel files ", "*.accdb, *.mdb"
      .Filters.Add "All Files", "*.*"
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
     'Loop through each file selected and return the pathname.
          For Each varFile In .SelectedItems
            PathName = CStr(varFile)

getFolder = PathName
         MsgBox "Procedure has been canceled"
      End If

   End With

End Function

Open in new window

("P:\*.mdb") has got to be a specific database.  To clarify, are you looking to open a dialog box to allow the user to pick a database?
do you have a function called getfolder?
If the answer is yes to the FilePicker question that I asked, then the answer would be as follows:

Set db = ws.OpenDatabase(getFolder())

I would recommend the following change to MarkVrenken's code:
Line 21:       .Filters.Add "Access files ", "*.accdb, *.mdb"

Other than that, code looks good.
Thanks, you are right about the adjustments, nice one!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.