Solved: Alter Table

[jmentor]

Can anyone help me achieve the following:

I know how to use ALTER TABLE when I need to add, delete
or modify a table such as
ALTER TABLE tblProducts DROP COLUMN fldProductID, but I need
to do this on multiple tables.
If someone can show me what code is required

Thank you

[Norie]

Well you could dynamically create and run the SQL.

Dim I As Long
Dim strSQL As String
For I = 1 To 10
strSQL = "ALTER TABLE tblProducts" & I & " DROP COLUMN fldProductID"
DoCmd.RunSQL strSQL
Next I


This would remove the field fldProductID from tables tblProducts1, tblProducts2...etc.

Obviously this isn't a practical example, you might want to store the table names in an array or something.

[jmentor]

Hi Norie

Thanks for that.
But what does the I stand for in as in
For I = 1 To 10
And does the 1 to 10 mean it will only deal
with 10 tables ?
Could it not be for all tables that start with the
prefix tbl as in tblProducts, tblCategories etc

Thanks for your help

[Norie]

The I is just a variable used for the loop.

Like I said this is not a practical example.

If you want to deal with every table in a database then you'll need more code.

Perhaps you could give some more explanation of what you want to do.

[jmentor]

Norie

Thanks for getting back.
There are around 20 / 30 tables that need changing
occasionaly such as adding and changing field specifications.
Using the ALTER TABLE allows me to carry out such
modifications en masse as and when required. Once I have
cracked the way you handle multiple tables prefixed with
tbl then these maintenance tasks become so much easier.
All the tables are linked, so I can use these commands
from a local station rather than on the server itself.

Hope that explains.

[xCav8r]

My guess is that explanation falls somewhat short of what Norie was looking for. It's hard to imagine that you would want to indiscriminately alter every table prefixed with tbl in your database with the same change. Perhaps you could describe in more detail what you want to accomplish (as opposed to how).

My lecturing aside, this is how you would deal with every table prefixed by tbl...


Sub AlterAllMyTables()
Dim obj As AccessObject
Dim db As Object
On Error GoTo AlterAllMyTables_Error
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Set db = Application.CurrentData
For Each obj In db.AllTables
If Left(obj.Name, 3) = "tbl" Then
DoCmd.RunSQL "ALTER TABLE " & obj.Name & " DROP COLUMN fldProductID;"
End If
NextTable:
Next obj
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
AlterAllMyTables_Exit:
On Error GoTo 0
Exit Sub
AlterAllMyTables_Error:
Select Case Err.Number
Case 3381 'no such field
Resume NextTable
Case Else
End Select
Resume AlterAllMyTables_Exit
End Sub


One final note. I noticed that you prefixed your field with fld. It's often helpful when working with tables later to prefix them using something more descriptive. ID fields, for example, are usually long integers. Thus, fldProductID would be more descriptively prefixed as lngProductID.

Here is a helpful link on prefixes in Access and VBA: http://www.xoc.net/standards/rvbanc.asp

[jmentor]

xCav8r

I would like to thank you. What you sent was exactly
what was needed.
As to the reason why and my vague reply, once in a while
all these linked tables need to be consolidated into one
table. There will also be the occasional field modification
and addition and, I have to confess, I wanted to know
anyway for my own satisfaction.

Apologies for the fld. Can't imagine why I would put that
in. Have never used such a prefix before. Thanks for the
link.

I will now mark this thread as solved