Copy a table and number it?

[Henrootje]

dear code wizards..............

U have helped me a lot in the past, I hope some of you have some good
ideas about this one:


I have a table called tblQROSK1

Now I am looking for a function that will do the following:

I want:

tblQROSK4 copied to tblQROSK5
tblQROSK3 copied to tblQROSK4
tblQROSK2 copied to tblQROSK3
tblQROSK1 copied to tblQROSK2
tblQROSK1 emptied.

I need this procedure repeated for every table of which the name starts
with 'tblQ'
Additionally I would like the code itself to check whether a table
already exists.
What I do now, I have code that copies the content of table 4 to 5
etcetera, (thank you all again) but that means tables 5-1 already have
to exist and if I change a field in one of the '1' tables I have to
recreate al the followoing tables. Furthermore, I noticed that copying
a table takes a lot less time then moving its data from one to another!

Any suggestions????

Thanks for thinking!!

Grtz Henro

 

[Roger Carlson]

First of all, since you acknowledge previous help, I assume someone has told
you that this very bad database design. You wouldn't have these problems if
you kept all the data in one table and just grouped the records with some
value.

Next, a couple of questions:
1) will there always be 5 tables?
2) or will there be an unknown number of tables, adding 1 everytime the
funtion is run?
3) Are there Relationships between these tables and any others?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Henrootje]

Roger,

we are aware that this does not seem to be ideal design but after a lot
of discussion we opted for this. It has to do with backups of data with
the possibility to revert to this data. There are a thousand reasons,
enough to bore you to d.... so please trust me on this one

There will be max 5 'generations' of tables (tblQROSK1 to tblQROSK5).

Number one will always exist, the other ones will not necessarily but
possibly

Grtz Henro

 

[Henrootje]

And no relations at all, sorry, forgot that

 

[Roger Carlson]

I understand. I just feel remiss if I don't say it.

There are a number of different ways to do this. You can use SQL, DAO or
ADO (different object models), or neither. I have opted for the latter as
you can use it in any version of Access without setting any references.

Place this code in a General Module:
-----------
Sub CopyTables()
Dim starttable As String
Dim icounter As Integer
Dim maxnumber As Integer

starttable = DMax("Name", "MSysObjects", "[name] Like ""tblQ*""")
maxnumber = Right(starttable, 1)
If maxnumber = 5 Then
CurrentDb.Execute "Drop table tblQROSK5"
maxnumber = maxnumber - 1
End If
DoCmd.SetWarnings False
For icounter = maxnumber To 1 Step -1
DoCmd.CopyObject "", "tblQROSK" & icounter + 1, acTable, "tblQROSK" &
icounter
Next icounter
DoCmd.SetWarnings True
CurrentDb.Execute "Delete * from tblQROSK1"
End Sub
-----------
Note: this code assumes if table 5 exists, it is to be deleted.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

Watch out for word-wrap on the DoCmd line. icounter should go on the end of
the previous line.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I understand. I just feel remiss if I don't say it.

There are a number of different ways to do this. You can use SQL, DAO or
ADO (different object models), or neither. I have opted for the latter as
you can use it in any version of Access without setting any references.

Place this code in a General Module:
-----------
Sub CopyTables()
Dim starttable As String
Dim icounter As Integer
Dim maxnumber As Integer

starttable = DMax("Name", "MSysObjects", "[name] Like ""tblQ*""")
maxnumber = Right(starttable, 1)
If maxnumber = 5 Then
CurrentDb.Execute "Drop table tblQROSK5"
maxnumber = maxnumber - 1
End If
DoCmd.SetWarnings False
For icounter = maxnumber To 1 Step -1
DoCmd.CopyObject "", "tblQROSK" & icounter + 1, acTable, "tblQROSK" &
icounter
Next icounter
DoCmd.SetWarnings True
CurrentDb.Execute "Delete * from tblQROSK1"
End Sub
-----------
Note: this code assumes if table 5 exists, it is to be deleted.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger,

we are aware that this does not seem to be ideal design but after a lot
of discussion we opted for this. It has to do with backups of data with
the possibility to revert to this data. There are a thousand reasons,
enough to bore you to d.... so please trust me on this one

There will be max 5 'generations' of tables (tblQROSK1 to tblQROSK5).

Number one will always exist, the other ones will not necessarily but
possibly

Grtz Henro

 

[Ron2005]

You could also create an empty table (version 0)
delete table 5,
rename 4 to 5,
rename 3 to 4,
etc
and then copy 0 to 1.

All doable with a macro

 

[Roger Carlson]

Unfortunately, not all of the tables will necessarily exist:
Quote:
There will be max 5 'generations' of tables (tblQROSK1 to tblQROSK5).
Number one will always exist, the other ones will not necessarily but
possibly
UnQuote:

It would be difficult to program a macro to handle a situation where
anywhere from 1 to 5 tables existed.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Ron2005]

I agree, but why would they not exist. Can you make them all exist.
Just create dummy/empty ones so that they exist.

If you are doing periodic backups, won't they all exist after 5
periods/cycles?

 

[Roger Carlson]

I don't know. That was just in the original requestor's specifications. I
assumed there was reason, so I programmed to it.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Ron2005]

Give him more than he asks for, It makes the system more usable and
versatile.

 

[Roger Carlson]

I'm sorry, I can't agree. Using macros and hard-coding tables makes the
process less flexible not more flexible. My code works with any number of
tables between 1 and 5 and if it needs to be extended to 10, a small code
change is all it takes.

Besides, the notion that as a developer, I should substitute my judgement
for my customer's is anathma to me.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Ron2005]

I guess I agree and disagree.

As a developer, part of the job is to decipher what the customer is
really asking for and implementing it in a way that gives him what he
has really requested. How it is done in the background is usually not
the prerequisit of the customer. Giving him the mechanism to access at
a minimum what he has asked for without hamstringing the
design/implementation for expanding on that is where the art of
development enters the picture.

As a developer you use keys to derive the data, for instance in a
dropdown, even though it might show the description which is what the
customer requested. What is the difference between that and having a
constant of 5 backups even though it is unknown if all will have 5 to
start with. After 5 cycles of backups they will all have 5 and both the
code approach and the macro approach will accomplish the same and both
require a simple change to expand. Content and processes ARE the
customer's, Exactly how they are implemented usually depends on the
approach, understanding, experience, background, expertise of the
developer and the tools involved.

Developing it in a manner that is easily maintained and expanded and
accurate and user friendly and usable is the goal.

I really don't know how I would implement it if the application and
customer were mine. I was simply passing on one approach that some
developers would feel comfortable using that was simple and straight
forward. He was asking for suggestions and that is all I was trying to
give.

Grtz, both Roger and I wish you success in whichever approach you
decide to implement.

Ron

 

[Henrootje]

Grtz is meant to be short for: GReeTZ

I am actually called Henro. I went after some long hot debating over
here with Roger's solution.

Changes are high that those tables might change somehow in the future
and with Rogers code it will be corrected automatically if sb forgets
to copy the tables. Furthermore, I do not need to be bothered by the
copying any more.\

Originally Ron's solution was implemented but I put my foot down
because I was getting annoyed by the continous copying.......... :s

Grtz (that is 'greets' for short ) Henro