I have some questions about a schema design I am working on. After much trial, it appears to me that a particular table is not and probably does not need to be related to any other table in the schema. So my questions are:
1. Is it right or quite typical for schemas to have some tables which are orphan (i.e. unattached to any other tables)?
2. If tables can exist in isolation, then what are the primary purpose they provide? I have a suspicion that if fields from an orphan table are used in query etc, then the possibility of Cartesian joins will arise. If so, how does one take care of avoiding such a problem?
Is there a way to print the schema of a table right out of Access?? Basically I just want to print out all the field names, data types and descriptions. When looking at the table in design view MS Access 2000 does not give you the option to 'Print'.
How do I display a more meaningful message instead of the cryptic error about having to enter data into blah blah blah. How can I trap that error and provide them a more meaningful message about entering data. I have tried the following;
Main form name frmPatientRecords Sub form name DentalRecords Subform Private Sub Form_BeforeUpdate(Cancel As Integer) If Nz(tblPatientDetails!recordid, 0) = 0 Then MsgBox ("sorry. Please complete the main record entry") Parent.SetFocus End If End Sub
I would like to add to my VB6 app the ability to build an array of what tables are in my Access database, and the fields that are in each of those tables.
I am working with this database using ADODB, but it doesn't give properties like Tables and Fields, for example.
I am happy to open this database some other way, grab this info, close that connection, and proceed using ADODB.
I am very inexperienced in databases and I am currently attempting to design a database and there is one part of the schema where I am unsure how to proceed.
I need to model schools, pupils and teams. Schools have a number of pupils as do teams.
I rushed in with the following design but it just seems wrong to me. I don't know much about database design but I don't like the idea of having a kind of loop/triangle in the schema like this.
We have added new columns to a table in our developer db. Now we need to get these columns into the client's system.
Their computer is running on Access runtime, so if we change our existing Access db into a Mde would it be possible to upgrade their database with the changes without corrupting any of the client's data? We're quite happy to overwrite the forms, queries etc. but what happens to the client's data? especially when we have made some changes to the database schema e.g added new columns to the table?
I have taken over exisiting databases and the names for the tables,forms,queries,reports are not technically named correctly.. What would be the easiest way to rename everything accordingly:
tbl for table qry for query rpt for report mcr for macro bas for module frm for form.
Of Course they have names but it is hard to tell when viewing a query for instance if you are looking at a table or a query.... in the SQL View.
I have a xml schema which defines all things necessary (tables, field names, relationships, etc) to build a database in access. The question is whether Access could decode xml schema and build the database (create all tables, fields and relationships) automatically by macro or something else. I have tried to load xml schema by using external data in Access. However Access can only recognize some tables, not everything.
I have a couple of elementar level questions on Form creation process and standards invloving a schema with several M-M relationships. I have a highly normalized design (thereby producing several junction tables). I have attached a picture of my schema to give an ideal of the schema I am talking about.
1. What is typcially the process of creating Forms on such a schema for the purpose of data-entry. Meaning, should there be several Forms (on the order of # of tables) or should there be a few that enables simulataneous data entry to many tables through one Form. What is typically the practise?
2. If there are 3 tables (A, B, C) so that A and C have a M-M relationship and B is a junction table that makes A &B and A & C have a 1 - M relationship, then how does one enter data for A, B, and C so that all the three get populated?
3. Can someone provide a copy or link to a sample database that has a similar normalized schema and Forms built into it that takes care of the data entry to the various tables?
I need to get x and y coordinates for each device, but the data has to get looked up from 2 other tables.
I have a table (called InstReclosers) that has device names. Each device is on a Section. I can go to another table (called InstSections) and look up what Node that particular section is tied to. Then I need to go to another table (called Nodes) to get the X and Y location for that particular node.
How I can go about getting this X and Y data into the InstReclosers table?
...in summary, InstReclosers has device name and section name. InstSections has section name and node name. Nodes has node name and XY coords. Need XY coords for each device in InstReclosers.
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code: SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next FROM calls WHERE (((calls.firm_id)=[firms].[id])) ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
Hi, I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
When the value "X - DISCONNECTED" is selected in the form I want the record to be removed from its existing table and sent to a new table which keeps all the "X - DISCONNECTED" records together.
Any ideas would be greatly appreciated. How would this be coded?
I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
I have one DB that is used for creating/storing customer ID's, and another DB that is used for creating/storing job information for customers.I have linked the table from the customer DB to the job DB.
There is a table in the job database that holds customer name and ID, and some VBA that generates unique job codes.
Is it possible to have the data from the linked table automatically update into the existing table?
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
Is there a way to append a pivot table to a table or possibly make a query based on a pivot table? I need to get a count of Part Numbers and I need the average price for all these parts. Additionally I want to ignore a count of less than 3.
Also I am having trouble filtering on the count in the pivot table... haha, so I was gonna Query on it later on.
How to update data from one table to another table using form.
I have data coming from design team in Database 1 and using form i want search data and assign the job to a person and store it in the database with his name. I have to do this because database from design team is read only.
