How To Use “DSN-Less” ODBC Connections with RDO and DAO
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.
Description of the Jet 4.0 Database Engine hotfix package for Windows XP SP2, Windows Server 2003 SP1, Windows Server 2003 SP2, Windows Vista, Windows Vista SP1, and Windows Server 2008: October …
Symptoms
This article describes the Microsoft Jet 4.0 Database Engine issues that are fixed in the Jet 4.0 Database Engine hotfix package that is dated October 23, 2007.
Resolution
Issues that the hotfix package fixesThis hotfix package fixes the following issue: The original release of this hotfix introduced an issue that also affects Windows Vista Service Pack 1 (SP1) and Windows Server 2008. Inserts and updates may fail in C++ Microsoft Jet DAO MFC (CDAO) applications after you install Windows Vista SP1 or Windows Server 2008. You may receive the following error message:
The field is too small to accept the amount of data that you tried to add. Try inserting or pasting less data. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
952337?(http://support.microsoft.com/kb/952337/) Insertions and updates in C++ Microsoft Jet DAO MFC applications may not work on a computer that is running Windows Vista Service Pack 1 or Windows Server 2008
This hotfix package fixes the following issues that were not previously documented in a Microsoft Knowledge Base article:When you use an Access database or a Jet database that contains tables that are linked to an Oracle 10g database server, a query may return unexpected results. This issue can occur when the query includes a complex SQL statement that has one or more nested JOIN statements. This issue can occur if the Oracle server was upgraded from an earlier version and the Access table links or Jet table links have not been refreshed after the upgrade.When you use a Microsoft Jet 4.0 database, you may receive an error message that resembles the following:
ApplicationName has encountered a problem and needs to close. We are sorry for the inconvenience. If you were in the middle of something, the information you were working on might be lost. Please tell Microsoft about this problem. We have created an error report that you can send to help us improve ApplicationName. We will treat this report as confidential and anonymous. To see what data this error report contains, click here. When you view the data that the error report contains, you see the following list that contains information about some common error signatures. This information may vary depending on the version of Microsoft Office Access or on the application that you use as the front end for the Jet 4.0 database engine.
Collapse this tableExpand this table
Application nameApplication versionModule nameModule versionOffsetMsaccess.exe10.0.4302.0 Msjet40.dll4.0.6218.0 00021eb3 Msaccess.exe10.0.2627.1 Msjet40.dll4.0.6218.000021eb3 Msaccess.exe10.0.2627.1 Msjet40.dll4.0.2927.17 00021ac2Msaccess.exe11.0.6566.0 Msjet40.dll4.0.9025.000021f23 Msaccess.exe11.0.6566.0 Msjet40.dll4.0.8618.0000221f3Msaccess.exe11.0.5614.0 Msjet40.dll4.0.8618.0000221f3Msaccess.exe11.0.6355.0 Msjet40.dll4.0.8618.0000221f3Msaccess.exe11.0.6501.0 Msjet40.dll4.0.8618.0000221f3Msaccess.exe10.0.2627.1 Msjet40.dll4.0.8618.0000221f3When you use a Microsoft Jet 4.0 database, you may receive an error message that resembles the following:
ApplicationName has encountered a problem and needs to close. We are sorry for the inconvenience. If you were in the middle of something, the information you were working on might be lost. Please tell Microsoft about this problem. We have created an error report that you can send to help us improve ApplicationName. We will treat this report as confidential and anonymous. To see what data this error report contains, click here. When you view the data that the error report contains, you see the following list that contains information about some common error signatures. This information may vary depending on the version of Microsoft Office Access or on the application that you use as the front end for the Jet 4.0 database engine.
Collapse this tableExpand this table
Application nameApplication versionModule nameModule versionOffsetMsaccess.exe 11.0.5614.0Msjet40.dll4.0.8618.000044d29Msaccess.exe11.0.6566.0 Msjet40.dll4.0.8618.000044d29Msaccess.exe10.0.6501.0Msjet40.dll4.0.8618.000044d29Msaccess.exe11.0.5614.0 Msjet40.dll4.0.8618.000044d29Msaccess.exe11.0.6355.0 Msjet40.dll4.0.8618.000044d29Msaccess.exe10.0.2627.1 Msjet40.dll4.0.8618.000044d29When you use a Microsoft Jet 4.0 database, you may receive an error message that resembles the following:
ApplicationName has encountered a problem and needs to close. We are sorry for the inconvenience. If you were in the middle of something, the information you were working on might be lost. Please tell Microsoft about this problem. We have created an error report that you can send to help us improve ApplicationName. We will treat this report as confidential and anonymous. To see what data this error report contains, click here. When you view the data that the error report contains, you see the following list that contains information about some common error signatures. This information may vary depending on the version of Microsoft Office Access or on the application that you use as the front end for the Jet 4.0 database engine.
Collapse this tableExpand this table
Application NameApplication versionModule nameModule versionOffsetIexplore.exe 6.0.2800.1106 Dao360.dll 3.60.8618.0 00002c5e Iexplore.exe 6.0.2900.2180 Dao360.dll 3.60.8618.0 00002c5e Iexplore.exe 6.0.2800.1106 Dao360.dll 3.60.8618.0 00002c0c Iexplore.exe 6.0.2900.2180 Dao360.dll 3.60.8618.0 00002bc9 Msaccess.exe11.0.6355.0 hungapp0.0.0.000000000Msaccess.exe11.0.6566.0 Dao360.dll 3.60.8618.0 00002c5e When you use a Microsoft Jet 4.0 database, you may receive an error message that resembles the following:
ApplicationName has encountered a problem and needs to close. We are sorry for the inconvenience. If you were in the middle of something, the information you were working on might be lost. Please tell Microsoft about this problem. We have created an error report that you can send to help us improve ApplicationName. We will treat this report as confidential and anonymous. To see what data this error report contains, click here. When you view the data that the error report contains, you see the following list that contains information about some common error signatures. This information may vary depending on the version of Microsoft Office Access or on the application that you use as the front end for the Jet 4.0 database engine.
Collapse this tableExpand this table
Application NameApplication versionModule nameModule versionOffsetMsaccess.exe11.0.6566.0 Msjet40.dll4.0.9635.00002f736Msaccess.exe10.0.4302.0 Msjet40.dll4.0.6218.000041681 Msaccess.exe10.0.2627.1 Msjet40.dll4.0.6218.000041681 Msaccess.exe10.0.4302.0 Msjet40.dll4.0.7328.00004234b Msaccess.exe11.0.6566.0 Msjet40.dll4.0.9025.040eaec65 Msaccess.exe10.0.2627.1 Msjet40.dll4.0.4431.3000416d1 When you use a Microsoft Jet 4.0 database, you may receive an error message that resembles the following:
ApplicationName has encountered a problem and needs to close. We are sorry for the inconvenience. If you were in the middle of something, the information you were working on might be lost. Please tell Microsoft about this problem. We have created an error report that you can send to help us improve ApplicationName. We will treat this report as confidential and anonymous. To see what data this error report contains, click here. When you view the data that the error report contains, you see the following list that contains information about some common error signatures. This information may vary depending on the version of Microsoft Office Access or on the application that you use as the front end for the Jet 4.0 database engine.
Collapse this tableExpand this table
Application NameApplication versionModule nameModule versionOffsetMsaccess.exe11.0.6355.0 Msjet40.dll4.0.8618.0000193b8 Msaccess.exe11.0.6566.0 Msjet40.dll4.0.8618.0000193b8 Msaccess.exe11.0.5614.0 Msjet40.dll4.0.8618.0000193b8 Msaccess.exe11.0.6355.0 Msjet40.dll4.0.8618.0000193b8 Msaccess.exe11.0.6501.0 Msjet40.dll4.0.8618.0000193b8 Msaccess.exe10.0.2627.1Msjet40.dll4.0.8618.0000193b8 Msaccess.exe10.0.6771.0Msjet40.dll4.0.8618.0000221f3 Msaccess.exe10.0.4302.0 Msjet40.dll4.0.8618.0000221f3
What are the main advantages of binary serialization?
An object is stored in a file, a database or even in the memory. However, data to be transferred over a network needs to be in a linear form for which serialization and deserialization are used.
Advantage of serialization is the ability of an object to be serialized into a persistent or a non-persistent storage media and then reconstructing the same object later by de-serializing the object.
Also Binary Serialization is faster, supports complex objects too with read only properties and even circular references.
.NET interview: What are the main advantages of binary serialization
An object is stored in a file, a database or even in the memory. However, data to be transferred over a network needs to be in a linear form for which serialization and deserialization are used.
Advantage of serialization is the ability of an object to be serialized into a persistent or a non-persistent storage media and then reconstructing the same object later by de-serializing the object.
Also Binary Serialization is faster, supports complex objects too with read only properties and even circular references.
Database Interview: Difference between Index defrag and Index rebuild?
When you create an index in the database, the index information used
by queries is stored in index pages. The sequential index pages are
chained together by pointers from one page to the next. When changes
are made to the data that affect the index, the information in the
index can become scattered in the database. Rebuilding an index
reorganizes the storage of the index data (and table data in the case
of a clustered index) to remove fragmentation. This can improve disk
performance by reducing the number of page reads required to obtain
the requested data
DBCC INDEXDEFRAG - Defragments clustered and secondary indexes of the
specified table or view.
Interview Questions: Indexes in SQL, What is Index? It’s purpose?
What is Index? It's purpose?
Indexes in databases are similar to indexes in books. In a database,
an index allows the database program to find data in a table without
scanning
the entire table. An index in a database is a list of values
in a table with the storage locations of rows in the table that
contain each value. Indexes can be created on either a single column
or a combination of columns in a table and are implemented in the form
of B-trees. An index contains an entry with one or more columns (the
search key) from each row in a table. A B-tree is sorted on the search
key, and can be searched efficiently on any leading subset of the
search key. For example, an index on columns A, B, C can be searched
efficiently on A, on A, B, and A, B, C.
What is the difference between ADO and ADO.NET?
ADO uses Recordsets and cursors to access and modify data. Because of its inherent design, Recordset can impact performance on the server side by tying up valuable resources. In addition, COM marshalling - an expensive data conversion process - is needed to transmit a Recordset. ADO.NET addresses three important needs that ADO doesn't address: 
1. Providing a comprehensive disconnected data-access model, which is crucial to the Web environment
2. Providing tight integration with XM
3. Providing seamless integration with the .NET Framework (e.g., compatibility with the base class library's type system). From an ADO.NET implementation perspective, the Recordset object in ADO is eliminated in the .NET architecture. In its place, ADO.NET has several dedicated objects led by the DataSet object and including the DataAdapter, and DataReader objects to perform specific tasks. In addition, ADO.NET DataSets operate in disconnected state whereas the ADO RecordSet objects operated in a fully connected state.
In ADO, the in-memory representation of data is the RecordSet. In ADO.NET, it is the dataset. A RecordSet looks like a single table. If a RecordSet is to contain data from multiple database tables, it must use a JOIN query, which assembles the data from the various database tables into a single result table. In contrast, a dataset is a collection of one or more tables. The tables within a dataset are called data tables; specifically, they are DataTable objects. If a dataset contains data from multiple database tables, it will typically contain multiple DataTable objects. That is, each DataTable object typically corresponds to a single database table or view. In this way, a dataset can mimic the structure of the underlying database.
In ADO you scan sequentially through the rows of the RecordSet using the ADO MoveNext method. In ADO.NET, rows are represented as collections, so you can loop through a table as you would through any collection, or access particular rows via ordinal or primary key index. A cursor is a database element that controls record navigation, the ability to update data, and the visibility of changes made to the database by other users. ADO.NET does not have an inherent cursor object, but instead includes data classes that provide the functionality of a traditional cursor. For example, the functionality of a forward-only, read-only cursor is available in the ADO.NET DataReader object.
There is one significant difference between disconnected processing in ADO and ADO.NET. In ADO you communicate with the database by making calls to an OLE DB provider. In ADO.NET you communicate with the database through a data adapter (an OleDbDataAdapter, SqlDataAdapter, OdbcDataAdapter, or OracleDataAdapter object), which makes calls to an OLE DB provider or the APIs provided by the underlying data source.
Asp.net Interview: Can you explain the difference between an ADO.NET Dataset and an ADO Recordset?
In ADO, the in-memory representation of data is the recordset. In ADO.NET, it is the dataset. There are important differences between them.
-
A recordset looks like a single table. If a recordset is to contain data from multiple database tables, it must use a JOIN query, which assembles the data from the various database tables into a single result table. In contrast, a dataset is a collection of one or more tables. The tables within a dataset are called data tables; specifically, they are DataTable objects. If a dataset contains data from multiple database tables, it will typically contain multiple DataTable objects. That is, each DataTable object typically corresponds to a single database table or view. In this way, a dataset can mimic the structure of the underlying database. A dataset usually also contains relationships. A relationship within a dataset is analogous to a foreign-key relationship in a database —that is, it associates rows of the tables with each other. For example, if a dataset contains a table about investors and another table about each investor’s stock purchases, it could also contain a relationship connecting each row of the investor table with the corresponding rows of the purchase table. Because the dataset can hold multiple, separate tables and maintain information about relationships between them, it can hold much richer data structures than a recordset, including self-relating tables and tables with many-to-many relationships.
-
In ADO you scan sequentially through the rows of the recordset using the ADO MoveNext method. In ADO.NET, rows are represented as collections, so you can loop through a table as you would through any collection, or access particular rows via ordinal or primary key index. DataRelation objects maintain information about master and detail records and provide a method that allows you to get records related to the one you are working with. For example, starting from the row of the Investor table for "Nate Sun," you can navigate to the set of rows of the Purchase table describing his purchases. A cursor is a database element that controls record navigation, the ability to update data, and the visibility of changes made to the database by other users. ADO.NET does not have an inherent cursor object, but instead includes data classes that provide the functionality of a traditional cursor. For example, the functionality of a forward-only, read-only cursor is available in the ADO.NET DataReader object. For more information about cursor functionality, see Data Access Technologies.
-
Minimized Open Connections: In ADO.NET you open connections only long enough to perform a database operation, such as a Select or Update. You can read rows into a dataset and then work with them without staying connected to the data source. In ADO the recordset can provide disconnected access, but ADO is designed primarily for connected access. There is one significant difference between disconnected processing in ADO and ADO.NET. In ADO you communicate with the database by making calls to an OLE DB provider. In ADO.NET you communicate with the database through a data adapter (an OleDbDataAdapter, SqlDataAdapter, OdbcDataAdapter, or OracleDataAdapter object), which makes calls to an OLE DB provider or the APIs provided by the underlying data source. The important difference is that in ADO.NET the data adapter allows you to control how the changes to the dataset are transmitted to the database — by optimizing for performance, performing data validation checks, or adding any other extra processing. Data adapters, data connections, data commands, and data readers are the components that make up a .NET Framework data provider. Microsoft and third-party providers can make available other .NET Framework data providers that can be integrated into Visual Studio.
-
Sharing Data Between Applications. Transmitting an ADO.NET dataset between applications is much easier than transmitting an ADO disconnected recordset. To transmit an ADO disconnected recordset from one component to another, you use COM marshalling. To transmit data in ADO.NET, you use a dataset, which can transmit an XML stream.
-
Richer data types.COM marshalling provides a limited set of data types — those defined by the COM standard. Because the transmission of datasets in ADO.NET is based on an XML format, there is no restriction on data types. Thus, the components sharing the dataset can use whatever rich set of data types they would ordinarily use.
-
Performance. Transmitting a large ADO recordset or a large ADO.NET dataset can consume network resources; as the amount of data grows, the stress placed on the network also rises. Both ADO and ADO.NET let you minimize which data is transmitted. But ADO.NET offers another performance advantage, in that ADO.NET does not require data-type conversions. ADO, which requires COM marshalling to transmit records sets among components, does require that ADO data types be converted to COM data types.
-
Penetrating Firewalls.A firewall can interfere with two components trying to transmit disconnected ADO recordsets. Remember, firewalls are typically configured to allow HTML text to pass, but to prevent system-level requests (such as COM marshalling) from passing.
