473,883 Members | 1,741 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Run an Access query from VBA in the background

1 New Member
Does anyone know of in VBA to have a query in the background. I already have warnings set to false. What I'd like to happen is the query is started then control returns to the subroutine while the query runs in the background.
So the user can move to another screen while the query runs.
Dec 27 '12 #1
8 13929
5,501 Recognized Expert Moderator Expert
You'll find that VBA is not normally an asynchronous programing language. That is to say, that each line of code normally executes and then the next, and so on.

With that said, What I have done with queries that seem to take awhile is to open a non-modal form that handles the query and once the form is open, the next line executes: So you get VBA-Line1-somecode, VBA-Line2-opensform, VBA-Line3-somemorecode- where line 1, 2, 3 execute in order. In the meantime, the form has opened and its onload and open events are running.
Now once that form is open, you can assign that query to be the record set for that open form and go from there.

In reality, I've only one database I need to do this for and that is because it isn't even remotely normalized!
Dec 27 '12 #2
8,834 Recognized Expert Expert
You should be able to Execute a Query Asynchronouosly by using an ADODB Command Object along with the adAsyncExecute Option of the Execute Method. Let's suppose that you had an enormous Products Table and that you wanted to increment the Reorder Levels for all Products by 25%. Let's further suppose that you did NOT want to wait until the Query was finished executing before you ran some other Code:
  1. Set a Reference to the Microsoft ActiveX Data Objects X.X Object Library
  2. Execute the following Code which should run the Query Asynchronously (Not Wait):
    Expand|Select|Wrap|Line Numbers
    1. Dim cmd As ADODB.Command
    2. Dim strSQL As String
    4. Set cmd = New ADODB.Command
    6. strSQL = "UPDATE Products SET Products.ReorderLevel = [ReorderLevel]*1.25;"
    8. With cmd
    9.   .ActiveConnection = CurrentProject.Connection
    10.   .CommandText = strSQL
    11.   .CommandType = adCmdText
    12.     .Execute adAsyncExecute
    13. End With
    15. 'Execution will immediately fall to the Sub-Routine where Code execution
    16. 'will continue while the Query is still running
    17. Call SomeRoutine
Dec 27 '12 #3
5,501 Recognized Expert Moderator Expert
Very true; however, OP didn't provide details as to wither this was all-in-one access or FE/BE relationship. Which is something I should have asked... wherein you could send the backend off to do it's work and leave the frontend open to the user.

I also tend to avoid the active-x stuff as MS has a history of breaking their stuff with an errant kill bit :( or the object is blocked by IT somehow - don't ask... we still don't know how they did it - just that it required a new, clean installation of my desktop.
Dec 27 '12 #4
8,834 Recognized Expert Expert
wither this was all-in-one access or FE/BE relationship
Would not make a difference unless it was a Client/Server architecture (*.adp), would it? The Query would still be processed at the FE, wouldn't it?
Dec 27 '12 #5
5,501 Recognized Expert Moderator Expert
Take for example a SQL-Server. The stored procedure when called would be asynchronous to the access front end.
Also if the query is sent as a pass-thru query wherein the JET/ACE doesn't deal with the SQL in the least - it simply passes the SQL to the backend.

For example. The main Lab LIMS (laboratory information management system) is ran on a SQL server. I have a front-end built (actually, one of the IT guys tweeked one of my frontends to get the ODBC calls correct) using MSACCESS to pull several of my reports. In this case, the calls are sent to the SQL stored procedure and the reports are que'd and sent back. I'm not 100% sure what they've done on SQL side to get that to work (I suspect SSRS is running); however, the PDF shows up in my email :). The server is HUGE and pulls from labs from around the world; thus the asynchronous query is a must - as called from the stored procedure ( How to Execute SQL Stored Procedures from Microsoft Access ) (also a tidbit here: Get stored procedure output value back in VBA.

So, as I mentioned... totaly remiss of me not to inquire about the nature of the database.
Dec 27 '12 #6
32,584 Recognized Expert Moderator MVP
I believe ADezii's point is that a connection could even be made to the currently open FE database and his approach would still work Z.

For completeness, I should add that there is also a dbRunAsync flag available when opening DAO objects also, but which only works with ODBCDirect workspaces. DAO ODBCDirect Connection and QueryDef objects also support an Execute() method which takes a dbRunAsync flag.
Dec 27 '12 #7
5,501 Recognized Expert Moderator Expert
I still am learning. :)
None-the-less I get the impression here of fe/be and not all-in-one.... but this then leads me to the following:

