Re: Help with MS Access Error Trap
From: Mary Chipman (mchip_at_online.microsoft.com)
Date: 08/23/04
- Next message: Joe Celko: "Re: Join to one of two tables (based on a value in the source table)"
- Previous message: Adam Machanic: "Re: using select into in a stored procedure."
- In reply to: rob: "Help with MS Access Error Trap"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 23 Aug 2004 10:32:13 -0500
In future please do not spam your message to multiple newsroups, post
to the relevant newsgroup only, which in this case is
access.programming, not SQL Server. The problem you are having is with
your access code and is not a SQL Server issue per se.
--Mary
On Mon, 23 Aug 2004 09:20:11 -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
>
>
>
>
- Next message: Joe Celko: "Re: Join to one of two tables (based on a value in the source table)"
- Previous message: Adam Machanic: "Re: using select into in a stored procedure."
- In reply to: rob: "Help with MS Access Error Trap"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
