473,898 Members | 2,344 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query Def and multi user environment

I'm trying to build a search utility for users to find 'inquiries' in
my database that involves several tables. This is normally easy to do
with the query builder, but I have a unique situation involving a multi
select listbox. Unfortunatly, my SQL skills are somewhat limited, so
I'm not sure if there is an easy way around it. To simplify the
explanation, I'll simplify the table/field setup to get at the meat of
the question.

I have a table with a field called IssueID in table tblMain, and
another field called ProductType in tblProductType. Each IssueID may
have many ProductTypes. tblMain and tblProductType linked together in a
one to many relationship.

Now, I have a search form, which allows users to enter in parameter
query criteria for tblMain. However, criteria for the linked table
tblProductTypes are in a multi select list box. Clicking the 'Find'
button needs to find all matches in tblMain limited to only IssueID's
that contain at least one of the ProductTypes in tblProductType.

It's easy enough to build an SQL statement of the listbox by cycling
through all selected objects and concatenating a string together. The
sticking point is that I need (well, at least I think I need) an actual
object to reference for the INNER JOIN statement in the SQL statement
for tblMain.

I researched some code for QueryDef's, but I'm not sure this will do
the trick. I found the following code in the Help File, which is easy
enough to understand.

Sub NewQuery()
Dim dbs As Database, qdf As QueryDef
Dim strSQL As String

' Return reference to current database.
Set dbs = CurrentDb
' Refresh QueryDefs collection.
dbs.QueryDefs.R efresh
' If RecentHires query exists, delete it.
For Each qdf in dbs.QueryDefs
If qdf.Name = "RecentHire s" Then
dbs.QueryDefs.D elete qdf.Name
End If
Next qdf
' Create SQL string to select employees hired on or after 1-1-94.

strSQL = "SELECT * FROM Employees WHERE HireDate >= #1-1-94#;"
' Create new QueryDef object.
Set qdf = dbs.CreateQuery Def("RecentHire s", strSQL)
' Open query in Datasheet view.
DoCmd.OpenQuery qdf.Name
Set dbs = Nothing
End Sub

The problem here is that I'm seeing the old query getting deleted and a
new one made each time this would run. I'm thinking that could be a
problem in a multi user environment if two users clicked the 'Find'
button at the same time. I.E., the queryDef is made by User1, but
before the SQL statement executes to reference that new query, User2
has already deleted and replaced the query with their own criteria
based off the multiple objects user2 selected in the listbox.

Is there a way I can do this all in a single SQL statement? Or are my
fears about queryDefs in multi-user environments unfounded?

Thanks in advance for any help or advice any of you may have.

Jun 13 '06 #1
4 3194
The problem here is that I'm seeing the old query getting deleted and a
new one made each time this would run. I'm thinking that could be a
problem in a multi user environment if two users clicked the 'Find'
button at the same time. I.E., the queryDef is made by User1, but
before the SQL statement executes to reference that new query, User2
has already deleted and replaced the query with their own criteria
based off the multiple objects user2 selected in the listbox.

Is there a way I can do this all in a single SQL statement? Or are my
fears about queryDefs in multi-user environments unfounded?

Why would this be a problem at all if you split the FE and BE? Then
the qdf would be in the copy of the FE on the local machine, so by
definition, only one user could use that qdf at a time....

Jun 13 '06 #2

pi********@hotm ail.com wrote:
The problem here is that I'm seeing the old query getting deleted and a
new one made each time this would run. I'm thinking that could be a
problem in a multi user environment if two users clicked the 'Find'
button at the same time. I.E., the queryDef is made by User1, but
before the SQL statement executes to reference that new query, User2
has already deleted and replaced the query with their own criteria
based off the multiple objects user2 selected in the listbox.

Is there a way I can do this all in a single SQL statement? Or are my
fears about queryDefs in multi-user environments unfounded?

Why would this be a problem at all if you split the FE and BE? Then
the qdf would be in the copy of the FE on the local machine, so by
definition, only one user could use that qdf at a time....

I wish I could split it. But, I'd have to distribute the FE to 500+
people (It has low numbers of concurrent users before any of you freak
out), and any time a change was made I'd then have to figure out an
effective means of redistributing it to all 500 people. Since it's not
a capital project, the IS department wont give me any backup by just
pushing it out to the designated users.

It's basically a research tool that people will be using infrequently
(perhaps two or three times a month for some). They don't use it often,
but when they do it has to work without any hassles. (such as upgrading
their FE to the latest version).

