Modify Linked SQL table in Acess via VBA code

[Bob Bonta]

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?

 

[pietlinden]

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 tableon
SQL.  

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

Create an ADO connection to the SQL database, log into the connection
with an account with the proper rights, and then execute a SQL
statement to modify the table. Allen Browne has a bunch of examples
on his website.
www.allenbrowne.com

 

[david]

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]

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 ~

 

[Douglas J. Steele]

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

 

[Bob Bonta]

That's what I'm trying to do. However, I'm in Enterprise Mgr (SQL) and
trying to run a T-SQL command to turn off the IDentity Seed and it's not
working.

Looking for syntax assistance.

 

[Douglas J. Steele]

You'd likely be better off asking in a SQL Server group, but I don't see
anything in my copy of Books Online.

 

[david]

Which is why I specifically told you how to replace the path and filename
string in your code, using the ODBC link information from your ODBC
linked tables.

The sample SQL I gave you was for the sample question you asked.

Over and out,

(david)