• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

Using "Select * INTO" and original table field captions carryover.

Experts,
    Should be an easy one. When you use a "SELECT * INTO ..." statement to create a new table, why dont the original table field captions carryover to the new table (they differ from the field names due to spaces making the forms more understandable)??

Dieszel
0
DieszelDawg
Asked:
DieszelDawg
  • 3
  • 2
2 Solutions
 
tcullerCommented:
I believe that the answer lies in the fact that SQL is the standardized way of manipulating data, and while it can create a table, the commands are quite limited in standard SQL.  The caption field property is an MS Access property, not "standard."
I think the answer lies in copying and pasting the Access object, rather than moving the data via SQL.  The approach is probably to be found in a VBA reference somewhere.  I will try and find an actual code snippet for you.

HTH,

Tim
0
 
Leigh PurvisDatabase DeveloperCommented:
Yeah it's pretty standard type code to loop through and set the properties you want.

For example to just view one

Debug.Print currentdb.TableDefs("TableName").Fields("FieldName").Properties("Caption")

You could set them the same way
Loop through the fields of the old table - setting the captions in the newly created one.

The only thing to be aware of is that the Caption property doesn't exist when the field has no caption - so you'll be creating the property for each field.
0
 
tcullerCommented:
Okay, I found something that works in VBA.  The standard approach is to use the DoCmd.CopyObject method.  The sample below copies the Employees table in Northwind, making a backup copy (in the same database) as BUEmployees.  It could also make a copy of the table in another database by specifying the other database name as the first parameter (see the help for CopyObject from any code module).
I used two procedures, one calling the other, and passing it the table name to be copied:
Public Sub CopyTable(strTableName As String)
DoCmd.CopyObject , "BU" & strTableName, acTable, strTableName

End Sub

Public Sub TEST()
Dim strTableName As String
strTableName = "Employees"
Call CopyTable(strTableName)

End Sub

HTH,

Tim
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
tcullerCommented:
P.S.
    The backup copy of the table which is created using the DoCmd.CopyObject method carries over ALL properties, including the caption property.

Tim
0
 
DieszelDawgAuthor Commented:
Tim and LPurvis,
    Makes sense ... I forgot ny own rule about viewing everything I do in Access as VB and SQL, not just one or the other.
0
 
DieszelDawgAuthor Commented:
Tim and LPurvis,
     Im doubling and splitting the points.... When I asked the question I should have the the WHERE claus, which looking back is why I didnt go the COPYOBJECT route.... I didnt want the whole table recreated, just certain catagories with possible appends of other catagories later. LPurvis, I'll give it a try your way... Thanks both.

Dieszel
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now