Using a macro to change design view of a table

[Tuxla]

Hi
I have inherited a few databases and there are regular reports I now have to
run from them. Macros have been set up, but I would like to make the report
easier.

Is it possible to change the design view of a table by using a macro? I know
how to open the table in design view using a macro, but there are fields that
I need to change the data type each time I run the report which is a pain.
Can a macro do this for me?

I'm using Office 2000

Thanks for your help

 

[Ken Snell \(MVP\)]

What you seek to do generally is not a good plan -- changing the design view
of an object via macro or VBA code as part of the normal operation of the
database is prone to all types of problems, including bloat, design errors,
etc.

Tell us why you need to change the table's design view, and likely we can
suggest an alternative approach.

 

[tina]

if you need to change the data type of a field temporarily, recommend you
write a query and use a conversion function in the query to make the change.
then base your report on the query, or on a query that uses that conversion
query in place of the original table. look up "type conversion functions" in
Help for information on available functions,

hth

 

[SmartbizAustralia]

Weird question in that it is missing alot of relevant information

You can populate a temp table with the data you want or even use a
query and change the querydef!

So that query the report uses, can be changed on the fly....

But if you give us more info .. then you'l get a better answer..

Regards,
Tom Bizannes
Excel and Access Specialist
Sydney, Australia

 

[Jeff Boyce]

Based on your description, I'm assuming that you think you can only create a
report from a table. NOT TRUE!

If you want to get the best use of Access' relationallly-oriented
features/functions, you need to feed it well-normalized data.

If you want to create a report, use a query to assemble the data you want to
see in the report, then base the report on THAT, not directly on a table.

If your reporting requirements change, you can either modify the query (and
down-stream report definition), or you can build a form that helps you
customize the data obtained and build a single report that displays the
different sets of data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tuxla]

Thanks everyone. I didn't realise it would potentially be such a problem. It
is changing some of the fields from text to number and vice versa. I will
continue to do it manually as you don't recommend I do it in the macro

Thanks

 

[Jeff Boyce]

I'll assume you wish to change "some of the fields from text to number ..."
to meet a specific need. What is that need?

I ask, not out of curiosity, but because there may be ways other than the
one you've settled on to accomplish what you need.

For example, if you are trying to export data to Excel or some other
destination, and need a particular format, you can keep the data the way it
is in the table and use a query to generate the altered view you are looking
for...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP