Visual Basic Q&A

Symptoms
When you attempt to use the events of an ADO Data Control or the DataEnvironment with a reference to a version of the Microsoft ActiveX Data Objects (ADO) later than version 2.0, you receive the following error message:

Compile error:
Procedure declaration does not match description of event or procedure having the same name.
Resolution
The ADO Data Control and the Data Environment were compiled using Microsoft Data Access Components version 2.0.

Symptoms
When you compile your ADO project, you receive the following error:

Compile error:User-defined type not definedThis can occur on either a Connection or Command object.
Resolution
You may have referenced one of the following libraries instead of theMicrosoft ActiveX Data Objects (ADODB) type library:Microsoft ActiveX Data Objects Recordset (ADOR) type library.
-or-Microsoft ActiveX Data Objects (Multi-dimensional) (ADOMD) type library.

Symptoms
The Access AutoNumber field is always available when a server-side cursor is used. When a client-side cursor is used, the AutoNumber field is only returned immediately when an Access 2000 database is used with OLE DB Provider for Jet 4.0 driver and with the Jet 4.0 ODBC driver.
Resolution
The following table shows when the AutoNumber field is immediately available without a requery.
Client-side cursor for Access 97 and 2000 with different drivers:

Collapse this tableExpand this table
DriverAccess 97Access 2000Office XPJet OLE DB 3.51NOUnrecognized Database FormatUnrecognized Database FormatJet OLE DB 4.0Returns 0YESYESJet ODBC 3.51NONONOJet ODBC 4.0Returns 0YESYES
The following code sample demonstrates the results shown in the above table: Open a standard EXE project in Visual Basic. Form1 is created by default.Under Project References, select Microsoft ActiveX Data Objects .Place two CommandButtons on the form.Paste the following code in the form code window:

Dim cn As ADODB.ConnectionDim rs As ADODB.RecordsetPrivate Sub Command1_Click()rs.Open “select * from ORDERS”, cn, adOpenKeyset, adLockOptimisticEnd SubPrivate Sub Command2_Click()rs.AddNewrs!CustomerID = “ALFKI”rs!EmployeeID = 1rs.UpdateBatchDebug.Print rs!OrderID & Chr(9) & rs!CustomerID & Chr(9); rs!EmployeeIDEnd SubDim sconnect As StringPrivate Sub Form_Load() Dim sconnect As StringSet cn = New ADODB.Connection Set rs = New ADODB.Recordset’Change the paths to the mdb’s in the following statements for your machine; ‘Uncomment ONE of the statements to set sconnect to a valid connection string. ‘ Using JETOLEDB drivers’Test Office 97 using JETOLEDB’sconnect = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb”’sconnect = “Provider=Microsoft.Jet.OLEDB.3.51;Data Source=D:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb”‘ Test Office 2000 using JETOLEDB’sconnect = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Program Files\Office2000\Office\Samples\northwind.mdb”’sconnect = “Provider=Microsoft.Jet.OLEDB.3.51;Data Source=D:\Program Files\Office2000\Office\Samples\northwind.mdb”‘Test Office XP using JETOLEDB’sconnect = “Provider=Microsoft.Jet.OLEDB.3.51;Data Source=D:\Program Files\Microsoft Office\Office10\Samples\northwind.mdb”’sconnect = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Program Files\Microsoft Office\Office10\Samples\northwind.mdb”‘ Using ODBC drivers’Test Office 97 using ODBC drivers’sconnect = “Driver={Microsoft Access Driver (*.mdb)};” & _”Dbq=nwind.mdb;” & _”DefaultDir=D:\Program Files\Microsoft Visual Studio\VB98\;” & _”Uid=Admin;Pwd=;” ”Test Office 2000 / 9 using ODBC drivers’sconnect = “Driver={Microsoft Access Driver (*.mdb)};” & _”Dbq=northwind.mdb;” & _”DefaultDir=D:\Program Files\Microsoft Office\Office\Samples;” & _”Uid=Admin;Pwd=;”‘Test Office XP / 10 using ODBC drivers’sconnect = “Driver={Microsoft Access Driver (*.mdb)};” & _”Dbq=northwind.mdb;” & _”DefaultDir=D:\Program Files\Microsoft Office\Office10\Samples;” & _”Uid=Admin;Pwd=;”cn.CursorLocation = adUseClient’cn.CursorLocation = adUseServercn.Open sconnectEnd Sub

