ALTERing a table in an External Database file 1

[VickyC]

Not sure how to do this: Can I add a column to a table that is in an EXTERNAL mdb file?
So, how do I use code in TEST_A.mdb like "ALTER TABLE tbl_Data ADD COLUMN NewCol Integer" when tbl_Data is in TEST_B.mdb?

Thanks for any assistance

 

[dhookom]

I typically do this with DAO code. I create a DAO.Database object pointing to the remote database and then append the field to the tabledef.

Duane
Hook'D on Access
MS Access MVP

 

[VickyC]

Thanks Duane

Your DAO suggestion worked really well with this code:

Function Append_Field_EXT_db__DAO(strDataBasePathName As String, strTblName As String, strFldName As String)
    
    Dim db As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
    
    Set db = DBEngine.Workspaces(0).OpenDatabase(strDataBasePathName)
    Set tdf = db.TableDefs(strTblName)
    
    tdf.Fields.Append tdf.CreateField(strFldName, dbInteger)

    Set fld = Nothing: Set tdf = Nothing: Set db = Nothing
End Function

For what it's worth, here is the SQL code that I couldn't get to work. (Syntax error in ALTER statement)
I suspect the problem has to do with the 'IN', but it seems to be consistent with code I've seen on the web.
Any thoughts?

Function Append_Field_EXT_db__DDL(strDataBasePathName As String, strTblName As String, strFldName As String)
    
    Dim db As DAO.Database, strSQL As String
    
    strSQL = "ALTER TABLE " & strTblName & " IN '" & strDataBasePathName & "' ADD COLUMN " & strFldName & " INTEGER;"
    
    Set db = CurrentDb()
    db.Execute strSQL, dbFailOnError
    
    Set db = Nothing[s][/s]
End Function

Thanks in advance
Vicky

 

[PeterWallace]

Try This

Sql = "Alter Table Recon Add Column Reconlevel Long"
AddThisColumn Sql

Sub AddThisColumn(Sql As String)
Dim rs As ADODB.Recordset
Dim ConectString As String
ConectString = "Provider=MSDataShape;Data Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & TheDatabase
Set rs = New ADODB.Recordset
On Error GoTo buggerit
rs.Open Sql, ConectString, adOpenStatic, adLockReadOnly

CarryOn:
Set rs.ActiveConnection = Nothing
Set rs = Nothing
Exit Sub
buggerit:
'Debug.Print Err.Description
GoTo CarryOn
End Sub
Peter Wallace