how to use SQL to create a column and then populate it with the current date.

[PuddinPie]

Hello,

Pretty much what the title says. I can use SQL to create a column in an existing table but just can't get it to populate the date and time in that new column.

Here's my code:

Function AddtblDate()
Dim tblname As String
tblname = "2006-12 Demographic Table"
strSQL = "ALTER TABLE [" & tblname & "] ADD F1 Timestamp"
DoCmd.RunSQL strSQL
End Function

Thank you.

 

[boblarson]

To populate the date/time in the new column you would need to run an UPDATE QUERY after the field has been created.

 

[PuddinPie]

Would you be able to tell me that the SQL to that would look like?

 

[boblarson]

Your add column code appears to be a bit off. If you are adding this to Access then there is no Timestamp datatype, but you would use DateTime.

Function AddtblDate()
Dim tblname As String

tblname = "2006-12 Demographic Table"

strSQL = "ALTER TABLE [" & tblname & "] [B][COLOR=red]ADD COLUMN DateTimeStamp DateTime[/COLOR][/B]"
CurrentDb.Execute strSQL, dbFailOnError

[B][COLOR=red]strSQL = "UPDATE [" & tblname & "] Set DateTimeStamp = Now[/COLOR][/B]
[B][COLOR=red]CurrentDb.Execute strSQL, dbFailOnError[/COLOR][/B]
End Function

 

[gemma-the-husky]

unless you have imported the table, and need to add the column - you ought to design the column in to the table in the first place.

 

[boblarson]

unless you have imported the table,

And that was the assumption I was going under.