Hi, I'm new so forgive me if this is the wrong forum for this.
The short story is this: I'm developing a project in Access and I'm trying to keep connections to the backend database to a minimum to keep performance high. The problem is Access likes to open a connection and keep it open even when it isn't actually doing anything. Right now, I'm trying to write reports and when you run a query or a report it keeps a connection open to the backend so long as the query or report is open. What I would like is a way to just run a simple query to retrieve the data and then work with that data in memory without maintaing a connection to the backend.
In my low-tech way, I'm determining if a connection is open by looking for a lock file for the backend database.
I had the idea of using an ADO disconnected recordset, but while that seemed to work ok with forms, it doesn't seem to work for reports.
My "last resort" solution is to write code to create temporary files in their own seperate mdb files to allow users to have an open connection without putting a load on the "master" database, but I'm hoping for a simpler solution.
Any ideas? Thanks!
The short story is this: I'm developing a project in Access and I'm trying to keep connections to the backend database to a minimum to keep performance high. The problem is Access likes to open a connection and keep it open even when it isn't actually doing anything. Right now, I'm trying to write reports and when you run a query or a report it keeps a connection open to the backend so long as the query or report is open. What I would like is a way to just run a simple query to retrieve the data and then work with that data in memory without maintaing a connection to the backend.
In my low-tech way, I'm determining if a connection is open by looking for a lock file for the backend database.
I had the idea of using an ADO disconnected recordset, but while that seemed to work ok with forms, it doesn't seem to work for reports.
My "last resort" solution is to write code to create temporary files in their own seperate mdb files to allow users to have an open connection without putting a load on the "master" database, but I'm hoping for a simpler solution.
Any ideas? Thanks!