Symptoms
You may use ADOMD with the MSOLAP provider to return an Out of Process Cellset. This is useful with DCOM/MTS business objects. This code sample requires the MSOLAP OLEDB provider on the client computer and the Food Mart OLAP database on SQL Server OLAP Services computer. The MSOLAP OLEDB provider is installed when you install OLAP client components from SQL Server 7.0 CD.
Resolution
ServerSteps to AccomplishCreate a new Visual Basic ActiveX EXE Project. Class 1 is created by default.Set a Project Reference to the Microsoft ActiveX Data Objects (Multi-Dimensional) 1.0 Object Library.Change the name of the Project to ADOBusObj.Paste the following code into Class1:

Private strSQL As StringPrivate strConnect As StringDim adoCat As New ADOMD.CatalogPublic Function GetRs() As ADOMD.CellSetDim adoCst As New ADOMD.CellSetWith adoCstSet adoCst.ActiveConnection = adoCat.ActiveConnection.Source = strSQL.OpenEnd WithSet GetRs = adoCstEnd FunctionPrivate Property Get ConnectStr() As StringConnectStr = strConnectEnd PropertyPrivate Property Let ConnectStr(strCn As String)strConnect = strCnEnd PropertyPublic Property Get SQL() As StringSQL = strSQLEnd PropertyPublic Property Let SQL(nSQL As String)strSQL = nSQLEnd PropertyPublic Sub ADOMDConnect(strConnect As String, Optional CmdTimeOut As Integer = 20)adoCat.ActiveConnection = strConnectConnectStr = adoCnEnd Sub
ClientCreate a new Visual Basic Standard EXE Project. Form1 is created by default.Set a Project Reference to the Microsoft ActiveX Data Objects (version 2.0 or later) Library.Set a Project Reference to the ActiveX EXE ADOBusObj created earlier. Change the connection string and the SQL string to reflect your OLAP server’s configuration.Paste the following code into the General Declarations section of Form1:
NOTE: A cube query (MDX query) has the following layout that defines the number of Axes in the query. The count of the fields referenced between SELECT and FROM in the MDX statement are the number of Axes in the query.

SELECT <axis_specification> [, <axis_specification>...] FROM <cube_specification>WHERE <slicer_specification>

Option ExplicitConst strConnect = “Data Source=<DataSource>;PROVIDER=MSOLAP;INITIAL CATALOG=FoodMart”Private Sub Form_Click()On Error GoTo ErrorHandlerDim adoCst As ADOMD.CellsetDim objAdoData As ADOBusObj.Class1Dim strOutput As StringDim intStrLen As IntegerDim intDC0 As IntegerDim intDC1 As IntegerDim intPC0 As IntegerDim intPC1 As IntegerDim i As IntegerDim j As IntegerDim k As IntegerSet objAdoData = CreateObject(“ADOBusObj.Class1″)With objAdoData.SQL = “Select {[Measures].members} On Columns,” & _”Non Empty [Store].[Store City].members ” & _”Properties [Store].[Store Type], [Store].[Store Manager] ” & _”On Rows From Sales”.ADOMDConnect strConnect, 20 ‘Establish connection.End With’Return the Cellset from MD Data Object.Set adoCst = objAdoData.GetRs’it is known up front there are two axes for this query so,’just check each axis for number of dimensions.intDC0 = adoCst.Axes(0).DimensionCount – 1intDC1 = adoCst.Axes(1).DimensionCount – 1intPC0 = adoCst.Axes(0).Positions.Count – 1intPC1 = adoCst.Axes(1).Positions.Count – 1For i = 0 To intDC0For j = 0 To intPC0intStrLen = Len(adoCst.Axes(0).Positions(j).Members(i).Caption)If intStrLen > 15 Then intStrLen = 0strOutput = strOutput & “[" & adoCst.Axes(0).Positions(j).Members(i).Caption & "]” & _String(3, vbTab) & Space(15 – intStrLen)Next jNext iDebug.Print strOutput & vbCrLfFor i = 0 To intPC1strOutput = “”For j = 0 To intDC1Debug.Print “– ” & adoCst.Axes(1).Positions(i).Members(j).Caption & ” –”Next jFor k = 0 To intPC0intStrLen = Len(adoCst(k, i).FormattedValue)If intStrLen > 15 Then intStrLen = 0strOutput = strOutput & adoCst(k, i).FormattedValue & _Space(15 – intStrLen) & String(4, vbTab)Next kDebug.Print strOutputNext iMsgBox “Success”, vbOKOnly, “MD Data Object”Exit SubErrorHandler:MsgBox “Change Failed:” & vbCrLf & _Err.Number & _vbCrLf & Err.Description, _vbOKOnly, “Data Object”Exit SubEnd Sub

