Modules & VBA :: Creating Temporary Relationships Between Tables
May 3, 2015
I'm thinking about creating temporary (while db open) relationships between tables, since transfering tables is possible only if no linked relationships are present. However it's important to preserve in some cases referential integrity.Looking online, I found this code:
Code:
Public Function CreateRelation(primaryTableName As String, _
primaryFieldName As String, foreignTableName As String, _
foreignFieldName As String) As Boolean
[code]...
Even with the comments I don't really understand what exactly this code does or doesn't do.I tried the code. It seems (?) to create a relationship. Since the relationship doesn't show up in the relationships table, I'm not really sure if it's there.
- why it's not visible among other relashionships (or is it me?? maybe, it wasn't the code that was working but the query...)
- how to implement referential integrity
- what's the behaviour of this supposed relationship. Is it permanent or not? If not when is it deleted?
I'm trying to create a temp table, which is populated by a string, (taken from a recordset).
My problem is incorporating the String into the SQL statment, and making it work,
What I'm trying to do is to create a temp table, and populate it with the first record of the recordset, (which is an e-mail address).
(The recordset and the strings work fine). It's the SQL statement which doesn't work.
Here's the code I have :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ Set DBS = CurrentDb Set RST = DBS.OpenRecordset("SELECT Contact FROM Contacts_to_be_Mailed") Dim My_Count As Long Dim ContactString As String
RST.MoveFirst ContactString = RST(0)
Dim strTable As String strTable = "TempContact" DoCmd.RunSQL "INSERT * INTO " & strTable & " FROM ContactString "
I'm making a library database program thing... There's an option for the user to view all books on loan.
I have two tables:
Books, which has columns ID*, ISBN, Author, Title, Year, Location BorrowerStorage, which has columns Book ID, Name, Email Address, Desk Number
Book ID in BorrowerStorage is related to the Books primary key.
Now, for the viewing all books on loan, I want it to produce a read only table which contains all the entries from the BorrowerStorage table and the corresponding Title/Author columns (i.e. the records for which the ID in Books column = BookID in Borrower Storage column)...
I'm trying to create multiple relationships between the same two tables, but I run into problems every time I try. I'm using Access 2007.
Specifics: I have a table with information on meetings and there are two hosts. So I have fields Host 1 and Host 2. I have a second table that lists possible hosts and their personal information. I have a relationship between table 1 "Host 1" and the Host in table 2; I cannot create another working relationship between table 1 "Host 2" and the Host in table 2.
how I can get that to work? With just the one relationship, I can go to table 2 of the hosts, click on their name, and see all their meetings.But if I add another relationship, it takes out all of the information. I've been working on this for over an hour,
I am trying to create four tables: Company, Contact, Activities, and Opportunities.
I want them to relate hierarchically. A Company can have many contacts, contacts can have multiple Activities and Opportunities. But you can't have contacts without a company and you can't have Activities and Opportunities without having a contact. I want all PK's in all tables to link to one another, that you cannot create one without the other.
How I can do this in Access 2010?
YYMM00000-000000-A0000 CompanyID-ContactID-ActivityID or YYMM00000-000000-O0000 CompanyID-ContactID-OpportunityID
I have a database split into a frontend and backend.
The front end has several make-table queries that create tables of data on which pivot tables and other analyses queries are based. The make-table queries get re-run every now and then as new data get entered.
The resulting tables are huge - 500K records in some cases. It seems like a bad idea to store them in either the front and back end.
Should I create a second "backend" that holds just these temporary tables? If so, what's the best way to design the make-table queries so they make the table in this "backend"?
I want to store a query into a table, which I will delete later on. But somehow it shows me an error: Data type conversion error at the qdf = CreateTableDef assignment line.
Code: Public Sub LF_Query() Dim i As Integer Dim strSQL As String Dim qdf As TableDef
I have been trying to create a login form that allows the user to change his/her temporary password logging it to the proper table along with timestamp and who done it info.But, after spending the morning trying to find the proper syntax I am flummoxed.
I can get everything to work accept the update of the fields. I can get the command to work (writes to the location) but it does the pop-up what is the parameter thing when it works. I have all the information just need to get it in so the command recognizes it.
DoCmd.SetWarnings False DoCmd.RunSQL "UPDATE lut_TeamList SET Pass = txt_Password.value WHERE TeamListID = Me.cbo_UserName.Value" DoCmd.RunSQL "UPDATE lut_TeamList SET UpdatedBy = Me.cbo_UserName.Value WHERE TeamListID = Me.cbo_UserName.Value" DoCmd.RunSQL "UPDATE lut_TeamList SET UpdatedWhen = Now() WHERE TeamListID = Me.cbo_UserName.Value DoCmd.SetWarnings True
I am using SQL 2000 and have two tables that I want to create a relations ship, so that when I add data to one it gets added to the other.
Table_Main QuoteID int PartNumber nvarchar (both make up key field)
Table_Secondary QuoteID int Item nvarchar (both make up key field)
When I use the database diagram in MS Access (ADP not MDB) I get: "The columns in Table 'Table_Main' do not match an existing primary key or UNIQUE contraint."
Othere then then actual name of the filed, they all match, what gives?
I'm a student working on a project in which I've inputted information from course evalution forms into new databases. I have a database for each semester and then tables for the individual workshops. Problem is, these are many to many relationships and there is no real way to link the tables. My friend suggested a redesign, which would essentially mean splitting up the information for an individual form. (i.e. course instructor in one table, comments on workshop in another table) I have a big problem with this because I would be eliminating peoples' ability to see all the information for one workshop in the same table, which in my mind is just as useless. I'm interested in hearing your thoughts if I'm taking the right approach. Thanks.
I have an assignment with absolutely no directions other than
Quote: 1.Open the Relationship window 2.Create the following relationships: The strDeptCode field in tblDepartment should be related to the strDept field in tblHumanResourcesData The strEmpID field in tblPersonalData should be related to the strEmpID field in tblHumanResourcesData The strParkingLotCode field in tblParkingInfo should be related to the strParkingLotCode field in tblHumanResourcesData 3.Save the relationships and close the Relationship window.
Where do I go to begin this? I couldn't find any real help with the help sources in Access. Nothing that made any sense at least.
Oh - I am using Access 2003 by the way.
Any help or direction would be extremely appreciated!
ITEM , SIZE A , 12 B , 12 C ,100 D ,12 E ,100 F ,100 G , 150
I would like to do a query that Joins the "Size" in each table, and then matches an "Item" to a "Location". However, because of how a normal join works, I cannot seem to figure out how to limit the "Location" field from producing duplicates in the match.
I have 5 tables and 2 forms. The primary form is what I input all the information into (Tracking) and the other form is to update employee information (update form).
The "Tracking" form is where I add information to 4 of the 5 tables. Here is where I'm stumbling. Would it be more practical to just have 1 table and just expand the fields, or have the form put the information into the separate tables. Those 4 tables are Employee, phone, spotter and radio.
I'm wanting to keep a running tally of who doesn't turn in what equipment on what day.
I am converting / developing a database that stores information pertaining to individual birds and their recaptures over many years. Here is a condensed version of the many tables in this database:
tblIndividual Bird: Autonumber (Primary Key) Band Number - also, unique to the individual bird Sex - M or F etc ..
tblCaptureInformation: Autonumber (Primary Key) Band Number - look-up from tblIndividualBird (using hidden Primary Key) Capture #- # which indicates what capture this is (ex. Intial capture - 1) Place Age Date etc ...
Each time a bird is captured, we record information pertaining to TIME, MEASUREMENTS, and NEST INFO. So, I have seperated the data based on these headings and made them into individual tables.
Now, my problem .... I have already created a relationship between CaptureInformation and Individual Bird. However, in the last 3 tables I would like to create a drop-down menu which shows the Band Number and Capture Number and make relationships there. What is the easiest way to do this? As of now, when I make a look-up field in the last 3 databases to show this info, the Band Number comes up with the Autonumber (because I am using the CaptureInfo table) which does not really help someone entering the data. Thanks for your help.
I am trying to create a db for service orders for customers. At the moment I have four tables, customer, service_order, parts and totals.
I have one form for customer records that has a button that when clicked opens another form for that customer's service orders. The service order form has two subforms, one for parts and one for totals.
When I try to add a new service order for my test customer it says "you cannot add or change a record because a related record is required in the table 'customer'.
As you can see here (http://www.abstractmusic.org/relationships.gif) I have three relationships setup. cust_no in customer table is a PK and so is service_order_no in service_order table.
Also I am having problems with the totals, as the fields are from different tables the equations won't work from within the subform (I guess I need some kind of query). I need the totals in a seperate table other wise I have a total for every part entry.
I am trying to set up a database to detail dances published in a magazine over the years.
I currently have all the information in an Excel Spreadsheet but know that Access would be better.
The columns in my spreadsheet are:
Dance Choreographer(s) Level Count Date Published Song 1 Artist 1 Count In 1 Song 2 Artist 2 Count In 2 Song 3 Artist 3 Count In 3 Song 4 Artist 4 Count In 4 Song 5 Artist 5 Count In 5 Song 6 Artist 6 Count In 6 Song 7 Artist 7 Count In 7
There can be two or more dances with the same name The same choreographer(s) could have written more than one dance The same count can be used for many dances About 15 dances are published on the same date One artist can have more than one song used One song can have more than one artist singing it One song and relevant artist can be used for more than one dance
I tried using Access For Dummies but it has confused me even more. I cannot work out what tables there should be and what relationships.
Not all dances have 7 songs for it - some have 1, some 2, some 3, etc.
What is listed as song 4 for one dance could be song 1 for another or song 5, etc.
I'm a novice and I'm confused. Maybe it's the way I think. I feel like there is an easier way that I'm overlooking, but I can't seem to get a satisfactory solution.How would YOU create your tables/relationships if you had the following:* The general purpose is to manage orders* You have to store information about the order (like order number, date)* You have to store information about from what company the order is from (like address)* You have to store information about from which department of that company the order comes from, each department has their own information that needs to be stored (like contact person).Keep in mind that you don't want to memorize which department is from which company nor do you want to be able to make the mistake of entering an order from a department that is not a part of that company.It seems like it should be an easy thing to do, but I'm stumped. I've thought about creating a new table for every company with a sub table for every department but that doesn't seem very practical. I tried creating one table called Company and one called Department, then merging them on a third table which is then linked to a fourth table called Orders. I'm not convinced this is the best way to do it, but it's my best guess at this moment.All help is greatly appreciated.
i know tecnically you can create a table with no relationships but is it "ok" to do so?
im using a table to store some values which are only referenced through a query but it is completly detatched and has no relationships with any other tables, im awear my database will function perfectlly happily but is it an acceptable programming standard?
I am creating a database of medieval labor contracts and have come across an issue.
I have a table of Contracts, and a second table of People. I want the table of People to show every contract in which that person appears. Each contract has multiple roles - there is always at least a Laborer and an Employer.
The same person might appear as a laborer in one contract, and an employer in a second contract and I want my People table to pull every contract in which that person appears, regardless of the role they play in the contract.
So far I have not been able to get this to work. I set up two different one-to-many relationships which link the People table primary key (personID) to two separate columns in the contract table. However, in the People table, instead of pulling contracts in which the person appears as either Laborer or Employer, it will only pull contracts in which the person appears as both Laborer AND employer (a situation which will never occur in my actual data but which I tried out as a test).
I have one database called asset management. It consists of one main table called cyber assets. Most fields in this table are linked to a manually created lookup table inorder to restrict user input. There are also two additional, none lookup, tables used to list a) the IP addresses (there can be more than one) and b) another similar 1 to many type table. Basically this DB is used to manage basic cyber asset data, excluding most items related to configuration management.
So, this above DB serves the purposes of asset management. Now I essentially need a similar DB for Patch Management. What I've done for this is to assess each patch initially (i.e. just by looking at the patch title and determining if we even have any of those device. i.e. this assessment is not based on OS, model number... just a general 'may' or 'may not' be applicable). Here's what this SEPARATE DB looked like:
Since each patch is essentially assessed against itself, or maybe a better way to describe it is against the users memory of what we do and don't have, only a single table and form was needed.
So now we've been thru this process and the DB is filled, all initial assessments are complete. The next step is to take all the ones that are applicable to our company (based on the initial assessment when you answer, yes is applicable) and do assessments based on each device we have.So what I want to do is to link the two DBs on a new table called Patches_by_device, inside the original patching DB... so the relationships would look like this:
But as you can see, the linked table CYBER_ASSETS has some sort of undefined relationship type, which is causing my issues.So the next thing I did was to autocreate a form based on the Patches_by_device table, and here's the result.I need to change the patch_key to the Patch_ID+Patch description+URL, etc, and to change the device key to the the UNID+IP+functional description, etc...so I changed the form record source like this:
Now I should be able to change the control source of the Patch_key and Device_key to more useful information. so I changed: Patch_key control source to Patch_ID and Device_key control source to UNID (which is in the cyber assets table)
As you can see, it worked for the patch_ID but not the UNID which is part of the linked table.Must it be within one DB, because we have a ton of other modules to implement (e.g. config management, vulnerability assessments, audit stuff, and more...) and I'd like all these to be in individual DBs, all liked back to the main cyber_assets/Asset management DB.I've considered just modifying that patch table so that each device has its own column heading in the table, but this will cause issues when new devices are added.
