keeping 'Current Project' as the ADO path

[miconian]

I'm using ADO to make a SQL query in VBA.

I don't want to have to specify the path to the database. The database I'm querying will always be the same file in which the code is found, and its location will change. But I'm not sure how to set the path to "this file."

Here is a snippet from my code to give you a general idea:

Function AnythingThere()

Dim strConnection As String
Dim cnConnection As ADODB.Connection
Dim cmdCommand As ADODB.Command
Dim rsRecordset As ADODB.Recordset

Set cnConnection = New ADODB.Connection
Set cmdCommand = New ADODB.Command
Set rsRecordset = New ADODB.Recordset

' Right here is the problem. I don't want to have to specify a path and filename.
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & CurrentProject.Path & "\MyDatabaseName.accdb;"

cnConnection.Open strConnection

rsRecordset.Open "SELECT * FROM tblSupplier", cnConnection

 

[miconian]

I think it's actually just
"Data Source=" & CurrentProject.Path & "\" & CurrentProject.Name
If that's true, then I've answered my own question.

 

[xenou]

CurrentProject.FullName also works (it is path + name already).

However, if by "the database I'm querying will always be the same file in which the code is" you mean the code is in your access database then you can use the current project connection without creating a new connection:

Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open "Select x FROM y", cn

Example:

Sub Foo()
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim i As Long
Dim s As String

    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    Call rs.Open("SELECT * FROM Table1;", cn)
    If Not rs.EOF Then
        Do While Not rs.EOF
            s = ""
            For i = 0 To rs.Fields.Count - 1
                s = s & rs.Fields(i).Value & "|"
            Next i
            Debug.Print Left(s, Len(s) - 1)
        rs.MoveNext
        Loop

    Else
        Debug.Print "No Records."
    End If
    rs.Close
    Set rs = Nothing

My_Exit:
If Not rs Is Nothing Then
    rs.Close
    Set rs = Nothing
End If
Exit Sub

ErrHandler:
Debug.Print "Error " & Err.Number & ": " & Err.Description
Resume My_Exit

End Sub

 

[miconian]

Thanks very much. Your code is going to be my new template for a bunch of stuff I'm doing.

 

[miconian]

p.s. Since I'm keeping it all in the same file, should I be using DAO instead?

 

[xenou]

I use and do recommend DAO for Access VBA programming - it's very good at interacting with the Access database engine and is being actively supported and developed by the Access development team (rumors of it's replacement by ADO a few years back turned out to be incorrect).

In that case, you'd find it goes like this:

Dim rs As DAO.Recordset
CurrentDB.OpenRecordset "SELECT * FROM Table1", dbOpenDynaset
'Do Stuff
rs.Close
Set rs = Nothing

My only tips are that with DAO it is sometimes recommended you are careful to close and set DB objects to nothing (such as your recordset objects). I don't know if this is really true or not, but supposedly it's not as good as ADO in this regard and objects that you open need to be explicitly closed. Also, take some time to read the VBA DAO help files and figure out how they are organized (it's confusing at first but after a while becomes a useful resource at your fingertips - especially if you are used to ADO since there are differences).

If you google DAO Allen Browne you get many good pages! For example:
http://allenbrowne.com/ser-29.html
http://allenbrowne.com/func-dao.html
http://allenbrowne.com/ser-04.html