Is there any VBA code for MS Access which will return name of all the Queries, Reports, Form and Modules from the DB

[strive4peace]

@SachAccess

> Need help to understand Linked Table Manager.

the Linked Table Manager tool helps you change the path(s) to your back-end(s)

> the purpose of Linked Table.

use linked tables when more than one person will be using the application. If that is the case, put tables in a different database(s) (back-end(s)). Then make a front-end database that links to the shared back-end(s). The front-end (FE) will have queries, forms, reports, macros, and modules, and possibly some resident tables ~ however, the tables to share will be linked to a back-end(s) that all can share. Each user will have their own copy of the front-end.

 

[Pat Hartman]

Linked tables are just that. In the Front end where all the forms, reports, queries, macros, and modules are, the tables are not physically present but instead they "link" to a different database which is called the Back End. It is the Back End which can be shared to support multiple users.

In a proper development environment, you have a minimum of two versions of the BE and usually three. Production (the final version used for real work and NEVER for testing), System Testing (the version that is used by the users for testing new versions of the FE), Unit Testing (the version used by the programmer to implement changes and test them).

The Linked tables manager is used to link between the various BE versions and is almost always only used by the programmer. It is very rarely used by the users since very few applications would ever have a need to switch between versions of Production data.

 

[SachAccess]

Thanks a lot for the help. Have a nice weekend.

Linked tables are just that. In the Front end where all the forms, reports, queries, macros, and modules are, the tables are not physically present but instead they "link" to a different database which is called the Back End. It is the Back End which can be shared to support multiple users.

In a proper development environment, you have a minimum of two versions of the BE and usually three. Production (the final version used for real work and NEVER for testing), System Testing (the version that is used by the users for testing new versions of the FE), Unit Testing (the version used by the programmer to implement changes and test them).

The Linked tables manager is used to link between the various BE versions and is almost always only used by the programmer. It is very rarely used by the users since very few applications would ever have a need to switch between versions of Production data.

 

[SachAccess]

@SachAccess, here's another SQL statement to get info from MSysObjects for object names and types that uses SWITCH to specify the object type and ordering ... it also filters out temporary stuff

SELECT Switch([Type]=1,"10",[Type]=5,"20",[Type]=-32768,"30",[Type]=-32764,"40",[Type]=-32766,"50",[Type]=-32761,"60",[Type]=4,"11",[Type]=6,"12",[Type]=8,"80",[Type]=3,"90",1=1,99) AS Ordr, Switch([Type]=1,"Table",[Type]=5,"Query",[Type]=-32768,"Form",[Type]=-32764,"Report",[Type]=-32766,"Macro",[Type]=-32761,"Module",[Type]=4,"ODBC Table",[Type]=6,"Linked  Table",[Type]=8,"Relationship",[Type]=3,"container") AS ObjectType, m.Name, m.Type, m.Flags
FROM MSysObjects AS m
WHERE (((m.Type)<>3) AND ((m.Flags)>=0) AND ((Left([Name],1))<>"~" And (Left([Name],1))<>"{" And (Left([Name],1))<>"_") AND ((Left([Name],4))<>"MSys" And (Left([Name],4))<>"MSys"))
ORDER BY Switch([Type]=1,"10",[Type]=5,"20",[Type]=-32768,"30",[Type]=-32764,"40",[Type]=-32766,"50",[Type]=-32761,"60",[Type]=4,"11",[Type]=6,"12",[Type]=8,"80",[Type]=3,"90",1=1,99), Nz(Switch([Type]=1,10,[Type]=5,20,[Type]=-32768,30,[Type]=-32764,40,[Type]=-32766,50,[Type]=-32761,60,[Type]=4,70,[Type]=6,80,[Type]=8,90,[Type]=3,100),999), m.Name;

you can add more columns as desired

Thanks for the help. Have a nice weekend.

 

[SachAccess]

Thanks for the help. Please correct me if am wrong. I will use the MS Access file I have as an example.
So, the file with Linked Tables are Front End. This file has all the forms, reports, queries, modules and macros.
The file which has original table will be called as Back End.
I checked that file too, it only has tables, there is nothing in the file apart from the tables.

So the Linked Tables in the Front End are just mirror copy of the Back End tables.
The changes done in the Front End Linked Tables affect Back End tables too ? And vice versa.
I am not sure about my my understanding.
Just trying to make sure if my understanding is correct.

PS - Few tables are linked to SharePoint. What pre-caution I should take while accessing, editing tables linked to SharePoint.
What are the things that I should keep in mind while working on tables linked to SharePoint.
Thanks.

 

[isladogs]

The linked tables in the frontend are NOT mirror copies of the backend tables. They ARE the backend tables.
So any changes made in the front end are actually changing the backend tables.

It doesn't matter whether those linked tables are in Access, SharePoint, SQL Server etc. Exactly the same points apply.

 

[Pat Hartman]

Regardless of whether the linked tables are Jet/ACE, SharePoint, SQL Server, or any other RDBMS, When you make a data change via Access using a form, query, or directly to the table in DS view, the change is made directly to the physical table in the BE Database. There is no intermediate storage. If you run an Action query such as update or append or delete, you might get a "do you really want to do this" message depending on how you run the query but that's it.

If you need to change the structure of a linked table, you CANNOT do it in the FE. You MUST open the BE database and make the change there. However, you need to ensure that no user is actively working with the BE at the time you change the physical structure so this must always be done in a planned, carefully monitored way. Best to make BE changes in the evening or on weekends when it is easy to lock users out.

