Write Standalone "Open DataBase Connection" Function

[ilcaa]

Hi, I am trying to write a standalone function that opens a connection to Access and keeps it open until another function closes it... I am trying to call this function from another function(s) multiple times so Id like to open it and leave it accessible to other Subs or Functions...

So far I have this but it errors out, the problem I see is how do other function see that the connection is opened and use it... thanks for any help.

Const ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="&QuaccessDB & ";Persist Security Info=False"

FUNCTION OpenConnection()

Dim CN as New ADODB Connection
CN.Open ConnectionString

END FUNCTION

FUNCTION n_Days_Performance()

query = ' my query to run'

'Open Connection to Access DB via Function
OpenConnection

Dim RS as New ADODB RecordSet

rs.Open query, CN

'Do calculations based on returned data'

result = (cal1/cal2)/cal3

n_Days_Performance = result

END FUNCTION

 

[Comfy]

Try defining CN as a Public object.

At the moment it can only been seen by other object within the OpenConnection Function.

https://msdn.microsoft.com/en-us/library/office/gg264241.aspx

 

[Kyle123]

How about:

Public Function DataBaseConnection() As ADODB.connection

    Const ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myDataSource;Persist Security Info=False"
    Static cnn As ADODB.connection
    
    If cnn Is Nothing Then Set cnn = New ADODB.connection
    
    If cnn.State = adStateClosed Then
        cnn.Open ConnectionString
    End If
    
    Set DataBaseConnection = cnn
    
End Function

 

[ilcaa]

this is what i have , it errors in the 3rd Function. Do i have to pass a DataBaseConnection parameter to the external function that retrieves the query (3 vba code) ??

Public Function DataBaseConnection() As ADODB.connection

        Const ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myDataSource;Persist Security Info=False"
        Static cnn As ADODB.connection
        
        If cnn Is Nothing Then Set cnn = New ADODB.connection
        
        If cnn.State = adStateClosed Then
            cnn.Open ConnectionString
        End If
        
        Set DataBaseConnection = cnn
        
    End Function

SUB TEST()

sym = 'goog'
Set X = DataBaseConnection()
range("a1").vvalue = LastDate(sym)

END SUB


FUNCTION LastDate(sym)

Dim rs as New ADODB RecordSet
Dim query as String

query = 'my query'

rs.Open Query, X , adOpenForwardOnly, adLockReadOnly

LastDate = rs.Fields(0)

 

[Kyle123]

You need to pass X into your LastDate function, or just use DataBaseConnection in LastDate function directly

You should also be using option explicit

 

[ilcaa]

If I add DataBaseConnection to LastDate function then it will open it up everytime LastDate is called... right? This is what I want to avoid. I want to open it from the Sub 1x and pass it to whatever Function with a query in, Sub will contain a loop to retrieve data, so I dont want to open/close connection for each loop...

How can I pass X to LastDate Function?

ei Function LastDate(X as connection) is that it? thanks

 

[Kyle123]

No, it won't, that's the point of the database connection function, it only opens it once then reuses it. That's what the static keyword is for

 

[ilcaa]

so within the LastDate function I will add DBConnection like this .... correct? and I can remove the reference to DBConenction from my original sub?

FUNCTION LastDate(sym)

Dim rs as New ADODB RecordSet
Dim query as String

query = 'my query'

x=DatabaseConnection()  'Public Function that accesses DB Connection

rs.Open Query, X , adOpenForwardOnly, adLockReadOnly

LastDate = rs.Fields(0)

End Function

 

[Kyle123]

Yes, would need to be set x though

 

[ilcaa]

yes i forgot to include Set X =


thanks for your help with this...