Running of Update Queries when I open the Database
Thank you for taking the time to report an issue.
What's wrong... Please write below.
I have two update queries in my Access database. Query_Update_Avail and Query_Update_NotAvail. The queries simply set the value in a field to Yes or No indicating the line item is available or not. The queries work fine.
I have a checkbox on my form that when checked I would like to execute the Query_Update_Avail. When unchecked it would execute the Query_Update_NotAvail query. Both suppressing any warnings regarding "you are about to update X records"
I'm sure this is simple but I just need the syntax
ATM I have to click each query and input the same parameter 2-3 times. It is quite painstaking to do. Everything in my database is functional, I just couldn't find a way to shortenit.
One function has different types of queries: Update, Append, and Delete.
I have many update queries in access 2003. I have to run all the times and I would to create SQL, VBA or combination of both to create a process where I can do this by just one query, button, function etc. I can't do this in a macro because the list of these update queries can increase or decrease at anytime and then I would have to update the macro. It defeats the purpose. All of these queries start with common name "0010 01."
I want something where it says look up queries if it starts with 0070 01 then Run it.
Using this following SQL
WHERE Name Like "0010 01*";
I can come up with the list of queries but I am not sure how utilize this into VBA or anywhere else to automate the process to meet my requirements.
Like I said earlier I want something where it says look up queries if it starts with 0070 01 then Run it. I am open to other options if you have any
What I want is 1) to open the form with no queries running, giving chance to insert an ID in the main form
2) for each query to run only when its page is selected
These queries, defined as Microsoft Action queries, will allow the user to update data (Update Query), delete data (Delete Query) or add data from one database table to another (Append Query).
The Form was created using several queries which were built from tables off of the server. The tables update every day,
The problem I am running into is that it takes about 2 minutes to open the DB (the DB opens directly to the form and all queries run immediately to update), which is annoying to end users and might deter them from actually using the tool.
I have tried moving the DB to SharePoint, but that did not work due to the size of many of the tables.
Is there a way to, maybe, write a macro that runs each query individually, and stores the result from each query? Any ideas on how to automate the running of these queries, and store the results?
Source information loaded in my database comes from a text file (550 MB, 670,000 rows & 100 columns) extracted from Peoplesoft. There are 38 tables:
One table that stores all data. (670,000 records and 150 columns)
And additional 34 tables used to map data. The biggest ones have 74,000, 7,000, and 11,000.
There are 70 queries and 30 queries are Update queries that update the main table that contains all records.
This same database has 2 recorded macros. One of them runs all 26 update queries.
The columns have no primary key or indexes or an identifier that makes the record unique.
I also need to constantly compact and repair it.
1) The macro takes 1:30 hours to run 26 update queries alone. It takes too long!
2) After this macro runs, some of the queries are successful and some are not but no error message is generated...
Dim AppAccess As Object
' Create instance of Access Application object.
Set AppAccess = CreateObject("Access.Application")
'Open Service Desk KPI Metrics Queries.mdb database in Microsoft Access window.
AppAcsess.OpenCurrentDatabase "C:\Documents and Settings\ricop.dongol\My Documents\Service Desk KPI Metrics Queries.mdb", False
'False so as not to open in exclusive mode
Set AppAccess = Nothing
after run command I received error msg object required?