0

I have this database project where currently there are numerous large tables with all the data we need. An append query is called to fill out a temporary table which will narrow down our search results. Finally, a select query is called to grab just the correct data we want from the temporary table. With this query table filled, it populates a list on a user form. This query is attached to the temporary table. The user can modify this data in the list. These changes are recorded into the temporary table and when the user clicks save, the temporary table updates the data in our database. If they cancel the changes, only the temporary table changes not the database tables. The issue is that I want to get rid of this temporary table as it can retain information leading to errors.

Currently I am trying to use one query to obtain the data we need and use this query to populate my list. The only issue is how can I modify the list data and only update the database when the user presses save. In other words, what can I use to replace the temporary table? Can a query obtain information, the user can edit the data in the query, and this query can then update the data in the database tables when the user so chooses? Can a query act as my temporary table?

If you guys have any ideas, please let me know! I haven't found this problem on the internet so maybe writing to a query using lists and VBA isn't possible.

Alex Korfel
  • 19
  • 1
  • 1
    Sure, you can use ADO to query a database. Here's an example. https://stackoverflow.com/questions/10352211/vba-ado-connection-and-query-parameters – Ryan Wildry Oct 12 '17 at 15:15
  • As suggested by others use ADO. You can populate a `UserForm` with `Select` and then on click of a button run `Insert into` or `Update`. In this case `UserForm` will replace your temporary table. – Victor K Oct 12 '17 at 15:22
  • 1
    Please illustrate your points with actual data. Lot of words that are general in scope, not specific enough to be helpful. – Parfait Oct 12 '17 at 17:10
  • _I want to get rid of this temporary table as it can retain information leading to errors._ ... Well, if that is so, so will your query. – Gustav Oct 13 '17 at 10:08
  • @Gustav A SELECT query won't contain anything until it's executed. It doesn't retain information. – nicomp Oct 13 '17 at 15:34
  • @nicomp: Sure: _so will your query result_. – Gustav Oct 13 '17 at 15:49

2 Answers2

0

I think you could experiment with a disconnected recordset.
I made some experiences with that a few years ago, then asked a question and finally got an answer that seemed good, but which I never really tested.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
0

"I want to get rid of this temporary table as it can retain information leading to errors."

I actually suggest that you keep the temporary table. You can easily take steps to ensure it does not retain information.

First, I would suggest you use the same temp table each time (i.e. don't create a new temp table every time you do what you described in your question). Just clear the temp table before and after each time you need to use it. A simple currentDb.execute "delete * from TempTable" at the beginning and end of your procedure should do the trick.

Another thing that might help: I like to keep my tables in a separate back end database. These tables would exist as linked tables in my front end database. This allows me to have a VBA script that runs when a third "shell" database is opened which copies the front end database to a designated folder on the user's local C drive. Having your database structured in this way would allow you to keep that temporary table in your front end database, separate from your actual database tables in the back end. Anytime a user opens up the database (via the "shell") they will be starting with a fresh clean temp table. There are a number of other advantages to this structure as well (different users can make changes at the same time, need to compact less frequently, runs faster).

David Furman
  • 1
  • 2