CODEWITHSUNDEEP
vbaexcelms-access
AiRiFiEd
AiRiFiEd

Reputation: 311

Opening ADODB Connection from Excel - Error 424

Am trying to create a generic function to return an open connection. However, code bugs out at the last line when i set the function to the connection object. Need some directions to get this going. Thank you!

''
' Function to open an ADODB connection and return the connection object
' @param    strDBPath string containing full path to database of interest
' @param    strUserID optional string containing user name to use when establishing the connection (default: vbNullString)
' @param    strPassword optional string containing password to use when establishing the connection (default: vbNullString)
' @param    intOptionsEnum optional integer to open the connection synchronously (-1) (defualt) - 16 to open connection asynchronously
' @return   Object containing opened ADODB connection
' @remarks  Have only tested this on .mdb database (not .accdb)
Public Function vfnc_StartConnection( _
    strDBPath As String, _
    Optional strUserID As String = vbNullString, _
    Optional strPassword As String = vbNullString, _
    Optional intOptionsEnum As Integer = -1 _
    ) As Object

    Dim objConn As Object: Set objConn = CreateObject("ADODB.connection")
    Dim strDataSource As String: strDataSource = "Data Source=" & strDBPath & ";"
    '#If VB7 And Win64 Then
        strProvider = "Provider=Microsoft.ACE.OLEDB.12.0; "
    '#Else
        'strProvider = "Provider=Microsoft.Jet.OLEDB.4.0; "
    '#End If
    Set vfnc_StartConnection = objConn.Open(strProvider & strDataSource, strUserID, strPassword, intOptionsEnum) 'Error occurs here
End Function

Upvotes: 0

Views: 1375

Answers (2)

YowE3K
YowE3K

Reputation: 23974

The Open method of the Connection object does not return an object. Therefore it isn't appropriate to set something to objConn.Open.

You would probably just want to return the opened objConn object itself, i.e.

    'Open the connection
    objConn.Open strProvider & strDataSource, strUserID, strPassword, intOptionsEnum

    'Now that the connection has been opened, return the connection to the calling routine
    Set vfnc_StartConnection = objConn

Upvotes: 3

hstdggsdtgsdafssarf456
hstdggsdtgsdafssarf456

Reputation: 129

Here is my take on that, using ConnectionString properity, partially based on answer by YowE3K. 

Public Function vfnc_StartConnection(strDBPath As String, Optional strUserID As String = "admin", Optional strPassword As String = vbNullString, Optional intOptionsEnum As Integer = -1) As ADODB.Connection

    Dim objConn As ADODB.Connection
    Set objConn = New ADODB.Connection

    Dim strDataSource As String
    Dim strprovider As String
    strDataSource = "Data Source=" & strDBPath & ";"
    strprovider = "Provider=Microsoft.ACE.OLEDB.12.0; "

    objConn.ConnectionString = strprovider & strDataSource & ";User ID=" & strUserID & ";Password=" & strPassword & ";"
    objConn.Open

    Set vfnc_StartConnection = objConn
End Function

Upvotes: 3

Related Questions