ALTER TABLE syntax error

  • Thread starter Thread starter dean.brunne
  • Start date Start date
D

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
 
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];"
Click to expand...
[snip]

Your concatenation and quotes are mixed up.

"ALTER TABLE [" & strTable & "] DROP COLUMN [Planning
Customer Name];"
 
[email protected] said:
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];"
Click to expand...

[snip]

Your concatenation and quotes are mixed up.

"ALTER TABLE [" & strTable & "] DROP COLUMN [Planning
Customer Name];"
Click to expand...

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?
 
[email protected] said:
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];"
Click to expand...

[snip]

Your concatenation and quotes are mixed up.

"ALTER TABLE [" & strTable & "] DROP COLUMN [Planning
Customer Name];"
Click to expand...

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];"
Click to expand...


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
 
[email protected] said:
(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];"
Click to expand...
Click to expand...
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];"
Click to expand...

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 -
Click to expand...

Perfect. Thanks Marsh!!
 
Back
Top