Visual Basic Q&A

Symptoms
Microsoft Visual Basic ships with an add-in utility known as the Visual Data Manager (VISDATA) for which source code is also provided in the form of a sample project named Visdata.vbp. Visdata.vbp is installed with other Visual Basic samples as part of the MSDN Library installation.
This article describes how to modify the source code for the Visual Data Manager add-in in order to use the add-in with Microsoft Access 2000 format database files.

Resolution
Adapting VISDATA to work with Microsoft Access 2000 format databases requires changing only one project reference, adding one menu item, and adding one line of code to process the new menu item.
Update the DAO Reference from DAO 3.51 to DAO 3.60. (This step alone is sufficient to allow you to open and work with Access 2000 files.)Open Visdata.vbp in the Visual Basic design environment.On the Project menu, open the list of References.Deselect the existing reference to Microsoft Data Access Objects 3.51, and then locate and select Microsoft Data Access Objects 3.60. If you are using Visual Basic 6.0, be sure to move the DAO 3.6 reference above the ADO library reference in the list.Add a new menu item and code to allow creating a new Access 2000 database.Open the main form, frmMDI, in the design environment.From the Tools menu, open the Menu Editor.In the Menu Editor window, scroll down in the existing menu items to File / New / Microsoft Access.In order to insert the new menu item after the existing entry for Access Version 7.0 MDB files, select the following entry: dBase. Click the Insert button, and then click the RIGHT ARROW button to make the new, blank entry a subitem under the Microsoft Access heading. Enter Access 2000 as the Caption and mnuDBNMDB2K as the Name. Close the Menu Editor by clicking OK.Open the frmMDI code module and select the mnuDBNMDB2K_Click event procedure in the drop-down list. Enter the following line of code:

NewMDB dbVersion40In Visual Basic 6.0 only, update ADO connection strings to support Access 2000.VisData uses both DAO and ADO. Use the Search function on the Visual Basic Edit menu to locate all references in the project to the Microsoft Jet OLE DB Provider version 3.51 and update 3.51 to 4.0.In Visual Basic 6.0 only, add code that will enable the Grid toolbar button, which displays a table of records in a DBGrid control rather than one record per form.Open the module modVisData and find the OpenTable procedure.After the lines:

If gsDataType = gsMSACCESS Then.ConnectionString = “PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=” & gsDBName insert the additional line:

.CursorLocation = adUseClient Then open frmDataGrid and find the Form_Load event procedure.After the line:

Set datDataCtl.Recordset = mrsFormRecordset insert the additional lines:

datDataCtl.RefreshSet grdDataGrid.DataSource = datDataCtlgrdDataGrid.Refresh If you were to omit this step, you might see an empty grid; or the grid may display with the very first cell empty.Recompile the VISDATA project and copy the compiled executable to the Visual Basic directory (which is \Program Files\Microsoft Visual Studio\Vb98 by default in Visual Basic 6.0).

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
This article describes how to get more information on the ODBC Call Failederror. When errors occur using ODBC databases, Visual Basic will provide an”ODBC Call Failed” error message. This generic error message provides nospecific detail so you must cycle through the Errors collection to getadditional information. Below is a code sample that shows the difference inbehavior.
Resolution
The DBEngine has an Errors collection that can be manipulated by the FOR-EACH construct. The JET Engine can store multiple errors in the DBEngineErrors collection. In Visual Basic 3.0, it was possible to parse the stringusing the routine shown on Page 175 of the Visual Basic 4.0 ProfessionalFeatures Book under the “Guide to Data Access Objects” section. The #symbol was used to separate the “ODBC Call Failed” message from thedetailed ODBC description in Visual Basic 3.0. However, this is notnecessary under Visual Basic versions 4.0 and 5.0.
For the example below, a two-field table called MyTable has been set up onan ODBC Source and a primary key set on the ID Field. Two records have beenadded as below:

FieldIDDescription===============================Record 11HelloRecord 22World
The code below will generate an error by trying to add a record with aduplicate primary key value to test the code:
Start a new Standard EXE project. Form1 is added by default.Add a CommandButton to Form1.Add the following code to the General Declarations section of Form1:

Option ExplicitPrivate Sub Command1_Click()Dim db As DatabaseDim rs As RecordsetOn Error GoTo trapSet db = OpenDatabase(“”)Set rs = db.OpenRecordset(“Select * from MyTable”)rs.AddNewrs.Fields(0).Value = 2rs.UpdateExit Subtrap:MsgBox Errors.CountMsgBox Err.Number & ” ” & Err.DescriptionEnd Sub Press the F5 key to run the project. Click on the CommandButton and youshould receive error 3146, “ODBC Call Failed.” Although the Error countis greater than one, only one message will be displayed.Remove the code from within the error trap and replace it with oneof the following error handlers:

