Bound or Unbound (1 Viewer)

[TurboDieselOne]

I'm just getting into unbound forms and using querys to update table and am finding the forms a lot quicker in loading and the data a lot safer but the work in the design is far more extensive than I though as every action and reaction has to be coded. But the experianced gained may be fruitful in my further development in this field. Enough about me. What I wish to bring to the fore front is this.

A databse on a server in a peer-peer network and a front end with just the forms but no tables what so ever the qrys actually update the Backend as it foes with all the forms so no one gets more than they ask for and the network may actualy be faster. Is this trualy possible?

 

[WayneRyan]

Turbo,

That is definitely possible! The performance gains that you
mention are very attractive to some users. Access over a
network can be a very slow and cumbersome thing.

By going with unbound forms, you can substantially reduce
the amount of network traffic and have a very quick-loading
and quick-running app.

However, the cost of developing the database skyrockets. There
are many things that Access does for the developer that you
will have to forgo or do yourself in code.

Additionally, there are some very subtle things to consider.
If you are using SQL Inserts and/or Updates these are very
prone to "invisible" failures. They fail, but do not
communicate it to your software. Failures due to required
fields missing, etc.

It is a very worthwhile ambition, I would tend to vote
against it, but it will be interesting to see what other
opinions are on this topic.

btw, I'm a little biased. The worst app that I have ever
written was an equipment purchasing system that specified
unbound forms reflecting the modification history with delta
math making it a transactional based system. It worked, but
it was twenty times harder than it could have been and had
many, very subtle bugs.

Wayne

 

[pono1]

As Wayne said, it's possible to use unbound forms and, yes, if you're looking for speed it can make a big difference -- but, yes, your development time will increase significantly: you will have to do so much more in code (record concurrency, record refreshes, etc.) than otherwise. While you will certainly get a much better feel for the complexities of databasing, you may also feel a bit frustrated along the way; it may seem like you're getting less return for each line of code.

A couple of semi-popular alternatives:

Use bound forms but don't return all recs at once, only one at a time -- alter a form's recordsource property on the fly to do this. Keep subforms unlinked until users actually need them. Likewise, leave the rowsource of listboxes and combo boxes empty until a user clicks them (why run the queries beneath them if it's not necessary?), or feed them from local tables if the data is static.

Or use ADP (or ADE) files -- that is, put your BE onto SQL server and create an Access project. Access ADP files are geared toward network use (supporting features such as disconnected recordsets)...

A pretty good book on the topic.

Regards,
Tim

 

[Pat Hartman]

If your backend is Access, you are still dragging all the data over the network. Unbound forms will load faster but in and of themselves they do not reduce network traffic.

Network traffic is only reduced by using a "real" RDBMS rather than Jet to hold the tables. Your forms and queries should ALWAYS be based on queries with where clauses. Basing forms/queries directly on tables or on queries with no criteria opens a pipeline between Access and the backend db and Jet just keeps pumping data from the server to the client until the entire table has been transferred. With selection criteria, queries against ODBC linked tables will (for the most part) be run on the server and only return the requested rows.

 

[pono1]

Maybe we're gliding off in different, subtle directions here. An Access backend will not necessarily result, a priori, in all records slogging across the wire.

Here's a simple test that anyone with two PCs on a LAN can do: In an Access back end file create a serious table -- one with, say, 500,000 recs. In an Access front-end file, create two queries: one that unwisely uses an unindexed field in the criteria to bring back a single record from the BE, another that uses an indexed field in the criteria to bring back a single record from the BE.

Run code to fill a FE form with a record using either query...

'A more efficient Jet query

	Dim lngCusID as long
	Dim sSQL as String

	lngCusID = 10

'CusID is indexed
	sSQL =  SELECT Customer.CusID Customer.CusName 
		FROM Customer 
		WHERE Customer.CusID = lngCusID

	Me.RecordSource = sSQL 'fill form with rec

There will be a pronounced difference in response time -- favoring the indexed field query.

For more evidence, use a sniffer on the BE to collect packets as they jump on and off the network adapter; the stats will show much less data moving across the wire when your query uses an indexed field as criteria.

One thing, though -- this is not a performance panacea since indexed fields will create extra traffic when you are adding, editing, and deleting relevant recs.

Regards,
Tim

 

[TurboDieselOne]

So Lets say we do it like this. We have an Unbound Form called Main. In this form is a text box(Unbound) after the update event it runs a query that searches the table for Name "Unbound Text Box". it finds it and then what loads the other Unbound text boxes with the data from the Query so how will one do apply the results to the unboiund text boxes Oh and Conmbos for City postcode Country etc.