Re: Modify Linked SQL table in Acess via VBA code

You can run T-SQL commands as pass-through queries in Access.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Bob Bonta" <BobBonta@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:84F3EB41-8839-43DA-9A12-9BE47AEB89B3@xxxxxxxxxxxxxxxx
Thanks David ... however, I specifically mentioned linked SQL tables - no
path & filename to reference.

For all listening in ... I specifically need to turn off the Identity
Seed,
purge/append data into the table, then turn the Identity Seed back on.

Source data is coming from a linked Access table
Destination is into the linked SQL table via ODBC

tia ,

~ Bob Bonta ~


"david" wrote:

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

  • 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 ... access to the SQL database via Enterprise Mgr, ... since I can't modify a linked table to a SQL database with the ...
    (microsoft.public.access.modulesdaovba)
  • Re: Data entry, deletion and modification problems.
    ... the mis-match of provided column and the inserted field. ... Hope you may help to modify it for me, ... con.Execute sql, Recordsaffected ...
    (microsoft.public.access.formscoding)
  • Re: Modify Linked SQL table in Acess via VBA code
    ... string in your code, using the ODBC link information from your ODBC ... The sample SQL I gave you was for the sample question you asked. ... I need to modify one of the linked tables via code (I don't have direct ...
    (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: Modify Linked SQL table in Acess via VBA code
    ... with tables linked with a DSN-less connection. ... 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)