Adding field to existing table

  • Thread starter Thread starter ReidarT
  • Start date Start date
R

ReidarT

I want to add a field to an existing table in a backend database.
Is it possible by DAO or by ADO
regards
reidarT
 
ReidarT said:
I want to add a field to an existing table in a backend database.
Is it possible by DAO or by ADO
Click to expand...


You can Execute an SQL DDL statement

Alter Table tblname Add Column fieldname . . .
 
Hi ReidarT,

From your description, I learnt that you'd like to add a field to an existing table in a backend database.
Based on my experience, there are two ways to to perform that:

1) Add the table column directly in the back-end database.

Create a Data Definition Query as below:
Alter Table table_Name Add column_Name Datatype

2) Add it in the front-end database:

Open the back-end database, execute the statement above using Docmd.RunSQL and finally refresh the
linked table if neccessary:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub RefreshLinks()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog

' Open the catalog.
cat.ActiveConnection = CurrentProject.Connection

Set tbl = New ADOX.Table

For Each tbl In cat.Tables
' Verify that the table is a linked table.
If tbl.Type = "LINK" tbl.Name = "table_Name" Then
tbl.Properties("Jet OLEDB:Link Datasource") = "C:\test\test.mdb"
' To refresh a linked table with a database password set the Link Provider String
' tbl.Properties("Jet OLEDB:Link Provider String") = "MS Access;PWD=Admin;"
End If
Next
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Does this answer your question? Please feel free to let me know if this help solves your problem. If there is
anything more I can do to assist you, please feel free to post it in the group.

Regards,

Billy Yao
Microsoft Online Support
 
Back
Top