How do I make my data source in the current directory?

Question

My db access is always in a diferrent path but in the same folder than my woorkbook.

Private Sub CommandButton14_Click()

  Dim cn As Object
    Dim rs As Object
    Dim strSql As String
    Dim strConnection As String
    Set cn = CreateObject("ADODB.Connection")

    'here I want to use current directory as path for my mdb
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=D:\FicheMacro\PGDB.mdb"
    strSql = "SELECT Count(*) FROM AQ_DGE_MOD;"
    cn.Open strConnection
    Set rs = cn.Execute(strSql)
    MsgBox rs.Fields(0) & " rows in MyTable"
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub

I'm not familiar with Connections, but [this](https://stackoverflow.com/a/2814014/1726522) may help. — CMArg, Aug 08 '17 at 16:06

score 2 · Accepted Answer · answered Aug 08 '17 at 16:24

2

As said in the comments, I'm not familiar with connections. But something similar to the following might work.

'insert this two lines in your code
Dim folderPath As String
folderPath = Application.ActiveWorkbook.Path

'change strConnection to the following
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & folderPath & "\PGDB.mdb"

answered Aug 08 '17 at 16:24

CMArg

1,525
3
13
28

Glad to help :)! – CMArg Aug 08 '17 at 18:41

How do I make my data source in the current directory?

1 Answers1