Insert Into SalesTemp (field1) (select field1 from Sales)

Thinker wrote:
I just tried that insert into. Don't ask me why, but as soon as I 
took the select statement out of the parenthesis, it worked!

Private Function sbPrepareFields(strTableName As String, _
        strFieldToRename As String, strFieldNewName As String, _
        strFieldFromScratch As String) As Boolean

    Dim dbEng As DBEngine
    Dim db As Database
    Dim wrk As Workspace
    Set dbEng = New DBEngine
    Set wrk = dbEng.Workspaces(0)
    Set db = wrk.OpenDatabase(App.Path & "\Sample.mdb")  'This line in VB
    'Set db = CurrentDb  'Use this Line if within Access
    
    Dim tdfNew As TableDef
    Set tdfNew = db.CreateTableDef(strTableName)
    Dim x As Object
    Dim nCount As Integer
    nCount = 0
        For Each x In db.TableDefs(strTableName).Fields
            wrk.BeginTrans
            If db.TableDefs(strTableName).Fields(nCount).Name = strFieldToRename Then    '***This will rename Fields
                db.TableDefs(strTableName).Fields(nCount).Name = strFieldNewName
            End If
            nCount = nCount + 1
            wrk.CommitTrans
        Next
        
'*** This will add a new field
    If strFieldFromScratch <> "" Then
        wrk.BeginTrans
        db.TableDefs(strTableName).Fields.Append tdfNew.CreateField(strFieldFromScratch, dbLong)
        wrk.CommitTrans
    End If
        
    db.Close
    wrk.Close
    
    Set dbEng = Nothing
    Set wrk = Nothing
    Set db = Nothing
    Set tdfNew = Nothing
End Function

Private Sub Command1_Click()
sbPrepareFields "tblInvoice", "SomeName", "SomeNameNew", "FromScratchNEW"
End Sub