Binding forms to multiple tables

[Bee]

Hi,

I wonder if it's possible to bind one form to more than one table without using 'sub-form'?
I basically have one text box that I want to bind to a different table than the one the form is linked to and I don't know if I should use a subform.

Regards,
B

 

[DES]

Well you could either bind the form to a query that is made up of the two tables so you have access to the field you want to make a text box out of or you could use a dlookup.

 

[Bee]

Well you could either bind the form to a query that is made up of the two tables so you have access to the field you want to make a text box out of or you could use a dlookup.

That's great. Thank you.

 

[ghudson]

You should never link a form directly to a table. Always use SQL as the record source.

 

[DES]

You should never link a form directly to a table. Always use SQL as the record source.

I have mulled over this for a couple of days and I am sorry but damn this bugs me.

First of all there is rarely a time when the word 'never' should be used so flippant, and although I do agree that it is unwise to link a form to a table the number of times I have bothered to link it to SQL for this forum's users is almost none existant. Especially in light of the fact that our aid is usually being given to people who do not understand SQL.

I think making this statement only adds to my belief that ghudson tends to be very condecending in his comments in general, wasting server space by constantly nagging people to use the search procedure while backhandedly providing somewhat clouded suggestions.

Personally given the audience it would have been better to explain that linking a Query to a form instead of a table allows you to create datasources that can consist of multiple tables as well as allow you to make additional fields in the query which would not exist in a table such as adding two fields to get a third value. It also allows you to provide for a variety of sorting orders as well as allowing you to limit the data by providing for multiple criteria options. In short you can control the data provided to the datasources and thus the forms better by using a query.

As for ghudson - undeniably you know Access. Patience well that is another issue! Sorry dude but you can find that one in the search someplace as well.

 

[Bee]

I have mulled over this for a couple of days and I am sorry but damn this bugs me.

First of all there is rarely a time when the word 'never' should be used so flippant, and although I do agree that it is unwise to link a form to a table the number of times I have bothered to link it to SQL for this forum's users is almost none existant. Especially in light of the fact that our aid is usually being given to people who do not understand SQL.

I think making this statement only adds to my belief that ghudson tends to be very condecending in his comments in general, wasting server space by constantly nagging people to use the search procedure while backhandedly providing somewhat clouded suggestions.

Personally given the audience it would have been better to explain that linking a Query to a form instead of a table allows you to create datasources that can consist of multiple tables as well as allow you to make additional fields in the query which would not exist in a table such as adding two fields to get a third value. It also allows you to provide for a variety of sorting orders as well as allowing you to limit the data by providing for multiple criteria options. In short you can control the data provided to the datasources and thus the forms better by using a query.

As for ghudson - undeniably you know Access. Patience well that is another issue! Sorry dude but you can find that one in the search someplace as well.

Thank you for the explanation. In fact, I did not link my form directly to a table, I linked it to a query. Access 2003 tend to create a query in the back if you use the wizard to create your form and it can be accessed via properties > record source > ... the three-dot-button.

As for linking a form to a table directly, I have heard it's not recommended especially in multi-user environements. What are the problems that can arise if linked to table please?

Regards,
B

 

[DES]

B:

When you use the wizard to create a form one fo the first things it asks is what to bind it to. It provides a combo box for you to from and in this combo is a listing of all existing tables and queries. Without picking one of those the wizard can not continue. Now if you have not made and saved any queries then all you can choose from are the tables, thus binding the table to the form.

Once the wizard is finished and your form is created you can go into edit mode to change the design of the form and as such you go to the forms properties > record source .> ... to see what the record source consists of. At this point the table name will be displayed in the record source.

If you based it on a table and use this method to see what the record source is then it will ask if you want to invoke the query builder. So at this point you are still bound to a table and not a query. Use the query builder and when you are done it will ask if you want to save the query and if you do Access 2003 saves what you just did to an SQL statement which it saves in the record source and in fact it displays it there as opposed to the table name that had been there in the first place. So the form is now bound to an SQL statement.

Now at this point to work with the record source for this form you always need to find the record source on the forms property and either again invoke the query builder or learn to read SQL.

My general preference for people learning to use Access is that they create the record source they want to use in the form of a query before they ever use the forms wizard. Once they create the data they want to be working with in this manner they can then use the wizard to bind the new form to the query instead of a table. The main advantage of this method is that the query is saved in plain site on the queries tab and can easily be modified as needed.

