Option Compare Database
Option Explicit

Public Function vcAlterTable(strSourceTable As String, sAlterSQL As String, sUseConnectionStringFrom As String)
Dim db As DAO.Database
Dim qdExtData As QueryDef
Dim strSQL As String

On Error Resume Next

Set db = CurrentDb
If ObjectExists("QUERY", "sqlALTER_TABLE") Then DoCmd.DeleteObject acQuery, "sqlALTER_TABLE"
strSQL = sAlterSQL
Set qdExtData = db.CreateQueryDef("sqlALTER_TABLE")

qdExtData.Connect = CurrentDb.TableDefs(sUseConnectionStringFrom).Connect
qdExtData.ReturnsRecords = False
qdExtData.SQL = strSQL

CurrentDb.QueryDefs("sqlALTER_TABLE").Execute

'lets refresh the link
CurrentDb.TableDefs(strSourceTable).RefreshLink

qdExtData.Close
db.Close
Set db = Nothing
MsgBox "ALTER TABLE STATEMENT COMPLETED - PLEASE CHECK THE TABLE IN DESIGN VIEW"
End Function

this function might already be present
Function ObjectExists(strObjectType As String, strObjectName As String) As Boolean
' Pass the Object type: Table, Query, Form, Report, Macro, or Module
' Pass the Object Name
     
     Dim db As Database
     Dim tbl As TableDef
     Dim qry As QueryDef
     Dim i As Integer
     
     Set db = CurrentDb()
     ObjectExists = False
     
     If strObjectType = "Table" Then
          For Each tbl In db.TableDefs
               If tbl.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next tbl
     ElseIf strObjectType = "Query" Then
          For Each qry In db.QueryDefs
               If qry.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next qry
     ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
          For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
               If db.Containers(strObjectType & "s").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     ElseIf strObjectType = "Macro" Then
          For i = 0 To db.Containers("Scripts").Documents.Count - 1
               If db.Containers("Scripts").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     Else
          MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
     End If
     
End Function

