Updating Tables While Data Is Viewed
Apr 18, 2007
I have created a Production Tracking database for my company using
Access 2003. Everyone here uses Access 2000, so I have saved the file
as an access 2000 database. I am importing three tables from an ODBC
connection. These tables are being update by another department
constantly. I am using a centralized form for users to view the
information.
If the users are viewing the information and we try to update the
tables they are viewing, we get an error. My question is: Is there a
way around this problem?
This what I have done:
In the module I wrote, I am importing the tables, then renaming them
to the tables the users are viewing, and then altering the structure
of one of the tables. We cannot use linked tables, the reason is we
have only three licenses to access the software through the ODBC
connection. So as soon as three people open the access database they
take those three connections.
Any Ideas?
Thanks,
Phil
View Replies
ADVERTISEMENT
Jan 23, 2006
Hey, I am trying to figure out how to update an unbound textbox on a form to reflect conditional data in other boxes when each record is viewed (pages works with reports but I cant find something similar with forms). such as if I had an unbound textbox that I want to = firstname+" "+lastname I would want for it to update it every time a user goes to the next record.. I can't seem to figure out a command or method that executes every time someone goes to the next record.. (and Im not too familiar with all the commands that come without a GUI).. thanks for your time
View 4 Replies
View Related
Oct 12, 2004
This has to be a common problem.
I have an environment where I have two databases. One holding tables (The Data) and one holding everything else "The Programs" (Forms, Queries etc)
When the application gets enhanced I currently have no problem updating "The Programs" since I can just copy the new MDB on top of the old MDB.
This is not the case with "The Data", if I need to change any of the tables (since they contain live data, that is modified all the time) I have to physically connect to the machine holding the Data.MDB and modify the tables. The problem is there are getting to be many locations where these changes need to be made.
Is there any way to update the tables from a script (I used to use SQL to update remote tables in a mainframe world)
Thanks
View 3 Replies
View Related
Oct 8, 2004
Ok. Here's the situation. I have query setup. This query has 2 columns:
1. Agent Name
2. Sale Value
I have 2 tables.
Table1: Includes Sales for this pay period only
Table2: Includes all Sales From past to present.
We are setting up our system on Tiers and Each sale value is going to change. So, Here's what I need:
I need to run an update query that first Looks up all the records in Table1 based on Agent Name. The field that needs to be updated is called SaleValue. It needs to be updated with what's in my Query mentioned above.
Then, all the values in Table1, need to be copied (or updated) into Table2. This can be done based on the Primary Key (SaleID), which exists in both tables. So I guess we will need to run 2 separate update queries. Any ideas on how this can be done?
View 8 Replies
View Related
Dec 24, 2012
I have an Access db in a 3 person multi-user environment on a Windows network.
There is a "PRODUCTION" db and a "DEVELOPMENT" db.
Let's call them PROD and DEV.
PROD has the most current data, shipping records, item master, customer data, sales, etc. - but not the most current structure.
DEV has the most current struture - all the front end stuff - forms, functions, modules, etc. - but not the most current data.
The way I've handled this in the past (it seemed to work) was to take the PROD db and rename it to PRODX. Then take the DEV db and rename it PROD. Then open DEV (now called PROD) using the usual shortcut to PROD.
So now that opens fine, and I have to update all the tables from PRODX - and I mean ALL of them - since I don't really know what data has changed since the last update.If I try to delete records I get blocked by access because of all the related records (I don't have cascade delete set on every relationship). So I delete the whole table - ALL of them (this requires me to also delete the relationships). Then I IMPORT all the tables from PRODX (these have the current data). But now the relationships are all gone. It seemed at first that the relationships were back and intact - but when I last looked they were gone. So this is my problem.
HOW do I COMPLETELY empty ALL of the tables - or even delete them - and then restore them or repopulate them from an exact copy of the db but with current table data - and WITHOUT affecting the relationships?
Obviously any back end structural changes have to be handled differently. Usually by manually making the same change on the PROD db that I had in the DEV db - because deleting the table will cause the structural changes to be lost.
One last thing - I've been working this way on an un-split db, and now I'm in the process of splitting it - which SHOULD make updates much easier.
View 5 Replies
View Related
Jul 9, 2014
I have a database containing 10,000+ trouble tickets identified by a unique field called "Ticket ID". This is the primary key for my table. (It's on just one table.) These tickets came from an excel sheet that was exported out of a different database. (For various reasons, we've moved the tickets to access.)
Among many descriptor fields, I have a field called "SPR Type" that shows what type of ticket each entry is. (Bug, Defect, Enhancement, etc, etc) The problem we have here is... not all of the ticket entries have an SPR Type listed on my access table because not all of the tickets coming out of the original excel sheet had one.
I was given a new excel sheet containing 400 to 500 ish Ticket IDs and the SPR Type that applies to each of those tickets. How can I take that sheet and use it to update my access table? Ie. Use the excel sheet containing Ticket ID and SPR Type to populate the blank SPR Type fields for each of their respective Ticket IDs?
**Update: I'm trying something like this...
UPDATE [SPR Priority] inner join [EB Update of SPR] on [SPR Priority.Ticket ID] = [EB Update of SPR.Ticket ID]
SET [SPR Priority.SPR Type] = [EB Update of SPR.SPR Type]
But I am getting an error that says "Invalid Bracketing of name [SPR Priority.Ticket ID] and similar errors. What am I getting wrong here?
I thought about an Update query that would populate the SPR Type fields based on Ticket IDs. I could import the new excel sheet into a separate access table and update from there.
View 2 Replies
View Related
Mar 11, 2013
I am working with MS Access. The database has 2 tables.
-Parent and Student and ParentID is the Primary key as a parent may have multiple Students
-There is a form that lets me add students for a particular parent
One of the fields in the Parent Table is FeeDue. I added a field in the Parent Table called NumOfStudents..What I want to do is as follows: When a student is added on the Student Form, I want fee to be calculated automatically for display AND update the FeeDue field in Parent table. Event handler executed when a student is added (checkbox clicked)
OnClick()
{
Read NumOfStudents from Parent Table
if(student_added = true)
NumOfStudents++
else /* This is to cover student withdrawl*/
NumOfStudents--
if(NUmOfStudents = 1)
Fee= 400
else
Fee = 500
}
student_added check box is on the student form NumOfStudents and FeeDue are fields in Parent
View 1 Replies
View Related
Dec 1, 2013
I have a table in my DB called, devices. This contains all the information about various devices we have deployed in the field.
These devices are also contained in 2 other separate MySQL DB's.
What I need to do is add 2 additional field to my access table for the DeviceRecno and DeviceID of the same device from the MySQL DB's.
Adding the field is easy, but I cant think of a way to enter the recno and ID from the other DB's without typing them in manually for each one.
The common between them all is the serial number of the device, and I can get a list of serial numbers, recno's and ID in an excel sheet.
like a vlookup in excel to easily populate all the existing records with the recno and id's from the other db's?
When I created the access system there was no intention to link it to the other DB's for any reason, but that has now changed due to a lot of reasons.
View 4 Replies
View Related
Jan 24, 2005
I am updating an old Access app. The first form, FORM_A, of the app displays in maximized window - as I want them all to display. I click on a button to open a new form, FORM_B, and do whatever I need to on that form, close that window to return to the first window, FORM_A. When I do, FORM_A is now displaying much smaller.
FORM_A's Has Modal property is =Yes.
When I try to change the "Has Modal" property to NO, it alerts me that all the module and macro code will be deleted - and it is. I made a copy before continuing so I still have a copy of it.
I have the OnLoad event set to a macro that short maximizes the window. I use the same macro for the On Focus event, but it does not change the display on focus.
I've tried changing it to Has Modal = No, then copying the old code back into the blank module for that form - doesn't change the display of FORM_A.
Any ideas? Thanks.
View 1 Replies
View Related
Jun 17, 2005
I want to populate a combo box on a form with the last 10 recently viewed records from a recordset.
I have got the last 10 most recently viewed items (records) in an array
MRUL_Buffer (1 to 10) As String
I can't figure out how to get the values in the array into a combo box on the same form.
Any ideas....
Cheers
View 3 Replies
View Related
Jun 16, 2006
What I would like to do is have a field in a given record increment itself every time that record is selected. For example, say you have a table that stores items in the inventory: each item has a times_search_for_field.
Now, lets say a user runs a search which results in the following SQL:
SELECT * FROM Inventory WHERE experation_date=Today();
Now I want to have every record returned by that SQL statement increment it's times_search_for_field incremented by one. I'm just looking for the most efficiant way to do this.
I know I chould just have my asp .net code loop through the results and call an SQL UPDATE statement for each record, but it seems there should be a more efficient way of doing this.
Mike.
View 1 Replies
View Related
Mar 18, 2013
how can I get dates to show in last updated and last viewed fields..I did get last updated working but for some reason when I tried to do last viewed, last updated vanished :/
I did try writing code (but im a novice lol, I think that's why last updated vanished).I have got 2 unbound fields called HiddenLastViewed and HiddenLastUpdated thought I might need them.
View 14 Replies
View Related
Oct 23, 2013
I am working on a database which has two tables used as part of a registration and login process.
I would like a couple of fields from table one to automatically update in table two, once the fields in table one are populated without using an 'on click' event.
The reason I would prefer not to use an onclick is because the completion of the form used to generate the users table does not require any buttons for the data to save.
View 1 Replies
View Related
Jan 5, 2013
My question is this: I have a table where I'm entering employees' hours worked. Basically, it's something like this:
ID WorkerNumberDateworkedTimeStartTimeEnded
121/2/201310:00:00 AM3:00:00 PM
221/3/20132:00:00 AM11:00:00 AM
321/4/201312:15:00 AM11:30:00 AM
421/5/201310:25:00 PM11:00:00 AM
531/2/201311:00:00 AM3:30:00 PM
631/3/201312:00:00 PM10:00:00 PM
731/10/20137:00:00 AM4:00:00 PM
I have a query that (easily) determines how many hours an employee has worked on any given day. What I can't figure out at all, is how to write a query that can figure out how much time an employee had off in between shifts.
Thus far I'm able to run a query that separates this main table into individual workers by their id numbers, but can't figure out how to determine time off between shifts - as the last hour worked one day, and the first hour worked the next day are on two different lines (they are two different table entries).
View 4 Replies
View Related
Dec 19, 2006
Hello Tech gurus,
I have a database with existing data, that is not normalized, :eek: and all the data is in one field... This what I am dealing with:
As you notice the first row of data in field1 C10A CHOLEST&TRIGLY has 3 spaces to the right the next row ALTORVASTIN has five spaces to the right, the next line LIPITOR has 7 spaces to the right, and so forth, hopefully you get at what I am trying to do, I just looked at the data and it is not showing up in view of the leading spaces, but I am trying to move the data based ONLY on the position of leading spaces, example all data that has 3 leading spaces would go into its own separate column, and all data that has 7 leading spaces would go into a separate column, I have tried to use the left, mid, len functions but I cannot figure out how to move the text and keep it whole only by data position of spaces.
Field1:
C10A CHOLEST&TRIGLY
ATORVASTATIN
LIPITOR
PFIZER
SIMVASTATIN
SIMVASTATIN TEVA
TEVA
M1A ANTIRHEUMATIC N-STEROID
ETODOLAC
ETOPAN
TARO PHARMA
Thanks so much for your help... :confused:
Nana :D
View 10 Replies
View Related
Oct 15, 2005
i have 4 tables in access and i need to be able to add information in one table and it to appear in the other 3 without me having to go in to each individual table and enter it.
Is this possible?
View 1 Replies
View Related
May 18, 2007
Good morning, I'm a newbie....and I inherited this db.
I have an existing table. I need to add two rows of information. When I add the rows, they are empty. I have tried running a query with all the fields from the table plus the two rows I need, but I loose 90+ records. What am I doing wrong???
Thank you for any help...
View 12 Replies
View Related
Mar 8, 2005
I have a button on a form that queries my table for observations that meet a certain set of conditions, and then updates a field to mark these observations. But I keep getting the error message 'Compile error: Method or data member not found.' I have used the code successfully in a previous database and cannot figure out why it won't work here. Thanks for any suggestions. Here's the code:
Private Sub OK_Click()
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Appointment Letter JW Q")
With rst
.MoveFirst
Do While Not .EOF
.Edit
![Status] = "08"
![DateMail] = Date
.Update
.MoveNext
Loop
End With
End Sub
View 11 Replies
View Related
Jan 26, 2005
Need help and advice desperately,
I am trying to update about 10 tables each time a visitor visits my site..
however there are often missing counts, meaning the total data in each table are not the same frequently They are supposed to be since all the tables get updated each time.
Im using MS Access 2003.
What could be the problem?
Below is an example of one of the function that updates one of the tables in my DB.
************************************************** *****
function GetIdRes(sName)
'Get ResID
sUserID = Request("UserID")
sSQL = "SELECT ResID, ResName, Total, UserID FROM Resolutions WHERE UserID = '"&sUserID&"' AND ResName = '" & sName & "'"
rs.Open sSQL,,,adCmdTable
if rs.eof then
rs.AddNew
rs("ResName") = sName
rs("Total") = 0
rs("UserID") = sUserID
end if
rs("Total") = rs("Total") + 1
rs.update
GetIdRes = rs("ResID")
rs.close
end function
************************************************** ****
I've got another 10 similar tables. The Column "Total" is often different among the tables.
Please kindly advise...
Thanks
View 2 Replies
View Related
Apr 25, 2005
I have one table (call it tblMax) which holds a number designating a maximum amount for items. I have a second table (call it tblItem) which holds the items current inventory, along with several other values about the item. Is there a way to reference fields from both of these tables on one form, AND have the record be able to update? I've used a query to bring them all to one form, but the record is not able to update using that method. The tblMax will be used for reference only, after the initial values are entered. I also tried leaving the form's Control Source blank and typing in Control Sources for each field, but was unable to get that to work. TIA.
View 1 Replies
View Related
May 21, 2005
I have a table called Primary Employees and another table called Secondary Employees. I also have a form where you can add new employees, which updates the Primary Employees table. What I would like to do is, after I add a new employee have the new data update both the Primary and Secondary table at the same time. These tables have the same information within them. These two tables are currently relational to one another, but I still have to go in and manual update the Secondary Employee table after I add a new employee. I have to have two databases because of other form requirements. Any help you could provide would be greatly appreciated.
View 4 Replies
View Related
Jul 17, 2005
Hi Im quite new to access, but Ive got a little database running thats contains balances of customers account over 30 days 60 days etc and logs querys on there accounts, Im given a spreadsheet each week which shows that the customer may have paid something to their account so I need to import these new balances into the table and update the record. Can anyone give me any pointers as how Id achieve this, as append query or import dont seem to fit what I need.
View 14 Replies
View Related
Oct 2, 2005
Hi all,
I have a front-end database with a linked (data) database. Problems arise when this goes onto other PC's on different directories and I get the error that it can't find the linked database. This is easily fixed by updating with the linked table manager, but as I am not sure which directories the database will be run from on other PC's, is there a solution that easily updates the linked tables? (the two database files will always be in the same folder). I realise that if I can identify a common directory that can be used on all potential PC's, then this problem can be avoided, but I can't guarantee this.
Thanks in advance,
Atholl
View 1 Replies
View Related
May 15, 2006
Hi everyone!
My names james, im 18 and im a data manager for a school.
My schools database stores student data such as names, classes and exam grades. I have a table containing each students personal details such as name and address. Each pupil is assigned a unique PupilID which is the primary key. I then have a number of other tables linked to it with one to many relationships. Each of these tables holds a different set of exam results. The tables are linked through PupilID. The primary keys for the other tables are auto numbers.
The problem is this: when i add a new student to the Students table i cannot get their PupilID to automatically be entered into the other tables containing grades as part of a new field. How can i make this happen?
Thanks.
View 9 Replies
View Related
Oct 24, 2006
I will be updating a table in my database every week, via imports from a xls file. What I have going on now is a very inefficient way of updating. What I do is import into a table (x) and just keep adding to the table through the import wizard saying add to exsiting table. Then do a make-table query where I do "group-by" to get the unique ones and then run it to get the new table. I am sure there is an easier way to do this. Possibly, find duplicates in the import and then just append to the main table? Don't know how to do that one? Also I wanted to create a button that will ask for the xls file and then do the appending and clean up once the file has been asked for...any help please!
View 4 Replies
View Related
Jan 22, 2007
Ok due to user error (and my own fault for not archiving an old database) we now have two databases which have had data entered into with the same table structure.
The table structure we have at the moment is with the related field in []:
Propertytbl[Propertycode] is related to Systemtbl[sysID] which is related to Assessmenttbl.
There is also another Temperaturetbl linked to Systemtbl by [sysID]
The problem i have is sysID is an autonumber. I can't simply paste in the difference in data as the assessmenttbl records and Temperaturetbl records will point to a different system (as the sysID will change in the Systemtbl data)
Can anyone think how i can do this either through using queries or programmatically so that i don't have to enter 5000 records manually.
I've tried using append queries but this produces the same affect as above.
Many thanks
Matt Collins
View 1 Replies
View Related