So why use a query instead of a table. Well that is the reason this whole thread started.

First of all binding directly to a table only allows you access to data in a single table, where as binding to a query allows you to get information from more then one table. Now if you used the query builder to get the answer to your original question great, but did it save it as a query or SQL in the record source, and if it is saved as SQL then can you read and understand SQL?

Next suppose you are collecting data in a table for a number of years. If the form is bound to the table then you have a number of years records to go through. If you used a query instead you can use (for example) a text box on the form to put in a year, then use that text box in the query's criteria to restrict the records being returned to the data source so you only have that year's data to go through.

As another example suppose you are saving sales in a table. If you know that a sales tax will always be 7% why save the sales tax in the table? It will only make the database larger because you are saving extra data. Instead create a new field in the query that is the "Sales" * .07 and call that field Sales Tax. Now on the form the sales tax will be an available field for the form and it will be calculated automatically.

The reasons are just too many and too complex to try and list them all here. So to try and simplify the answer for you it kinda goes like this. If all you want to see is data from a table in exactly the same order you entered it into the table then use the table. However if you want to control in any way the way the data in the table is displayed or ogmented then use a query.

Lastly make the query to ensure you are getting what you need and then base the form on that query.

Hope this helps.

 

[Pat Hartman]

It isn't that there are problems with linking forms directly to tables (except in a client/server environment), it's just that queries provide much more flexibility including the ability to get data from multiple tables into a single RecordSource.

Usually the reason for using an ODBC back end is that you have too much data to use Access effectively or network issues caused by large datastreams. Therefore, your objective should be to bring the minimum amount of data down from the server. Binding a form to a table defeats any benefit you might have obtained by using an ODBC backend. Especially in a client/server situation, forms should be bound to queries that include criteria to limit the data retrieved from the server. There is no way that a user is going to look at 100,000 records, so why bind a form to a table like that. Use a query to select a meaningful set of data.

 

[Bee]

First of all binding directly to a table only allows you access to data in a single table, where as binding to a query allows you to get information from more then one table. Now if you used the query builder to get the answer to your original question great, but did it save it as a query or SQL in the record source, and if it is saved as SQL then can you read and understand SQL?

DES:

When I used the query builder, it showed SQL in the record source box. If I click on the button with the three dots, it takes me to the query and I can modify it if I want to.

Answering your question, yes I can read SQL. However, the way Access automatically writes SQL statments is slightly different than the SQL I learnt.

Back to the original question:
Is this right? I heard binding a form to a table can lock records in a multi-user environment.

Regards,
B

 

[neileg]

DES:
I don't think that 'never' is too strong a term. I can't think of any situation where binding a form to a table is better (however you want to define better) than using a query. Even if there seems to be no merit in using a query over a table, the first time you want to ammend what you have done, you wish you'd used a query in the first place.

As for ghudson's attitude, I'm sure he gets frustrated by users of these forums not doing a serach first, just like I do. However, instead of ignoring these posts, he provides an answer. If the tone is more direct than you would like, you can ignore his posts, or ignore the forum.

 

[The_Doc_Man]

Bee: Regarding binding forms to table and locking issues - it matters more that you recognize this fact: A table and a query are both representative of recordsets. Forms want RECORDSETS. They don't care from where.

Binding to a table is equivalent but not identical to SELECT * FROM table; You can still put filtration criteria on the implied SELECT statement by using the Form's filter option.

Binding to a query allows you to have more flexibility up front. Whether you used a query builder from the query grid or an SQL statement or a wizard or black magic, you can do more in the query. If the query is pre-defined, Access can analyze it to optimize it. Even if the query is dynamic, you have at least a few extra options. See also issues in using Queries run BY_OWNER as a way to implement special security actions/protections.

In EITHER case, locking depends on a setting on the form. You are allowed to set locking to none (not recommended for forms that update), edited record (recommended), or all records (not recommended for shared environments). But what gets locked depends on the nature of the recordset. If you select ALL RECORDS then if the underlying query is one-table, you lock one table. If two tables (JOIN), you lock two tables.

GHudson (et al.) - the word "never" is too strong here. But only just barely too strong. You bind a form to a table when you are doing special types of maintenance on that one table and no other table, and you only use the form in Exclusive Mode, and no "ordinary" users can see the form or open it, and you use the form-to-table binding because it otherwise represents some level of convenience. In no other case would I bind a form directly to a table.

 

