ALTER TABLE syntax error

[dean.brunne]

The code below returns a syntax error in my Alter Table Statement. My
goal is to loop through and delete the same column from all the tables
that do not end in "_F" in a different database.

Appreciate your help. Dean

Function DeleteFields()

Dim rs As DAO.Recordset
Dim rsIn As DAO.Recordset
Dim fld As DAO.Field
Dim varIndex As Variant
Dim DB1 As Database
Dim strSQL As String
Dim strTable As String

Set DB1 = DBEngine.OpenDatabase("C:\Documents and Settings\dbrunne\My
Documents\26 Hawkeye\Regional Trade Baselines
\Fpro_Link_Regional_Non_Promoted.mdb")
strSQL = ("SELECT MSysObjects.Name FROM MSysObjects WHERE " & _
"(MSysObjects.Type=1) AND" & _
"(Left$([Name],1)<>'~')AND" & _
"(Left$([Name],4)<>'Msys')AND" & _
"(Right$([Name],2)<>'_F')" & _
" ORDER BY MSysObjects.Name")
Set rs = DB1.OpenRecordset(strSQL)


Do Until rs.EOF
strTable = rs!Name

DoCmd.RunSQL "ALTER TABLE & 'strTable' & DROP COLUMN [Planning
Customer Name];"

rs.MoveNext

Loop


End Function

 

[Marshall Barton]

The code below returns a syntax error in my Alter Table Statement. My
goal is to loop through and delete the same column from all the tables
that do not end in "_F" in a different database. [snip]
DoCmd.RunSQL "ALTER TABLE & 'strTable' & DROP COLUMN [Planning
Customer Name];"

[snip]

Your concatenation and quotes are mixed up.

"ALTER TABLE [" & strTable & "] DROP COLUMN [Planning
Customer Name];"

 

[dean.brunne]

The code below returns a syntax error in my Alter Table Statement. My
goal is to loop through and delete the same column from all the tables
that do not end in "_F" in a different database. [snip]
DoCmd.RunSQL "ALTER TABLE & 'strTable' & DROP COLUMN [Planning
Customer Name];"

[snip]

Your concatenation and quotes are mixed up.

"ALTER TABLE [" & strTable & "] DROP COLUMN [Planning
Customer Name];"

Hi Marsh-Thanks for the help. I made an error in my structure as the
table in the ALTER TABLE statement is located in a different database
than the one the code runs in. The table is in the database DB1. I
tried changing the SQL statement to
"ALTER TABLE [" & strTable & "] IN 'c:\My Documents\otherdatabase.mdb'
DROP COLUMN [Planning Customer Name];"

but this did not work. Any suggestions?

 

[Marshall Barton]

The code below returns a syntax error in my Alter Table Statement. My
goal is to loop through and delete the same column from all the tables
that do not end in "_F" in a different database. [snip]
DoCmd.RunSQL "ALTER TABLE & 'strTable' & DROP COLUMN [Planning
Customer Name];"

[snip]

Your concatenation and quotes are mixed up.

"ALTER TABLE [" & strTable & "] DROP COLUMN [Planning
Customer Name];"

Hi Marsh-Thanks for the help. I made an error in my structure as the
table in the ALTER TABLE statement is located in a different database
than the one the code runs in. The table is in the database DB1. I
tried changing the SQL statement to
"ALTER TABLE [" & strTable & "] IN 'c:\My Documents\otherdatabase.mdb'
DROP COLUMN [Planning Customer Name];"

Did not work ???

AFAIK, the DDL statements do not allow for the IN option.

Try something more like:

Dim dbBE As Database
Set dbBE = OpenDatabase("c:\My Documents\otherdatabase.mdb")
dbBe.Execute "ALTER TABLE [" & strTable _
& "] DROP COLUMN [Planning Customer Name];"
dbBE.Close : Set dbBE = Nothing

 

[dean.brunne]

(e-mail address removed) wrote:
The code below returns a syntax error in my Alter Table Statement. My
goal is to loop through and delete the same column from all the tables
that do not end in "_F" in a different database.
[snip]
DoCmd.RunSQL "ALTER TABLE & 'strTable' & DROP COLUMN [Planning
Customer Name];"
[snip]
Your concatenation and quotes are mixed up.
"ALTER TABLE [" & strTable & "] DROP COLUMN [Planning
Customer Name];"

Hi Marsh-Thanks for the help. I made an error in my structure as the
table in the ALTER TABLE statement is located in a different database
than the one the code runs in. The table is in the database DB1. I
tried changing the SQL statement to
"ALTER TABLE [" & strTable & "] IN 'c:\My Documents\otherdatabase.mdb'
DROP COLUMN [Planning Customer Name];"

Did not work ???

AFAIK, the DDL statements do not allow for the IN option.

Try something more like:

Dim dbBE As Database
Set dbBE = OpenDatabase("c:\My Documents\otherdatabase.mdb")
dbBe.Execute "ALTER TABLE [" & strTable _
& "] DROP COLUMN [Planning Customer Name];"
dbBE.Close : Set dbBE = Nothing

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Perfect. Thanks Marsh!!