CODEWITHSUNDEEP
excelvbams-accessconnection
steviemac2000
steviemac2000

Reputation: 55

Creating connections to Access database from Excel using VBA

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)

Upvotes: 2

Views: 1135

Answers (2)

Hamed
Hamed

Reputation: 96

Use this:

Path = ActiveWorkbook.Path & "\"

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

Path = ThisWorkbook.Path & "\"

Upvotes: 0

Storax
Storax

Reputation: 12167

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

Upvotes: 1

Related Questions