I know you can manually delete table relationships in an .mdb, but I was wondering: Can you programatically delete table relationships? If it is possible, how would it be done?
Thanks in advance to all you nice folk who reply.
Hi
I am in the process of designing a sports database and have come across a problem that I can not seem to fix.
I have 3 tables that are linked, they are Customer, Program (this is the customers exercise program) and Health (the customers health records).
I have tried to set up a query that deletes a customer if a command button is clicked. This works find as long as the table I am deleting the data from is not linked to another table.
However when I try to delete a customer record it says that I can't as the data is linked and I have to delete the relationships first.
The only way I can think of doing this is to setup a query that deletes all the data in each table at the same time. I can not get this to work though.
Is this a problem that can be solved or will I have to change the way my relationships are set up?
I would appreciate any help or suggestions.
Thanks
I have a DB setup with users and their information. How can I remove a user from the DB without losing their data?
Basically I just don't want the users seeing this (deleted) user in the list on a form. So maybe "hide" is a better term than delete.
Details:
Access 2010
Table "Employees" - holds username and some other user specific info.
Table "Hours" - holds username related data(how many hours they worked on specific dates).
Form "WorkHourEntry" - The form I use to allow the users to login and enter their data(hours).
Then have a combo box that displays all the users in a list. This list/combo box is where I want to hide users from when the employee leaves the company or dept. and is no longer needed. But, we need to keep their history/data.
Hello,
I have a list of dates of births in a table, but the way they are always imported (from some piece of crappy software somewhere) is in the format of: 01.01.2005 (using full stops).
Access doesn't like this, and insists on slashes, or dashes. Now, if I do a find/replace, it works perfectly, just replacing all the full stops with slashes. However, I want to do this quite regularly...any ideas how to do it programatically in VBA?
Thanks!
Any help much appreciated!
Is it possible to unlink and link to tables in a back end using VBA in the front end?
I need to do this or something similar because when I send updates of the front end out I may have added an extra table and thus want to be able to get the user to run an update module to link any new tables.
Not sure if this is at all possible???
Any ideas???
Any insert command to load Oracle profiler log data into table...
??
I have a form that I am populating a ComboBox via a Query. It works but sometimes it flakes out and does not populate the box.
What I want to do is run this through code.
I am a bit stuck. I think that I need to set the recordset, Create a SQL query, Run the query and push the results to the ComboBox. Think this should be on the Click event of the combobox.
Dont really know where to get started.
Does anyone have a rough example of:
1. Getting the recordset
2. A general SQL query (how to run it)
3. How to push the results to the ComboBox
THANK YOU IN ADVANCE
hi All..
I could set the Combobox.Value to one of the values in the combo box and combo box displays that value. But when I try to use Combobox.Value subsequently it does not have any value. I noticed that the value property is populated only when the combox box item is manually selected(highlighted).
How can I highlight the selection in a combo box?
Thanks in advance
I know how to convert an Access database from a previous version of Access to a higher version of Access, using the shell command:
x = Shell("C:Program FilesMicrosoft OfficeOfficeMSACCESS.EXE " & Chr(34) & strOLDDB & Chr(34) & " /Convert " & Chr(34) & strNewDb & Chr(34))
However, what do I have to do to programatically revert from my current version of Access to a previous version? Is there a shell option similar to the /CONVERT choice? It would be nice if it was as simple as /REVERT
Thanks once more to you forum gurus!
I have a subfom that displays perfectly in datsheet view.
One of the columns changes its contents depending on a value. i.e. a list of units or a list of costs. I change the data using a Recordset update.
When in the Main Form I am trying to change the column label that appears in the SubForm header above the column, to either 'Units' or 'Cost, to match the data.
I am strugging to find the correct syntax, can anyone help?
Also, can I change this via the Recordset update?
Thanks :confused:
All I am trying to do is insert to have a form with a "Delete Record" button on it. The problem is I don't want anyone to be able to delete a record, I would like someone to have to insert a password to confirm the delete.
View 13 Replies View RelatedI have a query based on two tables, joined on one field (this field is the primary key in Table A. Table B doesn't have a primary key). When working inside this query, it gives me the option to delete records. However, when I try, the row disappears from the query but then if I save and close and either re-open table A or the resulting query, that record is back. It doesn't actually delete. If I go to table A directly and delete it, it's gone for good.
How can I make it so that when I delete the record inside the query it actually deletes? And if that's not an option, can I make it so that it doesn't seem like I can delete records inside the query so that people don't make the mistake?
I have two simple tables. I want to delete the records from Table1 that are on Table2. I've created a select query that gives me what I want but when I change to a delete query, I get this message: "Unable to delete from specified tables"
I think my problem has something to do with security but I can't figure out what to change.
Hi all. I really need help with this. Does anyone see a problem with my relationships , i have attached a copy.
Any help will be greatly appreciated
ClaireB
I am new to database design and curious about relationships. In a hypothetical situation, if I have four tables: tblProducts, tblOrders, tblEmployees, and tblCustomers, how could the relationship be set up?
The Products are stored to keep track of the quantity on hand and the employees will also need to be tracked as to how many products they sell. Customers are recorded and Orders wrap up all three tables with the fields: OrderID, ProductID, EmployeeID, and CustomerID.
Would it make sense to say that many products can be bought by many customers and/or many employees can place many orders.
Hi
I've selected a number of tables to the relationship window. When I drag the primary key on one table to the corresponding foreign key on another table, the link created is one-to-one. I want one-to-many. Why did it create one-to-one? How do I change this to one-to-many?
Thanks
Hi all. I am new to the use of access and have been told that I need to create a one-to-many relationship to be able to have several records from a drop down list saved to one unique number (my primary key).
Is this correct and if so how do I do this? If it is not correct what is the best way to do what I require?
Thanks for helping.
Hi
I have created two tables which are linked with a one-to-many relationship
Using a form the user enters a date. Then in VB I add a value to the date and store this in the second table.
I have simplified this to try to find out what was going wrong and found that when the database adds a value to the second table which only has two fields it first of all adds it to the begining of the record set and the id field does not update to that of the first table where the relationship is joined.
Do I just captutre the value of the id field and add this to the recordset.
How to I get the database to look foir the end of record set before I appent the new generated record.
Hi,
I have looked for this on the help section; however, it does not seem to be covered. How would you change a one-to-many relationship to one-to-one? Access seems to automatically guess that the many side is where the foreign key.
Can it be changed from Access or is entirely on how you design your tables?
Thanks,
Bee
Hi !!
I am doing a school project on a school database. In my DB I have a table with all students records (eg. Student ID, Name, Surname, DOB, class etc) and I have another table where the student fees payment slips will be printed.
The fee table will only have the ID, name, surname and class fields and fee will be input for every student for every term.
I want the fee table to get all the info except the fee from the first table automatically when I give a student ID.
I have created a one to many with only Student ID fields linked, NOTHING.
Then I tried to link many fields on each side (eg Name to Name, Surname to Surname etc) but I got a msg "No unique index found for the referenced field in the primary table"
Plz help me !!
Thanks
would having a db with several tables and NOT setting any relationships up in the relationships window affect the db in any way?
i have such a db, which originally had all the relationships set etc. however, as i encountered errors and had to replace tables and delete entries, i must have at some point deleted all the relationships.
i just relaised that the db now has no relatioships set up (all the primary keys are still in the tables) so i'm wondering if this matters?
cheers
Jose
I'm trying to define relationships between a few tables in Access 2003 and It's only doing a one to one. How do I get it to do a one to many?
View 5 Replies View RelatedPlease someone help! This is very urgent!
I am designing a database for a clinic
basically,
One Form is a Demographic Form, which was been designed. Each person was given an identifier code.
However, they all did 3 questionnaires. Each one of them has their ID code on it.
Is it possible for me to make a query so that when I search ID code, it actually brings up all the information on every questionnaire?
If I have a table "tblContinents" with just two fields ContinentID and Continent and I have another table called "tblCountries" with three fields CountryID, Country and Continent.
After reading several articles, i decided not to make the Continent field in the tblCountries as a lookup field but rather I will do that at form level via a combo box. In such situation, since i am not creating a lookup field at table level, do you think that it is necessary to create the relationship between the two tables in the "relationship window", or shall i just leave it as it is, and if the need arises i will create such "relationships" in queries?
Thanks very much.
hi, im new here and i dont really know much about databases with access. im currently doing an assignment at school requiring me to do a entity relationship diagram for all my tables, which i do not know how to do. Can anyone please assist me asap? thank you very much in advance
below is the link where i uploaded it because it was too big to be uploaded onto this post. :)
http://www.megaupload.com/?d=E6AU28CL
In my Db I have TblQuotes & TblQuoteItems
I want the one-to-many relationship to be based on my own generated quote reference rather than the PK/FK.
My quote reference looks something like this IN123/10
Does the character '/' cause a problem with creating the relationship as I get the error message "No unique index found" even though I know that they exist?
Thanks