Alter Table - VB Approach

[Whelan]

Hey Guys,

I have an SQL Alter Table statment which removes two columns from a table.

However, the name of the table changes daily (the date is added onto the
end). Is there anyway of incorporating a Variable for the name within the SQL
Code?

I can't seem to find away of achieving this so far. Here's the code which
obviously doesn't work at the moment, due to the variable being used as the
table name.

Function AmendTodaysNameChanges()

Dim Table As String

TableNAME = "Name Changes Report" & Format(Date - 1, "dd-mm-yyyy")

Dim strSQLRemoveColumns As String
DoCmd.SetWarnings False
strSQLRemoveColumns = "Alter table [tablename] Drop Column
id,adt,dateid;"
DoCmd.RunSQL strSQLRemoveColumns
DoCmd.SetWarnings True

End Function

 

[BeWyched]

Change:

strSQLRemoveColumns = "Alter table [tablename] Drop Column
id,adt,dateid;"

to:

strSQLRemoveColumns = "Alter table " & TableNAME & " Drop Column
id,adt,dateid;"

Good luck.

BW

 

[BeWyched]

Sorry, I forget the []'s ! - use:

strSQLRemoveColumns = "Alter table [" & TableNAME & "] Drop Column
id,adt,dateid;"

Change:

strSQLRemoveColumns = "Alter table [tablename] Drop Column
id,adt,dateid;"

to:

strSQLRemoveColumns = "Alter table " & TableNAME & " Drop Column
id,adt,dateid;"

Good luck.

BW

Hey Guys,

I have an SQL Alter Table statment which removes two columns from a table.

However, the name of the table changes daily (the date is added onto the
end). Is there anyway of incorporating a Variable for the name within the SQL
Code?

I can't seem to find away of achieving this so far. Here's the code which
obviously doesn't work at the moment, due to the variable being used as the
table name.

Function AmendTodaysNameChanges()

Dim Table As String

TableNAME = "Name Changes Report" & Format(Date - 1, "dd-mm-yyyy")

Dim strSQLRemoveColumns As String
DoCmd.SetWarnings False
strSQLRemoveColumns = "Alter table [tablename] Drop Column
id,adt,dateid;"
DoCmd.RunSQL strSQLRemoveColumns
DoCmd.SetWarnings True

End Function

 

[Whelan]

BeWyched, that is ruddy brilliant.

Thank you very much!!

Tom

Sorry, I forget the []'s ! - use:

strSQLRemoveColumns = "Alter table [" & TableNAME & "] Drop Column
id,adt,dateid;"

Change:

strSQLRemoveColumns = "Alter table [tablename] Drop Column
id,adt,dateid;"

to:

strSQLRemoveColumns = "Alter table " & TableNAME & " Drop Column
id,adt,dateid;"

Good luck.

BW

Hey Guys,

I have an SQL Alter Table statment which removes two columns from a table.

However, the name of the table changes daily (the date is added onto the
end). Is there anyway of incorporating a Variable for the name within the SQL
Code?

I can't seem to find away of achieving this so far. Here's the code which
obviously doesn't work at the moment, due to the variable being used as the
table name.

Function AmendTodaysNameChanges()

Dim Table As String

TableNAME = "Name Changes Report" & Format(Date - 1, "dd-mm-yyyy")

Dim strSQLRemoveColumns As String
DoCmd.SetWarnings False
strSQLRemoveColumns = "Alter table [tablename] Drop Column
id,adt,dateid;"
DoCmd.RunSQL strSQLRemoveColumns
DoCmd.SetWarnings True

End Function