I have a table called DataTable. I want to produce a new table 

DataTableNew which is identical except for an extra field in which all 

records are the name of the original table, ie DataTable.



I can do this manually with an update query followed by a make table 

query. But I have several hundred tables, all with identical structure, 

to convert. It would be nice to automate the process so that I can just 

prompt for the table name and the whole process is carried out.



I am having difficulty getting to grips with using SQL statements in VBA 

code and in using variables in SQL statements. Can anybody help in 

getting me started please.

Hey Clive,



SQL embedded in VBA code can be a bit tough to get your head around at

first, but is definitely worth it for the power it gives you.  You're

basically writing a program (in VBA) that writes another program (in SQL),

and then runs it.  



The two stumbling blocks for me were 1) figuring out which things I wanted

to execute in the VBA program and which I wanted to execute in SQL, and 2)

making my decisions in 1) understood to VBA and SQL.  The biggest part of 2)

is remembering to put the right delimiters around date and text values that

wind up as literals in your SQL.



My advice is to build your SQL up in a variable & do a lot of

debug.print'ing it, so you can see what it looks like & even copy/paste it

into the SQL view of a new query.  If you do that you'll get all the

interactive error messages you're used to from doing things manually (which

are not always helpful, but frequently are).



Getting to your task at hand, the following should suit:



' ================================================

Public Function NewTable(strTableName As String) As Boolean

Dim strSQL As String

Const strcAppendToName As String = "New"

On Error GoTo Finish



   ' Note the embedded single-quotes delimiting the original table

name--that's a 

   ' string literal by the time it gets to SQL

   strSQL = "SELECT " & strTableName & ".*, '" & strTableName & "' as

SourceTable" _

      & vbNewLine & "INTO " & strTableName & strcAppendToName _

      & vbNewLine & "FROM " & strTableName & " ; "

   

   With DoCmd

      .SetWarnings False

      .RunSQL strSQL

      .SetWarnings True

   End With



Finish:

   NewTable = (Err.Number = 0)



End Function

' ================================================



Gratuitous extra comment: the nature of this task (and the phrase "several

hundred tables" associated with an Access database) makes me wonder if

you're dealing with a well designed table structure.  If it's at all

feasible, you might want to take a hard look at how your tables are arranged

& see if it can't be improved.



Cheers,



-Roy



-----Original Message-----

From: Clive Astley [mailto:clive.astley@p...]

Sent: Sunday, February 25, 2001 8:27 AM

To: Access

Subject: [access] Automating action queries





I have a table called DataTable. I want to produce a new table DataTableNew

which is identical except for an extra field in which all records are the

name of the original table, ie DataTable.



I can do this manually with an update query followed by a make table query.

But I have several hundred tables, all with identical structure, to convert.

It would be nice to automate the process so that I can just prompt for the

table name and the whole process is carried out. 



I am having difficulty getting to grips with using SQL statements in VBA

code and in using variables in SQL statements. Can anybody help in getting

me started please.