Use of insert to put data into a table (1 Viewer)

[st3ve]

Something so simple yet I am just not getting it!!!

I want to pull the field names from my table ,tblQ17, and list them in a single column table I have ready, called Allfields. (Field name is NameOfField)

Code is ...

Sub TestThis()
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Set rst = CurrentDb.OpenRecordset("tblQ17")
For Each fld In rst.Fields
MsgBox fld.Name
fredde = fld.Name

'....works ok so far...

' This line works OK to put a fixed value into the table....
DoCmd.RunSQL "INSERT INTO Allfields (NameOfField) SELECT 'sssss'"

'' but i want to do this...
DoCmd.RunSQL "INSERT INTO Allfields (NameOfField) .*******. (meaning to be Now put the field name found above into the table Allfield in column NameOfField)

Next fld

rst.Close
Set rst = Nothing
Set fld = Nothing
End Sub

Simple? Yeah?

Please help.

 

[Dugantrain]

DoCmd.RunSQL "INSERT INTO Allfields (NameOfField) .*******.

should be

DoCmd.RunSQL "INSERT INTO Allfields (NameOfField) VALUES (" & _
     fld.Name & ")"

 

[Pat Hartman]

I wouldn't use a query to do this. It's a little silly to incur the overhead of "compiling" a query and running it for each row that you want to append. use the .AddNew method of DAO or ADO. Help includes code examples.

 

[st3ve]

Job Done

Solid advice as ever - thank You, Pat.

I'm going with Dugantrain's solution for now, though I found that I had to add an extra pair of '........' around the field name as in :-

DoCmd.RunSQL "INSERT INTO Allfields (NameOfField) VALUES ('" & fld.Name & "')".

I'll try the add new method next time.

Thanks both.