SEO

Please excuse if you feel this is posted in wrong group (I have posted in
Access groups as well)... I do know there is at least some some cross-over
base here that may help...

I am trying to establish links from Ms Access to tables in a SQL server
database. There is an Access table (tblLinkMaster) that contain all the
required linkage information. If
the database is attached and the network connection is good, the code below
works fine and all is
well with the world.

However, when I detach the database and test to see what the user would see,
I get... Connection failed: SQLSTATE:'08004' SQL Server Error: 4060 Server
rejected the
connection; Access to selected databse has been denied. If I then click OK
and cancel, the program errors
out in a messy manner.

(Program stops HERE
' Then get new link info
db1.Execute ("qapptblLinkTable"))

If the database is not available, I would prefer that the whole procedure
stop and exit gracefully.

Any Ideas on how this can be error trapped nicely ?

Thanks !


Function GetMasterLinkData()
' Based on Data in Local table (entered via form) - tblLinkMaster /
qrytblLinkMaster
Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset
Set db1 = CurrentDb()
Set rs1 = db1.OpenRecordset("qrytblLinkMaster")

Dim strLinkName As String
Dim strDBName As String
Dim strTableName As String
Dim strDSNname As String
Dim strServerName As String

rs1.MoveFirst
If IsNull(rs1!LinkName) Or rs1!LinkName = "" Or _
IsNull(rs1!DatabaseName) Or rs1!DatabaseName = "" Or _
IsNull(rs1!TableName) Or rs1!TableName = "" Or _
IsNull(rs1!ServerName) Or rs1!ServerName = "" Then
MsgBox ("The Initial Set-up Infromation Is Incomplete - Please contact
an Administrator")
Exit Function
Else
strLinkName = rs1!LinkName
strDBName = rs1!DatabaseName
strTableName = rs1!TableName
strDSNname = ""
strServerName = rs1!ServerName
Call LinkTableDAO(strLinkName, strDBName, strTableName, strDSNname,
strServerName)

End If

' Now remove previous link info
db1.Execute ("DELETE from tblLinkTable")

' Then get new link info
db1.Execute ("qapptblLinkTable")

rs1.Close
Set rs1 = Nothing
db1.Close
Set db1 = Nothing

End Function


Public Function LinkTableDAO(strLinkName As String, strDBName As String,
strTableName As String,

strDSNname As String, strServerName As String)

Dim db As DAO.Database
Dim tdf As DAO.TableDef

On Error Resume Next
Set db = CurrentDb
' if link pre-exists, then delete it
Set tdf = db.TableDefs(strLinkName)
If Err.Number = 0 Then
' Found an existing tabledef
db.TableDefs.Delete strLinkName
db.TableDefs.Refresh
Else
' No existing tabledef
' Ignore error and reset
Err.Clear
End If

' Create a new TableDef object
Set tdf = db.CreateTableDef(strLinkName)
' set connect and source table table name prperties to establish link
tdf.Connect = "ODBC;Driver={SQL Server};Server=" & strServerName &
";Database=" & strDBName &

";Trusted_Connection=Yes"
tdf.SourceTableName = strTableName

' Append to the database's TableDefs collection
' IF SQL SERVER DB NOT ATTACHED - THEN ERROR OCCURS HERE
db.TableDefs.Append tdf

db.Close

End Function

You need to implement "On Error Goto..." error handling in your code.
See the VBA help files for more information, or post on the
Access.programming newsgroups.

--Mary

On Mon, 23 Aug 2004 10:04:54 -0400, "rob" <rwc1960@bellsouth.net>
wrote:

>Please excuse if you feel this is posted in wrong group (I have posted in
>Access groups as well)... I do know there is at least some some cross-over
>base here that may help...
>
>I am trying to establish links from Ms Access to tables in a SQL server
>database. There is an Access table (tblLinkMaster) that contain all the
>required linkage information. If
>the database is attached and the network connection is good, the code below
>works fine and all is
>well with the world.
>
>However, when I detach the database and test to see what the user would see,
>I get... Connection failed: SQLSTATE:'08004' SQL Server Error: 4060 Server
>rejected the
>connection; Access to selected databse has been denied. If I then click OK
>and cancel, the program errors
>out in a messy manner.
>
>(Program stops HERE
>' Then get new link info
> db1.Execute ("qapptblLinkTable"))
>
>If the database is not available, I would prefer that the whole procedure
>stop and exit gracefully.
>
>Any Ideas on how this can be error trapped nicely ?
>
>Thanks !
>
>
>Function GetMasterLinkData()
>' Based on Data in Local table (entered via form) - tblLinkMaster /
>qrytblLinkMaster
> Dim db1 As DAO.Database
> Dim rs1 As DAO.Recordset
> Set db1 = CurrentDb()
> Set rs1 = db1.OpenRecordset("qrytblLinkMaster")
>
> Dim strLinkName As String
> Dim strDBName As String
> Dim strTableName As String
> Dim strDSNname As String
> Dim strServerName As String
>
> rs1.MoveFirst
> If IsNull(rs1!LinkName) Or rs1!LinkName = "" Or _
> IsNull(rs1!DatabaseName) Or rs1!DatabaseName = "" Or _
> IsNull(rs1!TableName) Or rs1!TableName = "" Or _
> IsNull(rs1!ServerName) Or rs1!ServerName = "" Then
> MsgBox ("The Initial Set-up Infromation Is Incomplete - Please contact
>an Administrator")
> Exit Function
> Else
> strLinkName = rs1!LinkName
> strDBName = rs1!DatabaseName
> strTableName = rs1!TableName
> strDSNname = ""
> strServerName = rs1!ServerName
> Call LinkTableDAO(strLinkName, strDBName, strTableName, strDSNname,
>strServerName)
>
> End If
>
> ' Now remove previous link info
> db1.Execute ("DELETE from tblLinkTable")
>
> ' Then get new link info
> db1.Execute ("qapptblLinkTable")
>
> rs1.Close
> Set rs1 = Nothing
> db1.Close
> Set db1 = Nothing
>
>End Function
>
>
>Public Function LinkTableDAO(strLinkName As String, strDBName As String,
>strTableName As String,
>
>strDSNname As String, strServerName As String)
>
> Dim db As DAO.Database
> Dim tdf As DAO.TableDef
>
> On Error Resume Next
> Set db = CurrentDb
> ' if link pre-exists, then delete it
> Set tdf = db.TableDefs(strLinkName)
> If Err.Number = 0 Then
> ' Found an existing tabledef
> db.TableDefs.Delete strLinkName
> db.TableDefs.Refresh
> Else
> ' No existing tabledef
> ' Ignore error and reset
> Err.Clear
> End If
>
> ' Create a new TableDef object
> Set tdf = db.CreateTableDef(strLinkName)
> ' set connect and source table table name prperties to establish link
> tdf.Connect = "ODBC;Driver={SQL Server};Server=" & strServerName &
>";Database=" & strDBName &
>
>";Trusted_Connection=Yes"
> tdf.SourceTableName = strTableName
>
> ' Append to the database's TableDefs collection
> ' IF SQL SERVER DB NOT ATTACHED - THEN ERROR OCCURS HERE
> db.TableDefs.Append tdf
>
>db.Close
>
>End Function
>
>
>
>
>

I understand I have misapplied pieces of your code (page 189) in some
manner... and must still be doing that with the error trap I attempted to
add....

When I tried the code below within "LinkTableDAO", even when the database
is attached I get an error resulting in a Quit... if I take out the error
trap and the database is attached, it all works just fine.

In addition, I did post to 2 Access newsgroups with no response, then after
looking into BOL regarding SQLSTATE errors and ODBC connections, I did feel
the topic was at least somewhat relevant to 2 sql newsgroups (programming
with SQL and ODBC), my intention is certainly not to spam these fine
newsgroups.

' Append to the database's TableDefs collection
'IF SQL SERVER DB NOT ATTACHED - THEN ERROR OCCURS HERE
On Error GoTo ErrHandle
db.TableDefs.Append tdf

db.Close

ErrHandle:
Quit

Best Regards


"Mary Chipman" <mchip@online.microsoft.com> wrote in message
news:ua3ki09utajdj3km4fhm62np3ijrek91jh@4ax.com...
> You need to implement "On Error Goto..." error handling in your code.
> See the VBA help files for more information, or post on the
> Access.programming newsgroups.
>
> --Mary
>
> On Mon, 23 Aug 2004 10:04:54 -0400, "rob" <rwc1960@bellsouth.net>
> wrote:
>
> >Please excuse if you feel this is posted in wrong group (I have posted in
> >Access groups as well)... I do know there is at least some some
cross-over
> >base here that may help...
> >
> >I am trying to establish links from Ms Access to tables in a SQL server
> >database. There is an Access table (tblLinkMaster) that contain all the
> >required linkage information. If
> >the database is attached and the network connection is good, the code
below
> >works fine and all is
> >well with the world.
> >
> >However, when I detach the database and test to see what the user would
see,
> >I get... Connection failed: SQLSTATE:'08004' SQL Server Error: 4060
Server
> >rejected the
> >connection; Access to selected databse has been denied. If I then click
OK
> >and cancel, the program errors
> >out in a messy manner.
> >
> >(Program stops HERE
> >' Then get new link info
> > db1.Execute ("qapptblLinkTable"))
> >
> >If the database is not available, I would prefer that the whole procedure
> >stop and exit gracefully.
> >
> >Any Ideas on how this can be error trapped nicely ?
> >
> >Thanks !
> >
> >
> >Function GetMasterLinkData()
> >' Based on Data in Local table (entered via form) - tblLinkMaster /
> >qrytblLinkMaster
> > Dim db1 As DAO.Database
> > Dim rs1 As DAO.Recordset
> > Set db1 = CurrentDb()
> > Set rs1 = db1.OpenRecordset("qrytblLinkMaster")
> >
> > Dim strLinkName As String
> > Dim strDBName As String
> > Dim strTableName As String
> > Dim strDSNname As String
> > Dim strServerName As String
> >
> > rs1.MoveFirst
> > If IsNull(rs1!LinkName) Or rs1!LinkName = "" Or _
> > IsNull(rs1!DatabaseName) Or rs1!DatabaseName = "" Or _
> > IsNull(rs1!TableName) Or rs1!TableName = "" Or _
> > IsNull(rs1!ServerName) Or rs1!ServerName = "" Then
> > MsgBox ("The Initial Set-up Infromation Is Incomplete - Please
contact
> >an Administrator")
> > Exit Function
> > Else
> > strLinkName = rs1!LinkName
> > strDBName = rs1!DatabaseName
> > strTableName = rs1!TableName
> > strDSNname = ""
> > strServerName = rs1!ServerName
> > Call LinkTableDAO(strLinkName, strDBName, strTableName, strDSNname,
> >strServerName)
> >
> > End If
> >
> > ' Now remove previous link info
> > db1.Execute ("DELETE from tblLinkTable")
> >
> > ' Then get new link info
> > db1.Execute ("qapptblLinkTable")
> >
> > rs1.Close
> > Set rs1 = Nothing
> > db1.Close
> > Set db1 = Nothing
> >
> >End Function
> >
> >
> >Public Function LinkTableDAO(strLinkName As String, strDBName As String,
> >strTableName As String,
> >
> >strDSNname As String, strServerName As String)
> >
> > Dim db As DAO.Database
> > Dim tdf As DAO.TableDef
> >
> > On Error Resume Next
> > Set db = CurrentDb
> > ' if link pre-exists, then delete it
> > Set tdf = db.TableDefs(strLinkName)
> > If Err.Number = 0 Then
> > ' Found an existing tabledef
> > db.TableDefs.Delete strLinkName
> > db.TableDefs.Refresh
> > Else
> > ' No existing tabledef
> > ' Ignore error and reset
> > Err.Clear
> > End If
> >
> > ' Create a new TableDef object
> > Set tdf = db.CreateTableDef(strLinkName)
> > ' set connect and source table table name prperties to establish link
> > tdf.Connect = "ODBC;Driver={SQL Server};Server=" & strServerName &
> >";Database=" & strDBName &
> >
> >";Trusted_Connection=Yes"
> > tdf.SourceTableName = strTableName
> >
> > ' Append to the database's TableDefs collection
> > ' IF SQL SERVER DB NOT ATTACHED - THEN ERROR OCCURS HERE
> > db.TableDefs.Append tdf
> >
> >db.Close
> >
> >End Function
> >
> >
> >
> >
> >
>