QueryDef objects also support an Execute() method which takes a dbRunAsync flag
Now is this within a DAO ODBCDirect Connection or is these native to both an all-in-one and a split db?
Dec 28 '12 #8
32,584 Recognized Expert Moderator MVP
Now is this within a DAO ODBCDirect Connection or is these native to both an all-in-one and a split db?
I'm not quite sure I understand exactly what you're asking (or maybe even implying), but my understanding is that a DAO ODBCDirect connection could be used to connect to a BE file (Only ODBC though, not Jet.), although I'm not sure whether or not any BE can be connected using that type (I would guess not from the wording of the .Connect Help page). It seems to me that the splitting of any part of the project into FE/BE or not has no bearing whatsoever on whether this feature is available. Only the type of link to the file used for .Execute().

Does that make sense?

I still am learning. :)
Like the rest of us Z, you're learning as you teach. I'm certainly not past learning from other experts (and even those not recognised as such) on here. I'm sure (absolutely sure that is) that were you not otherwise employed you could earn your living at this. No worries.
Dec 28 '12 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

by: david liu | last post by:
access 2000 query: here's what i want to do. from an asp page, perform a search on a table in access. i have used sql code in the asp page itself, but i'd rather execute a query in access. i have success in running any query (basic SELECT, SELECT with conditions _other_ than LIKE, etc..) for some reason, when i execute the query below from the asp page, i get no results. the search.asp page just has a text box in a form that submits...
by: Art | last post by:
Hi, Can anyone point me to an example of how I would execute a query I've created in an Access DB. I've copied the SQL down to my VB.net application and that works fine, but it's ugly. I'd like to direcly execute the query that I created in Access - if possible. Also, I would like to be able to add a parameter to that query. For example, if I'm in Access and put an unknown field in the query, I get prompted to enter that info when the...
by: s_wadhwa | last post by:
SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber, UCASE(Buildings.BuildingName) AS BuildingName, Buildings.MasterPlanCode, Buildings.UniformBuildingCode, Buildings.FunctionalCategoryCode, Buildings.OwnershipCode, Buildings.ConditionCode, Format$(,"0000000") AS dBasicGrossArea, Format$(,"0000000") AS dCoveredUnenclosedGrossArea, IIf(Month()>9,Month(),"0" & Month()) & Year() AS dDateOccupancy, Buildings.YearConstructed,...
by: ShastriX | last post by:
Getting a weird error while trying out a query from Access 2003 on a SQL Server 2005 table. Want to compute the amount of leave taken by an emp during the year. Since an emp might be off for half a day (forenoon or afternoon), have the following computed field: SessionOff: ( And ) The query works fine when there's no criterion on SessionOff.
by: zwasdl | last post by:
Hi, I'm using MS Access to query against Oracle DB via ODBC. Is it possible to use HINT in Access? Thanks, Wei
by: Yashesh Bhatia | last post by:
Hi Sorry if it's a trivial question. I'm a newbie to js and did some reading since the last 3-4 hrs but have not been able to get the answers, hence this post. essentially i'm trying to access the bacground color of a div which i need to use in javascript. here are my files
by: =?Utf-8?B?bXNjZXJ0aWZpZWQ=?= | last post by:
Has anyone successfully used an Access query from .NET? I am trying to do this and am getting a weird error. .NET calls queries 'stored procedures'. The error I am getting says "Schema could not be retrieved for this stored procedure' ... "The underlying enumerator did not support enumerating objects of type 'ProcedureParamater'". .Net does recognize the stored procedure i.e. it does appear in the drop-sdown list.
by: k-man | last post by:
Hi: I have an MS Access query for a table called MyTable. One of my fields in the query is a custom field that looks like "MyField: = MyFunction(ID)" where ID is a field in MyTable. I have ASP code and I can use it to read the query OK when "MyField" is not part of the query. If I add in MyField and then run my ASP code to try to read all the query fields, I get an error message on my web browser which says something to the effect...
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one field between them. The join field in both tables are indexed and I'm selecting 1 field from each table to lookup. The Access query is taking more than 60 second to retrieve 1 record and if I execute the same query within the Query Analyzer, it...
by: OzNet | last post by:
I have a query (with calculated fields) in Access (2007) and the data changes depending on the dates and staff person selected. I need to produce a series of graphs based on the data in this query and it seems to me that Excel is far easier to create graphs than Access. When I use the Get External Data feature of Excel and the dialog box with the tables and queries appears, the query I need is not listed. Is this because the query has...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.