Database Locking During Query Creation - Need Help!!

Oct 13, 2005

why is it that as soon as you fix one problem another one needs dealing with :mad:

OK, the problem i have is that i have a BE/FE configuration database in a multiuser environment. I have built all the tables and the relationships in the BE configuration. In the FE configurations i have built the main forms to input data. It has no come to the point where i have begun to create the queries to allow reporting and data searching. And this is where i have a problem. I am trying to create a simply query and i do mean simple (at the moment i simply want 2 fields from 1 table and 2 fields from another table) no calculations or anything i just want it to display the data. I have tried creating this query numerous times and i keep getting the same error.

"The wizard is unable to open your query in datasheet view, possibly because another user has a source table open in exclusive mode. Your query will be opened in design view"

the above is the error i get when i use a wizard to create the query, after getting this message and going into design view i try to view the results in datasheet view and get the following error "Type Mismatch in expression"

If i dont use a wizard and create it in design view, when i go to view the results i get the second error message everytime.

If i create a query from 1 table only the query works without errors, but i always get errors when i have more than one table in the query.

Also i am the only person with the database open so therefore it is total impossible for the database to be open by another user in exclusive mode.

Anyone know what the problem might be or how to fix. Your answers will be very much appreciated.

View Replies


ADVERTISEMENT

ERD Creation Of IT Audit And HelpDesk Database

Dec 9, 2005

Hello forum!

Im pretty new to access and im trying to create a database for the company i work at so that to begin with they can log details on all hardware they have.

See the image attatched, at the moment i wish to store details on there computers and also on other hardware is it ok sofar?
http://img210.imageshack.us/my.php?image=erd9xa.jpg

View 1 Replies View Related

Problem With Planning Database + Creation Of Forms

Aug 11, 2005

Hi guys
Quick question. im running a planning database which has got 3 tables Objectors, Representations and Agents. Ive put these together using a one-to-many join matching up the index fields from Objectors and Representations. Ive just constructed a form which has a main form being the Representations and the sub forms with Objectors and Agents. When I want to update or add records a message saying " you cannot add or change a record because a related records is required in table Objectors.

Can any one tell me how I can get around this or possible solutions to this is? Any comments or feedback greatly appreciated!

Cheers

View 5 Replies View Related

General :: Creation Of Database To Replace Forms

Apr 1, 2014

I have been given a project from work and although I have built multiple databases before, for some reason this one is stumping me. I need to create a database to capture data which was previously being captured on multiple spreadsheets to simplify the statistics program at my company. Each spreadsheet captured key pieces of information, I've attached an example of each. Each sheet needs to be set up so that an individual can enter their information for their location and that one person (me) can enter the full company information. For example the hours form, from this form I need to capture the employee count and manhours, whereas from the district sheet I need to capture contractor hours, vehicle data (second tab), and performance data on a monthly basis. The final entry needs to be able to enter all of the counts on the counts link form in order to create a table similar to the link form.

View 1 Replies View Related

Database Creation And Testing Complete / Will Making It ACCDE Clear Everything Except Forms

Feb 7, 2015

I've created and tested my database. What step or steps do I take now to make it so it's a program that only shows the forms and menu's I created, not all the Tables, Queries etc and the formatting options? Is there a link that explains what to do step by step?

View 2 Replies View Related

Locking A Database

May 8, 2005

I have a company wanting to asses my database and i am willing to show them but i need to be able to lock the database so that they can view it but cannot edit anything in it either adding deleting or plan editing. How can i do this.

Bullfrog
The Amateur

View 4 Replies View Related

Locking Database

Dec 6, 2007

Hello,

As my username states, I am a rookie to MS Access. I built a database using Access 2002, I will distributed it to other people, I will like to lock changes to certain areas such as forms, design view, etc.

Can this be done? The help is greatly appreciated!

Rookie2007

View 14 Replies View Related

Locking A Database

Feb 17, 2005

I have looked on the forums and cant quite find what im looking for so i wanted to ask you lot for help.

I have a database i have created.

It will be sent out to people to populate via a series of forms i have created.

