Problem creating Field

[Ragnar78]

Hy,

I use a Table Query to generate a table, and i want to add a Field in this newly created table.
So this is the code i use...

The problem is that it does not create the field and it blocks at
!WEEK_STATUS

Sub Moveinto()

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim MyTable As DAO.TableDef
Dim x As String

Set MyDB = CurrentDb
Set MyTable = MyDB.CreateTableDef("Retreived_Data", dbOpenDynaset)

With MyTable
     .Fields.Append .CreateField("WEEK_STATUS", dbText, 7)
End With

Set MyRS = MyDB.OpenRecordset("Retreived_Data", dbOpenDynaset)
    
    With MyRS
         .MoveFirst
         .Requery
         While Not .EOF
               x = CStr(Year(.Fields("SYSMODTIME").Value) & "w" & Format(.Fields("SYSMODTIME").Value,"ww"))
               .Edit
               !WEEK_STATUS = x
                Debug.Print .Fields(0).Value & .Fields(4).Value
               .MoveNext
               .Update
         Wend
    End With

End Sub

Can anyone help me out? thx in advance

 

[Norie]

For a start you need to append MyTable to MyDB tabledefs collection.

Also remove the dbOpenDynaset from the CreateTableDef method.

 

[Ragnar78]

How do i do that?

With MyTable 
     .Fields.Append .CreateField("WEEK_STATUS", dbText, 7) 
End With

Dosen't this part of the code Add a field to the table and considers it as a new added object?

 

[Norie]

MyDB.TableDefs.Append MyTable

 

[Ragnar78]

Sub Moveinto()

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim MyTable As DAO.TableDef
Dim K As Field
Dim x As String

Set MyDB = CurrentDb

Set MyTable = MyDB.TableDefs!Retreived_Data

With MyTable
     .Fields.Append .CreateField("WEEK_STATUS", dbText, 7)
End With


Set MyRS = MyDB.OpenRecordset("Retreived_Data", dbOpenDynaset)

    
    With MyRS
         .MoveFirst
         .Requery
         While Not .EOF
               x = CStr(Year(.Fields("SYSMODTIME").Value) & "w" & Format(.Fields("SYSMODTIME").Value, "ww"))
               .Edit
               !WEEK_STATUS = x
                Debug.Print .Fields(0).Value & .Fields(4).Value
               .MoveNext
         Wend
    End With

End Sub

Ok i managed to fix it with this code...
If you have any comment on it please tell me so that i might improve.
Thanks again for all the help...