بعد أن أثير أكثر من مرة تساؤلات فى قسم الاكسس عن الفرق بين كائنات الوصول للبيانات المختلفة ، لم أجد أفضل من مقدمة  هذا الكتاب لاعطاء فكرة عن ال ADO , DAO , &RDO

 اسم الكتاب : برمجة ADO

من سلسلة ميكروسوفت برس

Microsoft - Press

الكاتب : ديفيد سكيبا Davis Sceppa

الناشر للنسخة العربية : الدار العربية للعلوم

ترجمة : مركز التعريب والبرمجة ASC

 مقدمة الكتاب

Where Are We and How Did We Get Here?

During the past five years, Microsoft has promoted Data Access Objects (DAO), and then Remote Data Objects (RDO), and now ActiveX Data Objects (ADO) as the primary data access technology for Visual Basic developers. It seems that Microsoft has been pushing a different data access technology with each successive version of Microsoft Visual Studio. Today, new versions of ADO are available on Microsoft's Web site and ship with other products and technologies, such as Microsoft Windows 2000, Microsoft Windows NT 4 Service Packs, Microsoft Internet Explorer versions 3 and later, Microsoft SQL Server 6.5 Service Pack 5 and SQL Server 7, Microsoft Office 2000, and even Microsoft Expedia Streets & Trips 2000.

One of the goals of ADO is to simplify data access. ADO is built upon some fairly complex technologies—OLE DB and ODBC (open database connectivity)—and is designed to allow you to programmatically access and modify data stored in a wide variety of databases. This broad reach is a departure from previous data access technologies. For the sake of comparison, let's take a quick glance at ADO's predecessors: DAO and RDO.

أين نحن الأن و كيف وصلنا الى هنا ؟

       خلال السنوات الخمس الماضية ، روجت مايكروسوفت كائنات الوصول الى بيانات ( ADO )

من ثم كائنات البيانات البعيدة ( RDO) و الأن كائنات البيانات أكتيف إكس (ADO) كتقنية الوصول الإساسية الى البيانات من أجل مطوري  فيجوال بيسك . يبدو أن مايكروسوفت كانت تدفع باتجاه تقنية وصول الى البيانات مختلفة مع كل إصدار لاحق من مايكرو سوفت فيجوال ستديو . اليوم يوجد  هناك إصدارات حديثة من ADO متاحة على موقع الوب لمايكروسوفت و تتنقل مع منتجات و تقنيات أخرى ، مثل مايكرو سوفت ويندوز 2000، مايكرو سوفت ويندوز أن تى 4 سيرفيس باك ، مايكروسوفت إنترنت إكسبلورر 3 و مابعده ، مايكروسوفت SQL سيرفر 6.5   سيرفس باك 5  و SQL سيرفر 7 ، مايكرو سوفت أوفيس .

Data Access Objects

DAO was originally designed to interact with Microsoft Access databases. Although you can use DAO to access SQL Server and Oracle databases, many developers complain about DAO's performance with these large database systems. Others complain that DAO doesn't permit programmers to access some of the richer, more powerful features of SQL Server and Oracle, such as output and return parameters on stored procedures.

One of my coworkers likes to say that using DAO to work with an Oracle database is like performing brain surgery on yourself…without anesthetics…while wearing oven mitts. Extreme? Yes—but he does have a point. DAO is tuned to work with desktop databases, not client/server databases. Frustrated by DAO's performance and access limitations, developers who wanted to work with SQL Server and Oracle databases generally sought other options.

كائنات الوصول الى بيانات DAO

       لقد تم تصميم DAO بالأساس من أجل التفاعل مع قواعد بيانات مايكروسوفت أكسس . على الرغم من أنك تستطيع استخدام DAO للوصول الى قواعد بيانات SQL سيرفر وأورا كل ، فإن العديد من المطورين يستاءون من أداء DAO مع قواعد البيانات الضخمة هذه ، فيما يشتكي البعض الآخر من أن DAO لا تسمح للمبرمجين بالوصول الى مزايا أغنى و أقوى لكل من SQL سيرفر أورا كل مثل الخرج و البارامترات المعادة فى الإجرائيات المخزنة .

       يقول أحد زملائى أن استخدام DAO للعمل مع قاعدة بيانات أوراكل هو مثل أداء جراحة دماغية لنفسك ... بدون مخدر ... و أنت تلبس قفازات بيسبول . رأى متطرف ؟ و لكن لدية حق . لقد ضبط DAO للعمل مع قواعد البيانات المكتبية ، و ليس قواعد البيانات مستضاف / ملقم . مع كل هذه الصعوبات و مع مشاكل DAO و محدوديات الوصول يفكر المطورون الذين يريدون العمل مع قواعد بيانات SQL سيرفر وأوراكل عادة بخيارات أخرى .

Remote Data Objects

Microsoft provided another option in RDO, which originally released with Visual Basic 4 Enterprise Edition. RDO's object model closely resembles the hierarchy of structures in the ODBC API. Programmers found that RDO provided much faster access to client/server database systems, such as SQL Server and Oracle, than DAO did. Although those familiar with the ODBC API quickly learned how to work with the RDO object model, developers lacking experience with that API, such as those who had been using DAO, found the RDO technology difficult to use.

