Help with populateing a table using VBA

[alguzman]

Need help with filling a table using VBA. I created the table fine but Having trouble with the sql string to fill that table with the information form the Order's table. All I want is that one field to be populated with that same field from the order's table.




Function MakeTable()
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim idx As Index
Dim strSql As String

Set db = CurrentDb()

Set tbl = db.CreateTableDef("NewOrder")
Set fld = tbl.CreateField("OrderID", dbLong)
tbl.Fields.Append fld

strSql = "INSERT INTO NewOrder ( OrderID ) SELECT Order.OrderID FROM [Order]"

Set idx = tbl.CreateIndex("PrimaryKey")
idx.Primary = True
idx.Required = True
idx.Unique = True

Set fld = idx.CreateField("OrderID")
idx.Fields.Append fld
tbl.Indexes.Append idx

db.TableDefs.Append tbl
RefreshDatabaseWindow
MsgBox "The " & tbl.Name & " table was successfullly created"

End Function

 

[chenn]

What about using a SQL statement to create your table...

DoCmd.RunSQL "CREATE TABLE tblORDERS(Order varChar)"

And then using another SQL statement to populate....

DoCmd.RunSQL "INSERT INTO tblORDERS ( [Order] ) SELECT OldTable.Order FROM OldTable;"

It looks like you are on the right track right now, but I don't see you actually ever running your sql statement. However, my code will reduce your code from 20 lines to 2 lines.

Enjoy.

 

[alguzman]

Answered My own question

I put in the following code after the table was created:

Set qdf=currentDB.createQueryDef ("",strSQL)
qdf.execute

worked...