Help with MS Access Error Trap

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 FunctionYou 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 O
K
>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:
>
cross-over[vbcol=seagreen]
below[vbcol=seagreen]
see,[vbcol=seagreen]
Server[vbcol=seagreen]
OK[vbcol=seagreen]
contact[vbcol=seagreen]
>

Help with MS Access Error Trap

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... [vbcol=seagreen]
> 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:
cross-over[vbcol=seagreen]
below[vbcol=seagreen]
see,[vbcol=seagreen]
Server[vbcol=seagreen]
OK[vbcol=seagreen]
contact
>

help with interview questions

Team;
I need to come up with a better list of interviewing questions for SSRS
candidates.. feel free to let me know what I should add, remove or
change.
Thanks!
PS - if anyone wants to develop SSRS reports in Redmond; drop me an
email aaron_kempf@.hotmail.com or even add me to messenger
a) what is an RDL file
- an XML representation of the Reports layout; this is typically
'deployed' to live inside of the ReportServer database.
b) what are some of the competitive advantages to using SSRS over
crystal?
- price / cost
- integrated development environment (IDE)
- easy to deploy
c) what is the difference between the 'ReportServer' websites and the
'ReportManager' website.
- reportServer Provides services for execution and distribution of
reports - includes web services, metadata; etc
- report manager is a Web-based administration tool for managing
the Report Server
d) what tools can you use to build reports?
2000
- visual studio
- notepad
2005
- business intelligence development studio
- visual studio
- report builder - geared towards non-technical / end users
- notepad
e) where can you store reporting services reports
- RDL file
- SSRS Server
- embed within a Windows Forms application
f) how do you move a RDL file to the SSRS Server?
- right-click 'deploy' in solution explorer
g) how can you test a RDL file without deploying it to a server
- right-click 'build'
or
- hit the 'preview' tab towards the top left
h) what languages can you use for scripting within SSRS?
- VB.net; C#
i) what is an expression
- a simple way to add logic to your report. Make this section
visible if today is Tuesday for example.Hi Hooker,
They are fine .i would suggest you to add some questions on RS 2005
Development.It seems these questions are completely on Managing the
report server and RDL File.
Include some questions which decides the report development skills like
1) how do you limit no of rows for a page
2) how can you change the colour of a row depending on the value
3) some questions regarding exporting
dbahooker@.hotmail.com wrote:
> Team;
> I need to come up with a better list of interviewing questions for SSRS
> candidates.. feel free to let me know what I should add, remove or
> change.
> Thanks!
> PS - if anyone wants to develop SSRS reports in Redmond; drop me an
> email aaron_kempf@.hotmail.com or even add me to messenger
> a) what is an RDL file
> - an XML representation of the Reports layout; this is typically
> 'deployed' to live inside of the ReportServer database.
> b) what are some of the competitive advantages to using SSRS over
> crystal?
> - price / cost
> - integrated development environment (IDE)
> - easy to deploy
> c) what is the difference between the 'ReportServer' websites and the
> 'ReportManager' website.
> - reportServer Provides services for execution and distribution of
> reports - includes web services, metadata; etc
> - report manager is a Web-based administration tool for managing
> the Report Server
> d) what tools can you use to build reports?
> 2000
> - visual studio
> - notepad
> 2005
> - business intelligence development studio
> - visual studio
> - report builder - geared towards non-technical / end users
> - notepad
> e) where can you store reporting services reports
> - RDL file
> - SSRS Server
> - embed within a Windows Forms application
> f) how do you move a RDL file to the SSRS Server?
> - right-click 'deploy' in solution explorer
> g) how can you test a RDL file without deploying it to a server
> - right-click 'build'
> or
> - hit the 'preview' tab towards the top left
> h) what languages can you use for scripting within SSRS?
> - VB.net; C#
> i) what is an expression
> - a simple way to add logic to your report. Make this section
> visible if today is Tuesday for example.