‘ DAO Error HandlerDim MyError As ErrorMsgBox Errors.CountFor Each MyError In DBEngine.ErrorsWith MyErrorMsgBox .Number & ” ” & .DescriptionEnd WithNext MyError’ RDO Error HandlerDim MyError As rdoErrorMsgBox rdoErrors.CountFor Each MyError In rdoEngine.rdoErrorsWith MyErrorMsgBox .Number & ” ” & .DescriptionEnd WithNext MyError Press the F5 key to run the project. You should see a detailed messageand then the 3146 Error for “ODBC Call Failed.”

Symptoms
This article describes how to use Data Access Objects (DAO) to callMicrosoft SQL stored procedures from Microsoft Visual Basic for Windows. Astored procedure is a precompiled collection of SQL statements, oftenincluding control-of-flow language.
NOTE: This article assumes you already know how to open an ODBC database.
Resolution
The method of calling depends on whether the SQL stored procedure returnsrecords or not. For example:
Stored procedures that do not return records (or rows) can be executedfrom Visual Basic with the Execute method in Visual Basic as follows:

MyDb.Execute “sp_name”, dbSQLPassThroughi = MyDb.RowsAffectedYou can also use ExecuteSQL:

i = MyDb.ExecuteSQL(“sp_name”)However, this syntax is obsolete, and you should replace it with theExecute method and RowsAffected property syntax given at the beginningof this section.
The Execute (and ExecuteSQL) method runs the stored procedure sp_name.The RowsAffected property returns the number of rows the storedprocedure affected. This method is strictly for action queries such as:

Delete Authors where name like “fred%”Using Execute with an SQL statement that uses “SELECT…” returnsrecords that causes a run-time error.Stored procedures that return records (or rows) require a Snapshot-typeRecordset to capture the values. Listed below are two examples:
Example 1The following example using a Data Control on a Visual Basic Form:

Data1.Options = dbSQLPassThroughData1.Recordsource = “sp_name”‘ Name of the stored procedure.Data1.Refresh’ Refresh the data control.When you use the SQLPassThrough bit, the Microsoft Jet database engineignores the syntax used and passes the command through to the SQLserver.
Example 2Using Data Access Objects:

Dim Rs as Recordset’ Open your desired database here.Set MyDB = DBEngine.Workspaces(0).OpenDatabase(…Set Rs = MyDB.OpenRecordset(“sp_name”, dbOpenSnapshot, _dbSQLPassThrough) You must use dbOpenSnapshot. dbOpenDynaset and dbOpenTable do notapply to pass-through queries.
How to Pass Parameters to a Stored ProcedureTo pass parameters, include them after the name of the stored procedurein a string. For example:

‘ String specifying SQL.SQL = “My_StorProc parm1, parm2, parm3″…’ For a stored procedure that doesn’t return records.MyDb.Execute SQL, dbSQLPassThroughi = MyDb.RowsAffected…’For a stored procedure that returns records.set Rs = MyDB.OpenRecordset(SQL, dbOpenSnapshot, dbSQLPassThrough) The object variable (Rs) contains the first set of results from thestored procedure (My_StorProc).
Another ExampleThe following contains more example code showing both methods:

Dim db as DatabaseDim l as LongDim Rs as RecordsetSet Db = DBEngine.Workspaces(0).OpenDatabase _(“”, False, False, “ODBC;dsn=yourdsn;uid=youruid;pwd=yourpwd:”)’ For SPs that don’t return rows.Db.Execute “YourSP_Name”, dbSQLPassThroughl = Db.RowsAffected’ For SPs that return rows.Set Rs = Db.OpenRecordset(“YourSP_Name”, dbOpenSnapshot, _dbSQLPassThrough)Col1.text = Rs(0) ‘ Column one.Col2.text = Rs!ColumnNameCol3.Text = Rs(“ColumnName”)

Symptoms
When you use the DbSQLPassThrough constant in Data Access Objects (DAO) version 3.51, if the text is exactly 256 characters long, the last character is truncated and replaced with a “0″ character. The same behavior also occurs if the text is exactly 511, 766, or 1,021 characters (and so on).
Resolution
This error occurs because of UNICODE conversion problems that are located in the DAO/Jet SQLPassThrough engine.

Symptoms
The number of connections opened on SQL Server increases indefinitely if a Microsoft Data Access Objects (DAO) recordset is opened and closed repeatedly when you use DAO version 3.6. This does not happen if you use DAO version 3.51. You may see this behavior if you use sqlpassthrough in which the connection string is specified in the OpenDatabase statement.
Resolution
Use a Querydef to create the connection instead of creating the connection in the OpenDatabase statement.