Code to copy table and prompt for new name

[Andy]

I want to create a button on a form that will let the user copy a set table
and then prompt them to enter the name of the new table, then paste the
copied table into the same database under the user defined name.

Anyway to do from a form rather than manually F11 and copy/paste?
Ta

 

[Dennis]

Dim strNewName As String

strNewName = InputBox("Enter New Table Name", "New Name")
DoCmd.CopyObject , strNewName, acTable, "YourExistingTableName"

 

[Andy]

Worked like a dream.
Thankyou

Dim strNewName As String

strNewName = InputBox("Enter New Table Name", "New Name")
DoCmd.CopyObject , strNewName, acTable, "YourExistingTableName"

 

[Mark Norton]

Hi - apologies first, I'm a pretty low level user. Could you tell me how you did this - module? Macro? Not sure what to do with your code. I want to create a fairly simple function - basically I want (users to be able) to Copy a table, then rename it, but ad-hoc/prompted rather than specifying the name in the CopyObject macro. I’m creating a simple Birdwatching program, and the copied table is a new type of list e.g. Life List, County List, Year List etc

Many Thanks

 

[fredg]

Hi - apologies first, I'm a pretty low level user. Could you tell
me how you did this - module? Macro? Not sure what to do with your
code. I want to create a fairly simple function - basically I
want (users to be able) to Copy a table, then rename it, but
ad-hoc/prompted rather than specifying the name in the CopyObject
macro. I’m creating a simple Birdwatching program, and the copied
table is a new type of list e.g. Life List, County List, Year List
etc

Many Thanks

It appears you are replying to another post. I have no idea what that
post was about.
Deciphering this message, it appears you wish to copy and rename a
table, having the user enter the new table name.
You can use code to do this very easily.
Click on Modules + New.
Copy and paste this code into the new module:

Public Sub CopyAndRename()
Dim strName As String
strName = InputBox("The new table name is?", "Name the new table.")
DoCmd.CopyObject , strName, acTable, "ExistingTableName"
End Sub

Change "ExistingTableName" to whatever the current actual table name
is.

Save the module.
You can then call it from anywhere in your computer, using:

CopyAndRename

I rather suspect, however, that your database structure is not
correct.
Having separate tables for, for example, the Year (i.e. Year List), is
not a good way to utilize Access.

 

[John W. Vinson]

Hi - apologies first, I'm a pretty low level user. Could you tell me how you did this - module? Macro? Not sure what to do with your code. I want to create a fairly simple function - basically I want (users to be able) to Copy a table, then rename it, but ad-hoc/prompted rather than specifying the name in the CopyObject macro. I’m creating a simple Birdwatching program, and the copied table is a new type of list e.g. Life List, County List, Year List etc

Many Thanks

NONE of these should be in Tables. Instead you should use a Query with
criteria. You can display a query datasheet, or base a Form on the query, or
base a Report on the query, or export the query, or... take your pick.
Creating a new table for every subset of the data just clutters your database
with tables of forgotten meaning, bloats your database and ruins performance.

 

[tina]

i've tried to piece together what you're doing, since the post that starts
this thread seems to be coming into the middle of a dialogue with MVP John
Vinson. i gather that you want a "list of lists" (?) for each user, and
initially set up multiple tables to that end, and John steered you away from
multiple identical tables into using one table - which would be standard
relational design advice.

Thanks very much for your reply - after what you said I thought maybe a
better way to do it would be to create a lists table with one column for the
species, and have tick lists as columns representing a different type of list,
thereby merely extending an individual table rather than creating a new table
every time a new list was needed. As you said, the latter would be complete
overkill.

but here it seems that you are again straying from normalized design. each
table represents an entity, or subject, in relational design. each field
represents a characteristic that describes that entity. when you store data
in fieldnames (such as ListA, ListB, ListC), you are breaking normalization
rules. since your post seems to indicate that one species may be included in
many lists, there is a one-to-many relationship between species and lists.
so you need a child table to hold the names of the lists that are associated
with each species. each list for a species is *one record* in the child
table, NOT one field. your users can add one list for a species, or 100, or
1,000 - whatever applies - and without changing the table structure at all.

if you want examples of the actual tables' relational structure, you'll need
to provide some detail about current structure of the table(s) that identify
a user, and the table(s) that identify a species, and the table(s) that link
users to species. since i gather this database has something to do with
birdwatching, and i know nothing at all about birdwatching, it would help if
you'd explain what these "lists" are and why a user may want to associate
many of them with one species.

hth

Thanks very much for your reply - after what you said I thought maybe a
better way to do it would be to create a lists table with one column for the
species, and have tick lists as columns representing a different type of list,
thereby merely extending an individual table rather than creating a new table
every time a new list was needed. As you said, the latter would be complete
overkill. The number of lists would in mosts cases level off at about 10 or
12 - there are only so many lists a person needs - even birdwatchers!

So i've been trying to figure out how to do the following - create a macro
(or code?) so users can enter a new column in a table, rename it, and change
the data type. (I'm happy to do it all straight into the table, but if other
people want to use the database themselves, messing about with the tables may
not be the best; a macro or command buttons would be much better)

I created a (pretty basic - standard access operators) macro that got as far
as creating a new column in an existing table, renaming the column (in quite
a basic way, it just opened the actual table
with the column name highlighted and ready to change), but I couldn't change
the data type to 'Yes/No' (Boolean) with the macro; it would also need to be
a tick box for the users.

I wouldn't have known how to interrupt the macro and create a prompt for the
user to rename the new column, which would be far preferable to opening the
actual table to do it. I have been racking my brains, maybe put three macros
on a form, 1 to create the column, 1 to rename it, and one to change the data
type - a 3 part list creation process.

Any ideas? Is this too big a request for this arena? Many thanks in advance
for any advice

Mark Norton

how you did this - module? Macro? Not sure what to do with your code. I
want to create a fairly simple function - basically I want (users to be
able) to Copy a table, then rename it, but ad-hoc/prompted rather than
specifying the name in the CopyObject macro. I’m creating a simple
Birdwatching program, and the copied table is a new type of list e.g. Life
List, County List, Year List etc

 

[tina]

hmm, okay. your explanation was helpful. where i think you're getting
confused is the difference between recording data and extracting information
from it.

So basically, the DB is to Record observation details (could be easily
adapted to other hobbies, trainspotting, fishing etc) but I also want to have
the facility to record lists of species seen.

you don't need to record "lists". what you need to do is identify what data
must be recorded about an instance of a species sighting, in order to
include that instance (record) in a defined list (report).

This is what birdwatchers tend
to do (and plane, train, eddie stobart spotters do too, I imagine) - keep
separate lists of things seen: "How many birds have I seen: Ever; This Year;
Last Year; Whatever year; In my back garden; In my local park, In a
particular county/Country etc etc"

you've already begun to define your data needs. for example: if you record
the species and date and time of each sighting, then you can query the
records for a list of

species = bird
time frame = this year (or last year, or a specific year, or all years,
etc.)

if you also record where each sighting occurred - i would probably recommend
a table of locations that each user can enter records into, so each user's
database has a locations list that is relevant to him/her - then you can
query the records for, as another example, a list of

number of each species, sighted at each location, in a given time frame

include location specifics *in the locations table*; for example if one
location record just says "my local park", that's fine - but include fields
in the location table for county, state, country, etc, so each location is
fully defined. that way you can query for "my local park" sightings, or for
all sightings in France (if French locations are on your locations list) -
which may include your local park, if you live in France.

