Symptoms
Microsoft Jet versions 3.51 and 4.0 provide a Paradox ISAM driver that allows access to Paradox 4.x tables. However, the Paradox ISAM driver does not support the BINARY BLOB (type B) Paradox 4.x data type. Thus, fields that use this data type will not be available in Visual Basic.
Resolution
This behavior is by design.
Symptoms
When you use ODBCDirect with Visual Basic, you receive the following error on accessing a recordset object that was created inside of a transaction that has been committed or rolled back:
3670 Cursor is not valid
Resolution
This error occurs because you are using server-side cursors on your connection and the cursor is being closed when the transaction is committed or rolled back. Whether a server-side cursor is closed on a transaction commit or rollback depends on the database driver that you are using. For the SQL Server driver, the default is to close the server-side cursor on the commit or rollback of a transaction.
Symptoms
In systems that do not have MDAC 2.1 installed on them, when you use the FoxPro ODBC ISAM Driver (named “Microsoft FoxProDriver(*.dbf)” in the ODBC Administrator) for 2.5 or 2.6 tables, it allowsyou to view the contents of a table created in Visual FoxPro. However, whenyou try to update or add new records, the following error message mightappear:
Operation must use an Updateable Query.
Resolution
The older ISAM driver is not able to accurately read the table header ofthe Visual FoxPro table. Visual FoxPro supplies additional information inthe header of the table for links to a database container, which the olderISAM ODBC driver does not accommodate. Snapshot recordsets are updateableas long as the table is in 2.5 or 2.6 format.
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
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.