PumpMeister
asked on
Eliminating error messages when an external data connection is unavailable in Excel 2007
I have a spreadsheet that has some data connections to an Access db on a shared network drive. My users would normally use this spreadsheet locally or via VPN. However, I would also like to allow them to use it when they cannot connect to the network or VPN in.
In this situation, the user would just be using the last query results obtained from the Access db...no refresh since the data connection to the Access db is not active.
However, in testing this scenario, I am seeing a lot of error messages from excel about the missing data source. These messages would be very disconcerting to the users of the spreadsheet.
Is there any way to trap or eliminate these error messages?
I am very VBA savy, so if it requires a VBA approach, that is fine.
In this situation, the user would just be using the last query results obtained from the Access db...no refresh since the data connection to the Access db is not active.
However, in testing this scenario, I am seeing a lot of error messages from excel about the missing data source. These messages would be very disconcerting to the users of the spreadsheet.
Is there any way to trap or eliminate these error messages?
I am very VBA savy, so if it requires a VBA approach, that is fine.
ASKER
I do not think that what you wrote is what I am after. I am familiar with all the db stuff.
I already have data connections set up in Excel. The issue is that I want to eliminate the error messages that show up when the source db is unavailable. (e.g., when not connected to the network where it resides.)
Steve
I already have data connections set up in Excel. The issue is that I want to eliminate the error messages that show up when the source db is unavailable. (e.g., when not connected to the network where it resides.)
Steve
>The issue is that I want to eliminate the error messages that show up when the source db is unavailable.
Yep. What you need to do is capture any error from the database open command (and other commands) so that you can gracefully handle errors.
For example:
On Error Resume Next
MyDatabase.Open "Provider=Microsoft.Jet.OL EDB.4.0; Data Source='C:\full\path\to\da tabase.mdb '; User Id=admin; Password=;"
If Err.Number <> 0 Then
' We have a problem
Else
' We have a good connection
End If
Kevin
Yep. What you need to do is capture any error from the database open command (and other commands) so that you can gracefully handle errors.
For example:
On Error Resume Next
MyDatabase.Open "Provider=Microsoft.Jet.OL
If Err.Number <> 0 Then
' We have a problem
Else
' We have a good connection
End If
Kevin
ASKER
Kevin:
I am not currently using any VBA to open the external DB.
I have it set up with data sources/MS Query (4 cxns, 1 of which is a query) so that Excel does all the work for me. Trying to keep the Excel workbook "light weight" if possible... no VBA/macros so I do not have to deal with all the trust stuff.
Are you suggesting using VBA to test the connection to the external db?
Ok, that sounds plausible.
However, would the VBA (in a workbook open event) run prior to Excel attempting to connect or after? Not sure about the sequence of events. Also, even if I do detect that the connection is bad via VBA, how can I really prevent the error messages that happen when Excel does its data connection stuff through the data source setup?
Steve
I am not currently using any VBA to open the external DB.
I have it set up with data sources/MS Query (4 cxns, 1 of which is a query) so that Excel does all the work for me. Trying to keep the Excel workbook "light weight" if possible... no VBA/macros so I do not have to deal with all the trust stuff.
Are you suggesting using VBA to test the connection to the external db?
Ok, that sounds plausible.
However, would the VBA (in a workbook open event) run prior to Excel attempting to connect or after? Not sure about the sequence of events. Also, even if I do detect that the connection is bad via VBA, how can I really prevent the error messages that happen when Excel does its data connection stuff through the data source setup?
Steve
>Are you suggesting using VBA to test the connection to the external db?
Yes.
>However, would the VBA (in a workbook open event) run prior to Excel attempting to connect or after?
You need to move the database control to VBA. A couple of ideas here. You move everything to VBA, or you can instruct Excel to refresh the data connection manually and then trigger it from VBA.
Kevin
Yes.
>However, would the VBA (in a workbook open event) run prior to Excel attempting to connect or after?
You need to move the database control to VBA. A couple of ideas here. You move everything to VBA, or you can instruct Excel to refresh the data connection manually and then trigger it from VBA.
Kevin
ASKER
Ok, I think the second approach is probably better for my purposes... just triggering a refresh from VBA.
I have two cases in which I would need a refresh... on workbook open & on an update of a particular cell that has a data validation dropdown. I would set up event handlers for each to trigger the refresh.
I found the connections collection in the VBA object browser, but I don't see a method for a refresh.
Any suggestions?
Much thanks!
Steve
I have two cases in which I would need a refresh... on workbook open & on an update of a particular cell that has a data validation dropdown. I would set up event handlers for each to trigger the refresh.
I found the connections collection in the VBA object browser, but I don't see a method for a refresh.
Any suggestions?
Much thanks!
Steve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. I did some hunting in the Object Browser and on MSDN, and stumbled across the EnableConnections method for Excel 2007. This would enable me to have the connections disabled on workbook load, then use VBA to test the availability of the access db that is used by the connections... if it is available, then I could enable connections, and do a RefreshAll. All the other refresh stuff (e.g. refresh on cell value change) would still be handled by Excel once the connections are enabled.
So, how do I disable the existing connections without deleting them? Is this only available in VBA, or is there a way to do it from the Excel user environment?
Steve
So, how do I disable the existing connections without deleting them? Is this only available in VBA, or is there a way to do it from the Excel user environment?
Steve
It didn't answer the question, nor did the site's search produce anything for EnableConnections method
The following text and sample code illustrates how to:
-> open a database connection
-> use a stored procedure to perform a query
-> execute any SQL command against a database
-> open a recordset using a custom query
-> open a recordset using a table name
-> check for an empty recordset
-> read all records in a recordset
-> add a record to a recordset
-> delete a record from a recordset
-> copy a recordset with headers to a worksheet
-> close a recordset and database
This sample code, except for Open database method, can be used with any database such as Access, SQL Server, or Oracle. When using a database, most interaction happens via a recordset. Data is manipulated almost entirely using Recordset objects. Any number of Recordset objects can be created and used at the same time - each representing a different query or the same query. Different Recordset objects can access the same tables, queries, and fields without conflicting.
After opening a Recordset the Recordset can contain zero or more records. One record in the Recordset is always the current record except when the Recordset BOF or EOF property is true in which case no record is the current record. The current record is the record that is affected by any record-specific methods. To move amongst the records in a Recordset use the MoveNext, MovePrevious, MoveLast, and MoveFirst Recordset methods. A specific record can be made the current record by setting the AbsolutePosition property to the index number of the desired record. Fields in the current record are access as illustrated below.
Value = MyRecordset!Field1
MyRecordset!Field2 = Value + 1
When the current record is changed use the Update method to apply the changes to the database. Use the Add method to add a new record and the Delete method to delete the current record. The Add method can be used even if the query returns an empty recordset.
Caution about using the RecordCount method: The RecordCount for a serverside recordset may return a -1. This occurs with ActiveX Data Objects (ADO) version 2.0 or later when the CursorType is adOpenForwardonly or adOpenDynamic and when with ADO 1.5 only when the cursortype is adOpenForwardonly. To get around this problem use either adOpenKeyset or adOpenStatic as the CursorType for server side cursors or use a client side cursor. Client side cursors use only adOpenStatic for CursorTypes regardless of which CursorType is selected.
Before writing any ADODB code the data objects library "Microsoft ActiveX Data Objects x.x Library" must be referenced in the VBA project (Tools->References).
For additional information on the ADODB interface see the MSDN pages at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadoapireference.asp
Sample code:
Dim MyDatabase As ADODB.Connection
Dim MyCommand As ADODB.Command
Dim MyRecordset As ADODB.RecordSet
Dim Column As Long
' Open database connection
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "Provider=Microsoft.Jet.OL
' For more information about Open syntax:
' http://msdn.microsoft.com/en-us/library/ms808201.aspx
' Additional help constructing connection strings can be found at http://www.connectionstrings.com/
' Query database using stored procedure (requires command object)
Set MyCommand = New ADODB.Command
Set MyCommand.ActiveConnection
MyCommand.CommandText = "qrySomeQuery" ' <- name of procedure
MyCommand.CommandType = adCmdStoredProc
With MyCommand
.Parameters.Refresh
.Parameters.Append .CreateParameter("QueryTex
.Parameters.Append .CreateParameter("QueryTex
.Parameters.Append .CreateParameter("QueryLon
.Parameters.Append .CreateParameter("QueryDat
.Parameters.Append .CreateParameter("QueryDat
.Parameters.Append .CreateParameter("BooleanP
' For more information about CreateParameter syntax:
' http://msdn.microsoft.com/en-us/library/ms808298.aspx
End With
' Open recordset using command object
Set MyRecordset = New ADODB.Recordset
MyRecordset.Open MyCommand, , adOpenDynamic, adLockPessimistic
' For more information about Open method syntax:
' http://msdn.microsoft.com/en-us/library/ms808656.aspx
' Build a custom query using command object
Set MyCommand = New ADODB.Command
With MyCommand
Set .ActiveConnection = MyDatabase
.CommandType = adCmdText
.CommandText = "SELECT * From tblMyTable WHERE (tblMyTable.MyID = 1)"
End With
MyRecordSet.Open MyCommand, , adOpenDynamic, adLockReadOnly
' Execute any SQL statement
MyDatabase.Execute "INSERT INTO TableName (Field1, Field2) VALUES ('" & Range("A1").Value & "','" & Range("A2").Value & "')"
' Open a recordset by specifying specific table (no query)
MyRecordset.Open "TableName", MyDatabase, adOpenDynamic, adLockPessimistic
' Open a recordset by specifying query without using command object
MyRecordset.Open "SELECT * FROM MyTable", MyDatabase, adOpenDynamic, adLockPessimistic
' Test for no records
If MyRecordset.BOF And MyRecordset.EOF Then
MsgBox "No records in table"
End If
' Determine total records (see notes above about inconsistencies with the RecordCount method)
MsgBox "Total records: " & MyRecordset.RecordCount
' Look at all records in record set
While Not MyRecordset.EOF
MsgBox "Record number: " & MyRecordset.AbsolutePositi
MyRecordset.MoveNext
Wend
' Find a specific record given a field value
MyRecordset.MoveFirst
MyRecordset.Find "ID='ABC123'"
If Not MyRecordset.BOF And Not MyRecordset.EOF Then
MyRecordset!Field1 = "Match Found"
MyRecordset.Update
End If
' Copy the entire recordset to a worksheet (this technique does not copy field names)
Sheets("Sheet1").[A2].Copy
' Create headers and copy data
With Sheets("Sheet1")
For Column = 0 To MyRecordset.Fields.Count - 1
.Cells(1, Column + 1).Value = MyRecordset.Fields(Column)
Next
.Range(.Cells(1, 1), .Cells(1, MyRecordset.Fields.Count))
.Cells(2, 1).CopyFromRecordset MyRecordset
End With
' Update current record
MyRecordset!Field1 = "Some data"
MyRecordset!Field2 = "Some more data"
MyRecordset.Update
' Move specific fields from current record to worksheet
With Sheets("Sheet1")
Cells(Row, "A") = MyRecordset!Field1
Cells(Row, "B") = MyRecordset!Field2
End With
' Add new record and set field values
MyRecordset.AddNew
MyRecordset!Field1 = "Some data"
MyRecordset.Update
' Update an existing record or add it if it does not exist
MyRecordset.MoveFirst
MyRecordset.Find "Field1='"& SourceSheet.Cells(Row, "A") & "'"
If MyRecordset.BOF Or MyRecordset.EOF Then
MyRecordset.AddNew
End If
MyRecordset!Field1 = SourceSheet.Cells(Row, "A")
MyRecordset!Field2 = SourceSheet.Cells(Row, "B")
MyRecordset.Update
' Delete current record
MyRecordset.Delete
' Close recordset
MyRecordset.Close
' Close database
MyDatabase.Close
Kevin