What is ‘Analyze Table’ (1 Viewer)

[SachAccess]

I was scrolling through MS Access Ribbon and found this feature.
Tried playing with this. Selected ‘No, I want to decide’ option. When I proceed, got a pop-up saying ‘You didn’t split your table. Please split it, or choose Cancel to exit the wizard’. I do not know, how to split the table and what will be impact of splitting the table.
Can anyone please help me understanding why table splitting is done and what analyzing table does.
I hope am not asking too basic questions. Apologies.

 

[MajP]

How Table Analyzer Wizard works - Microsoft 365 Apps

Provides information about how to run the Table Analyzer Wizard and how the Table Analyzer Wizard works.

docs.microsoft.com

An improperly designed table will repeat data. Instead the data should be stored once in another table. Then to display the related information you use a query to bring the two tables together. This is a fundamental principle of db design. The analyzer points this out.

 

[The_Doc_Man]

Can anyone please help me understanding why table splitting is done

Table splitting is done because you plan to share your database among users. The optimum way to do this is to split the single-file (a.k.a. monolithic) DB into two parts - a front-end (FE) and a back-end (BE). Then you place the BE file, which will contain tables and nothing else, into an area where your users can see it and interact with it - THROUGH THE FE. The FE contains all the Queries, Forms, Reports, Macros, and Modules; these are the elements of your DB that actually DO something. (Data by itself does nothing; it just sits there.)

We do this split so that we can distribute copies of the FE file to each user to run locally from their individual workstations. This optimizes the file locking situation, because in this split case, Access uses file locks to minimize user interference. But in the FE file (which has no data and is therefore unlikely to be updated during a session), all of the file locks are private. There can be no lock interference in the FE even if two users open (a copy of) the same form at the same time. If you actually allow users to share the FE directly rather than through a copy, you run into collisions when you open forms.

With this data split, most report-oriented objects open as SHARED READ (with respect to locking). The write operations are (a) brief and (b) done several disk buffers at a time, but NOT the whole file. So EXCLUSIVE WRITE locks only rarely experience a LOCK COLLISION (which is the origin of the error message RECORD LOCKED.) If you use Optimistic Locking on your queries, Access goes in, writes, and steps out again - VERY quickly. So you usually never see the actual interference because it occurs only for the briefest moment.

There is more to it than this, but it is a starting point for WHY you split DBs. You do it to minimize the effect of Windows-imposed file locks when you share the data of your database.

 

[MajP]

Here is a good example. First the employee name, first, last, mi is repeated in the table. This information is pulled out and then the tables are related by a Primary key

First Normal Form (1NF) - Normalising Your Database | Database Solutions for Microsoft Access | databasedev.co.uk

1st Normal Form (1NF): Database design theory includes design standards called normal forms. The process of making your data and tables match these standards is called normalizing data or data normalization. By normalizing your data, you eliminate redundant information and organize your table...

www.databasedev.co.uk

 

[SachAccess]

Table splitting is done because you plan to share your database among users. The optimum way to do this is to split the single-file (a.k.a. monolithic) DB into two parts - a front-end (FE) and a back-end (BE). Then you place the BE file, which will contain tables and nothing else, into an area where your users can see it and interact with it - THROUGH THE FE. The FE contains all the Queries, Forms, Reports, Macros, and Modules; these are the elements of your DB that actually DO something. (Data by itself does nothing; it just sits there.)

We do this split so that we can distribute copies of the FE file to each user to run locally from their individual workstations. This optimizes the file locking situation, because in this split case, Access uses file locks to minimize user interference. But in the FE file (which has no data and is therefore unlikely to be updated during a session), all of the file locks are private. There can be no lock interference in the FE even if two users open (a copy of) the same form at the same time. If you actually allow users to share the FE directly rather than through a copy, you run into collisions when you open forms.

With this data split, most report-oriented objects open as SHARED READ (with respect to locking). The write operations are (a) brief and (b) done several disk buffers at a time, but NOT the whole file. So EXCLUSIVE WRITE locks only rarely experience a LOCK COLLISION (which is the origin of the error message RECORD LOCKED.) If you use Optimistic Locking on your queries, Access goes in, writes, and steps out again - VERY quickly. So you usually never see the actual interference because it occurs only for the briefest moment.