Symptoms
If you run an ADODB command stream and specify SQL SELECT with the FOR XML AUTO clause, an XML document stream is fetched from SQL Server and displayed in the Visual Basic Intermediate window.
You must set the following properties for the Properties collection of the ADODB.Command object: Output Stream. This property designates where the resulting XML data stream will be piped.Dialect. The dialect defines the syntax and the general rules that the provider uses to parse the string or the stream. By setting the command language dialect, you specify how the Microsoft OLE DB Provider for SQL Server interprets the command text that is received from ActiveX Data Objects (ADO). The dialect is specified by a globally unique identifier (GUID) and is set by using the Dialect property of the Command object.
Resolution
Create a new Visual Basic Standard EXE. Form1 is created by default. On the Project menu, select References, and then set a reference to Microsoft ActiveX Data Objects 2.6.Place a CommandButton on Form1, and then place the following code in its click event:
Note You must change User ID=<username> and Password=<strong password> to the correct values before you run this code. Make sure that the User ID has the appropriate permissions to perform this operation on the database.

Private Sub Command1_Click()Dim sConn As StringDim sQuery As StringDim outStrmsConn = “Provider=SQLOLEDB;Data Source=.;Initial Catalog=Northwind;User ID=<username>;Password=<strong password>;”Dim adoConn As ADODB.ConnectionSet adoConn = New ADODB.ConnectionadoConn.ConnectionString = sConnadoConn.CursorLocation = adUseClientadoConn.OpenDim adoCmd As ADODB.CommandSet adoCmd = New ADODB.CommandSet adoCmd.ActiveConnection = adoConnsQuery = “<ROOT xmlns:sql=’urn:schemas-microsoft-com:xml-sql’>”sQuery = sQuery & “<sql:query>SELECT * FROM PRODUCTS FOR XML AUTO</sql:query>”’sQuery = sQuery & “</ROOT>”Dim adoStreamQuery As ADODB.StreamSet adoStreamQuery = New ADODB.Stream’Open the command stream so it may be written toadoStreamQuery.Open’Set the input command stream’s text with the query stringadoStreamQuery.WriteText sQuery, adWriteChar’Reset the position in the stream, otherwise it will be at EOS.adoStreamQuery.Position = 0′Set the command object’s command to the input stream set above.Set adoCmd.CommandStream = adoStreamQuery’Set the dialect for the command stream to be a SQL query.adoCmd.Dialect = “{5D531CB2-E6Ed-11D2-B252-00C04F681B71}”‘Create the output stream to stream the results into.Set outStrm = CreateObject(“ADODB.Stream”)outStrm.Open’Set command’s output stream to the output stream just opened.adoCmd.Properties(“Output Stream”) = outStrm’Execute the command, thus filling the output stream.adoCmd.Execute , , adExecuteStream’Position the output stream back to the beginning of the stream.outStrm.Position = 0′Create temporary string.Dim str As String’Assign the stream’s output to the temp string to format.str = outStrm.ReadText(-1)’Add a cr/lf pair for each row in the result stream.str = Replace(str, “><”, “>” & vbCrLf & “<”)Debug.Print strGoTo ByeRecError:Debug.Print Err.Number & “: ” & Err.DescriptionBye:Set adoCmd = NothingIf adoConn.State = adStateOpen ThenadoConn.CloseEnd IfSet adoConn = NothingEnd Sub Specify either the SQL 2000 Server or, if the server is on your local machine, use the period symbol (.) or (local). Note that the Immediate window of Visual Basic displays the results.

Symptoms
When you attempt to use the events of an ADO Data Control or the DataEnvironment with a reference to a version of the Microsoft ActiveX Data Objects (ADO) later than version 2.0, you receive the following error message:

Compile error:
Procedure declaration does not match description of event or procedure having the same name.
Resolution
The ADO Data Control and the Data Environment were compiled using Microsoft Data Access Components version 2.0.