The object model itself wasn't the problem for most programmers learning RDO: the nuances inherited from the ODBC API posed the greatest obstacles. Suddenly, programmers had to bone up on cursors and bookmarks. They had to learn many of the ins and outs of specific database systems. Does the error message "The connection is busy with results from another hstmt" ring any bells out there? If you try to do the impossible on an ODBC connection to your database, RDO won't save you. Instead, you'll get that error. DAO hid the problem from you by automatically creating another connection to your database to perform the action you requested.

Another challenge that RDO posed for programmers accustomed to writing DAO code was that RDO lacked many of DAO's features, such as sorting, searching, and filtering. Other DAO functionality unavailable in the RDO world includes data definition language (DDL) interfaces to ODBC API functions such as CreateTable and CreateField.

كائنات البيانات البعيدة RDO

      قدمت مايكروسوفت خياراَ أخر مع RDO ، تم إصداره أول الأمر مع الإصدار Enterprise من فيجوال بيسك 4. يشبه نموذج الكائن RDO بشكل كبير هيكلية البنى فى واجهة التداخل ODBC API .

لقد وجد المطورون أن RDO تقدم وصولا أسرع الى أنظمة قواعد البيانات مستضاف / ملقم ، مثا SQL سيرفر و أوركل ، مما تقدمة DAO . على الرغم من أن هؤلاء الذين يعرفون ODBC API تعلموا بسرعة كيف يعملون مع نماذج الكائن RDO ، فإن المطورين عديمى الخبرة بواجهة التداخل هذه ، مثل أولئك الذين كانوا يستخدمون DAO وجدوا أن تقنية RDO صعبة الإستخدام .

إن نموذج الكائن بحد ذاته لم يشكل مشكلة لمعظم المبرمجين الذين يتعلمون RDO : و لكن التشابهات الموروثة من واجهة التداخل ODBC API  هى التى فرضت العقبات الكبرى . فجأة التحدى الأخر الذى أبرزته RDO للمبرمجين المعتادين على كتابة الشيفرة فى DAO هو أن RDO ينقصها العديد من مزايا DAO ، مثل الفرز ، البحث و التصفية . وظيفة أخرى متاحة فى DAO ليست كذلك فى عالم RDO هى واجهات تداخل لغة تعريف البيانات ( DDL ) الى دالات ODBC API مثل Createfield / Create Table .

Best of Both Worlds: ActiveX Data Objects

Programmers clamored for a data access technology that combined the simplicity and relative ease of use of DAO with the speed, power, and control of RDO. Initially introduced as part of the Microsoft Internet Information Server 3 package, ADO was intended to be all things to all people. Of course, such lofty goals are rarely fulfilled.

