Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for valid SQL server connection
Hi folks!
I'm a relative novice at Excel 2003 and VBA, and a complete novice using SQL. The project I'm working on is to build a Excel macro to retrieve records from SQL database tables and stuff selected fields into cells on a spreadsheet. I have some fragments of working code that I'm trying to expand to fit my purpose. 'Define the SQL access handles Dim rs As New ADODB.Recordset Dim cn As New ADODB.Connection Dim strSQL As String 'Connect to the database. cn.ConnectionString = "provider = SQLOLEDB;server=SQLSRVR;database=DBASEXYZ;Trusted_ Connection = yes;Integrated Security=SSPI" cn.ConnectionTimeout = 30 cn.Open rs.ActiveConnection = cn rs.CursorLocation = adUseClient ' allows access to recordcount That code is then followed by the the SQL query and so forth. However, what I want to do beforehand is to check whether I have a valid connection established to the SQL server database. I presume that there is some property, et cetera I can test immediately following the cn.Open statement that should let me find out whether the open was successful, but in my thrashing about with the help files and a treeware manual or two, I've yet to learn any more about it. Can anyone give me a hand? -- Robert W. King United-Bilt Homes, Inc. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for valid SQL server connection
"Robert W. King" wrote ...
I want to do beforehand is to check whether I have a valid connection established to the SQL server database. I presume that there is some property, et cetera I can test immediately following the cn.Open statement that should let me find out whether the open was successful As you are opening the Connection synchronously, you would get a run-time error if the connection failed. If you are riding over run-time errors (e.g. using On Error Resume Next) you could subsequently check the connection's State property. Alternatively, you could connect asynchronously but sinking the Connection object's events in a class module and get any error message when the ConnectComplete event e.g. Option Explicit Private WithEvents m_Con As ADODB.Connection Private Sub m_Con_ConnectComplete( _ ByVal pError As ADODB.Error, _ adStatus As ADODB.EventStatusEnum, _ ByVal pConnection As ADODB.Connection) End Sub Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to check valid Date value? | Excel Worksheet Functions | |||
How to check that a string is a valid formula ? | Excel Programming | |||
ODBC connection for insert into SQL Server | Excel Programming | |||
sql server connection | Excel Programming | |||
check for valid file | Excel Programming |