[Bee]

Bee: Regarding binding forms to table and locking issues - it matters more that you recognize this fact: A table and a query are both representative of recordsets. Forms want RECORDSETS. They don't care from where.

Binding to a table is equivalent but not identical to SELECT * FROM table; You can still put filtration criteria on the implied SELECT statement by using the Form's filter option.

Binding to a query allows you to have more flexibility up front. Whether you used a query builder from the query grid or an SQL statement or a wizard or black magic, you can do more in the query. If the query is pre-defined, Access can analyze it to optimize it. Even if the query is dynamic, you have at least a few extra options. See also issues in using Queries run BY_OWNER as a way to implement special security actions/protections.

In EITHER case, locking depends on a setting on the form. You are allowed to set locking to none (not recommended for forms that update), edited record (recommended), or all records (not recommended for shared environments). But what gets locked depends on the nature of the recordset. If you select ALL RECORDS then if the underlying query is one-table, you lock one table. If two tables (JOIN), you lock two tables.

GHudson (et al.) - the word "never" is too strong here. But only just barely too strong. You bind a form to a table when you are doing special types of maintenance on that one table and no other table, and you only use the form in Exclusive Mode, and no "ordinary" users can see the form or open it, and you use the form-to-table binding because it otherwise represents some level of convenience. In no other case would I bind a form directly to a table.

The Doc Man:

Thanks for the information, I will definately look for more info on locking as I don't know much about it. When a table gets locked, what happens to it? And is this issue fixable?

Regards,
B

 

[ghudson]

Never say never.

 

[ions]

I thought the only reason to use a query vs a table as a recordsource on a File Server system is to control the order of the way the records appear.
(With a table you can never be sure in what order the records will appear .... I learned this from Pat Hartman)

How could there be any optimization if I just set ORder to Ascending and display all the records?

 

[The_Doc_Man]

ions - many reasons exist to use a query. You named one right off the bat - controlling record order. Another is filtering record content. Another is to implement a JOIN. Another is to include a computation.

Bee:

When a table gets locked, what happens to it? And is this issue fixable?

Well, ... first a quibble. It ain't broke in the first place, so don't worry 'bout tryin' to fix it.

Second, and more important to you, a table stays locked until the entity that locked it releases it. So "what happens to the table?" NOTHING - that is, nothing except whatever was done by the locking entity.

More on "what happens" - The lock is embodied in an entry in the LDB file. The table itself doesn't really change. Other users attempting to lock the table will get Error events suggesting that the table in question is locked. These are trappable events that can be intercepted by the On Error GoTo {statement-number}.

This is the same result - from your program's point of view - as would occur if only locking the edited record and you were trying to get to the locked table. If you have No Locking, then this won't occur - but you will have potential issues in data integrity.

 

[ilcaa72]

i am way late on this but i am stuck and this spoke to me...

i removed the table Record Source in order to ue multiple tables (1 to fill the textboxes with data, the other to store a persons input)

but once i add a 2nd table to the query and add a field, & save, when i return to the form it is missing all the data from the 1st table... when i remove the 2nd table, i can see the data again from original table. both these tables have a relationship to each other...

any assistance, or what i can check? thnaks

 

[The_Doc_Man]

This PROBABLY merits a new thread. However, I'm not a stick-in-the-mud about such things. Having tweaked, I will now move on.

You CANNOT bind a single form to multiple tables. Access does not allow this. You must bind forms that touch multiple tables via one of a few strategies.

* You can build a JOIN query to link the two tables, but see discussion below about dependency issues.
* You can make the form drive one table and use DLookup to look up a few things that are totally independent and that have scattered sources.
* You can make a sub-form with no obvious borders and make it the same background color as the background of your main form, then you can set a filter on the sub-form, force it to .Requery, and then make it become visible. The sub-form done this way requires extra maintenance and is very tricky to keep synchronized with the independent main form.

When you bind a form to a query that involves a relationship AND the relationship arrow "points the wrong way" AND you have a new data element/record/entry as the independent side of a parent/child OR one/one relationship, there is NO CHILD/PARTNER to be selected - because you cannot have a parentless child or a peerless one/one record and you still creating the parent/peer. So you might expect that nothing would be selected because it doesn't exist yet.


Rethink this design carefully.

 

[ilcaa72]

thanks for the feedback, you gave me some food for thought, at least i can start eliminating some options. thanks