Visual Basic Q&A

Symptoms
With Microsoft Visual Basic versions 4.0, 5.0, and 6.0 for Windows, you canspecify your ODBC (Open Database Connectivity) driver and server in yourconnect string when using RDO (Remote Data Object) and DAO (Data AccessObjects) which eliminates the need to set up a DSN (Data Source Name). Wecall this a “DSN- Less” ODBC connection because you do not need to set up aDSN in order to access your ODBC database server.
To do this, you specify a “driver=” and “server=” parameter in your connectstring as in the following example.
Note You must change Username= <username> and PWD =<strong password> to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database.

cnstr = “driver={SQL Server};server=myserver;” & _”database=mydb;Username=<username>;PWD=<strong password>;dsn=;”Set cn = en.OpenConnection(“”, False, False, cnstr)
NOTE: The driver name must be surrounded by curly brackets. For example:”{SQL Server}.”
(CAUTION: DSN-Less connections will not work in Visual Basic 4.0 16-bit. Ifyou try to use them you will get a General Protection Fault in moduleODBC.DLL at 0006:080F.)
Resolution
In Microsoft Visual Basic version 3.0 for Windows, you had to create a DSNthat added an extra step when distributing your application because eachworkstation had to have the DSN created in order to access the specifiedserver and database. This was done either manually with the ODBC Adminutility, through code with the RegisterDatabase function, or through codewith the SQLConfigDatasource API function. For additional information onhow to do this setup manually, please see the following articles in theMicrosoft Knowledge Base:
123008?(http://support.microsoft.com/kb/123008/EN-US/)TITLE: How to Set Up ODBC Data Sources When Distributing an App
126940?(http://support.microsoft.com/kb/126940/EN-US/): RegisterDatabase Fails After ODBC Version 2.x Installed
132329?(http://support.microsoft.com/kb/132329/EN-US/): RegisterDatabase Method Does Not Modify ODBC.INI File
Sample ProgramThe following RDO example uses a “DSN-less” ODBC connection so you do notneed to set up a DSN with the ODBC Admin utility beforehand.
Start a new project in Visual Basic. Form1 is created by default.Add a command button to Form1, Command1 by default.Paste the following code into the General Declarations section of Form1.
Note You must change Username= <username> and PWD =<strong password> to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database.

Dim en As rdoEnvironmentDim cn As rdoConnectionPrivate Sub Form_Load()MousePointer = vbHourglassDim strConnect As String’ Change the next line to reflect your driver and server.strConnect = “driver={SQL Server};server=jonfo5;” & _”database=pubs;Username=<username>;PWD=<strong password>;”Set en = rdoEngine.rdoEnvironments(0)Set cn = en.OpenConnection( _dsName:=”", _Prompt:=rdDriverNoPrompt, _ReadOnly:=False, _Connect:=strConnect)cn.QueryTimeout = 600MousePointer = vbNormalEnd SubPrivate Sub Command1_Click()MousePointer = vbHourglassDim rs As rdoResultsetSet rs = cn.OpenResultset(Name:=”Select * from authors”, _Type:=rdOpenForwardOnly, _LockType:=rdConcurReadOnly, _Options:=rdExecDirect)Debug.Print rs(0), rs(1), rs(2)MousePointer = vbNormalEnd Sub Note that you must change your DRIVER, SERVER, DATABASE, UID, and PWDparameters in the OpenConnection method. You also need to modify the SQLstatement contained in the Command1_Click event to match your own SQLdata source.Check the Microsoft Remote Data Object in the Project References.Start the program or press the F5 key.Click the Command1 button to create a rdoResultset and display the firstrow of data in the debug window.

Symptoms
With Microsoft Visual Basic versions listed above, you can specify yourODBC driver and server in your connect string when using RDO (Remote DataObjects) and DAO (Data Access Objects). This eliminates the need to set upa DSN (Data Source Name). This is called a “DSN-Less” ODBC connectionbecause you do not need to set up a DSN in order to access your ODBCdatabase server.
To do this, you specify a “driver=” parameter in your connect property.The following three examples show how this is done with the SQL Server,Access, and Oracle ODBC drivers:

‘Microsoft SQL Server ODBC Driver examplecnstr = “driver={SQL Server};server=myserver;” & _”database=pubs;uid=<username>;pwd=<strong password>”cn.Connect = cnstr’Microsoft Access ODBC Driver example (version 2.x)cnstr = “Driver={Microsoft Access Driver (*.mdb)};” & _”Dbq=c:\program files\devstudio\vb\biblio.mdb;” & _”Uid=Admin; Pwd=”cn.Connect = cnstr’Microsoft ODBC Driver for Oracle examplecnstr = “Driver={Microsoft ODBC Driver for Oracle};” & _”Server=OracleServer.world; Uid=demo; Pwd=demo”‘ Note that 1.0 version of the MicrosoftOracle driver used’ “ConnectString” notation instead of “Server”
NOTE: The driver name must be surrounded by curly brackets. For example:
“{SQL Server}”
The following information is taken from Visual Basic Books Online:
The connect string contains a series of semi-colon-delimitedarguments as defined by the ODBC interface – including theODBC driver itself. That is, all ODBC drivers have specificargument requirements so you should consult the documentationincluded with the driver for specific information. Thisconnect string is passed to the ODBC API SQLDriverConnectfunction along with the hEnv for the associated rdoEnvironmentobject.
Resolution
If you do want to set up a DSN, you can use the following methods:
Manually with the ODBC Admin utility(Odbcad32.exe).Through code with the RDO rdoRegisterDataSource method.Through code with the DAO RegisterDatabase method.Through code with the ODBC API SQLConfigDatasource API function.
Sample ProgramThe following RDO example uses a “DSN-less” ODBC connection so you do notneed to set up a DSN with the ODBC Admin utility beforehand.
Start a new project in Visual Basic. Form1 is created by default.Add a CommandButton to Form1, Command1 by default.Paste the following code into the code window of Form1.
Note You must change UID =<username> and PWD =<strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.

Private Sub Command1_Click()Dim Cn As New rdoConnection’creatable rdoConnectionDim Qr As New rdoQuery’creatable rdoQueryDim Rs As rdoResultset’pointer to rdoResultsetDim cnstr As String’hold connection infocnstr = “driver={SQL Server};server=myserver;” & _”database=pubs;uid=<username>;pwd=<strong password>”Cn.Connect = cnstrCn.CursorDriver = rdUseClientBatchCn.EstablishConnection Prompt:=rdDriverNoPromptSet Qr.ActiveConnection = CnQr.SQL = “Select * From Authors”Set Rs = Qr.OpenResultset(Type:=rdOpenKeyset, _LockType:=rdConcurBatch)Debug.Print Rs(0), Rs(1), Rs(2)End Sub Note that you must change your DRIVER, SERVER, DATABASE, UID, and PWDparameters in the Connect method. You also need to modify the SQLstatement contained in the Command1_Click event to match your own SQLdata source.Start the program or press the F5 key.Click the Command1 button to create an rdoResultset and display thefirst row of data in the debug window.

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.

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
If you attempt to execute a SQL INSERT statement that contains a literalstring having over 16,379 characters with the Microsoft ODBC Driver forAccess, the following error message occurs:

[Microsoft][ODBC Microsoft Access 97 Driver]
Syntax error in INSERT INTO statement.SQL UPDATE SQL statements with literal strings over 16,379 bytes also failwith a similar error.
Resolution
The SQL parser for the Microsoft ODBC Driver for Access prior to 4.0.4202 does not accept string literals larger than 16,379 bytes. This is the defined limit for literal strings in a SQL statement for the Microsoft ODBC Driver for Access.
With the Microsoft ODBC Driver for Access versions 4.0.4202 and later, you can execute a SQL INSERT containing a string literal having over 16,379 characters. However, you will get the following error message with a SQL INSERT that contains a literal string having over 516,076 characters:

[Microsoft][ODBC Microsoft Access Driver]
Query is too complexThis error occurs with both Access 97 and Access 2000 databases.