hopefully the above makes some sense to you. what you're attempting sounds
like a lot of fun to set up, actually - a challenge to thoroughly analyze
the process and design a tables/relationships structure that will support it
and allow for planned expansion in targeted areas. i really recommend that
you read up on relational design principles; Access is a fiend at extracting
valuable information out of just this kind of minute detail data - *if* you
leverage the power of the software by implementing good design. i believe
that process analysis is going to be key to your success, so i suggest you
check out the tip at http://home.att.net/~california.db/tips.html#aTip1. the
book mentioned there provides good instruction on process analysis as well
as relational design principles.

hth

Many thanks for taking the trouble to reply, Tina. Yes, I seem to have sort
of barged in on an existing thread, I originally asked the original poster if
it worked. Apologies to the other thread contributors if you are getting
auto-emailed!

Tina, in a nutshell the Database is primarily to record the details of field
trips (or single observations),but also to make various lists - such as total
birds seen, birds seen this year, birds seen locally, countywide, particular
countries etc

I have created a table of species (actually I have created a list of birds,
plants, mammals and butterflies, as many birdwatchers record other
fauna/flora. Each species has the decription of either 'Bird, Flower,
Butterfly, Mammal in its record)

Other tables: a location table (not populated, for the 'user' to populate
with various location fields/details. Various (small) weather tables: Wind
Speed, Wind Direction, Type of weather, Temperature, and a Habitat table
(type of habitat). The reason for this granularity is for reporting/query
purposes, common questions ornithologists ask are types of birds seen under
what conditions.

There is a 'Field Trips' table which has lookup fields to all the above
tables, with the addition of a date table (This is the top part of the form) .
There is also a 'Field Trip species' table which contains the species (lookup
to species table), Number of species seen, time, and a free text details
field. This is the 2nd part of the form; the 'Field Trips species' table is
related to 'Field Trips' so the reports I've writen show the full details of
all birds seen on any field trip.

I have a form which has the components of 'Field Trips' table on it and a
sub-form of 'Field Trip Species' table, which is how the information is
inputted into the tables. I'm sure it's dead clunky but it just about
works. It's not a live project yet, I'm still bolting it together. I may
run a very basic version for myself soon, and continue to work on the DB with
a view to sharing it, and perhaps developing it with SQL and VB express as a
learning project for myself.

So basically, the DB is to Record observation details (could be easily
adapted to other hobbies, trainspotting, fishing etc) but I also want to have
the facility to record lists of species seen. This is what birdwatchers tend
to do (and plane, train, eddie stobart spotters do too, I imagine) - keep
separate lists of things seen: "How many birds have I seen: Ever; This Year;
Last Year; Whatever year; In my back garden; In my local park, In a
particular county/Country etc etc"

I hope this describes what I'm doing, I'm sorry if it all seems a bit
amateurish, but I suppose that's because it is! But I want to get a lot
better at this. I've really not decided yet how I'm going to create the
lists, but it is a pretty crucial part of birdwatching activity, so I'm going
to have to figure out a way, and one which doesn't involve people going into
table design to do stuff. Obviously it's fine for me, but not for people who
don't know access very much except as users.

Once again, I really appreciate you taking an interest in my question.

i've tried to piece together what you're doing, since the post that starts
this thread seems to be coming into the middle of a dialogue with MVP John
Vinson. i gather that you want a "list of lists" (?) for each user, and
initially set up multiple tables to that end, and John steered you away from
multiple identical tables into using one table - which would be standard
relational design advice.

but here it seems that you are again straying from normalized design. each
table represents an entity, or subject, in relational design. each field
represents a characteristic that describes that entity. when you store data
in fieldnames (such as ListA, ListB, ListC), you are breaking normalization
rules. since your post seems to indicate that one species may be included in
many lists, there is a one-to-many relationship between species and lists.
so you need a child table to hold the names of the lists that are associated
with each species. each list for a species is *one record* in the child
table, NOT one field. your users can add one list for a species, or 100, or
1,000 - whatever applies - and without changing the table structure at all.

if you want examples of the actual tables' relational structure, you'll need
to provide some detail about current structure of the table(s) that identify
a user, and the table(s) that identify a species, and the table(s) that link
users to species. since i gather this database has something to do with
birdwatching, and i know nothing at all about birdwatching, it would help if
you'd explain what these "lists" are and why a user may want to associate
many of them with one species.

hth

[quoted text clipped - 3 lines]

Creating a new table for every subset of the data just clutters your database
with tables of forgotten meaning, bloats your database and ruins

performance.

 

[tina]

you're very welcome. good luck with it! and of course you can post back any
time; i don't know how long i'll keep a "watch" on this thread though. and
if you have more questions as you continue, make sure you start a new
thread; most folks ignore older threads, and you'll want a lot of people to
lay eyes on your question(s), so you'll have a better chance of getting the
answers you need.

Hi Tina, Thanks very much for the advice. I definitely need to get a good
grasp of the fundamentals of dbase design to ultimately save time by getting
things right earlier, rather than having to redesign every time I learn a bit
more! I'll check that link out. You're right about linking the field trip
sightings and the various lists, and that as you say will warrant some
serious jigging. I also think I need to create the ability to short-circuit
the above process of adding to the list via observation records by also
creating a direct entry option; people who have been birdwatching for years
may have forgotten the details of where they saw a bird for the first time,
so perhaps forcing to them to create a new observation record in order to
populate a list (which could be hundreds - more if you include
plants/butterflies and mammals) might be forcing them a bit - but we'll see -
as you say I need to have a good think about the mechanics of it all - and
yes, it is quite good fun, especially when bits of it start to work!!

Again, many many thanks for your trouble. I may update you if that's OK
later on...

hmm, okay. your explanation was helpful. where i think you're getting
confused is the difference between recording data and extracting information
from it.

So basically, the DB is to Record observation details (could be easily
adapted to other hobbies, trainspotting, fishing etc) but I also want to have
the facility to record lists of species seen.

you don't need to record "lists". what you need to do is identify what data
must be recorded about an instance of a species sighting, in order to
include that instance (record) in a defined list (report).

This is what birdwatchers tend
to do (and plane, train, eddie stobart spotters do too, I imagine) - keep
separate lists of things seen: "How many birds have I seen: Ever; This Year;
Last Year; Whatever year; In my back garden; In my local park, In a
particular county/Country etc etc"

you've already begun to define your data needs. for example: if you record
the species and date and time of each sighting, then you can query the
records for a list of

species = bird
time frame = this year (or last year, or a specific year, or all years,
etc.)

if you also record where each sighting occurred - i would probably recommend
a table of locations that each user can enter records into, so each user's
database has a locations list that is relevant to him/her - then you can
query the records for, as another example, a list of

number of each species, sighted at each location, in a given time frame

include location specifics *in the locations table*; for example if one
location record just says "my local park", that's fine - but include fields
in the location table for county, state, country, etc, so each location is
fully defined. that way you can query for "my local park" sightings, or for
all sightings in France (if French locations are on your locations list) -
which may include your local park, if you live in France.

hopefully the above makes some sense to you. what you're attempting sounds
like a lot of fun to set up, actually - a challenge to thoroughly analyze
the process and design a tables/relationships structure that will support it
and allow for planned expansion in targeted areas. i really recommend that
you read up on relational design principles; Access is a fiend at extracting
valuable information out of just this kind of minute detail data - *if* you
leverage the power of the software by implementing good design. i believe
that process analysis is going to be key to your success, so i suggest you
check out the tip at http://home.att.net/~california.db/tips.html#aTip1. the
book mentioned there provides good instruction on process analysis as well
as relational design principles.

hth

Many thanks for taking the trouble to reply, Tina. Yes, I seem to have sort
of barged in on an existing thread, I originally asked the original

poster if
[quoted text clipped - 83 lines]

Creating a new table for every subset of the data just clutters your database
with tables of forgotten meaning, bloats your database and ruins

performance.