423,504 Members | 1,125 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,504 IT Pros & Developers. It's quick & easy.

Routine to copy a table and prompt user for name - Access 2016 Windows Platform

P: 10
I am trying to write a procedure in Access that will copy a table, prompt the user for the new name, then remove all the records from the original table. I thought I would be able to use a make table query followed by a delete query but the make table query does not prompt the user for the new table name. I am finding that vba in Access is much more difficult for me than in the other Office programs so I am sorry that this may seem so trivial based on the other questions I see posted on this site. Any help will be greatly appreciated.
Aug 6 '18 #1

✓ answered by zmbd

Kime1266
This could be done using the Table Definitions property of the database (Dim objTable as DAO.TableDefs) or as an action query using the database.exectute method ( SELECT oldTable.* INTO NewTable FROM oldTable; );


HOWEVER,

This would not normally be done in a properly designed database

One of us will be happy to assist; however, before we go down this "rabbit hole" can you explain why you have chosen this particular course of action?

This WILL lead to what is known as "Bloat" within your database causing it to prematurely hit the upper size limit.

Kime1266 I've sent a copy of some reference materials to your Bytes.com-Inbox

Share this Question
Share on Google+
7 Replies


zmbd
Expert Mod 5K+
P: 5,283
Kime1266
This could be done using the Table Definitions property of the database (Dim objTable as DAO.TableDefs) or as an action query using the database.exectute method ( SELECT oldTable.* INTO NewTable FROM oldTable; );


HOWEVER,

This would not normally be done in a properly designed database

One of us will be happy to assist; however, before we go down this "rabbit hole" can you explain why you have chosen this particular course of action?

This WILL lead to what is known as "Bloat" within your database causing it to prematurely hit the upper size limit.

Kime1266 I've sent a copy of some reference materials to your Bytes.com-Inbox
Aug 6 '18 #2

P: 10
You're so right, zmbd, and I should have thought about how it was going to be perceived before I asked my question. This IS NOT and was never really intended to be a "properly" designed database so to speak. This is why this site is so impressive because you did catch that and, as usual, after presenting my question and receiving an answer, the "light bulb" goes on (dimly, I might add). I am going to take a different direction. I am terribly sorry I wasted your time and thank you again for your superb expertise.
Aug 6 '18 #3

zmbd
Expert Mod 5K+
P: 5,283
I am terribly sorry I wasted your time and thank you again for your superb expertise.
> You didn't waste anyone's time - the only bad question is the one left un-asked and there is, or will be, someone else out there with the exact same question.
(ok, so there are some bad questions like the "Can you do this project/homework for me..." LOL)

> Here's the thing, there are some really very good occasions where creating a new table is the only route to go; HOWEVER, when I've been stuck in such a situation what I do is instance a new Access "back end" as a temporary file (I'll even use the %temp% directory to do this as everyone has read/write privileges) create the table and when done just delete the temporary file.
Typically I'll do this when importing a data file that may have some improper data types or isn't properly normalized, better to corrupt the temporary file than the main production file.
Aug 6 '18 #4

P: 10
Your being so nice about it and your recommendation is so valued. Every time I ask a question, I have gotten such wonderful response & also the little voice in my head that says, "Why didn't you think of that?" You're really quite invaluable to me. Thank you again for being so nice about it. Have a great rest of your day!
Aug 6 '18 #5

NeoPa
Expert Mod 15k+
P: 31,046
Frankly Kim, if students weren't allowed to make mistakes and ask wrong questions then none of us would ever learn anything. So, allow yourself to - just as we have to allow people to - including ourselves.

Obviously ZMBD is a very experienced teacher and is too wise to to run out of patience when he simply sees the ordinary, normal, learning process play out.

On the other hand I would never argue with you saying he's a nice lad.
Aug 6 '18 #6

zmbd
Expert Mod 5K+
P: 5,283
imma nice lad - my mommy said so Nahhh! :)
Aug 7 '18 #7

NeoPa
Expert Mod 15k+
P: 31,046
Rubbish punctuation - but mummy always knows best ;-)
Aug 7 '18 #8

Post your reply

Sign in to post your reply or Sign up for a free account.