CODEWITHSUNDEEP
sqlpostgresqlms-access
A. W.
A. W.

Reputation: 671

PostgreSQL & Access -- Connecting at start vs. Connect when needed

I have a PostgreSQL 9.5 database with an Access 2016 front-end, using an ODBC driver to connect the two. If I wanted to query data, I would start with the following commands:

Dim conn As New ADODB.Connection
conn.Open "Driver={PostgreSQL ANSI};Server=localhost;Database=[name];User Id=[UID];Password=[pass];"

Dim cmd As New ADODB.Command
cmd.ActiveConnection = conn

My question is this: Is there any reason why I shouldn't establish this connection the moment the application opens, using that connection whenever I need to run a query, as opposed to opening and closing the connection each time I run a query?

I'm unsure what, if any, overhead is involved in establishing such a connection in Access, and I've been unable to find any information on the topic. Apologies if this is a naive question.

Upvotes: 1

Views: 58

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49169

I the connection is cached by Access anyway.

Once you touch, or open any linked table, then the connection is now active, and re-used by Access.

In general if the query is against a single table, then there little reason to not just code against the linked table.

Eg:

   Dim rst     As DAO.Recordset
   Dim strSQL  As String

   strSQL = "select * from tblInvoices where invoiceNum = 13355"

   Set rst = CurrentDb.OpenRecordset(strSQL)

If you using a pass-though query, then this works well:

Sub Test555()

   Dim rst     As DAO.Recordset

   With CurrentDb.QueryDefs("qryPass")
      .SQL = "select * from tblInvoices where invoiceNum = 13355"
      Set rst = .OpenRecordset
   End With

End Sub

The reason for above is you thus don’t mess or have to bother with connection strings in your code.

Just ensure that you table re-link code also re-links any pass-through query.

Upvotes: 2

Related Questions