Access Export Table Fire DDL Trigger in SQL Server

Question

question

chase88 asked May 19, '13

Access Export Table Fire DDL Trigger in SQL Server

With Access v2010 open, a user exports a table named `BC_MASTER_NEW` to our SQL Server 2008 R2 via ODBC. I need SQL Server to rename the current `BC_MASTER` SQL Server table with `_OLD` (`BC_MASTER --> BC_MASTER_OLD`), and then rename the new imported table from Access with the standard name (`BC_MASTER_NEW --> BC_MASTER`). To automate this, I created a DDL trigger with the `CREATE_TABLE` event which fires when the table from Access is made in the schema and the first rename (`BC_MASTER --> BC_MASTER_OLD`) works. But the second rename (`BC_MASTER_NEW --> BC_MASTER`) gives an error in Access saying Could not execute query; could not find linked table. [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'BC_MASTER_NEW', (#208) [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (#8180). How can I get SQL Server to rename that new imported table from Access? Is it because the import from Access hasn't closed yet when the trigger fires and it can't use that new table name? Here's the trigger: CREATE TRIGGER BC_1 ON DATABASE FOR CREATE_TABLE AS IF EXISTS(SELECT sobjects.name FROM sysobjects sobjects WHERE sobjects.xtype = 'U' and name='BC_MASTER_NEW') BEGIN EXEC sp_rename 'BC_MASTER', 'BC_MASTER_OLD'; EXEC sp_rename 'BC_MASTER_NEW', 'BC_MASTER'; END thanks

sql-server-2008-r2 trigger access odbc ddl

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2013-05-19T19:23:02Z

ThomasRushton answered May 19, '13

> Is it because the import from Access > hasn't closed yet when the trigger > fires and it can't use that new table > name It sounds as though that's the problem, yes. [Race Conditions][1], y'see. Can you rewrite your Access script to create the table `BC_MASTER_NEW` and populate it, and then get the Access script to call the `sp_rename`s, that might be a solution. [1]: http://en.wikipedia.org/wiki/Race_condition

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

chase88 commented · May 24, 2013 at 01:08 AM

@Thomas: thanks for your help, it pointed me in the right direction. I ended up not using the trigger approach. Instead I created an ODBC SQL Pass-Through Query in Access. So after the user exports the new table, they click the saved query which runs a stored proc on sql server to rename the tables. No problems or errors.

0 ·

question