Re: Modify Linked SQL table in Acess via VBA code

Set db = OpenDatabase("Path & Filename")
db.Execute "ALTER " & strTableName & " ADD " & _
strNewFieldName & " TEXT(" & _
intFieldSize & ");"

You can get the "Path & Filename" from one of your linked tables:
strDB = db.TableDefs("mylinkedTable").connection

(david)


"Bob Bonta" <BobBonta@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BD630579-1062-4D1C-8765-DF566B873DDB@xxxxxxxxxxxxxxxx
I have a SQL backend database which is accessed through an Access front-end
with tables linked with a DSN-less connection.

I need to modify one of the linked tables via code (I don't have direct
access to the SQL database via Enterprise Mgr, etc). With an Access BE
database, I would enter the following:

Set db = OpenDatabase("Path & Filename")
Set tdf = db.TableDefs(strTableName)
tdf.Fields.Append tdf.CreateField(strNewFieldName, dbText,
intFieldSize)

However, since I can't modify a linked table to a SQL database with the
above method, I am looking for a similiar snippet which will allow me to
directly modify the SQL table, then I could refresh the link to update the
view to the table.

I'm speculating that I can directly access the SQL table utilizing the
same
connection string definition as when I link the table to modify the table
on
SQL.

Is this possible? What would be the syntax for that operation?

--
~ Bob Bonta ~


.

Relevant Pages

  • Creating a custom field in Project Server
    ... integer associated to a "bug" (for use with a bug-tracking application). ... this field added to one of the MS Project tables in the actual SQL Database? ... If you don't modify those tables, ...
    (microsoft.public.project)
  • Re: Creating a custom field in Project Server
    ... the number fields in Project are present in the SQL database. ... Modify or develop a table to accept the data. ... For project information and macro examples visit ...
    (microsoft.public.project)
  • Re: Modify Linked SQL table in Acess via VBA code
    ... Destination is into the linked SQL table via ODBC ... I need to modify one of the linked tables via code (I don't have direct ... since I can't modify a linked table to a SQL database with the ... connection string definition as when I link the table to modify the ...
    (microsoft.public.access.modulesdaovba)
  • Re: Modify Linked SQL table in Acess via VBA code
    ... Thanks David ... ... Destination is into the linked SQL table via ODBC ... I need to modify one of the linked tables via code (I don't have direct ... since I can't modify a linked table to a SQL database with the ...
    (microsoft.public.access.modulesdaovba)
  • Re: C# and MSSQL - how to connect?
    ... i need to connect in my application to sql database ... and modify few rows. ... Navigate to http://www.google.com ... Hit the button ...
    (microsoft.public.dotnet.languages.csharp)