Where would I arrange things fe/be-wise?

[Access9001]

Say I have the following entities:

1. A separate DBF file that has a bunch of data, rawdata.DBF
2. Make-table queries that take rawdata.DBF and... make tables, say, RawTables.
3. There may be processes that append to tables instead of recreating them.
4. Queries that manipulate RawTables in various ways -- Query1, Query2, Query3, etc.
5. There may be VBA processes that get used in these Queries themselves.
6. VBA processes that do some heavier lifting on things (exporting files, formatting, emailing, reporting, etc).
7. Front-end databases that utilize the Queries and allow users to read/write to tables.

My question to you all:
I: In terms of "general database layout," what would go where? What kind of locking would I use and where? Which general commands should I avoid and why? How do I minimize user-collision? I generally want to keep things as unlocked as possible to avoid update-collisions.

I know this may be a big question but I would *really* appreciate an answer on this because it's been driving me nuts. I've read many online tutorials but I feel like none of them apply to this sort of setup.

Thanks!

 

[The_Doc_Man]

In a true FE/BE configuration, all queries, forms, reports, macros, and modules go in the FE file. All data goes into a BE file.

It is possible but a bit unwieldy to have multiple BE files, but not unheard of.

Let me address a couple of points more or less randomly in a single narrative.

To minimize collisions, you do the split as mentioned. Then ALWAYS ALWAYS ALWAYS access the BE information through queries. Make your forms use the queries rather than direct table opening. Why? 'cause the queries "hide" the fact that the FE/BE split occurred. Once you apply the Linked Table manager, you are almost home free because the queries keep all of the issues hidden.

Users who have private copies of the FE file will NEVER EVER in a gazillion years collide with each other over opening forms, reports, etc. - because they each have local/private copies of those things. If you make forms use record-level locking and optimistic locking, that reduces lock issues. If you have any VBA code that does direct recordset operations, do them through queries, use dbOpenDynaset, dbConsistent, and dbOptimistic for the 2nd, 3rd, and 4th OpenRecordset parameters. Then you only lock something when you are about to update it. Lock, in, out, unlock and ZOOM you are done.

If you have data from multiple sources but don't always need those multiple sources "hot" then maybe you only have the stuff you want to keep "hot" in the BE file that is always open. You can surely open other databases on the fly if you know the file name and path. (OpenDatabase... look it up.) If you need to consult or import data from a third or fourth file, that might be the way to do it. Only open it when you need it. Ditto, Excel data sources, which you can directly open using application objects.

Oh, if you are going to do dynamic open/close, then that book "Everything I Need to Know, I Learned in Kindergarten" is appropriate. If you open it, close it. If you took it out, put it back when you are done. Don't leave your FE database and computer connected to the BE when you aren't using it. (I.e. turn out the lights when you aren't in the room.)

After you've chewed on this for a while, if you have specific questions, bring them back here. The more specific the question, the more likely you are to get a useful answer.

 

[Access9001]

So generally the backend is nothing BUT data and nothing more? No queries, forms, functions, etc? Is there any speed slowdown to doing it split? So the queries would basically look like "select * from [thisdatabase].[this table] and insert into etc"?

What if the frontend only has one copy via a shared drive?

 

[Access9001]

What would a sample setup be at the most basic level? Say we have the following as an example:

1. External dbf files, ext1.dbf and ext2.dbf that get dumped from another application (no control over this)
2. Backend DB, be.mdb
3. Frontend DB, fe.mdb

I want to:

-Create table1 via maketable query using ext1.dbf as its source
-Create table2 via maketable query using ext2.dbf as its source
-Have a query that joins table1 and table2 on a common field named Commonfield.
-VBA code/forms that kick this entire process off automatically (the maketables)

Where would I place what, and what might the queries look like in form? Where would I have linked tables?

 

[ghudson]

It is best to empty the table then append the data to the table instead of making the table each time you need to refresh the data.

 

[Access9001]

Why?

10char

 

[ghudson]

It reduces bloat and prevents any problems if an object [query, form, report] is bound to the table you are replacing if the make table procedure fails.

 

[Access9001]

So I would have to do each one with two commands? First delete from table, then append?

I can indeed do that instead -- but I am still confused about the general structure of all this, regardless.

 

[ghudson]

This simple command will purge the records in your table. Use brackets if your table name has a space in it. [Your Table Name Here]

CurrentDb().Execute "DELETE * FROM YourTableNameHere"

Then run your append SQL...

DoCmd.SetWarnings False
    DoCmd.RunSQL ("INSERT INTO YourTable ( [Account Number], Status ) SELECT YourTable.[Account Number], YourTable.Status FROM YourTable;")
DoCmd.SetWarnings True

All your shared tables go in the back end. Then you create table links from the front end to the back end. The front end contains all your queries, forms, reports. Each use will need their own front end copied to their computer and each front end is link to the shared back end.

 

[Access9001]

Right, but my question is about how this would all look with the fe/be structure and linked tables. Would I need to refer to the be database in my queries if I am deleting/inserting from a frontend?

For example, would it be something like this:

Windows scheduler kicks off fe.mdb
fe.mdb performs a delete from table1 in be.mdb
fe.mdb inserts data from ext1.dbf into table1 in be.mdb
fe.mdb does the same thing with table2 and ext2.dbf
fe.mdb then uses queries to join the two tables and then exports them into an excel file/report/whatever we might want

Also confused how linktables fit into this whole thing and how I can reduce locking. Can linked tables "lock" a backend table? Can multiple users use frontends that point to the same linktable without causing lockage? Would the queries/delete/insert table commands operate on the linktables or the actual be.mdb tables?

EDIT: Alright, done editing my post.

 

[Access9001]

Furthermore, is there a way to change a maketable into an append query while also extracting the table dest from that query? It's going to be a huge pain otherwise, since it goes from:

running "maketableA" query
to
running "delete from some table referenced via maketableA before"
+
running "append query into that table instead of making the table"

 

[Access9001]

Okay so I wrote a function that extracts the name of the table from an append query so I can run a delete from table command beforehand:

Private Function getTableNameFromQuery(query As String) As String

Dim qry As DAO.QueryDefs
Dim db As DAO.Database
Dim i As Integer

Set db = CurrentDb
Set qry = db.QueryDefs
getTableNameFromQuery = ""

For i = 0 To qry.Count - 1
If InStr(1, qry(i).name, "~sq_f") = 0 Then 'Actual queries only
If qry(i).Type = 64 And qry(i).name = query Then
getTableNameFromQuery = Replace(Replace(Left(LastWord(qry(i).SQL, "INSERT INTO "), InStr(1, LastWord(qry(i).SQL, "INSERT INTO "), " ") - 1), "SELECT", ""), vbNewLine, "")
End If
End If
Next

End Function


Where the "LastWord" function is a function I wrote that takes the end portion of a word based on a delimiter (excluding the delimiter) -- so LastWord(qry(i).SQL, "INSERT INTO ") would return everything after "INSERT INTO "

So it lets me do this:





tablename = getTableNameFromQuery(rs("queryName"))

DoCmd.SetWarnings False
CurrentDb().Execute "DELETE * FROM " & tablename
DoCmd.SetWarnings True

DoCmd.SetWarnings False
DoCmd.OpenQuery (rs("queryName"))
DoCmd.SetWarnings True





But is this how it all should arrange:

be.mdb = all the tables with raw data
fe.mdb = has linktables that link to the tables in be.mdb. Instead of running make-tables, delete from the linktables and then insert the relevant data into them (doing these operations on the linktables will in turn change the data in the be.mdb).

Is this the best/fastest way to do things?

 

[The_Doc_Man]

BE is always your raw data tables. FE is everything else. Here is why this works to make things better in a shared environment.

Every user makes a copy of the FE in a local directory on their workstations. Now when you open queries, forms, reports, macros, or code modules, the lock implicit in using that data element is local to your machine and the definitions of those objects is also local to your machine. So you do not waste time downloading a messy form from the BE. All you do is retrieve data from tables. Your idea here is to avoid using plug-ugly networking as a substitute for a local disk drive. Even really fast networks are usually far slower than the ATA bus or SCSI bus of a local disk. So you reduce the number of times that you have to go out on the network to do things.

It gets better if you use the optimistic locking option. The locks that you invariably created when writing data or deleting it will last for the shortest time possible. (That is what "optimistic" locking means.) And you don't care about the locks on forms and queries and reports - because they are on a different disk and also because they don't (can't possibly) overlap with the data driving the forms etc.

A maketable query requires that you destroy interfering structures. An erase query followed by an append query only destroys data. Either one will require you to eventually do a compact and repair on the BE file, but interestingly enough, you still save some time on this because at least you don't have to muck about in the FE file.

As to the import process, you might have to look this up, but there is a method of getting external data via an import specification that allows you to say which fields to import from another (external) database file. If you erase but don't destroy the targeted tables, you might be able to figure out how to make a stored import specification so that you could just run a MACRO to do the erase followed by the import. No explicit append or maketable involved in this case.

 

[Dairy Farmer]

It is possible but a bit unwieldy to have multiple BE files, but not unheard of.

I have such a setup.

BE = Farm1, Farm2 and Company

Farm1 and Farm2 are identical in structure. The only difference is in the tables they have a field (Farm) that has a default value of 1 (in Farm1) or 2 (In Farm2). This is the name of the location.

Company contains some common data that is not changed after the database is commisioned. i.e. The farm names, Company name and start dates.

Each location (geograhically seperated) is given the FE and the 3 BE's. From time to time Farm 1 will send Farm 2 its copy of Farm1 and Farm 2 will send farm 1 its copy of farm2. This allows each farm to compare data.

I had to do this as, due to very poor (read slooooow) internet connections it was not possible to have a central, online, BE. This also negated the need to have update and append code. farms do not enter data into each others BE's they only need to be able to compare.

In my case (guessing from my username) it is two dairy farms that are part of the same company. Each farm records its production, animal numbers, feed used,..... . Farm2 would like to know how its figures compares to Farm1 and what the combined figures are.

 

[The_Doc_Man]

I should not second-guess you, but if you are doing a centralized application, you should not need to have separate back-ends, one per farm. It should just be one more field in some table that tells you which farm you are working so that you can have the data merged for overview reporting - without so many unwieldy links. However, that is indeed second-guessing and I should not make snap judgments. Just don't be surprised if a bunch of other posts comment on denormalized tables. (And technically, they would be very much correct if the two farm BE files are of identical infrastructure, different only in content.)

 

[Dairy Farmer]

The problem I had with "combining" the data is that I have a script that auto creates all the dates for the current month. The date is my PK in my "primary" table. Other tables add data that does not require all the dates in the month, hence I call it my "primary table". A little confusing I guess.

Also I dont want Farm 1 to change the data for Farm 2 and vice vesa. Although they can, but when swapping data file this is "fixed".

Thanks for the input. Gives everyone something to think about for their planned DB.

BTW could anyone confirm if, but splitting a BE, you can bypass the size limit on an Access DB? Awaiting the flood of SQL BE is better posts.

 

[Access9001]

My backend is too large to put into only one file unfortunately

 

[The_Doc_Man]

A backend too large for one file can certainly be split into multiple files and is one of the few really good reasons to split BE files. (The other "best" reason has to do with different security requirements.)

The comment about "user from farm A should not be able to update data for farm B" tells me that your database isn't secured properly. This would not happen if you blocked users from seeing the database window. (This can NEVER be allowed in any database for which the word "secure" appears in any part of the specification.)

 

[Dairy Farmer]

The comment about "user from farm A should not be able to update data for farm B" tells me that your database isn't secured properly.

Didn't put any security in place. Only my boss and me who use it. I set his FE to automatically backup on exit.

When you open the DB you have a main menu. It has a ComboBox to select the farm you want to work with. Selecting a form uses the choise in this box, via a querys, to display the data in the forms and reports.

Therefore I only need one set of queries, forms and reports.

Also, whichever is the selected farm when the DB is closed, becomes the default farm when the DB is opened again. Therefore no need to ask the user at startup which farm to use.

 

[ghudson]

Unless you are already doing this... I would use just one form for the data input and use a combo box to determine which farm in needed to filter the records based on the farm. Based on that choice, auto set the txtFarm text box [bound to a Farm field in the table] to identify which farm the record belongs to. No need to have a data entry form for each farm.