How can I lock it down so that:
1) you cant access any of the database apart from the forms
2) Lock it so no one can get in to view the code anywhere or make any amendments to the forms and database what so ever

View 3 Replies View Related

Help In Locking Database

Feb 1, 2005

I am new at this stuff. So please excuse my ignorance. I created a database which a lot of my colleagues want a copy. I don't mind giving them a copy. However I don't want them to change the design or copy it to give to others. I don't mind them adding their records.

Is this possible to do? Or should I just keep the database to myself.


Thanks in advance to your advice.

View 1 Replies View Related

Locking Of Access Database.

Nov 29, 2005

I have prepared an access database on a shared drive location(lan) with 5 tables. The main table has over 110 fields and is linked to a form. On an average the table stores 12000 records, which are updated via a form.

Normally, 7 users work on this form and do add/change/delete operations on the records.

The frequent issues I face are:
Corruption of records.
"Could not update, currently locked" errors.

I have constantly tried compact and repair option, but the same does not give required results.

Kindy advise a solution.

View 2 Replies View Related

Locking A Database File

Feb 2, 2006

Does anyone know how to lock a database so it can be accessed by only one person at a time? We have several users using a 'local' copy of the same database and frequently synchronizing with a network copy. We want to avoid a situation where more than one person is trying to synchronize with the master. Please advise - thanks!

View 2 Replies View Related

Split Database Locking

Jan 5, 2007

I have a call tracking database with 5 active users. Ocasionally the database will freeze, usually for several people at once. It will say:
"Can not update record; Currently Locked"

The only way to fix it is to close and re-open. I split the database, compiled it and then created an MDE file to distribute. They are using copies of the front-end, not links. The database locking more and more every day. But we are using it more. After parusing the forum I'm starting to suspect that this is a problem of "Primary Key duplication" If everyone trys to create a record at the same time, would the database lock because we all created the same record at the same time?

If so, how do I get around this? Use something other than Autonumber for the primary key? Or is this an entirely different problem?

By the way, my record locks are set to "No Locks"

Thanks!

View 2 Replies View Related

Locking Down The Structure Of A Database

Aug 7, 2007

Dear All,

is there any way to look-down the structure of tables within a database so users cannot change tables?

Is it possible to make this password protected so only certain users have rights to change the layout of the tables?

Would this still allow users to create queries and enter data?

Thanks, Steve

View 1 Replies View Related

How Do I Find Who Is Locking The Database?

Nov 13, 2007

Have an Access database that is used by multiple users, the lock file seems to have vanished. Yet the database is in a read only state and still locked. How do I unlock the database? How do I find out who is locking it? How do I kick the person off is neccesary to unlock the database? Thanks.

View 5 Replies View Related

Multiple User / Database Locking

Feb 16, 2006

Another baffling problem - there are far more traps for new players than I ever imagined. Any suggestions gratefully received.


I have a fe/be split database with multiple users on a network. All users have the same front end on their PCs and are either running Access 2000 or 2003.

Problem 1:

Some users cannot open the database at all, getting the error message "Could not open xxxxx.mdb - File already in use."

Problem 2:

Some users can open the database but get a message in the status bar saying "Recordset not updateable."


Other users, running either Access 2000 or 2003 have been able to use the database without difficulty. The number of users is in single figures and it is unlikely that more than 4 people have attempted to log on simultaneously.

The database default open mode is set to "shared", the default record locking is set to "No locks" and the "Open database using record-level locking box is checked".

I have been having difficulties setting the security for the database (see other postings on this site) and so it is currently unsecured with no password set for the admin user. Users are joined to their default system Workgroup Information Files.

My guess is that there is a setting on the Access installation of the user's PC that needs to be changed but this is only a guess - anybody any idea what I should try next?

Regards,

Keith.

View 5 Replies View Related

Queries Locking The Back End Database?

Jul 23, 2012

I have created two DBs, one Back End, and one FrontEnd. My FrontEnd contains all the UIs/Forms, Linked Tables, and the queries fetching data from linked tables, and the Back End consists of all the Tables.

Now, in my Model, the BackEnd DB will be stored in a common drive and will be accessed by multiple users through same FrontEnd.

