Visual Basic Q&A

Symptoms
When attempting to delete or update a record in a SQL Server cursor, the delete or update operation succeeds, but the following error returns:

Run-time error 3146 “ODBC call failed”.
Resolution
This behavior occurs when SQL Server does not return a message indicating the number of rows returned by a statement. SQL Server does not return a message indicating the number of rows affected by a statement after the following commands have been executed on SQL Server:

sp_configure “user options”, 512SET NOCOUNT ON

Symptoms
AdoGUIDz.exe is a self-extracting executable that contains a sample project that demonstrates using the globally unique identifier datatype (GUID) with Microsoft Access, SQL 6.5 and SQL 7.0. The sample code may be particularly helpful if you are attempting to manipulate GUIDs with ODBC versions 3.51 and below because those versions of ODBC do not support a native GUID datatype. ODBC versions 3.6 and above include the GUID datatype. Consequently, the methods for manipulating GUIDs with ODBC 3.6 are simpler.
NOTE: SQL 6.5 does not support a native GUID datatype so in order to store/retrieve GUIDs in SQL 6.5 you must use the VarBinary datatype and Byte Arrays.
Resolution
The following files are available for download from the Microsoft Download Center:
Adoguidz.exe(http://download.microsoft.com/download/vb60pro/sample/1/win98/en-us/adoguidz.exe) Release Date: DEC-29-1998
For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:
119591?(http://support.microsoft.com/kb/119591/EN-US/)How to Obtain Microsoft Support Files from Online Services Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.

FileNameSize———————————————————AdoGUID.bas3KBAdoGUID.exe60KBAdoGUID.frm25KBAdoGUID.frx1KBAdoGUID.mdb80KBAdoGUID.vbp2KBReadme.txt4KB Microsoft Access has a ReplicationID AutoNumber field that is a 16-byte (128 bit) Globally Unique Identifier (GUID) that uniquely identifies each record in the database. Please reference the sample project for the code that demonstrates how to SELECT specific GUIDs and Insert GUIDs using the AutoNumber field with Microsoft Access. The following function is a code snippet from the sample that demonstrates how to SELECT a specific GUID from an Access table using Microsoft ActiveX Data Objects (ADO):
Sample Code

Sub AccessReQueryADO()On Error GoTo ErrorMessageDim adoCn As adoDb.ConnectionDim adoRs As adoDb.RecordsetDim strCn As StringDim strSQL As StringstrCn = App.Path & “\adoGUID.mdb”Set adoCn = New adoDb.ConnectionWith adoCn.Provider = “Microsoft.JET.OLEDB.3.51″.CommandTimeout = 500.ConnectionTimeout = 500.Open strCn, “admin”, “”End WithIf Option7.Value = True ThenstrSQL = “SELECT * FROM GUIDtable WHERE ” & _”Instr(1,[colGUID],’” & strGUID & “‘)”ElsestrSQL = “SELECT * FROM GUIDtable”End IfSet adoRs = New adoDb.RecordsetWith adoRsSet .ActiveConnection = adoCn.LockType = adLockOptimistic.CursorLocation = adUseServer.CursorType = adOpenForwardOnlyEnd WithadoRs.Open strSQLtxtMessage.Text = “”While Not adoRs.EOFtxtMessage.Text = txtMessage.Text & _adoRs.Fields(“colGUID”).Value & “|”txtMessage.Text = txtMessage.Text & _adoRs.Fields(“colDescription”).Value & vbCrLfadoRs.MoveNextWendGoTo ExitSubErrorMessage:MsgBox Err.Number & ” : ” & vbCrLf & Err.DescriptionExitSub:Label6.Caption = “- ReQueried AccessADO GUID Table…”Set adoCn = NothingSet adoRs = NothingEnd Sub
Unlike SQL 6.5, SQL 7.0 supports a GUID datatype known as UNIQUEIDENTIFIER. This datatype is a 16-byte GUID stored in the same format as the Microsoft Access AutoNumber (GUID) datatype. There are subtle differences concerning how to Insert and Retrieve the GUIDs among the different database engines. Since SQL 6.5 does not support a native GUID datatype the methods used for storing/retrieving GUIDs are much different than SQL 7.0 or Microsoft Access. SQL 7.0 with the OLEDB provider is almost the same as Microsoft Access with or without the OLEDB provider (SQLOLEDB), as you will see in the sample code. With SQL 6.5 you must store the GUID as a VarBinary(16) datatype. Consequently, to retrieve the GUID with SQL65 you must use a stored procedure and build a Command object with the appropriate ByteArray parameter to pass to the stored procedure SELECT statement.
NOTE: You must use the same code techniques with SQL 7.0 as with SQL 6.5 if you are using the ODBC provider (MSDASQL) since in ODBC 3.51 and below the GUID datatype is not recognized.
The 16-byte (128 bit) data structure of a GUID:

typedef struct _GUID{unsigned longData1;unsigned shortData2;unsigned shortData3;unsigned charData4[8];} GUID;

* Data1An unsigned long integer data value.* Data2An unsigned short integer data value.* Data3An unsigned short integer data value.* Data4An array of unsigned characters. To demonstrate GUIDs with SQL 7.0 or SQL 6.5 in the sample project you must specify a valid (test) SQL 7.0/SQL 6.5 server and database. To do so, navigate to the Connection Info tab and change the Server and Database reference. The defaults are (local) Server and the Pubs database. Also, to use the native GUID datatype for SQL 7.0, you must change to the OLEDB provider (SQLOLEDB) by clicking the appropriate option button in the Provider frame at the top of the Form. If you select ODBC as the provider for SQL 7.0 then the application uses the same code as with SQL 6.5.
NOTE: The Connection Info tab has no bearing on Microsoft Access. The default database for Microsoft Access is included with the sample project AadoGUID.mdb as should reside in the Application path.
For each database you select in the sample project you must run Create Table to create the GUID test table and then Insert to automatically generate some test GUID data before running a ReQuery.
NOTE: CoCreateGUID() is called in the Insert sample code to generate the test GUID values.
In the sample application there is a ByteArray2GUID() function that is used to convert the VarBinary(16) byte array to a GUID string for display. Also, the function is used to convert the GUID string to a byte array for storage in the SQLServer VarBinary(16) datatype column. Note that the function is needed when using the GUIDs interchangeably between Microsoft Access and SQL 6.5. If you Export the Microsoft Access table to SQL 6.5 you will see that the bytes are not stored in the same order in which they display in the Microsoft Access table view. For example:

Reversed…Not Reversed…>—————-<|>—————<20C68F83-9593-0011-BFBB-00C04F8F8347 ‘SQLServer view after table Export.838FC620-9395-1100-BFBB-00C04F8F8347 ‘Microsoft Access view. NOTE: The bytes are in (DWord and Word) reverse order after Exporting the Microsoft Access table.
Because the Microsoft Access Upsizing Wizard results in the same storage of the bytes in SQL 6.5, you must use the ByteArray2GUID() and GUID2ByteArray() functions to remain compatible with the storage of the GUIDs in Microsoft Access. If you do not need to Export the Microsoft Access table to SQL 6.5 or upsize the Microsoft Access database to SQL 6.5 then you need only store the bytes in a straightforward fashion.
The following is a code snippet from the code sample that demonstrates the storage of the GUID in the byte format of Microsoft Access.
Sample Code

Sub SQL65InsertGUID()’Insert GUID record.On Error GoTo ErrorMessageDim adoCn As adoDb.ConnectionDim adoRs As adoDb.RecordsetDim strGUIDtmp As StringDim bytGUID() As ByteDim strCn As StringDim strSQL As StringstrCn = “Provider=” & strProvider & _”;Driver={SQL Server}” & _”;Server=” & txtServer & _”;Database=” & txtDatabase & _”;Uid=” & txtUserID & _”;Pwd=” & txtPasswordSet adoCn = New adoDb.ConnectionWith adoCn.ConnectionString = strCn.CommandTimeout = 500.ConnectionTimeout = 500.OpenEnd WithstrGUIDtmp = strGUIDbytGUID = GUID2ByteArray(FilterGUID(strGUIDtmp))strSQL = “SELECT * FROM GUIDtable WHERE 1=0″Set adoRs = New adoDb.RecordsetWith adoRsSet .ActiveConnection = adoCn.LockType = adLockOptimistic.CursorLocation = adUseServer.CursorType = adOpenForwardOnlyEnd WithadoRs.Open strSQLadoRs.AddNewadoRs.Fields(“colGUID”).Value = bytGUIDadoRs.Fields(“colDescription”).Value = “This is a test GUID”adoRs.UpdateGoTo ExitSubErrorMessage:MsgBox Err.Number & ” : ” & vbCrLf & Err.DescriptionExitSub:Label6.Caption = “[ASCII 176] Inserted SQL65 GUID Record…”Set adoCn = NothingSet adoRs = NothingEnd Sub’======================Function GUID2ByteArray(ByVal strGUID As String) As Byte()Dim i As IntegerDim j As IntegerDim sPos As IntegerDim OffSet As IntegerDim sGUID(0 To 2) As ByteDim bytArray() As ByteReDim bytArray(0 To 15) As BytesGUID(0) = 7sGUID(1) = 11sGUID(2) = 15OffSet = 0sPos = 0′AABBCCDD-AABB-CCDD-XXXX-XXXXXXXXXXXX ‘Microsoft Access view.’DDCCBBAA-BBAA-DDCC-XXXX-XXXXXXXXXXXX ‘SQLServer view.’Need to loop through to build the GUID byte array in the Microsoft’Access storage format since the first eight bytes are reversed.For i = 0 To UBound(sGUID)For j = sGUID(i) To (OffSet + 1) Step -2bytArray(sPos) = “&H” & Mid$(strGUID, j, 2)sPos = sPos + 1Next jOffSet = sGUID(i)Next iFor i = 17 To 31 Step 2bytArray(sPos) = “&H” & Mid$(strGUID, i, 2)sPos = sPos + 1Next iGUID2ByteArray = bytArray()End Function

Symptoms
This article describes how to use a Microsoft SQL Server distributed query to retrieve data from a secured Microsoft Access database.
Resolution
Microsoft SQL Server version 7.0 provides you the ability to perform queries against different databases by using OLE DB providers. You query databases by using: OpenQuery or OpenRowset Transact-SQL functions.
-or-
A query with four-part names including a linked server name. To set up a linked server to access a secured Microsoft Access database, use these steps: Configure the registry (by using the Registry Editor) to use the correct Microsoft Access Workgroup Information file (the .mdw file). Use the Registry Editor to add the full path name of the Workgroup Information file to the following registry entry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB Next, set the value to the path and name of the file, such as:

C:\…\MySystem.mdwTo open the registry editor, navigate to the Start button and click Run. In the Run dialog box, type Regedit, and then press OK.In the registry editor, navigate to this key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Right-click and select New/String Value.Type SystemDb, and then press Enter.Double-click the SystemDb key in the left window pane.In the Value Data text box, type the full path to your .mdw file.Close the registry editor. Execute the sp_addlinkedserver stored procedure to create the linked server. Specify Microsoft.Jet.OLEDB.4.0 as the provider_name, and specify the full pathname of the Microsoft Access .mdb database file as the data_source. The data_source is evaluated on the server, not the client, and the path must be valid on the server.Execute the sp_addlinkedsrvlogin stored procedure to create login mappings from local logins to Microsoft Access logins.
Steps to Query Secured Microsoft Access DatabaseModify the registry key shown in step 1 of the “More Information” section and add the location of your .mdw file.Start Microsoft Visual Basic 6.0 and select a Standard EXE project. Form1 is created by default.On the Project menu, select References, and set a reference to the Microsoft ActiveX Data Objects 2.1 Library or later.Place two command buttons and a DataGrid control on Form1 (named Command1, Command2 and DataGrid1 respectively).Paste the following code into the Declarations section of Form1:
Note You must change User ID=<User ID> and password=<Strong Password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

Dim adorst As New ADODB.RecordsetDim adoconn As New ADODB.ConnectionPrivate Sub Command1_Click()Dim strConn As Stringadoconn.Open “Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=master;User Id=<User ID>;Password=<Strong Password>;”adoconn.Execute “EXEC sp_addlinkedserver ‘SecuredJetLS’, ‘Jet 4.0′, ‘Microsoft.Jet.OLEDB.4.0′, ‘c:\…..\MyDatabase.mdb’”adoconn.Execute “EXEC sp_addlinkedsrvlogin ‘SecuredJetLS’, FALSE, NULL, ‘UserName’, ‘Password’”adoconn.CloseMsgBox “Successful Setup”End SubPrivate Sub Command2_Click()Dim SQL As String’ Using OpenQuery syntax.SQL = ” Select a.* from OPENQUERY(SecuredJetLS, ‘Select * from MyTable’) a”‘ Using OpenRowset syntax.’ SQL = “SELECT * From OpenRowset(‘Microsoft.Jet.OLEDB.4.0′,’c:\….\MyDatabase.mdb’; ‘UserName’;'Password’, MyTable)”‘ Using four-part name syntax.’ SQL = “Select * from SecuredJetLS…MyTable”adoconn.CursorLocation = adUseClientadoconn.Open “Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=master;User Id=<User ID>;Password=<Strong Password>;”adorst.Open SQL, adoconn, adOpenStatic, adLockReadOnlySet DataGrid1.DataSource = adorstEnd SubPrivate Sub Form_Load()Command1.Caption = “Setup Linked Server”Command2.Caption = “Query Linked Server”End SubPrivate Sub Form_Unload(Cancel As Integer)adorst.CloseSet adorst = Nothingadoconn.CloseSet adoconn = NothingEnd SubRun the project.Click Setup Linked Server. If you modify sp_addlinkedserver and sp_addlinkedsrvlogin in the connection string with the correct parameters, the linked server is created successfully.Click Query Linked Server. If you modify the connection string and the query text to the correct parameters, the DataGrid control is populated with your data.

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
This article describes how to attach and create Querydefs on external ODBCtables. The method for opening external ODBC tables is to attach the tablesto an .mdb file.
Jet does not support named QueryDefs on a non-attached ODBC database. A non-attached ODBC database is one that is opened directly with the OpenDatabasemethod of the WorkSpace object without the use of an .mdb file.
If it is not appropriate for the application to attach the ODBC tables, itis possible to create Querydefs with no name to accomplish the procedure.
For additional information, please see the following article in theMicrosoft Knowledge Base:
149055?(http://support.microsoft.com/kb/149055/EN-US/): Jet Doesn’t Support QueryDefs on a Non-Attached ODBC Table
Resolution
The following is information from “Guide To Data Access Objects,” Chapter7, Data Access Choices, that explains this procedure:
In many cases, attaching a table to access external data is faster thanopening a table directly, especially if the data is located in an ODBCdatabase. In Visual Basic version 4.0, SQL Passthrough is used to queryattached ODBC databases directly. Consider attaching external tables ratherthan opening them directly. Using external data in an ODBC databaserequires opening the external tables directly so performance issignificantly slower when using the data.
Sample ProgramThe following example describes how to attach to and create a Querydef onan ODBC table using a “DSN-less” ODBC connection. With this procedure, itis not necessary to set up a DSN with the ODBC Admin utility.
Start a new project in Visual Basic. Form1 is created by default.Add three Command buttons to Form1: Command1, Command2, Command3 bydefault.Paste the following code in the General Declarations section of Form1:

Dim db As DatabaseDim cn As StringPrivate Sub Form_Load()cn = “odbc;driver={SQL Server};server=myserver;” & _”database=pubs;uid=myuid;pwd=mypwd”If Dir(“mydb.mdb”) <> “” Then’ database exists, so just open it.Set db = OpenDatabase(Name:=”mydb”, Exclusive:=False, _ReadOnly:=False, Connect:=”")Else’database does not exist, create it and attach authors table.Set db = CreateDatabase(Name:=”mydb”, Connect:=dbLangGeneral, _Option:=dbVersion30)Dim td As TableDefSet td = db.CreateTableDef()td.Name = “Authors”td.SourceTableName = “Authors”td.Connect = cnEnd IfEnd SubPrivate Sub Command1_Click()Dim qd As QueryDefOn Error Resume NextSet qd = db.QueryDefs(“abc”)’ test for existence of querydef.If Error > 0 ThenSet qd = db.CreateQueryDef(Name:=”abc”)qd.Connect = cnqd.SQL = “Select @@Version” ‘native SQL ServerEnd IfSet qd = db.QueryDefs(“xyz”)’ test for existence of querydef.If Error > 0 ThenSet qd = db.CreateQueryDef(Name:=”xyz”)qd.Connect = cnqd.SQL = “Select * from titles” ‘ generic SQL.End IfOn Error GoTo 0End SubPrivate Sub Command2_Click()Dim rs As RecordsetDim qd As QueryDefSet qd = db.QueryDefs(“abc”)Set rs = qd.OpenRecordset()Call displayResults(rs)End SubPrivate Sub Command3_Click()Dim rs As RecordsetDim qd As QueryDefSet qd = db.QueryDefs(“xyz”)Set rs = qd.OpenRecordset()Call displayResults(rs)End SubSub displayResults(rs As Recordset)Dim f As Field, s As String, i As IntegerFor Each f In rs.Fieldss = s & f.NameNext fDebug.Print s’ print column headers.While Not rs.EOF And i < 5s = “”For Each f In rs.Fieldss = s & f.ValueNext fDebug.Print s’ print first 5 rows.rs.MoveNexti = i + 1WendEnd Sub NOTE: You need to change the DRIVER, SERVER, DATABASE, UID, and PWDparameters in the OpenConnection method. Also you must modify the SQLstatements contained in the Command1_Click event to match your SQL datasource.
Press the F5 key to start the program.Click the Command1 button to create the Querydefs. Click the Command2and Command3 buttons to execute the Querydefs. Note that the first fiverows of data appear in the Debug window.

Symptoms
ADO and ODBC do not support nested transactions. However, native Oracle SQLsupports the SAVEPOINT keyword that can be used to simulate nestedtransactions.
Resolution
The Microsoft Knowledge Base article 177138?(http://support.microsoft.com/kb/177138/EN-US/), entitled “INFO: NestedTransactions Not Available in ODBC/OLE DB/ADO” says this about nestedtransactions:
“Neither Open Database Connectivity (ODBC, nor any released MicrosoftOLE DB Provider supports Nested Transactions. ActiveX Data Objects (ADO)supports the feature, but only if the underlying provider exposes it.Currently none of Microsoft’s OLE DB providers support NestedTransactions.”
This is true for the Microsoft ODBC for Oracle driver. However, by usingthe SAVEPOINT keyword, you can simulate Nested Transactions. For moreinformation about native ODBC or ADO support for Nested Transactions,please see the article mentioned above.
The SAVEPOINT keyword basically sets a bookmark for uncommitted statementsin an Oracle session. You can rollback these statements by using the TOoption with the ROLLBACK statement. This all has to be done through Executestatements (such as in the form of <connection>.Execute) because the ODBCparser cannot parse the SAVEPOINT keyword properly.
The following code shows how this all works:

Conn = “UID=****;PWD=****;DRIVER={Microsoft ODBC for Oracle};” _& “SERVER=SamOracle;”Set Cn = New ADODB.ConnectionWith Cn.ConnectionString = Conn.CursorLocation = adUseClient.OpenEnd WithCn.BeginTransCn.Execute “SAVEPOINT ALPHA”Cn.Execute “INSERT INTO trantest VALUES(1,10)”Cn.Execute “INSERT INTO trantest VALUES(2,10)”Cn.Execute “SAVEPOINT BETA”Cn.Execute “INSERT INTO trantest VALUES(3,10)”Cn.Execute “INSERT INTO trantest VALUES(4,10)”Cn.Execute “ROLLBACK TO SAVEPOINT BETA”Cn.Execute “COMMIT”Cn.RollbackTrans NOTE: This assumes a table “Trantest” exists on the Oracle server.
This code will commit the first two INSERT statements and rollback thesecond two. You will notice that the whole set of statements is surroundedwith a CONNECTION level BeginTrans and CommitTrans. This is necessary sothat, at the ODBC API level, the SQLSetConnectOption SQL_AUTOCOMMIT is setto SQL_AUTOCOMMIT_OFF. After you have finished your transaction it is agood idea to re-set SQL_AUTOCOMMIT to SQL_AUTOCOMMIT_ON (the default) byexecuting either a CommitTrans or a RollbackTrans. Because you have eithercommitted or rolled backed your transactions with your Execute statements,it doesn’t matter whether you call CommitTrans or RollbackTrans; eitherway, they have nothing to commit or rollback. You are just calling thesefunctions to reset SQL_AUTOCOMMIT to SQL_AUTOCOMMIT_ON, which they both do.