Have an MDE file in our equipment, it's a language data base
one of the languages isn't fully translated so I did a query to
pull out those records that equaled the English field which
brought the 1800 lines down to 218 to go over and edit.
BUT
What I am worried about, well our Software Engineers are nervous
about is my trying to udate those records via an update query
and then putting the MDE back into the machine's computer.
Is there something that I should be careful about while doing this?
I know that I shouldn't try to repair and compact as MDE just
goes bye bye when that is done, changes into an MDB file.
Good Afternoon--- I'd like to use a query to update a table, however the query is based on several tables. I'm pretty sure this is possible, but I've only used a pre-existing query where this applied. Never had to build one like this from scratch.
Say for example you want to update the contact information for one record that occurs in several different tables without openning every table. I'm not sure that I should even be using a query to solve this problem, but any suggestions would save me a lot of time. Thanks!
Hi, I've spend ages reviewing the relationships between my tables and changing my query but cannot get this to work. I've read alot of the posts on the subject also but trying to apply the suggestions to my problem hasn't worked.
It is a database of engineering drawings. A drawing can have one or more revisions. I have written code to import the drawing and revision info into the datebase so when the drawings are actually received I need to be able to add the additional data in tblReceivedDrawings such as the tracking number, date received etc. These are my tables:
I have created a query called qSelDrawingsAndRevisions which combines tblDrawingRegister and tblDrawingRevisions.
The form is based on a query called qSelIncomingDrawings. This query joins qSelDrawingsAndRevisions and tblReceivedDrawings. It shows all the records from qSelDrawingsAndRevisions and related records from tblReceivedDrawings and is joined on the drawingNum and Rev.
Adding the extra info e.g. tracking num, date received etc about drawings that are in tblDrawingsRegister works fine. However if I want to add a completly new drawing I get an error message: "The Microsoft Jet database engine cannot find a record in the table 'tblDrawingsRegister' with key matching field(s) 'qSelDrawingsAndRevisions.DrawingNum'
How can I get it to update the drawingNum in tblDrawingRevisions and tblDrawingsRegister at the same time? Is it an issue with my query or relationships? Or do I need to write an OnUpdate procedure for the DrawingNum textbox so that it created a new record in the DrawingsRegister table to correspond with the one in the DrawingsRevisions table? I have a feeling that this might be overcomplicating it and that I just need to change the query?
Am very confused about how to approach this and would really appreciate a few pointers...
I am writing a vba procedure to updating some records in another Access database.
rsAccess.Open "SELECT * FROM AI_Table",conAccess, adOpenForwardOnly, adLockPessimistic
rsAccess!OCRExist = "Exist" rsAccess.Update
it has about 3 millions of records in that AI_Table. In the procedure, I perform some calculation and put the result into a TEXT(50) field in the AI_TABLE. As it was updating the records, I could see the size of the Access database file (the one contained AI_Table) grew very quickly, almost 1 MB/sec. I am pretty sure I am not adding that much data. If I stop the procedure and packed the database, it shrunk a lot.
I am just wondering if there is anything wrong with the way I am locking or updating the records.
I have recently had to add a new field to a FIRM table in our contact database, Firm.PhoneNumber (please don't ask why it wasn't there before!). The primary key in this table is Firm.FirmID.
I have a list available to me in .csv format that contains all the phone numbers I need, it has two 'columns', labelled FirmID and PhoneNumber.
The process is surely a simple one, I just need to update/copy/insert the corresponding phone numbers from the csv file into the Firm.PhoneNumber field. I won't bore you with what I've attempted thus far but could someone please provide me with the quickest possbile method for achieving this task?
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.
I have created a database that tracks the locations of all the faxes we receive in a day. We have a dedicated employee that enters them into the database as well as who they are assigned to and other pertinent information. At the end of the day, I monitor the network folders where the faxes are saved to change the status of the fax in the database to done, still needs processing, or awaiting approval. Is there any way that I could set up some VBA that would automatically update the status column for the record based on the location of the file? The file name is recorded in the database so then if it sees that that file is in the end folder it could automatically update the status to finished.
The response I got on another forum is as follows:
-The impression I'm getting is that you've got a database of information over here. -And it lists the file name, but not location. -And then you have a physical folder structure with the fax files in it. -And there are folders for done, awaiting approval, or processing. -So what you're looking for is something to read those folders and update the status column based on which folder it is in? -So at the start of the day we have:
New -Fax1.tiff -Fax2.tiff -Fax3.tiff -Fax4.tiff Finished - In progress -
And at the end of the day the folders look like this: New -Fax1.tiff Finished -Fax3.tiff -Fax4.tiff In progress -Fax2.tiff
And you want your code to browse those folders, and update the status of each filename based on it's folder?If the filenames are unique, you can just use VBA to open the folder, create an array of the files and then set the status. Create two separate functions, one for in progress, one for completed and call them one after the other. There'd be no need to check the current status, but you could store the wrong status if a file is duplicated, or a filename repeated.
I'm currently working on a database to track and inventory documents in my organization similar to a library catalog, but with a digital archive as well. I'll need the ability to "attach" documents to records in the main inventory (though using links instead of storing it in the db) and have the documents live in a centrally accessible location on our shared server.
I will need to have this function work through a few different tables and in a few different iterations, but I'm trying to work on the simplest part first, which is attaching a thumbnail/preview of a given document to the record for that document.
I've got some VBA working well which opens the file picker and copies the file to a directory relative to the DB. It also creates a folder with each employee's ID number if it doesn't exist already (which it gets from the Windows log in) and adds "Copied_" to the front of the file.
Now I'm trying to get the VBA code to interact with the record. Presumably I need to tell the form button I'm using to launch the VBA code to pass the primary key of the record being displayed to VBA. Then, at the end of the VBA code I need to take that value and find the record again from VBA so I can update the MediaThumbnailLink in the original record.
Does this order of operations make sense? If so, how should I go about passing these values back and forth and writing the new link value?
Here's the VBA I'm using so far...
Code: Option Compare Database Public Sub GoCopyThumbnail() Dim fDialog As FileDialog Set fd = Application.FileDialog(msoFileDialogFilePicker) Dim varFile As Variant Dim LUser As String
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?
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???
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
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.
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.
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.
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.
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.
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?
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!
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.
I have two databases that are set up exactly alike - one for each office (Corporate and satellite). The Satellite office will have to send us their updated project, Proposal, and change orders data (which are in three different tables) every week so Corporate can update their database for the Satellite office and Corporate will have to do the same for them.
What is the easiest way? I basically just want it to look for changes to the tables and then update the table accordingly. I need it very simple for them because they don't know Access. Any suggestions?
I have a combo box in my main form that gets its list of physician names from tblPhysician. When the physician is chosen and the record saved, it saves the PhysicianID to my main table, tblOncReg.
When I delete a physician, any record that had that name chosen continues to have that name in the record despite it being deleted from tblPhysician.
I thought that setting relationships would solve it, but when I select "Enforce Referential Integrity" and "Cascade Update Related Fields", I get an error saying "Invalid field definition 'ID' in definition of index or relationship.", and it doesn't set the relationship.
Would setting the relationship solve that problem?
Hi I have a command button that uses a macro called "update" to update the values in a table. When I click it everything works fine, but I'd like to do the same job "automatically", when the value of a text box changes. I tried to assign the update macro to the "after update" property of the text box, the macro seems to run, but it doesn't work. I also copied the code that is run when I click the command button, to the "after update" code in vb, but still no luck. The text box is bound to the table that I want to be updated, and shown in a subform. Any suggestions? Thanx in advance
Is it possible to update Access tables through a macro of some sort.
Thing is: I would like to collect a membership list in a table. I will add new members on my database but i would like to update another database to have the same data as me. Especially for this particular table.
I will have a look around (i never used access before) but if there is a tip on where to go and find such a feature would be much appreciated.
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.
