How to specify relative path to Access 2007 in ADO connection string? [Excel 2007]

sektor · September 3rd, 2009, 05:19 AM

I wanna connect to Access 2007 database from Excel 2007. When creating ADO connection string, I gotta specify FULL path to database. How to make it RELATIVE?

Shasur · September 3rd, 2009, 07:47 AM

Hi

If you are coding this from an Excel workbook you can try as shown below:

Code:

sPAth = ThisWorkbook.Path


Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPAth & "MyDB.mdb;Persist Security Info=False"
Cn.ConnectionTimeout = 40
Cn.Open

Cheers
Shasur

sektor · September 3rd, 2009, 08:06 AM

Sorry. Shasur. I didn't finish my sentence. I knew this method with ThisWorkbook.Path, but I wanted to get to know, can I omit ThisWorkbook.Path if database is located in the same folder with Excel file?

Shasur · September 3rd, 2009, 10:05 PM

Hi Sektor

Unfortunately the current path / Application path is not taken by the program if its left empty in the connectionstring

I feel it would take the default path that is set in the path variable - mostly Mydocuments if the path name is not mentioned for the database

Cheers
Shasur

sektor · September 3rd, 2009, 11:59 PM

By the way, yes, when I don't specify path, then home directory is taken.
If so, gonna use ThisWorkbook.Path! :)