Also, not all linked tables are updateable. For example, tables linked to flat files such as .txt, .csv, or .xlsx CANNOT be updated through the Access interface. Excel files (not .csv files) can be updated using OLE automation. to change other flat files, they need to be rewritten. You need to read the file sequentially using one filespec and then write out all the records including the updates using a second filespec. Although, you can append data to the end of a flat file, I've never attempted to do this via Access. In 25 + years, I've never had a reason to even try although in my mainframe days with COBOL, that process was very common.

RDBMS files such as SQL Server and SharePoint must have primary keys or unique indexes to be updateable. You also must have permission from the database manager. There are other rules also.

 

[isladogs]

@Pat Hartman
Whilst your answer describes good practice, it isn't entirely correct. For example:
a) you can change the structure of linked Access BE tables from the FE using a DDL query and SQL Server BE tables using a SQL script run from the FE
b) linked Excel tables can be updated in Access without using automation, To do so, you just need to set the IMEX value = 0

Edit Linked Excel Data from Access

This article describes two methods of making linked Excel files directly editable in Access

www.isladogs.co.uk

This method allows you to append new records & update existing records.
However you cannot delete records using this approach

 

[SachAccess]

The linked tables in the frontend are NOT mirror copies of the backend tables. They ARE the backend tables.
So any changes made in the front end are actually changing the backend tables.

It doesn't matter whether those linked tables are in Access, SharePoint, SQL Server etc. Exactly the same points apply.

Thanks for taking time on weekend and helping me.

 

[SachAccess]

Regardless of whether the linked tables are Jet/ACE, SharePoint, SQL Server, or any other RDBMS, When you make a data change via Access using a form, query, or directly to the table in DS view, the change is made directly to the physical table in the BE Database. There is no intermediate storage. If you run an Action query such as update or append or delete, you might get a "do you really want to do this" message depending on how you run the query but that's it.

If you need to change the structure of a linked table, you CANNOT do it in the FE. You MUST open the BE database and make the change there. However, you need to ensure that no user is actively working with the BE at the time you change the physical structure so this must always be done in a planned, carefully monitored way. Best to make BE changes in the evening or on weekends when it is easy to lock users out.

Also, not all linked tables are updateable. For example, tables linked to flat files such as .txt, .csv, or .xlsx CANNOT be updated through the Access interface. Excel files (not .csv files) can be updated using OLE automation. to change other flat files, they need to be rewritten. You need to read the file sequentially using one filespec and then write out all the records including the updates using a second filespec. Although, you can append data to the end of a flat file, I've never attempted to do this via Access. In 25 + years, I've never had a reason to even try although in my mainframe days with COBOL, that process was very common.

RDBMS files such as SQL Server and SharePoint must have primary keys or unique indexes to be updateable. You also must have permission from the database manager. There are other rules also.

Thanks a lot for the help! Now slowly I am understanding it. When I was able to make changes in the tables in both back end and front end then initially I thought, what is the purpose how having tables at two different places.

'If you need to change the structure of a linked table, you CANNOT do it in the FE' this line helped clearing my doubt.

 

[Pat Hartman]

I'm not sure what changes you made to the table in the FE. If you are talking about drawing lines in the relationships window, that isn't actually defining any relationships. It is just documentation of a sort. Relationships can only be defined in the physical database since that is where they would be applied. This is clearer when the BE is SQL Server. The logic is a little muddy with Jet/ACE but still true. Think about this. You have two FE's linked to one Jet/ACE BE. How would FE #2 know anything about relationships defined in FE #1? It couldn't so therefore ONLY what is defined in the actual, physical BE could ever be enforced.

 

[spaLOGICng]

Hi,
Is there any VBA code for MS Access which will return name of all the Queries, Reports, Form and Modules from the DB
What I am trying to do is, when I run this macro, it should give me list of all the queries, reports, forms, macros, modules from the DB.
May be it can create a table in DB itself or create a list in Excel, both is fine.
If anyone has such ready code, could you please help me.

HI,
I am a little late to the party as always, however here is a great resource for all your VBA programming needs, While it is a bit dated, it is still very relevant today...


Access Cookbook, 2nd Edition
by Ken Getz, Paul Litwin, Andy Baron
Released March 2004
Publisher(s): O'Reilly Media, Inc.
ISBN: 978059600678

You can purchase the book from O'Reilly directly or on Amazon. Digital Download and Companion Code is available.

 

[Pat Hartman]

I agree. It is an excellent book if you can learn by example. The only dated part is that it solves shortcomings with Access that existed at the time the book was written but have been fixed in newer versions. VBA is VBA and really hasn't changed much since the book was written. Just a few new functions.

 

[SachAccess]

HI,
I am a little late to the party as always, however here is a great resource for all your VBA programming needs, While it is a bit dated, it is still very relevant today...


Access Cookbook, 2nd Edition
by Ken Getz, Paul Litwin, Andy Baron
Released March 2004
Publisher(s): O'Reilly Media, Inc.
ISBN: 978059600678

You can purchase the book from O'Reilly directly or on Amazon. Digital Download and Companion Code is available.

Thanks for the help.

 

[SachAccess]

I'm not sure what changes you made to the table in the FE.

Hi,
Thanks for the help.
I only changed one value from one of the linked table in both Front End and Back End.
Both at different fields though. Changes were updates in both the Frond End and Back End.
Will try to provide more details going forward.
Have a nice day ahead.

 

[SachAccess]

Access Cookbook, 2nd Edition
by Ken Getz, Paul Litwin, Andy Baron

I have Excel VBA book from the same author! And another I have is from John Walkenbach.