December 18, 2003 at 8:44 am
I created an insert stored procedure which it opens up an MS-Access database first, and then insert a record to it. What I would like to do is to create an Error handler which will carry on the action (resume) when the connection of the MS-Access can't be made from the SQL. Basically, if there is no connection between them, I still would like the process to be carried on. Thanks.
December 18, 2003 at 8:49 am
If you can capture the @@ERROR value immediately after the step you wnat to trap
you can test that and then use a "goto" label to jump where you want to.
Be sure to assign the @@ERROR to a variable because it's value will change
with every activity in your SQL Server connection.
hth - jh
jh
Skål - jh
December 18, 2003 at 6:53 pm
I don't believe you will be able to trap the error in TSQL when the file can't be opened. Personally I feel that this should be done the other way around. The Access database should pull the data from SQL Server. The main reason for this is that Access databases are portable and SQL Databases aren't (at least not usually!). Since that is the case you will often no know where the Access database is. So what I would do is to have a table in your SQL Server DB that you put this data in and then have Access attach to it using ODBC. Then you can have a query in the Access mdb that grabs the data from the attached table and puts in the local database. This may be a little harder to automate but it can be done and is much less error prone.
Another way to do this would be to set up snapshot replication such that your SQL DB is a publisher and Access is the subscriber for this one table.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply