You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Last night I was working on a database where I wanted to run a temporary query from a linked table that I had. The query was temporary because it's being used in a tool that I'll only run a handful of times. Nevertheless, the data from the query is going to end up in a production database, so I wanted to view the results of the query before clicking the button that said, "Yes, go ahead and make this data live." The query is written using SQL text in a text box on a form because I'm going to pass it to a function, and the fact is that there are a few linked tables, with different schemas and criteria.
Creating a temporary query is pretty straight forward. DAO has had the ability to create a QueryDef object without a name for many years, and the database engine treats these as temporary in the sense that you can use them but they're not saved to the file. These work great for action queries, and perhaps even SELECT queries when you're going to process a Recordset. Again however, I really wanted to view the data in the query before processing the Recordset. Enter my dilemma.
It would be really great if the OpenQuery method had an acDialog parameter like OpenForm and OpenReport that would open the datasheet view of a query but block running code until the query window was closed. Barring this, it looks like I might need a form or some way to fake it. The problem with a form is that nothing appears in the form until there are controls on it. This means that creating a temporary form to display the temporary query is somewhat cumbersome. The other issue with using a form of course is that you can't create them at runtime in an ACCDE or MDE. Guess I'll use a query and try to fake the experience.
The ultimate point here of course is that the query is temporary. After viewing the data, I want it to go away. Here's some code written behind a button named cmdPreviewQuery which does this. As you can see, the SQL for the query is retrieved from a text box on the form called txtQuery.
Private Sub cmdPreviewQuery_Click() On Error GoTo PreviewQueryErrorHandler ' name of the temporary query Const TEMPQUERYNAME As String = "_TEMP_" Dim qd As DAO.QueryDef Dim db As DAO.Database ' create a query using the specified SQL text in txtWFQuery Set db = CurrentDb Set qd = db.CreateQueryDef(TEMPQUERYNAME, Me.txtWFQuery) ' open the query and wait for it to close DoCmd.OpenQuery TEMPQUERYNAME While (CurrentData.AllQueries(TEMPQUERYNAME).IsLoaded) DoEvents Wend Cleanup: ' suppress errors in case the query does not exist On Error Resume Next ' delete the query when we're done with it DoCmd.DeleteObject acQuery, TEMPQUERYNAME On Error GoTo 0 Exit Sub PreviewQueryErrorHandler: MsgBox "Unhandled error: " & Err.Number & vbCrLf & Err.Description, vbExclamation Resume Cleanup End Sub
Once the query is created using CreateQueryDef in DAO, we'll open the query using DoCmd.OpenQuery as mentioned earlier. Since there is no 'modal' or 'dialog' experience for the query, I'm using a loop that waits for the query to close by checking the IsLoaded property of the AccessObject object for the query. The DoEvents statement in the loop ensures you can still interact with the query if you wanted. This is handy because you could use the query designer to modify the query to build what you want - all interactively while the code is running.
I'd love to hear feedback about this. Has anyone needed to do this before and if so, how did you accomplish it?
Comments: (17) Collapse
According to blogs.msdn.com/.../send-us-your-most-used-forms.aspx I've sent a sample DB to Clint Covington. The DB contains a form & some useful functions.
We use this form to check/update data in BE database through remote access (LogMeIn). The form is a part of FE and can be open via a shortcut from a main form (datshboard). Password may be an option to open the form.
In this version the update/delete/insert queries are forbidden to run but it's very easy to bring them back to work. There's a function to log events and write the information in a SQL.sq which is a MDB database.
The sample DB is available here: www.alis.cz/.../frmDB.rar Enjoy!
Hi Vladimir, your file opens with an error in non-Czech countries because you need to use only the English version of Access (and other Microsoft tools) to develop applications
accessblog.net/.../localized-access-version-problem.html
There's another trick that we use to ease some calculations in queries.
The scenario:
1) We have several MDBs with same structure which are back-ends to an accounting system (still in development). Each MDB belongs to different company.
2) We need to handle data of each company separately.
3) We also need to join data from different companies and make same calculations that we perform on one company. The design:
1) There's a "dynamic" query where we store primary SQL-string to handle data from an approriate table/query, eg. qry_Cis3Uc03. SQL-string can vary from single SELECT on currently linked MDB:
SELECT Cis3Uc03.* FROM Cis3Uc03;
to a union of many companies:
SELECT Cis3Uc03.*
FROM [D:\Test\01\frmDB.mdb].Cis3Uc03
UNION ALL SELECT Cis3Uc03.*
FROM [D:\Test\02\frmDB.mdb].Cis3Uc03
FROM [D:\Test\03\frmDB.mdb].Cis3Uc03; 2) There's another query that is based on qry_Cis3Uc03, eg. qryCis3Uc03 (with some calculations).
3) We dynamically change SQL-string in qry_Cis3Uc03 to get data from various DBs through qryCis3Uc03 as an input for data presentation layer (reports/graphs). This is a simple & unexpensive solution to handle Access data from one or more companies. Sample is available here: www.alis.cz/.../SampleMDBs.rar (183kB) The easiest way is to unrar the archive in D:\Test... or you may want to re-link tables if you want to play with SELECT Cis3Uc03.* FROM Cis3Uc03; This is a just demonstration how to use "dynamic" queries. I'd recommend SQL-server for "more companies" scenario. ;-)
IMHO, dynamic queries is a powerful tool for Access developers. P.S. I'm sorry for my English but I hope you've got the idea.
Hi, grovelli, your link doesn't work.
Anyone else having problems with my A2002 sample MDBs? gravelli: What exactly you get when you try to open the sample DB?
"The expression On Open you entered as the event property setting produced the following error: A problem occured while Microsoft Access was communicating with the OLE server or ActiveX Control..."
robcooper, ClintC: What do you think? See "grovelli said on November 14, 2008 3:33 PM:" in this thread.
grovelli: "your file opens with an error in non-Czech countries because you need to use only the English version of Access (and other Microsoft tools) to develop applications"
You may be wrong. Developers in US can open my A2002 MDBs without any problem. To check out go to www.utteraccess.com and search for posts of Vladimir, e.g. www.utteraccess.com/.../showflat.php
At least, Peter Schroeder - UtterAccess VIP & Microsoft Access MVP, was able to open my MDB. ;-)
grovelli: Could you try Debug, Compile?
There must be a bug in Access: system name Tělo (A2002 CZ) was not interpreted as Detail (A2003 EN), see my conversation with grovelli.
Hi Rob,
In general your approach is good, but what i dont like - that query window is not modal, so user can switch to other window and loose it. For such cases i use a "general dtatsheet form" approach - you have a form with several textboxes and you set it recordsource and controls controlsource on form open. then you can open it in real dialog mode. you also do not need to save querydef, just pass sql text
Vladimir,
this is not a bug, this is a limitation of VBA, or COM, on which VBA based - it does not support unicode
My Access add-in Ezy SQL, which has been around for 5 years, automates this process, and also formats the SQL and pastes the text block into the VBE window, and includes many other features. Go to www.aadconsulting.com/ezysql.html or visit MS's Office Marketplace.
Alex: Tělo is a system name of Access for Detail section of a form/report. It does not have to do anything with VBA - except if there are any event procedures based on such names. Access should have NEVER translated system names. An interesting thing is that "Po klepnutí" (On Click) is correctly interpreted as Click if you create an event procedure, eg. btnOK_Click. This is just section names of forms/reports which are not handled correctly. So it's a bug...
I just don't understand why we have translations for system names. Those translations make various language version of Access incompatible. BTW, at the moment we're testing a huge MDB project that was created in A2002 }and continuing in A2007 MDB) in A2007 EN - we didn't experience any problem with system names yet, maybe due to Win XP CZ? Don't know.
Temporary action queries I create as select queries first prefixed by zzz. Then I call those inside the action query. I don't have any action queries as named queries in my production database. If the action query gets at all complex, such as joins between two tables, I always create it as a Select query so I can look at the data and decide it's reasonable. Then I run the Action query based on the Select query in the VBA code.
Comments: (loading) Collapse