MS Access VBA trapping SQL Server Connection Error

问题:

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

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!

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

---

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

回答1:

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