How to create table in Ms Access (TSQL)

abdusalam · July 21st, 2004, 11:58 PM

Hi,

How to create table in Ms Access 2k from SQL server through
T-SQL ?

Thanks,

Stay Beautiful,
Abdul Salam

dhay1999 · July 28th, 2004, 08:29 AM

Set up a linked server to the Access DB. You can then execute SQL statements against it. See the following link.

http://www.databasejournal.com/featu...le.php/3085211

abdusalam · July 30th, 2004, 02:06 AM

Hi,

Quote:

quote:
Set up a linked server to the Access DB. You can then execute SQL statements against it. See the following link.

http://www.conyc.com/galleryg/out.php

Yes, It's nice ..but how i can update or write trigger on tables (linked server tabls)... Is there any solution ?

Thanks,

Stay Beautiful,
Abdul Salam

dhay1999 · July 30th, 2004, 10:02 AM

you cannot write a trigger against a "linked" table. You would have to write the trigger in the souce database engine.

to do an update to a linked server it would be like this...
the "OPENQUERY(LinkedSever, 'SELECT Field1 FROM Table1') " basically replaces the table name you would use in a regualr SQL statement.

UPDATE OPENQUERY(LinkedSever, 'SELECT Field1 FROM Table1')
SET Field1 = Value1
WHERE field2= x

Is there a reason you don't convert the data directly into SQL? The you can write as many trigger etc as you wish. Cannot the application be changed to point to the newer database?

abdusalam · July 31st, 2004, 01:54 AM

Hi & Thanks Dhay1999 ...

Using ROWSET functions are good but it mean creating such whole db..

Stay Beautiful,
Abdul Salam