Set directory for data source in VBA

Question

The following code in VBA is used to connect to the Access data file:

Public Const sConnect As String = "Provider = Microsoft.ACE.OLEDB.12.0;" & _
                              "Data Source = D:\data.accdb"

I am trying to set the directory for the data source from MS Access by using the functions like ActiveWorkbook.Path or CurDir():

Public data_source As String
data_source = ThisWorkbook.Path & Application.PathSeparator & "data.accdb"

and then:

Public Const sConnect As String = "Provider = Microsoft.ACE.OLEDB.12.0;" & _
                                  "Data Source = data_source "

but it didn't work and the program said "Invalid outside procedure". It seems that ActiveWorkbook.Path works only inside a Sub? Is there any way to do this properly? Thank you a lot.

Answer 1

Constants have to be, well, constant. That means you can't use anything that has run-time state in a constant. Same thing for public accessible members (variables declared outside of a procedure) - VBA doesn't have the concept of a "static constructor", so you can't set the initial state of module level variables to anything that requires a function call (there are a handful of exceptions).

If you need to set the connection string dynamically, just build it at runtime. Put the constant part in a Const...

Public Const provider As String = "Provider = Microsoft.ACE.OLEDB.12.0;" & _
                                  "Data Source = "

...and append the data source right before you make your connection:

'Inside the procedure you create the connection from.
Dim source As String
source = ThisWorkbook.Path & Application.PathSeparator & "data.accdb"

Dim conString As String
conString = provider & source
'Use conString to create your connection.