Now when I am trying to fire a query in FrontEnd (aka FE) DB, it is locking up the Back Up database. Even though I have set the RecordLock property to No Locks to query and the form.

how to remove the locking?

View 5 Replies View Related

How To Make Creation Of A Record In A Table Trigger Creation Of New Table

Jul 17, 2013

I have a table that is a list of all of my events. Each record of events should have a child table that list all of the things that happened at the event. When a new record (event) is added how can I have a new child table created and linked to that record. Also I have a blank table to serve as a template for what each child should look like. How do I make sure this occurs? The child tables can have the same name as the index. I am just using numbers 1- for the index with 1 being the first event and so on?

View 8 Replies View Related

Modules & VBA :: Attachment Locking The Entire Database

Jun 13, 2013

I have access BD and every one use it through shared folder. My problem is in attachment ! , if i use the regular attachment filed in access there is one user only able to attach files and others can`t attach any thing until he finish.

I tried to do attachment through VBA like below which make it worse as it lock the DB so, no one can even open the DB and one user only access the DB .

Dim db As DAO.Database
Dim rsParent As DAO.Recordset2
Dim rsChild As DAO.Recordset2
Set db = CurrentDb
Set rsParent = me.Recordset

[Code] .....

How multi users attach files at the same time without locking the DB.....

View 3 Replies View Related

Run Query On Record Creation

Aug 9, 2007

I been tasked with a project to be written in access which I am rather unfamiliar with, web design is more my area.

For part of the project I need to copy some pricing fields from a pricing table to a new record at the point that the new record is created. The new record is created when a 'new form' button is clicked.

What I would like to know is what and where I should trigger the copy query from and what function should I be using?

cheers
John

View 1 Replies View Related

Need Help On Join Creation Query

Mar 12, 2008

Hi! Im wondering why my code below Does not work!.... any suggestion???


strStatement = "SELECT SessionData.RunNumber, SessionData.SessionID, SessionData.SequenceNo, SessionData.SubjectID, SessionData.Latency, SessionData.BeamBrk1, SessionData.BeamBrk2, SessionData.BeamBrk3, SessionHeader.SessionDate, SessionHeader.ChamberNo" & _
"SessionHeader.GroupID, SessionHeader.SubjectVariables, SessionHeader.TestDefName FROM SessionData" & _
"INNER JOIN SessionHeader ON SessionData.RunNumber = SessionHeader.RunNumber;"

getQueryname = InputBox("Enter a name for New Query?", "Enter a Label or Name for Query", "Enter Query name/Label Here", 500, 700)

Set qryEmployees = curDatabase.CreateQueryDef("getQueryname", strStatement)

View 4 Replies View Related

Record Locking - Multi User Worklist Type Database

Mar 9, 2006

All,


Bottom line I'm in bit of a pickle, work has moved forward the migration of Office97/NT4 to Office2003/XP by 2 months (clever lot) and was hoping not have to cram for this question until at least 3-5 weeks.

I'm looking for pointers/suggestions because I now have to test in Access2003 Runtime on MONDAY!!!!

What it is, is:

I have a query which is a list of items that need to be worked out of 65,000.
Of that 65,000, 20,000 end up in the query (Actually do need looking at after an Automated process)
(it's adapting the query/process I think I need)

Query:

EntID - UniqueID for a household
Applicant1 - Number lookup value for Applicant 1
Applicant2 - Number lookup value for Applicant 2
Qualifies - 1 = Yes, 2 = No, Null = Not worked.

That's basic building block of the Query which is Drives the main Form.

What I would like is a scenario similar to this:

Person A opens record 1

Person B opens record 2

Person C opens record 3

Person C finishes record 3

Person B finishes record 2

Person C opens record 4

Person B opens record 5 (he/she took a little break)

Now that's the way I would like it to work, but there will be other factors and this is where I'm all ears for anyone who is used to this type of system.

The problem I can't figure out is that the Main Form has several subforms, but none of the data is to be edited, it is there for visual purposes only.
The users will be creating records via the Main Form through code, but not directly into any tables/queries with which I can use conventional record locking, that I can see.

My thoughts are that Person A calls up a record based on Min EntID and somehow locks it so the Person B looks for Min EntID Where not locked.

--------------------------------------------------
Question Time:

Please, please can someone point me in the direction of how to do it and more importantly the correct order of events?
I have tried doing the Min EntID and locking the record, but while Person A is running the Min Query, Person B is running it also so they end up with the same record as B has the record on screen while A is locking it.

Also,
In the real world Person B might open record 2 and think, nah I can't be bothered.
I would like a proffessional opnion on whether C opens record 2 or should B be made to deal with it.

I think not given the huge time constrants landed square on my lap, I would get there with smaller questions, but I've spent the last 10hrs writing a Function, to get it ready for the testing lab, where the owner has changed the requirements 7 times and my head isn't working.

All or some help given will be GREATLY received.
Any further information needed, just ask.

Hope you can help.

Cheers,

Ian Mac

View 4 Replies View Related

Form Creation With Query Source

Aug 8, 2007

do you guys use a query as your data source in you form?
if not how do you build a form with mutilple tables with the same unique id.

View 1 Replies View Related

Queries :: Query Creation For Drop Down

Apr 17, 2013

I have an issue with designing a query.i have three tables (there is more but these should do for this)

Game Review
reviewer
Game

i need a query that will only select games that the reviewer has not reviewed.its to populate a box on a add review form so that when a reviewer has been selected, the next drop-down will only show games that have not been reviewed yet by that reviewer.

View 4 Replies View Related

Modules & VBA :: Record Locking Multi-users Over Network Split Database / Access 2007

Aug 5, 2015

I have designed a database which I intend to split for multi-users to access from one front end icon on a shared folder at work. I have designed a form bound to cmr record table and on it a subform to another table where cmrs activities will be saved. The form has buttons to and blank controls.

User can create a new activity entry by typing into the blank controls and pressing the save button which saves to the activity table. the edit button extracts a selected records details (selected on the subform) from the activity table and populates the blank field for a user to edit and then hit save to save changes. The delete button deletes a selected record from the activity table. I intend to have multi-users either accessing, viewing and a possibly editing the same customer at the same time. The simultaneous viewing is essential but the simultaneous editing, though not desired is inevitably going to occur.

What I would like to know is:

1. Can you lock an individual record in a table or does the whole table have to be locked. E.g If Colleague 1 is editing Cmr A's record in Table1 can he lock it so Colleague 2 can view and edit Cmr B's record in Table1

2.Can Colleague 1 access/read Cmr A's record in Table1 to retrieve details toe the form controls if Colleague 2 is viewing or editing Cmr A's record in Table1

3. If record lock is possible, how can I initiate it in my example code below.Edit activity record Code

Code:
Private Sub Edit_A_Click()
'Get Data to text box control
With Me.R_P_Data_P_Subfrm.Form
Me.txtrID = !rID
Me.txtrID.Tag = !rID
Me.txtrefNo = !refNo
Me.cmbrpc = !rPC

[code]....

View 8 Replies View Related

Many To Many Junctions Locking Query

Nov 13, 2013

I have a record source on a form - I have two junction tables for many to many and as soon as I add the second junction it locks the query and I can't edit the details/fields content.

View 14 Replies View Related

Locking User Access To Database Objects (MS Access 2000 And 2003)

Apr 15, 2007

I have two database applications and they are:
- the (A) application is for administration use.
- the (B) application is for normal users use.

the idea is that: I made the (A) application for administrators who have full control over the database objects (tables, forms, queries, and so on ...).

the (B) application I have created for normal users who will have only to use forms to insert some data and display data only.

but the two applications has a respective table called "vacation request" table. where I linked them, so the both administrators and users can share the data.

The real question is that: How can I prevent the users from seeing the database objects in their application. I used the database options which have helped me in hidding the database objectives when the users open the application, but unfortunately they managed to access to the database objects by pressing the special keys.

I would like to have an access to the (B) application when I want to make some modifications to the forms and then lock it from users where they only have to use the forms for requesting vacations and view the vacations.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved