2

I have an Excel workbook with several connections to queries in an Access database. I want to be able to share this with colleagues on a USB key, but the connection string has a direct path to the database like so:

DSN=MS Access Database;DBQ=C:\USERS\Me\Desktop\Database.accdb;DefaultDir=C:\;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;

From what I can tell a relative path can't be used here. Is there a way to get the current path of the Excel file when opened and create the connections in VBA? (the database file and the Excel file will always be in the same relative location)

Erik A
  • 31,639
  • 12
  • 42
  • 67
steviemac2000
  • 35
  • 5

2 Answers2

1

You can retrieve the path to the desktop like that

sPath = Environ("USERPROFILE") & "\Desktop"

That means you can use the following in your connection string

sPath & "\Datbase.accdb"

Update In order to make it more robust it would be a good idea to add a function if the database file exists. You could use a function similar to this

Function fileExists(s_directory As String, s_fileName As String) As Boolean

    Dim obj_fso As Object

    Set obj_fso = CreateObject("Scripting.FileSystemObject")
    fileExists = obj_fso.fileExists(s_directory & "\" & s_fileName)

End Function
Storax
  • 11,158
  • 3
  • 16
  • 33
0

Use this:

Path = ActiveWorkbook.Path & "\"

If you want the path to the workbook the code is residing in, use:

Path = ThisWorkbook.Path & "\"
Inarion
  • 578
  • 3
  • 14
Hamed
  • 96
  • 5
  • I think just worth mentioning is this only works because the OP said "the database file and the Excel file will always be in the same relative location". – Storax Sep 12 '18 at 12:39
  • 1
    @Storax Which is exactly why I think this is (conceptually) the better answer. OP mentions transporting workbook + database on a USB stick - this lets me assume it won't always be started from the desktop. In that regard, your method will fail. – Inarion Sep 12 '18 at 12:49
  • Yes, right, from that perspective this is the better answer. It, of course, depends on the needs of the OP. – Storax Sep 12 '18 at 12:53
  • @Storax Which (to me) wasn't entirely clear from the question when I first read it... ;) – Inarion Sep 12 '18 at 12:54