schema to text file

[Keith G Hicks]

I need a tool that can export an entire access adp or mdb schema (tables
layouts [if mdb], queries, forms, reports, macros & modules) to a text file.
The undocumented text export is woefully inadequate. I am working with an
adp file that is connected to a somewhat messy MS SQL database. I need to
figure out which backend objects are not in use anywhere in the adp. If I
could export the entire adp to a text file (not the data, just the schema) I
could do text searches on it. I have done a lot of work in Delphi and it's
all text based and very nice to work with. ADPs and MDBs are a joke in this
respect. I'd rather convert this to some non-containerized type of system
but cannot do that rigth now. I'm stuck with what I have. Any good, positive
suggestions would be appreciated.

Thanks,

Keith

 

[Tom van Stiphout]

On Sat, 24 Oct 2009 19:59:20 -0400, "Keith G Hicks" <[email protected]>
wrote:

For your ADP you need to fire up SQL Server Management Studio and (in
2008 version) right-click the database and select Tasks > Generate
Scripts.
For MDBs you may want to look into the Database Documenter.

-Tom.
Microsoft Access MVP

 

[Keith G Hicks]

Thanks for the info Tom.

I'm very familiar with the scripting in SQL. That's not the problem. And the
documenter in mdb's doesn't exist for adp's. The other problem is that even
with mdb's it just generates access reports which would need to be output to
pdf's in order to be searchable. It's a pretty darn limited feature to say
the least. Microsoft has continued to think of Access as a layman's type of
tool which is strange since I would wager that the majority of people using
Access (I don't mean "end" users) are developers.

Keith

For your ADP you need to fire up SQL Server Management Studio and (in
2008 version) right-click the database and select Tasks > Generate
Scripts.
For MDBs you may want to look into the Database Documenter.

-Tom.
Microsoft Access MVP

I need a tool that can export an entire access adp or mdb schema (tables
layouts [if mdb], queries, forms, reports, macros & modules) to a text
file.
The undocumented text export is woefully inadequate. I am working with an
adp file that is connected to a somewhat messy MS SQL database. I need to
figure out which backend objects are not in use anywhere in the adp. If I
could export the entire adp to a text file (not the data, just the schema)
I
could do text searches on it. I have done a lot of work in Delphi and it's
all text based and very nice to work with. ADPs and MDBs are a joke in
this
respect. I'd rather convert this to some non-containerized type of system
but cannot do that rigth now. I'm stuck with what I have. Any good,
positive
suggestions would be appreciated.

Thanks,

Keith

 

[Tom van Stiphout]

I'm sure the reason for the absence of the documenter in ADP is that
the back-end is entirely in SQL Server, and it has excellent reporting
tools.
If you don't like the MDB tools, nothing prevents you from writing a
bit of VBA code to export the data you need. DAO has excellent control
over the Tabledefs collection, the Fields collection, Indexes, etc. I
did exactly that for an in-house "db assessment" tool, and I'm sure
many power users have done the same.

-Tom.
Microsoft Access MVP

Thanks for the info Tom.

I'm very familiar with the scripting in SQL. That's not the problem. And the
documenter in mdb's doesn't exist for adp's. The other problem is that even
with mdb's it just generates access reports which would need to be output to
pdf's in order to be searchable. It's a pretty darn limited feature to say
the least. Microsoft has continued to think of Access as a layman's type of
tool which is strange since I would wager that the majority of people using
Access (I don't mean "end" users) are developers.

Keith

For your ADP you need to fire up SQL Server Management Studio and (in
2008 version) right-click the database and select Tasks > Generate
Scripts.
For MDBs you may want to look into the Database Documenter.

-Tom.
Microsoft Access MVP

I need a tool that can export an entire access adp or mdb schema (tables
layouts [if mdb], queries, forms, reports, macros & modules) to a text
file.
The undocumented text export is woefully inadequate. I am working with an
adp file that is connected to a somewhat messy MS SQL database. I need to
figure out which backend objects are not in use anywhere in the adp. If I
could export the entire adp to a text file (not the data, just the schema)
I
could do text searches on it. I have done a lot of work in Delphi and it's
all text based and very nice to work with. ADPs and MDBs are a joke in
this
respect. I'd rather convert this to some non-containerized type of system
but cannot do that rigth now. I'm stuck with what I have. Any good,
positive
suggestions would be appreciated.

Thanks,

Keith

 

[Jerry Martin]

Hello:

Would any of the Access utilities from FMS, Inc. ( http://www.fmsinc.com/ ),
such as the Total Access Analyzer (
http://www.fmsinc.com/MicrosoftAccess/BestPractices.html ), be of any use to
you?

Cheers,
Jerry

Thanks for the info Tom.

I'm very familiar with the scripting in SQL. That's not the problem. And
the documenter in mdb's doesn't exist for adp's. The other problem is that
even with mdb's it just generates access reports which would need to be
output to pdf's in order to be searchable. It's a pretty darn limited
feature to say the least. Microsoft has continued to think of Access as a
layman's type of tool which is strange since I would wager that the
majority of people using Access (I don't mean "end" users) are developers.

Keith

For your ADP you need to fire up SQL Server Management Studio and (in
2008 version) right-click the database and select Tasks > Generate
Scripts.
For MDBs you may want to look into the Database Documenter.

-Tom.
Microsoft Access MVP

I need a tool that can export an entire access adp or mdb schema (tables
layouts [if mdb], queries, forms, reports, macros & modules) to a text
file.
The undocumented text export is woefully inadequate. I am working with an
adp file that is connected to a somewhat messy MS SQL database. I need to
figure out which backend objects are not in use anywhere in the adp. If I
could export the entire adp to a text file (not the data, just the
schema) I
could do text searches on it. I have done a lot of work in Delphi and
it's
all text based and very nice to work with. ADPs and MDBs are a joke in
this
respect. I'd rather convert this to some non-containerized type of system
but cannot do that rigth now. I'm stuck with what I have. Any good,
positive
suggestions would be appreciated.

Thanks,

Keith

 

[Albert D. Kallal]

I need a tool that can export an entire access adp or mdb schema (tables
layouts [if mdb], queries, forms, reports, macros & modules) to a text
file. The undocumented text export is woefully inadequate.

I am Not really sure why the text export is not up to your linking here? You
do realize that this text export system is how Visual Source safe works with
ms-access? In other words, when you use source code control with ms-access,
you are checking in (and out) essentially text objects from the code
depository. By the way, using visual source safe also allows you to have
multiple developers working on one application at the same time. You can
check in/out individual objects and forms. (in other words, you not treating
the application as a single mdb file).

Note that the database documenter you mention also produces reports which
when you are viewing CAN be sent out to word, or text documents or even HTML
(use the export option while viewing the documenter reports). Not really
sure why you prefer pdf here, but that export option is available in 2007
even for documenter reports. However, the documenter will NOT work with
ADP's.

So, my bets are either visual source safe, or simply export the parts you
want with the save-as text option.

Any good, positive suggestions would be appreciated.

Well, I think the save-as text idea of the ADP is a start, but perhaps even
better for you since you worked with systems like Delphi is to use source
code control here. Since you mentioned more "serous" types of development
tools then I am betting you never started any Delphi project without using
some type of source code control system either.

While ms-access does support visual source safe, became the WHOLE system is
one simply a file, to backup or make copies of a WHOLE application, we only
have to deal with one simple and delightful file. That single file means
that I have a backup folder, and inside of that folder I have another backup
folder. So, it takes less then 10 seconds of my time to push up the two
copies and thus be working on a 3rd copy (that means I have 3 previous
days/copies of my work. Most people if you have a ton of text files have to
copy tons of different and many files..it is a pain where as in ms-access it
is a snap since we only deal with one file. So, this also explains why
source code control is often used in other systems, but no so much with
ms-access.

So, I thinking for a developer like you who don't find the adp environment
up to your liking, then use source code control here should make this whole
thing less of a joke for you.

once the VSS depository is built, you can use some freeware utilities to
export the whole thing to sql server (with text indexing etc).

http://www.windeveloper.com/vss2mdb/vss2mdb.htm

 

[Keith G Hicks]

I need a tool that can export an entire access adp or mdb schema (tables
layouts [if mdb], queries, forms, reports, macros & modules) to a text
file. The undocumented text export is woefully inadequate.

I am Not really sure why the text export is not up to your linking here?
You do realize that this text export system is how Visual Source safe
works with ms-access? In other words, when you use source code control
with ms-access, you are checking in (and out) essentially text objects
from the code depository. By the way, using visual source safe also allows
you to have multiple developers working on one application at the same
time. You can check in/out individual objects and forms. (in other words,
you not treating the application as a single mdb file).

Check out my post "export schema problems" on 10/12/2009

VSS doesn't handle what I need. Has nothing to do with multiple developers.
For example, in Delphi I can open up the text files for 10 forms at once in
a EditPad Pro (great text editor tool) and do a global search and replace on
all the forms at once without picking through each one. I know that
nothing's going to let me do that in Access but it's a good reason for text
files capability or at least exporting to text and then importing from text.

I'm also (and this is the main reason for this post) trying to search the
adp for instances of backend objects. One of the previous developers created
a lot of backend objects that are probably not in use and I want to remove
them during a major clean up. If Access were comprised of text files or I
could export the entire thign to a series of (very complete... not missing
anything) text files then my job would be easier. I found out that FMS Total
Access Anaylizer has a feature that lets you search the entire mdb or adp
for instances of any string. It seraches every object including container
properties, command bars, macros, etc. It seems to be very thorough. $300
for a single user license with limited support (email/phoen support for 30
days only - additional $300 for unlimited support) that only covers one
version of Access is VERY VERY pricey though.

Note that the database documenter you mention also produces reports which
when you are viewing CAN be sent out to word, or text documents or even
HTML (use the export option while viewing the documenter reports). Not
really sure why you prefer pdf here, but that export option is available
in 2007 even for documenter reports. However, the documenter will NOT work
with ADP's.

You're right. HTML and Word would work too. And that they don't include the
documenter in ADP is sad. What I need to search most are forms and reports.
The documenter would come in handy even though there are no native tables
and queries in ADP's. Sort of short sighted on MS's part IMO.

 

[Keith G Hicks]

I tried out their Access Analyzer yesterday after I posted this. I'd looked
at it before but never ran the demo. It actually comes closest to what I'm
looking for.

Hello:

Would any of the Access utilities from FMS, Inc. (
http://www.fmsinc.com/ ), such as the Total Access Analyzer (
http://www.fmsinc.com/MicrosoftAccess/BestPractices.html ), be of any use
to you?

Cheers,
Jerry

Thanks for the info Tom.

I'm very familiar with the scripting in SQL. That's not the problem. And
the documenter in mdb's doesn't exist for adp's. The other problem is
that even with mdb's it just generates access reports which would need to
be output to pdf's in order to be searchable. It's a pretty darn limited
feature to say the least. Microsoft has continued to think of Access as a
layman's type of tool which is strange since I would wager that the
majority of people using Access (I don't mean "end" users) are
developers.

Keith

For your ADP you need to fire up SQL Server Management Studio and (in
2008 version) right-click the database and select Tasks > Generate
Scripts.
For MDBs you may want to look into the Database Documenter.

-Tom.
Microsoft Access MVP


I need a tool that can export an entire access adp or mdb schema (tables
layouts [if mdb], queries, forms, reports, macros & modules) to a text
file.
The undocumented text export is woefully inadequate. I am working with
an
adp file that is connected to a somewhat messy MS SQL database. I need
to
figure out which backend objects are not in use anywhere in the adp. If
I
could export the entire adp to a text file (not the data, just the
schema) I
could do text searches on it. I have done a lot of work in Delphi and
it's
all text based and very nice to work with. ADPs and MDBs are a joke in
this
respect. I'd rather convert this to some non-containerized type of
system
but cannot do that rigth now. I'm stuck with what I have. Any good,
positive
suggestions would be appreciated.

Thanks,

Keith