How to modify a field in a linked table

[nickperez22]

I need to modify a field in a linked table to make it bigger, I know how to add a field, but am unsure of the syntax for modifying one. Please help.

 

[Golom]

I don't believe that you can because you don't have access to the table-defining elements of a linked table ... or at least only on a read-only basis. To modify a table's definition you would need to open a new instance of access on the other database and then run an ALTER TABLE tbl ALTER COLUMN ... within that instance. Note that "ALTER COLUMN" is available only in Access 2K or later.

Alternatively, you could use DAO or ADO to open the database and then use the TableDef and Field (DAO) or Catalog and Field (ADO/ADOX) objects to make your changes.

 

[r937]

golom, a linked table can also be via odbc and can therefore be any other database type, like oracle, sql server, mysql, etc.

nick, see if you can use a pass-through query to run the appropriate ALTER statement in the other database

rudy

http://r937.com/

SQL Consulting

 

[nickperez22]

I found a really easy solution in SQL, In this case I was changing a text field of size 6 upto a size 10

Dim db As Database
Dim qdf As QueryDef
Dim sSQL As String
Set db = OpenDatabase("path\database.mdb")

'Creates a dummy query
Set qdf = db.CreateQueryDef("", "Select * from Titles")

qdf.SQL = "ALTER TABLE Titles ALTER COLUMN ID Text(10)"
qdf.Execute