If I could develop something I knew I'd never have to change, then I'd
do it in a heartbeat. However, they are constantly re-organizing
departments and/or workflows, which can require significant changes to
the UI. In short, this is something that I have to constantly maintain,
and that just isn't easy to do in a FE/BE setup.

Jun 14 '06 #3
Okay, I see the problem!

You could do something like grabbing the currentuser or the machine
name and save the query with that name, which would make it unique.
(Or some variation of that, say as a suffix)

You might want to look at Tony Toews' site to read up on distributing
the FE automatically. Not trying to do the fingerwag thing, but just
open up some options you may not have considered.

http://www.granite.ab.ca/access/ufad.htm

Tony covers a lot of common problems, so you might want to take a look
before you write FE distribution off.

Hope it helps,

Pieter

Jun 14 '06 #4
Some good ideas there. I am already capturing Novel login names for
other purposes, makes sense that I could use that to prevent
collisions.

And thanks for the pointer to the Automatic FE distributer. I'll check
that out. A performance gain from distributed setup would certainly be
welcome as this database is beginning to chug under current workload.

Jun 15 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1756
by: Job Lot | last post by:
In a multi-user environment, how would I prevent users from adding Orders with identical OrderID. For instance I have following tables in my database Order ================== OrderID Identity field CustomerID OrderDate DeliveryAddress
2
2868
by: Scott Bryce | last post by:
I am creating a CGI application in Perl that uses an Access database. It will be hosted on an NT server. I have used flat file DBMs (tied hashes) on UNIX servers, but I am not familiar with how Access behaves in a multi-user environment. Will I need to use some sort of file locking/semaphore scheme, or does Access (or the ODBC drivers?) manage conflicts between multiple users? I want to avoid corruption of data should multiple users...
11
2429
by: deko | last post by:
If I release a new version of my mbd (in mde format) and users want to upgrade - how do they migrate their data? For example, if the original was released as data1.mde and then I release data2.mde (same table/data structures - just revised VBA code with perhaps a new/updated form or two), what's the best way for users to get their data into the new data2.mde? I'm thinking I could use a query - something like:
5
3703
by: premmehrotra | last post by:
I currently have a multi-user access database which is put on a shared drive L: on a Windows Servers. Entire database is one file premdb.mdb. Users access this database from their laptops. Following problems occur: 1. Access is way too slow in WAN environment. Server is located in New Jersey and users are in California and Puerto Rico. 2. Database often becomes corrupt 3. When one user updates some data in the database, other users...
3
4485
MMcCarthy
by: MMcCarthy | last post by:
I know this question has been covered extensively in the past, but I'm wondering if anyone has any new thoughts on the subject. I have a Frontend/Backend Ms Access database. The frontend will be an mde. This database will work in a multi user environment but should not be too heavily trafficed. I know I can compact the backend by checking for ldb and compacting file to a copy file then copying back to original. Likewise, I can...
2
5552
by: john | last post by:
Is it true that if I split my access database in backend and frontend and I implement custom auto increment for the ID fields, that my database is ready to be used in a multi-user environment? I found a zillion messages about auto increment and read some of them but it's confusing. Can someone point me to a simple custom auto increment example that I can download? Thank you, john
3
8167
by: olafbrungot | last post by:
I get the following error message when trying to connect to a host database (OBCD connection) with "MS Query". SQL0471N Invocation of routine "SYSIBM .SQLTABLES " failed due to reason "00E7900C". SQLSTATE=55023 I've found that this is a error with running the stored procedure SQLTABLES, but I don't understand why?
7
3527
by: irfanali | last post by:
Hallo All, I m trying to create a Multi User Data Program on Access, where my team members would input data on a daily basis. Well the Problems start now : - How do i create a Multi-user Environment, so that my Backend is on the Sharedrive(Local Server) and the Front end on the respective user systems? - Well, once i achieve that, then how do i hide the Access data like Tables, Queries and Forms from Viewing by the user on the Front...
0
1299
by: mirandacascade | last post by:
Apologies in advance for what I'm guessing may be a trivial question. Assume the following: 1) multi-user environment 2) standard (i.e. not a class module) module with: Public strSample as String 3) first user user opens app at 9:00 and runs code that results in setting the variable strSample to "abc"
0
9993
marktang
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...
0
9839
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,...
0
10857
jinu1996
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...
1
10946
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10481
tracyyun
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...
0
9658
agi2029
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...
0
6076
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4705
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3303
bsmnconsultancy
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.