Connection to Database

[scoop10000]

Could someone advise me on the best time to make a connection through to a database

I'm relatively new to VBA but started making connections through to the database successfully a few months ago. I now want to improve my understanding of the correct methodology.

I find now that I am adding a lot of connections strings within various actions on the same Access form. I presume I could make one connection on the onLoad event and set it to nothing on the close event. Would this be an inaccurate assumption.

For example the code below is the connection I use. Currently this is coded several times on the same form which i feel is inefficient

    Dim rstProducts As New ADODB.Recordset
    
    Set curdb = currentdb
    Set CurConn = New ADODB.Connection
         
    With CurConn
        .Provider = "Microsoft.jet.oledb.4.0"
        .ConnectionString = "data source=" & curdb.Name
        .Open
    End With
    
        
    Set rstProducts = New ADODB.Recordset

Any comments would be appreciated

 

[modest]

When working in the same database, it's easier to use DAO.

When working with other applications, that's the time to use ActiveX (ADO)

 

[scoop10000]

Thanks for that.

Longer term my intention is to work with other applications. i just guessed that if I took the steps now I would get a greater understanding

 

[modest]

Connection holds the connections string amongst other things (the path, the password, login, driver, etc) to the file.

Unless you globalize the connection data/variables, I dont believe you can just open it in the load and close it in the unload.