While the initial release of ADO lacked many of RDO's features, I believe that ADO 2.0 offered comparable functionality. Certain RDO features, such as mixed cursors, have yet to be implemented in ADO, but these features are few and far between. In fact, I'm at a loss to name a single significant feature available in RDO that was not available in ADO 2.0 in one form or another. (I'm sure someone will tell me otherwise; a great way to find such features is to make a statement like that in a book like this.)

With the release of version 2.1, ADO and its supporting libraries began offering nearly all features available in DAO. DDL libraries were added to ADO in version 2.1 to provide functionality similar to functions available with DAO, such as CreateTable, CreateField, and CreateIndex. Microsoft Jet and Replication Objects (JRO) in ADO 2.1 offers much of the Jet-specific functionality available via the DBEngine object in DAO. ADO 2.1 also added functionality to simplify the retrieval of newly generated identity values. ADO 2.5 adds no new functionality to more closely match the capabilities of DAO and RDO, because perhaps the only place where ADO lags behind DAO is in its searching and filtering capabilities.

So ADO has most of the functionality of RDO and DAO as well as many helpful features not available in previous data access technologies. Great. Why, then, can't I get a moment's peace at work? Why are programmers having problems building applications with ADO?

الأفضل من كلا العالمين : كائنات البيانات اكتيف اكس

لقد كان المبرمجون يطالبون بقنية وصول الي البيانات تجمع بين السهولة النسبية لاستخدام DAO  مع السرعة ، القوة و التحكم الذي تقدمه  RDO  بشكل بدائي عندما تم تقديمها كجزء من حزمة ملقم معلومات انترنت 3 لمايكروسوفت كان يراد من ADO   أن تكون كل شيئ لكل الناس . بالطبع فان الأهداف الكبيرة نادرا ما يتم تحقيقها.

فى حين أن الاصدار الأول ل DAO كان ينقصه العديد من مزايا RDO   فانني أعتقد بأن ADO 2.0  قدمت وظائف مماثلة . بعض مزايا RDO مثل المؤشرات الهجينة لم يتم تنفيذها بعد فى ADO  و لكن هذه المزايا هي قليلة و بعيدة بين الاثنين . فى الحقيقة أنا أعج أن أذكر ميزة واحدة متاحة فى RDO  لم تكن متاحة غى ADO 2.0  بشكل أو بآخر.

مع الاصدار 2.1    بدأت ADO  و مكتبات الدعم الملحقة بها بتقديم كل المزايا المتاحة فى DAO  تقريبا. لقد تم اضافة مكتبات DLL  الي ADO  فى الاصدار 2.1  لكي تقدم وظائف مشابهة للدالات فى DAO  مثل Create Table , Create Field , Create index . تقدم الكائنات JRO  

( ( Microsoft Jet and Replication  فى ADO 2.1  الكثير من الوظائف الخاصة ب Jet  المتاحة عبر الكائن  DBEngine  فى DAO  .

لقد قدمت ADO 2.1  أيضا وظائف لتبسيط استخراج قيم الهوية المولدة حديثا . لا تضيف ADO 2.5  وظائف جديدة لكي تطابق بشكل أقرب  أكثر امكانيات DAO  و RDO  لأنه ربما يكون المكان الوحيد الذي تختلف فيه  ADO  عن DAO   هو فى امكانيات البحث و التصفية

لذل تملك ADO  معظم وظائف RDO  و DAO  بالاضافة الي العديد من المزايا المفيدة غير المتاحة فى تقنيات الوصول الي البيانات السابقة

كل هذا عظيم .  و لكن لماذا لا نستطيع العمل بسلام ؟ لماذا يواجه المبرمجون مشاكل فى بناء التطبيقات مع ADO؟

Difficulties Using ADO

First of all, ADO is still a fairly new technology. The Microsoft Data Access SDK documentation that shipped with Visual Studio 6 (as part of MSDN Library Visual Studio 6) was for many programmers the primary source of information about ADO. Unfortunately, the information in the Data Access SDK is more reference-based than instructive—lacking samples, best practice information, and in-depth discussions about how the technology works.

One of the many reasons for the lack of detail in the documentation is that products are being released on Internet time. The team developing the data access components has been in "ship mode" for the past two years. Since these components are now included with many different Microsoft products—Internet Information Server 3 and 4, Internet Information Services 5, Visual Studio 6, SQL Server 7, Internet Explorer 4 and 5, Office 2000, and Windows 2000—the developers don't always have control over their own ship schedule. As with most products and teams under constant stress, something has to give, and in this case it's been the documentation. The development team has been operating in "ship now, document later" mode for too long.

Another reason programmers are having problems with ADO lies in its flexibility. While this flexibility is generally a strength, it has posed a challenge to the documentation team and been a source of confusion to programmers, because there is no "one way" to do things. For example, ADO offers many different ways to get data from your database into a Recordset object, yet much of the early documentation focused on only one of those methods; unfortunately, that method returned read-only data, not data that could be modified. For anyone who still hasn't been able to get an updatable Recordset object—we'll cover that topic in Chapter 4.

Lastly, building a multiuser database application with ADO, or with any other data access technology, isn't easy. Although tools such as Visual Basic 6 Data Environment Designer can save you a great deal of time, you should be wary of anyone who tells you that you can build a multiuser database application without having to write any code.

In many ways, ADO behaves like DAO in that it contains features that hide certain complexities from the programmer. I mentioned an RDO error message earlier—"The connection is busy with results from another hstmt"—that occurs when you try to use a connection that's already busy. ADO simplifies the programmer's task by establishing another connection rather than returning an error such as that. However, it's important to know what's going on behind the scenes and why: you wouldn't want to unwittingly create an application that establishes 8 or 10 connections per instance of your application. This book will discuss this and other such features in depth so that you don't stumble across them accidentally or employ them inappropriately.

مشاكل استخدام ADO

 قبل كل شيئ ما تزال ADO  تقنية حديثة نسبيا. إن مستندات Microsoft Data Access SDK التي تأتي مع فيجوال استوديو 6  (كجزء من مكتبة MSDN لفيجوال ستوديو 6 ) كانت بالنسبة لمعظم المبرمجين المصدر الأساسي للمعلومات حول ADO  . لسوء الحظ كانت المعلومات فى Data Access SDK مرجعية أكثر من كونها تعليمية – ينقصها الأمثلة ، معلومات عن أفضل الطرق و نقاش عميق حول كيفية عمل التقنية.

أحد الأسباب العديدة لنقص التفاصيل فى المستندات هو أن المنتجات يتم إصدارها فى عصر الانترنت . لقد كان الفريق الذي يطور مكونات البيانات فى نمط الانتقال طوال العامين الماضيين. بما أن هذه المكونات موجودة اآن مع العديد من منتجات ميكروسوفت المختلفة – ملقم معلومات الانترنت 3و4 ، خدمات معلومات الانترنت 5 ، فيجوال استوديو 6 ، SQL  سيرفر 7 ، انترنت اكسبلورر 4 و 5 ، أوفيس 2000 و ويندوز 2000، فان المطورين لا يتحكمون دوما بجدول الانتقال الخاص بهم كما هو الحال مع معظم المنتجات و الفرق التي تعمل تحت ضغط مستمر يجب أن تضحي بشيء ما و فى هذه الحالة كانت المستندات. لقد كان فريق التطوير يهمل فى نمط انقل الآن ، وثق لاحقا" و ذلك علي المدي البعيد .

سبب آخر للمشاكل التي يواحهها المبرمجون مع ADO  يكمن فىمرونتها . مع أن المرونة هي عادة قوة فلقد فرضت تحديا علي فريق التوثيق و أصبحت مصدرا للارباك بالنسبة للمبرمجين لأنه لا يوجد طريقة واحدة للقيام بالأشياء ، علي سبيل المثال تقدم  ADO  طرقا مختلفة عديدة  للحصول علي البيانات من قاعدة البيانات فى كائن Recordset  ، مع أن كثير من المستندات الأولية ركزت علي طريقة و احدة فقط من هذه الطرق فإن هذه الطريقة كانت تعيد بيانات مقرؤة فقط ، أي بيانات لا يمكن تعديلهابالنسبة لأي شخص لم يحصل بعد علي كائن Recordset  قابل للتحديث .

و أخيرا فان بناء تطبيق قاعدة البيانات متعدد المستخدمين مع ADO  أو مع أي تقنية أخري للوصول الي البيانات ليس سهلا . علي ارغم من أن أدوات مثل مصمم بيئة البيانات فى فيجوال بيزيك 6 يمكن أن توفر الكثير من الوقت ، يجب أن تحذر من أي شخص يقول لك بأنك تستطيع أن تبني تطبيق قاعدة بيانات متعدد المستخدمين بدون أن تكتب أي شيفرة.

فى العدديد من الأشكال تتصرف ADO  مثل DAO   فى أنها تحتوي مزايا تخفي تعقيدات معينة عن المبرمج – لقد ذكرت رسالة خطأ RDO  سابقا " الوصلة مشغولة بنتائج HSTMT آخر " تحصل عندما تحاول استخدام وصلة مشغولة مسبقا.

تبسط ADO  مهمة المبرمج عن طريق اقامة وصلة أخري عوضا عن اعادة خطأ مثل هذا. علي كل حال من المهم ان تعرف ماذا يحصل خلف الكواليس و لماذا : أنت قد لا ترغب فى أن تنشيئ تطبيق يقيم ثمان أو عشر وصلات من أجل كل مثيل للتطبيق بشكل غير مسئوول . يبحث هذا الكتاب ههذا المشكلة و غيرها من المزايا بعمق بحيث لا تمر عليها بشكل عرضي أو تستخدمها بشكل غير مناسب.

Codes For Handling Access Database ( DAO & ADO )

(From Access 2000 Client/Server Book )

Option Compare Database
Option Explicit

Sub CreateTableDAO() 'DAO Version
Dim dbsLocal As Database
Dim tdfLocal As TableDef
Dim fldLocal As Field
Dim idxLocal As Index

Set dbsLocal = CurrentDb() ' Create new TableDef.
' Add field to Table Definition
Set tdfLocal = dbsLocal.CreateTableDef("tblFoods")
Set fldLocal = tdfLocal.CreateField("FoodID", DB_TEXT, 5)
tdfLocal.Fields.Append fldLocal

Set fldLocal = tdfLocal.CreateField("Description", DB_TEXT, 25)
tdfLocal.Fields.Append fldLocal

Set fldLocal = tdfLocal.CreateField("Calories", DB_INTEGER)
tdfLocal.Fields.Append fldLocal
dbsLocal.TableDefs.Append tdfLocal

' Designate the FoodID field as the Primary Key Index
Set idxLocal = tdfLocal.CreateIndex("PrimaryKey")
Set fldLocal = idxLocal.CreateField("FoodID")
idxLocal.Primary = True
idxLocal.Unique = True
idxLocal.Fields.Append fldLocal

' Add the index to the Indexes collection
tdfLocal.Indexes.Append idxLocal
End Sub

Sub CreateTableADO() ' ADO Version
Dim cnnLocal As ADODB.Connection
Dim cmdLocal As ADODB.Command

' Create Connection Object and open it on CHAP28.MDB
Set cnnLocal = New ADODB.Connection
cnnLocal.ConnectionString = "dsn=Chap28;"
cnnLocal.Open

Set cmdLocal = New ADODB.Command
Set cmdLocal.ActiveConnection = cnnLocal
cmdLocal.CommandText = "CREATE TABLE tblFoods " _
& "(FoodID TEXT (5), Description TEXT (25), Calories INTEGER);"
cmdLocal.Execute

cmdLocal.CommandText = "CREATE UNIQUE INDEX PrimaryKey " _
& "ON tblFoods(FoodID) " _
& "WITH PRIMARY DISALLOW NULL;"
cmdLocal.Execute
End Sub

Sub DeleteTableDAO() ' DAO Version
Dim dbsLocal As Database

Set dbsLocal = CurrentDb
dbsLocal.TableDefs.Delete "tblFoods"
End Sub

Public Sub DeleteTableADO() ' ADO Version
Dim cnnLocal As ADODB.Connection
Dim cmdLocal As ADODB.Command

' Create Connection Object and open it on CHAP28.MDB
Set cnnLocal = New ADODB.Connection

cnnLocal.ConnectionString = "dsn=Chap28;"
cnnLocal.Open

Set cmdLocal = New ADODB.Command
Set cmdLocal.ActiveConnection = cnnLocal
cmdLocal.CommandText = "DROP TABLE tblFoods"
cmdLocal.Execute
End Sub

Sub CreateRelationDAO()
Dim dbsLocal As Database
Dim relLocal As Relation
Dim fldLocal As Field

Set dbsLocal = CurrentDb
Set relLocal = dbsLocal.CreateRelation()
With relLocal
.Name = "PeopleFood"
.Table = "tblFoods"
.ForeignTable = "tblPeople"
.Attributes = dbRelationDeleteCascade
End With
Set fldLocal = relLocal.CreateField("FoodID")
fldLocal.ForeignName = "FoodID"
relLocal.Fields.Append fldLocal
dbsLocal.Relations.Append relLocal
End Sub

Sub CreateQueryDAO()
Dim dbsLocal As Database
Dim qdfLocal As QueryDef
Dim strSQL As String

Set dbsLocal = CurrentDb
Set qdfLocal = dbsLocal.CreateQueryDef("qryBigProjects")
strSQL = "Select ProjectID, ProjectName, ProjectTotalEstimate " _
& "From tblProjects " _
& "Where ProjectTotalEstimate >= 30000"
qdfLocal.SQL = strSQL
End Sub


Sub IncreaseEstimateDAO() ' DAO Version
Dim dbsLocal As Database
Dim rstProjects As Recordset
Dim strSQL As String
Dim intUpdated As Integer

Set dbsLocal = CurrentDb()
Set rstProjects = dbsLocal.OpenRecordset("tblProjectsChange", dbOpenDynaset)
strSQL = "ProjectTotalEstimate < 30000"
intUpdated = 0
rstProjects.FindFirst strSQL
Do While Not rstProjects.NoMatch
intUpdated = intUpdated + 1
rstProjects.Edit
rstProjects.Fields("ProjectTotalEstimate") = _
rstProjects.Fields("ProjectTotalEstimate") * 1.1
rstProjects.Update
rstProjects.FindNext strSQL
Loop
Debug.Print intUpdated & " Records Updated"
rstProjects.Close
End Sub

Sub IncreaseEstimateADO() ' ADO Version
Dim cnnLocal As ADODB.Connection
Dim cmdLocal As ADODB.Command
Dim rstLocal As ADODB.Recordset
Dim intUpdated As Integer

' Create Connection Object and open it on CHAP28.MDB
Set cnnLocal = New ADODB.Connection

cnnLocal.ConnectionString = "dsn=Chap28;"
cnnLocal.Open

Set cmdLocal = New ADODB.Command
Set cmdLocal.ActiveConnection = cnnLocal
cmdLocal.CommandText = "SELECT * FROM tblProjectsChange " _
& "WHERE ProjectTotalEstimate < 30000"
rstLocal.CursorType = adOpenForwardOnly
Set rstLocal = cmdLocal.Execute()

intUpdated = 0
Do While Not rstLocal.EOF
intUpdated = intUpdated + 1
rstLocal.Fields("ProjectTotalEstimate") = _
rstLocal.Fields("ProjectTotalEstimate") * 1.1
rstLocal.Update
rstLocal.MoveNext
Loop
Debug.Print intUpdated & " Records Updated"
rstLocal.Close
End Sub

Sub RunUpdateQueryDAO() ' DAO Version
Dim dbsLocal As Database
Dim qdfLocal As QueryDef

Set dbsLocal = CurrentDb
Set qdfLocal = dbsLocal.QueryDefs("qryIncreaseTotalEstimate")
qdfLocal.Execute
End Sub

Sub RunUpdateQueryADO() ' ADO Version
Dim cnnLocal As ADODB.Connection
Dim cmdLocal As ADODB.Command

' Create Connection Object and open it on CHAP28.MDB
Set cnnLocal = New ADODB.Connection
cnnLocal.ConnectionString = "dsn=Chap28;"
cnnLocal.Open

Set cmdLocal = New ADODB.Command
Set cmdLocal.ActiveConnection = cnnLocal
cmdLocal.CommandText = "UPDATE tblProjectsChange " _
& "SET ProjectTotalEstimate = 30000 " _
& "WHERE ProjectTotalEstimate < 30000;"
cmdLocal.Execute
End Sub

Sub DeleteCusts(lngProjEst As Long)
Dim dbsLocal As Database
Dim rstProjects As Recordset
Dim intCounter As Integer

Set dbsLocal = CurrentDb
Set rstProjects = dbsLocal.OpenRecordset("tblProjectsChange", dbOpenDynaset)
intCounter = 0
Do While Not rstProjects.EOF
If rstProjects.Fields("ProjectTotalEstimate") < lngProjEst Then
rstProjects.Delete
intCounter = intCounter + 1
End If
rstProjects.MoveNext
Loop
Debug.Print intCounter & " Customer Records Deleted"
End Sub

Private Sub cmdAddRecordDAO_Click() ' DAO Recordset
Dim dbsLocal As Database
Dim rstProject As Recordset

Set dbsLocal = CurrentDb()
Set rstProject = dbsLocal.OpenRecordset("tblProjectsChange", DB_OPEN_DYNASET)
With rstProject
.AddNew
.Fields("ProjectName") = Me!txtProjectName
.Fields("ProjectDescription") = Me!txtProjectDescription
.Fields("[ClientID]") = Me!cboClientID
.Update
End With
Me!txtProjectID = rstProject!ProjectID
End Sub


Private Sub cmdAddRecordADO_Click() ' ADO Version
Dim cnnLocal As ADODB.Connection
Dim cmdLocal As ADODB.Command
Dim rstLocal As ADODB.Recordset

' Create Connection Object and open it on CHAP28.MDB
Set cnnLocal = New ADODB.Connection

cnnLocal.ConnectionString = "dsn=Chap28;"
cnnLocal.Open

Set cmdLocal = New ADODB.Command
Set cmdLocal.ActiveConnection = cnnLocal
cmdLocal.CommandText = "SELECT * FROM tblProjectsChange"
rstLocal.CursorType = adOpenDynamic
Set rstLocal = cmdLocal.Execute()

With rstLocal
.AddNew
.Fields("ProjectName") = Me!txtProjectName
.Fields("ProjectDescription") = Me!txtProjectDescription
.Fields("[ClientID]") = Me!cboClientID
.Update
End With
Me!txtProjectID = rstLocal.Fields("ProjectID")
End Sub

Private Sub cmdLastModified_Click()
Dim dbsLocal As Database
Dim rstProject As Recordset
Set dbsLocal = CurrentDb()
Set rstProject = dbsLocal.OpenRecordset("tblProjectsChange", DB_OPEN_DYNASET)
With rstProject
.AddNew
.Fields("!ProjectName") = Me!txtProjectName
.Fields("ProjectDescription") = Me!txtProjectDescription
.Fields("[ClientID]") = Me!cboClientID
.Update
.Bookmark = rstProject.LastModified
End With
Me!txtProjectID = rstProject!ProjectID
End Sub

Sub SortRecordsetDAO() ' DAO Version
Dim dbsLocal As Database
Dim rstTimeCardHours As Recordset

Set dbsLocal = CurrentDb
Set rstTimeCardHours = _
dbsLocal.OpenRecordset("tblTimeCardHours", dbOpenDynaset)
Debug.Print "NOT Sorted!!!"
Do While Not rstTimeCardHours.EOF
Debug.Print rstTimeCardHours![DateWorked]
rstTimeCardHours.MoveNext
Loop
Debug.Print "Now Sorted!!!"
rstTimeCardHours.Sort = "[DateWorked]"
Set rstTimeCardHours = rstTimeCardHours.OpenRecordset
Do While Not rstTimeCardHours.EOF
Debug.Print rstTimeCardHours.Fields("DateWorked")
rstTimeCardHours.MoveNext
Loop
End Sub

Sub SortRecordsetADO() ' ADO Version
Dim cnnLocal As ADODB.Connection
Dim cmdLocal As ADODB.Command
Dim rstLocal As ADODB.Recordset

' Create Connection Object and open it on CHAP28.MDB
Set cnnLocal = New ADODB.Connection
cnnLocal.ConnectionString = "dsn=Chap28;"
cnnLocal.Open

Set cmdLocal = New ADODB.Command
Set cmdLocal.ActiveConnection = cnnLocal

cmdLocal.CommandText = "SELECT * FROM tblTimeCardHours"
cmdLocal.Execute
rstLocal.CursorType = adOpenForwardOnly
Set rstLocal = cmdLocal.Execute()
Debug.Print "NOT Sorted!!!"
Do While Not rstLocal.EOF
Debug.Print rstLocal.Fields("DateWorked")
rstLocal.MoveNext
Loop

cmdLocal.CommandText = "SELECT * FROM tblTimeCardHours " & _
"ORDER BY DateWorked"
cmdLocal.Execute
rstLocal.CursorType = adOpenForwardOnly
Set rstLocal = cmdLocal.Execute()
Debug.Print "Now Sorted!!!"
Do While Not rstLocal.EOF
Debug.Print rstLocal.Fields("DateWorked")
rstLocal.MoveNext
Loop
End Sub


Sub FilterRecordSet()
Dim dbsLocal As Database
Dim rstTimeCardHours As Recordset

Set dbsLocal = CurrentDb
Set rstTimeCardHours = _
dbsLocal.OpenRecordset("tblTimeCardHours", dbOpenDynaset)
Debug.Print "Without Filter"
Do While Not rstTimeCardHours.EOF
Debug.Print rstTimeCardHours![DateWorked]
rstTimeCardHours.MoveNext
Loop
rstTimeCardHours.Filter = "[DateWorked] Between #1/1/95# and #1/5/95#"
Debug.Print "With Filter"
Set rstTimeCardHours = rstTimeCardHours.OpenRecordset
Do While Not rstTimeCardHours.EOF
Debug.Print rstTimeCardHours.Fields("DateWorked")
rstTimeCardHours.MoveNext
Loop
End Sub

Sub SeekProjectDAO(lngProjectID As Long)
Dim dbsLocal As Database
Dim rstProjects As Recordset

Set dbsLocal = CurrentDb()
Set rstProjects = dbsLocal.OpenRecordset("tblProjects", dbOpenTable)
rstProjects.Index = "PrimaryKey"
rstProjects.Seek "=", lngProjectID
If rstProjects.NoMatch Then
MsgBox lngProjectID & " Not Found"
Else
MsgBox lngProjectID & " Found"
End If
End Sub

Sub FindProjectDAO(lngValue As Long)
Dim dbsLocal As Database
Dim rstProjects As Recordset
Dim strSQL As String

Set dbsLocal = CurrentDb()
Set rstProjects = dbsLocal.OpenRecordset("tblProjects", dbOpenDynaset)
strSQL = "[ProjectID] = " & lngValue
rstProjects.FindFirst strSQL
If rstProjects.NoMatch Then
MsgBox lngValue & " Not Found"
Else
MsgBox lngValue & " Found"
End If
End Sub

Sub RunParameterQueryDAO(datStart As Date, _
datEnd As Date) ' DAO Version
Dim dbsLocal As Database
Dim qdfLocal As QueryDef
Dim rstLocal As Recordset

Set dbsLocal = CurrentDb
Set qdfLocal = dbsLocal.QueryDefs("qryBillAmountByClient")
qdfLocal.Parameters("Please Enter Start Date") = datStart
qdfLocal.Parameters("Please Enter End Date") = datEnd
Set rstLocal = qdfLocal.OpenRecordset
Do While Not rstLocal.EOF
Debug.Print rstLocal.Fields("CompanyName"), _
rstLocal.Fields("BillAmount")
rs.MoveNext
Loop
End Sub

Sub RunParameterQueryADO(datStart As Date, datEnd As Date)
Dim cnnLocal As ADODB.Connection
Dim cmdLocal As ADODB.Command
Dim rstLocal As ADODB.Recordset
Dim prsLocal As ADODB.Parameters
Dim prmLocal As ADODB.Parameter

' Create Connection Object and open it on CHAP28.MDB
Set cnnLocal = New ADODB.Connection
cnnLocal.ConnectionString = "dsn=Chap28;"
cnnLocal.Open

Set cmdLocal = New ADODB.Command
Set cmdLocal.ActiveConnection = cnnLocal

cmdLocal.CommandText = "SELECT * FROM tblBillAmountByClient " & _
"ORDER BY StartDate Where StartDate > ? And EndDate < ?"

Set prmLocal = cmdLocal.CreateParameter("StartDate", adDate, adParamInput)
prmLocal.Value = datStart
cmdLocal.Parameters.Append prmLocal
Set prmLocal = cmdLocal.CreateParameter("EndDate", adDate, adParamInput)
prmLocal.Value = datEnd
cmdLocal.Parameters.Append prmLocal
Set prmLocal = Nothing

rstLocal.CursorType = adOpenForwardOnly
Set rstLocal = cmdLocal.Execute()
Do While Not rstLocal.EOF
Debug.Print rstLocal.Fields("CompanyName"), rstLocal.Fields("BillAmount")
rstLocal.MoveNext
Loop
End Sub


Sub EnumerateDBs()
Dim wksLocal As Workspace
Dim dbsLocal As Database
Dim dbsLocal1 As Database
Dim dbsLocal2 As Database

Set wksLocal = DBEngine(0)
Set dbsLocal1 = CurrentDb
Set dbsLocal2 = ws.OpenDatabase("Nwind.MDB")
For Each dbsLocal In wksLocal.Databases
Debug.Print dbsLocal.Name
Next dbsLocal
End Sub

Sub EnumerateTables()
Dim dbsLocal As Database
Dim tblLocal As TableDef

Set dbsLocal = CurrentDb
For Each tblLocal In dbsLocal.TableDefs
Debug.Print tblLocal.Name
Next tblLocal
End Sub

Sub EnumerateQueries()
Dim dbsLocal As Database
Dim qryLocal As QueryDef

Set dbsLocal = CurrentDb
For Each qryLocal In dbsLocal.QueryDefs
Debug.Print qryLocal.Name
Debug.Print qryLocal.SQL
Next qryLocal
End Sub

Sub EnumFields()
Dim dbsLocal As Database
Dim tblLocal As TableDef
Dim fldLocal As Field
Set dbsLocal = CurrentDb
For Each tblLocal In dbsLocal.TableDefs
For Each fldLocal In tblLocal.Fields
Debug.Print fldLocal.Name
Debug.Print fldLocal.Type
Next fldLocal
Next tblLocal
End Sub

Sub EnumerateParameters()
Dim dbsLocal As Database
Dim qryLocal As QueryDef
Dim prmLocal As Parameter

Set dbsLocal = CurrentDb
For Each qryLocal In dbsLocal.QueryDefs
Debug.Print "*****" & qryLocal.Name & "*****"
For Each prmLocal In qryLocal.Parameters
Debug.Print prmLocal.Name
Next prmLocal
Next qryLocal
End Sub

Sub EnumRelations()
Dim dbsLocal As Database
Dim relLocal As Relation

Set dbsLocal = CurrentDb
For Each relLocal In dbsLocal.Relations
Debug.Print relLocal.Table & " Related To: " & relLocal.ForeignTable
Next relLocal
End Sub

Sub EnumContainers()
Dim dbsLocal As Database
Dim cntLocal As Container

Set dbsLocal = CurrentDb
For Each cntLocal In dbsLocal.Containers
Debug.Print cntLocal.Name
Next cntLocal
End Sub

Sub EnumerateForms()
Dim dbsLocal As Database
Dim cntLocal As Container
Dim docLocal As Document

Set dbsLocal = CurrentDb
Set cntLocal = dbsLocal.Containers!Forms
For Each docLocal In cntLocal.Documents
Debug.Print docLocal.Name
Next docLocal
End Sub

Sub EnumerateProperties()
Dim dbsLocal As Database
Dim cntLocal As Container
Dim docLocal As Document
Dim prpLocal As Property
Set dbsLocal = CurrentDb
Set cntLocal = dbsLocal.Containers!Forms
For Each docLocal In cntLocal.Documents
Debug.Print docLocal.Name
For Each prpLocal In docLocal.Properties
Debug.Print prpLocal.Name & " = " & prpLocal.Value
Next prpLocal
Next docLocal
End Sub

Sub ReferToCurrentDB()
Dim wksLocal As Workspace
Dim dbsLocal As Database

Set wksLocal = DBEngine(0)
Set dbsLocal = wksLocal.OpenDatabase("Nwind.mdb")
Debug.Print dbsLocal.Version
End Sub

Sub UseCurrentDBFunc()
Dim dbsLocal As Database
Set dbsLocal = CurrentDb()
Debug.Print dbsLocal.Version
End Sub

Sub OpenTable()
Dim dbsInfo As Database
Dim rstClients As Recordset

Set dbsInfo = CurrentDb()
Set rstClients = dbsInfo.OpenRecordset("tblClients")
Debug.Print rstClients.Updatable
End Sub

Sub OpenDynaSet()
Dim dbsInfo As Database
Dim rstClients As Recordset

Set dbsInfo = CurrentDb()
Set rstClients = dbsInfo.OpenRecordset("tblClients", dbOpenDynaset)
Debug.Print rstClients.Updatable
End Sub

Sub OpenQuery()
Dim dbsInfo As Database
Dim rstClients As Recordset

Set dbsInfo = CurrentDb()
Set rstClients = dbsInfo.OpenRecordset("qryHoursByProject", dbOpenSnapshot)
Debug.Print rstClients.Updatable
End Sub

Sub OpenQuery2(strQryParam As String)
Dim dbsInfo As Database
Dim rstEmployees As Recordset

Set dbsInfo = CurrentDb()
Set rstEmployees = _
dbsInfo.OpenRecordset("Select * From tblStaff " & _
"Where [Employee] Like '" & _
strQueryParam & "'", dbOpenSnapshot)
Debug.Print rstEmployees.Updatable
End Sub

Sub OpenRecordsetArgs()
Dim dbsLocal As Database
Dim rstLocal As Recordset

Set dbsLocal = CurrentDb
Set rstLocal = _
dbsLocal.OpenRecordset("tblProjects", dbOpenDynaset, dbReadOnly)
Debug.Print rstLocal.Updatable
End Sub

Sub RecordsetMovements()
Dim dbsLocal As Database
Dim rstLocal As Recordset

Set dbsLocal = CurrentDb
Set rstLocal = dbsLocal.OpenRecordset("tblProjects", dbOpenDynaset)

Debug.Print rstLocal!ProjectID
rstLocal.MoveNext

Debug.Print rstLocal!ProjectID
rstLocal.MoveLast

Debug.Print rstLocal!ProjectID
rstLocal.MovePrevious

Debug.Print rstLocal!ProjectID
rstLocal.MoveFirst

Debug.Print rstLocal!ProjectID
rstLocal.Close
End Sub

Sub FindRstLimits()
Dim dbsLocal As Database
Dim rstClients As Recordset

Set dbsLocal = CurrentDb()
Set rstClients = dbsLocal.OpenRecordset("tblClients", dbOpenSnapshot)
Do While Not rstClients.EOF
Debug.Print rstClients![ClientID]
rstClients.MoveNext
Loop
rstClients.Close
End Sub

Sub CountRecords()
Dim dbsLocal As Database
Dim rstProjects As Recordset

Set dbsLocal = CurrentDb()
Set rstProjects = dbsLocal.OpenRecordset("tblProjects", dbOpenSnapshot)

Debug.Print rstProjects.RecordCount ' Prints 0 Or 1
rstProjects.MoveLast
Debug.Print rstProjects.RecordCount ' Prints an accurate record Count
rstProjects.Close
End Sub

Sub FindPosition(lngValue As Long)
Dim dbsLocal As Database
Dim rstProjects As Recordset
Dim strSQL As String

Set dbsLocal = CurrentDb()
Set rstProjects = dbsLocal.OpenRecordset("tblProjects", dbOpenDynaset)
strSQL = "[ProjectID] = " & lngValue
rstProjects.FindFirst strSQL
If rstProjects.NoMatch Then
MsgBox lngValue & " Not Found"
Else
Debug.Print rstProjects.AbsolutePosition
End If
End Sub

Sub UseBookMark()
Dim dbsLocal As Database
Dim rstProjects As Recordset
Dim strSQL As String
Dim vntPosition As Variant

Set dbsLocal = CurrentDb()
Set rstProjects = dbsLocal.OpenRecordset("tblProjects", dbOpenDynaset)
vntPosition = rstProjects.Bookmark
Do Until rstProjects.EOF
Debug.Print rstProjects!ProjectID
rstProjects.MoveNext
Loop
rstProjects.Bookmark = vntPosition
Debug.Print rstProjects!ProjectID
End Sub

Private Sub cmdFindClient_Click()
Me.RecordsetClone.FindFirst "ClientID = " & Me!txtClientID
If Me.RecordsetClone.NoMatch Then
MsgBox Me!txtClientID & " Not Found"
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub
Sub ListAllDBObjects()
Dim dbsLocal As Database
Dim conLocal As Container
Dim docLocal As Document

Set dbsLocal = CurrentDb
For Each conLocal In dbsLocal.Containers
Debug.Print "*** " & conLocal.Name & " ***"
For Each docLocal In conLocal.Documents
Debug.Print docLocal.Name
Next docLocal
Next conLocal
End Sub

Public Sub PropsAndAttribs()
Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim fldLoop As ADODB.Field
Dim proLoop As ADODB.Property
Dim strCnn As String

' Open connection and recordset.
strCnn = "driver={SQL Server};" & _
"server=HomeServ;uid=lmk;pwd=pwd;database=ServSamp"

cnn1.Open strCnn
Set rstEmployees = New ADODB.Recordset
rstEmployees.Open "employee", cnn1, , , adCmdTable

' Display the attributes of the Employee table's properties.
Debug.Print "Property attributes:"
For Each proLoop In rstEmployees.Properties
Debug.Print " " & proLoop.Name & " = " & _
proLoop.Attributes
Next proLoop
rstEmployees.Close
cnn1.Close
End Sub

Public Sub OpenRecordsetADO()
Dim cnn1 As Connection
Dim rstEmployees As Recordset
Dim strCnn As String

' Open connection.
strCnn = "driver={SQL Server};" & _
"server=HomeServ;uid=lmk;pwd=pwd;database=ServSamp"
Set cnn1 = New Connection
cnn1.Open strCnn

' Open employee table.
Set rstEmployees = New Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "employee", cnn1, , , adCmdTable
End Sub