本文介绍了MS Access VBA捕获SQL Server连接错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在获取Access(2010)VBA来捕获与链接表的SQL Server(2008)的连接时遇到的错误.

I'm Having problems getting Access (2010) VBA to trap errors for connections to a SQL Server (2008) for linking tables.

大概是从ODBC驱动程序中得到了一个错误和弹出窗口?我想抑制这些错误并自己处理错误.我知道DAO.errors和ADO.errors集合,但是如果我无法获取错误来调用我的错误处理程序,这些集合将无济于事!

I'm getting an error and popup windows, presumably from the ODBC Driver? I want to suppress these and handle the error myself. I know about the DAO.errors and ADO.errors collections but these don't help if I can't get the error to call my error handler!

下面的代码将给出错误(除非您碰巧在名为myServer的服务器上的名为myDatabase的数据库中有一个名为myTable的表).我尝试使用ADODB而不是DAO,但是根本无法使用它.有什么想法吗?

The code below will give the error (unless you happen to have a table called myTable in a database called myDatabase on a server called myServer).I've tried to use ADODB rather than DAO but could not get this to work at all.Any ideas?

Public Function main()
    Dim myDB As DAO.Database
    Dim myTabledef As DAO.TableDef

    On Error GoTo Err_handler

    Set myDB = CurrentDb
    Set myTabledef = myDB.CreateTableDef("l_table")

    DoCmd.SetWarnings False

    myTabledef.Connect = "odbc;driver=SqLServer;" & _
        "DATABASE=myDB;SERVER=myServer;Trusted_Connection=Yes;"

    myTabledef.SourceTableName = "MyTable"

    myDB.TableDefs.Append myTabledef

    DoCmd.SetWarnings True

    Exit Function

Err_handler:
    MsgBox Err.Number & " - " & Err.Description

End Function


我在发布的代码中犯了一个错误,{Sql Server}在发布时变成了SqLServer.因此,给出错误的完整代码如下:


I made a mistake in the posted code {Sql Server} became SqLServer when I posted it.So the full code that gives the error is below:

Public Function main()
Dim myDB As DAO.Database
Dim myTabledef As DAO.TableDef

On Error GoTo Err_handler

Set myDB = CurrentDb
Set myTabledef = myDB.CreateTableDef("l_table")

DoCmd.SetWarnings False

myTabledef.Connect = "odbc;driver={Sql Server};" & _
    "DATABASE=myDB;SERVER=myServer;Trusted_Connection=Yes;"

myTabledef.SourceTableName = "MyTable"

myDB.TableDefs.Append myTabledef

DoCmd.SetWarnings True

Exit Function

Err_handler:
MsgBox Err.Number & " - " & Err.Description

End Function

推荐答案

在尝试附加TableDef之前,不会发生该错误

The error will not occur until you try to append the TableDef

Dim myDB As DAO.Database
Dim myTabledef As DAO.TableDef

On Error GoTo Err_handler

Set myDB = CurrentDb
scn = "odbc;driver=SqLServer;" & _
"DATABASE=myDB;SERVER=myServer;Trusted_Connection=Yes;"
Set myTabledef = myDB.CreateTableDef("l_table")

myTabledef.Connect = scn
myTabledef.SourceTableName = "Table1"
myDB.TableDefs.Append myTabledef

Err_handler:
Debug.Print Err.Number & " " & Err.Description

这篇关于MS Access VBA捕获SQL Server连接错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-23 09:05
查看更多