There is more to it than this, but it is a starting point for WHY you split DBs. You do it to minimize the effect of Windows-imposed file locks when you share the data of your database.

Thanks a lot for detailed post.

Table splitting is done because you plan to share your database among users. The optimum way to do this is to split the single-file (a.k.a. monolithic) DB into two parts - a front-end (FE) and a back-end (BE). Then you place the BE file, which will contain tables and nothing else, into an area where your users can see it and interact with it - THROUGH THE FE. The FE contains all the Queries, Forms, Reports, Macros, and Modules; these are the elements of your DB that actually DO something. (Data by itself does nothing; it just sits there.)

We do this split so that we can distribute copies of the FE file to each user to run locally from their individual workstations. This optimizes the file locking situation, because in this split case, Access uses file locks to minimize user interference. But in the FE file (which has no data and is therefore unlikely to be updated during a session), all of the file locks are private. There can be no lock interference in the FE even if two users open (a copy of) the same form at the same time. If you actually allow users to share the FE directly rather than through a copy, you run into collisions when you open forms.

With this data split, most report-oriented objects open as SHARED READ (with respect to locking). The write operations are (a) brief and (b) done several disk buffers at a time, but NOT the whole file. So EXCLUSIVE WRITE locks only rarely experience a LOCK COLLISION (which is the origin of the error message RECORD LOCKED.) If you use Optimistic Locking on your queries, Access goes in, writes, and steps out again - VERY quickly. So you usually never see the actual interference because it occurs only for the briefest moment.

There is more to it than this, but it is a starting point for WHY you split DBs. You do it to minimize the effect of Windows-imposed file locks when you share the data of your database.

Thanks lot for such a detailed post. About the 'lock' part from your post, Have just read as a theory.
With your post, getting the actual relevance of it.
Have a nice day ahead.

 

[SachAccess]

Here is a good example. First the employee name, first, last, mi is repeated in the table. This information is pulled out and then the tables are related by a Primary key

First Normal Form (1NF) - Normalising Your Database | Database Solutions for Microsoft Access | databasedev.co.uk

1st Normal Form (1NF): Database design theory includes design standards called normal forms. The process of making your data and tables match these standards is called normalizing data or data normalization. By normalizing your data, you eliminate redundant information and organize your table...

www.databasedev.co.uk

Thanks a lot for the help.

 

[MajP]

@SachAccess and @The_Doc_Man
There is some confusion here. There are two issues
Splitting a Table
Splitting a Database
To be clear these are entirely different issues. The original question deals with splitting a table as I addressed. That is what the table analyzer does. What the Doc Man is discussing is when you split the database with only tables in the "back end" and everything else (forms, reports, queries, code) in front ends.

You can use the wizard to split your database

Split an Access database - Microsoft Support

Split an Access database into front-end and back-end components to improve performance and safely share data with other users.

support.microsoft.com

 

[The_Doc_Man]

OK, MajP, on re-read I see that the question is perhaps a LEETLE bit ambiguous. I answered the way I read it. But in light of your post #7, I see there is another interpretation of the question. But that's OK. SachAccess seemed satisfied with the answers anyway, so we did good.

 

[SachAccess]

OK, MajP, on re-read I see that the question is perhaps a LEETLE bit ambiguous. I answered the way I read it. But in light of your post #7, I see there is another interpretation of the question. But that's OK. SachAccess seemed satisfied with the answers anyway, so we did good.

Am more than satisfied.

 

[SachAccess]

@SachAccess and @The_Doc_Man
There is some confusion here. There are two issues
Splitting a Table
Splitting a Database
To be clear these are entirely different issues. The original question deals with splitting a table as I addressed. That is what the table analyzer does. What the Doc Man is discussing is when you split the database with only tables in the "back end" and everything else (forms, reports, queries, code) in front ends.

You can use the wizard to split your database

Split an Access database - Microsoft Support

Split an Access database into front-end and back-end components to improve performance and safely share data with other users.

support.microsoft.com

You are correct. I was using 'Table Analyzer'. Will re-read all the posts in some time to understand more. At present, at work. Thanks.