question

chase88 avatar image
chase88 asked

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-r2triggeraccessodbcddl
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered
> 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 comment
10 |1200 characters needed characters left characters exceeded

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

@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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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