'Make a Table' Query

[Fred75]

I am facing a major hold-up in my Access Project. I am attempting to design a 'make a table' query which will encompass the ability to autoname the table by merging the textual content of the only two querried fields. Any chance of getting some good advice on how to tackle this? Thanks in advance.

 

[Sergeant]

This might work from VBA (not tested).

Sub MakeTable()
    strSQL = "SELECT [Field1], [Field2] INTO " & [Field1] & [Field2] & " FROM [Table]"
    DoCmd.RunSQL strSQL
    
End Sub

 

[Fred75]

Thanks Sergeant!

Sergeant, thank you for your time and assistance.

Building on your concept, and after doing some homework, the following code came into existance. Any feedback and/or suggestions is/are welcome.

===========

Sub MakeTable()

Dim strSql As String
Dim strSql1 As String
Dim StrSql2 As String
Dim NewTableName As String
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strConn As String

strSql1 = "SELECT DISTINCT [field1], [field2] "
strSql1 = strSql1 & "INTO [tbl_tmp] "
strSql1 = strSql1 & "FROM

; "

Set db = CurrentDb()
db.Execute (strSql1)
db.Close

Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\"

objConn.Open strConn

StrSql2 = "SELECT [field1] "
StrSql2 = StrSql2 & "FROM [tbl_tmp] "
objRS.Open StrSql2, objConn

NewTableName = objRS("field1")

If objRS.EOF Then MsgBox "No Data Found" Else
Do While Not objRS.EOF
objRS.MoveNext
Loop

objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = Nothing

MsgBox " NewTableName : [" & NewTableName & "] "

End Sub

==========

 

[Sergeant]

A bit confused...

Perhaps you are trying to do more than you said in your initial post. Are you trying to make the table in the same database as the source table? Or...are you trying to make this table in a different database?
Maybe you should give an overview of your desired process, as this may change the approach drastically.

 

[Fred75]

Overview Process

Sergeant, in reply to your last post, following is the process to achieve.

[ 'table' contains several records (field1; field2; etc.) from which a 'child' database needs to be created in the same database and named following a nomenclature which will replicate the textual value of field1 for quick referencing. ]

Buiding on your initial suggestion, I resumed the following coding process in order to create and populate the child table named 'NewTableName':
1. Select field1 and field2 in order to create a temporary table (tbl_tmp) and act as temporary storage of 'newtablename' value
2. Create a connection string to open the database connection
3. Create object variables for 'NewTableName' variable value allocation by:
3.1. Selecting field1 from tbl_tmp & Openning a recordset and
3.2. Assigning NewTableName to the record value returned.

I hope my coding was not redundent as this was the only way I could get the code to run. Would you recommend a quicker way of getting it created?

Thanks in advance.