Copy Table And Add To Its Name?

[voskouee]

I have the following code that copies tables and i need to add a string on the new table that will be copied. ANy suggestions

Set RsSOBP = CurrentDb.OpenRecordset("SELECT SOBP FROM RFG_Append;")

strtable = " RsSOBP!SOBP "
RFG = "RFG"

'create RFG tables
Do While Not RsSOBP.EOF
DoCmd.SetWarnings (False)
strsqlb = "SELECT Generic.* INTO & strtable &'RFG' FROM Generic;"
DoCmd.RunSQL strsqlb
DoCmd.SetWarnings (True)
RsSOBP.MoveNext
Loop

My generic tables are created with the following code... that works fine....

'create generic tables
Do While Not RsSOBP.EOF
DoCmd.SetWarnings (False)
strsqla = "SELECT Generic.* INTO " & RsSOBP!SOBP & " from Generic;"
DoCmd.RunSQL strsqla
DoCmd.SetWarnings (True)
RsSOBP.MoveNext
Loop


thanks guys in advance..

 

[boblarson]

A couple of things...
1. Curious as to why you are duplicating tables (in the course of normal operations this shouldn't occur - there are exceptions, but I'm wondering why).

2. If you do have to do that, why go through all of the SQL when you can just use

    DoCmd.CopyObject CurrentProject, "YourTableNameHere", acTable, "YourTableNameYouAreCopyingFrom"

 

[voskouee]

I am building a utility that imports data in a big table. from that table i have to go and look at one column that will give me the names of the tables that need to be created. this is done the firs time.

Second thing i have to run to that column again. get the names of the tables again and add the string "RFG" to it.

Third time i have to do the same but this time i need to add the string "TD"

at the end i must have one generic table, one with the generic name plus RFG, and another one with the generic name with TD.

thats y i cant use that command..

can i add a string on the sql i already wrote?

 

[boblarson]

You should still be able to use that command. However, what exactly do you mean by "imports data in a big table. from that table i have to go and look at one column that will give me the names of the tables?"

Can you give a better example? What total fields are in that table and what are some of the values?

I may not need the answers (although if given those we might be able to determine if there is a more efficient way of doing what you want), as you can replace the SQL strings with:

Set RsSOBP = CurrentDb.OpenRecordset("SELECT SOBP FROM RFG_Append;")

'create RFG tables
Do While Not RsSOBP.EOF

strTable = RsSOBP!SOBP & " RFG"

DoCmd.SetWarnings (False)
DoCmd.CopyObject CurrentProject, "strTable", acTable, "Generic"
DoCmd.SetWarnings (True)
RsSOBP.MoveNext
Loop