Featured

C.L.S.T.I.. Powered by Blogger.
Showing posts with label MS Access. Show all posts
Showing posts with label MS Access. Show all posts

0 Types of Queries

Types of Queries

    1. Select Query – selects a group of records from one or more tables and displays the result in a datasheet where you can update records.
    2. Cross-tab Query – group data into categories and displays values in a compact, spreadsheet-like format with summary totals.
    3. Parameter Query – a query that when run displays a dialog box prompting you for information such as criteria for retrieving records or a value.
    4. Action Query – makes changes to many records in just one operation. There are four types of action queries: make table, delete, append and update.
    5. SQL Query – query you create using an SQL statement.

  1. Select -Select Query
  2. Crosstab -Cross-tab Query
  3. Make Table - Action Query
  4. Append - Action Query
  5. Update - Action Query
  6. Delete - Action Query
  7. Union - SQL Query
  8. Pass-Through - SQL Query
  9. Data Definition - SQL Query
Read more

0 ADO - Delete

Private Sub cmdDelete_Click()
   
    On Error GoTo ERR_MAIN

    Dim objRS As ADODB.Recordset
   
    Set objConn = New ADODB.Connection
    Set objRS = New ADODB.Recordset
   
    ''1 CONNECT TO THE DATA SOURCE
    If Me.chkOracle Then
        objConn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=yti;" & _
                             "User ID=scott;Password=tiger;"
    Else
        objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=C:\JPI\11th Batch Training\dbMSAccess.mdb"
    End If
                
    objConn.Open
   
    ''2 OPEN RECORDSET
    With objRS
       
        .Open "SELECT * FROM EMP WHERE DEPTNO=40", objConn, adOpenKeyset, adLockOptimistic
       
        ''The Recordcount property is used to return the number of records in the recordset.
'        If .RecordCount > 0 Then
       
            ''MoveFirst - Moves the cursor to the first record in the recordset
            .MoveFirst

            Do Until .EOF
               
                ''Set the new salary - To accept the changes
                .Delete
               
                ''MoveNext - Moves the cursor to the next record in the recordset
                .MoveNext
            Loop
           
'        End If
                                   
        ''Close the recordset
        .Close
   
    End With
   
    objConn.Close
    Exit Sub
   
ERR_MAIN:
    MsgBox Err.Number & " : " & Err.Description
End Sub

Private Sub cmdCommit_Click()
    objConn.CommitTrans
    objConn.Close
End Sub

Private Sub cmdRollback_Click()
    objConn.RollbackTrans
    objConn.Close
End Sub
Read more

0 ADO - Update

Private Sub cmdUPDATE_Click()
   
    On Error GoTo ERR_MAIN

    Dim objRS As ADODB.Recordset
   
    Set objConn = New ADODB.Connection
    Set objRS = New ADODB.Recordset
   
    ''1 CONNECT TO THE DATA SOURCE
    If Me.chkOracle Then
        objConn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=yti;" & _
                             "User ID=scott;Password=tiger;"
    Else
        objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=C:\JPI\11th Batch Training\dbMSAccess.mdb"
    End If
                
    objConn.Open
   
    objConn.BeginTrans
   
    ''2 OPEN RECORDSET
    With objRS
       
        .Open "SELECT * FROM EMP WHERE DEPTNO=40", objConn, adOpenKeyset, adLockOptimistic
       
        ''The Recordcount property is used to return the number of records in the recordset.
'        If .RecordCount > 0 Then
       
            ''MoveFirst - Moves the cursor to the first record in the recordset
            .MoveFirst
           
            Do Until .EOF
                ''Set the new salary - To accept the changes
                .Update Array("SAL"), _
                        Array(.Fields("SAL").Value + 500)
               
                ''MoveNext - Moves the cursor to the next record in the recordset
                .MoveNext
            Loop
           
'        End If
           
        ''Close the recordset
        .Close
   
    End With
   
'    objConn.Close
    Exit Sub
   
ERR_MAIN:
    MsgBox Err.Number & " : " & Err.Description
End Sub
Read more

0 ADO - Add

Private Sub cmdADD_Click()
   
    On Error GoTo ERR_MAIN

    Dim objRS As ADODB.Recordset
    Dim intI As Integer
    Dim strFields
    Dim strValues
   
    Set objConn = New ADODB.Connection
    Set objRS = New ADODB.Recordset
   
    ''1 CONNECT TO THE DATA SOURCE
    If Me.chkOracle Then
        objConn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=yti;" & _
                             "User ID=scott;Password=tiger;"
    Else
        objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=C:\JPI\11th Batch Training\dbMSAccess.mdb"
    End If
                
    objConn.Open

    ''2 OPEN RECORDSET
    With objRS
       
        .Open "EMP", objConn, adOpenKeyset, adLockOptimistic
       
        For intI = 1 To 2
            strFields = Array("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO")
            strValues = Array(9997 + intI, "EDMON" & CStr(intI), "ANALYST", 7566, #4/1/2002#, 1000 * intI, 500 * intI, 40)
           
            ''Tell Access that we want to be in "add" mode
            .AddNew strFields, strValues
                        
            ''To accept new record
            .Update
        Next
       
        ''Close the recordset
        .Close
   
    End With
   
    objConn.Close
    Exit Sub
   
ERR_MAIN:
    MsgBox Err.Number & " : " & Err.Description
End Sub
Read more

0 ADO - Open

Option Explicit
Dim objConn As ADODB.Connection

Private Sub cmdOpenEMP_Click()
   
    On Error GoTo ERR_MAIN

    Dim objRS As ADODB.Recordset
   
    Set objConn = New ADODB.Connection
    Set objRS = New ADODB.Recordset
       
    ''1 CONNECT TO THE DATA SOURCE
    If Me.chkOracle Then
        objConn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=yti;" & _
                             "User ID=scott;Password=tiger;"
    Else
        objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=C:\JPI\11th Batch Training\dbMSAccess.mdb"
    End If
   
    objConn.Open
   
    ''2 OPEN RECORDSET
    With objRS
       
        .Open "EMP", objConn
   
        ''The Recordcount property is used to return the number of records in the recordset.
        'If .RecordCount > 0 Then

            ''MoveFirst - Moves the cursor to the first record in the recordset
            .MoveFirst

            ''When the cursor is pointing at the beginning of the file (BOF),
            ''  it is actually set before the first record.
            ''  The BOF property is set to True when the pointer is at BOF.
            ''The EOF is the opposite of the BOF property.
            ''  When the cursor is pointing at the end of the file (EOF),
            ''  it is actually set after the last record.
            ''  The EOF property is set to True when the record pointer is at EOF.
            Do Until .EOF

                ''Display EMPNO and ENAME
                Debug.Print .Fields("EMPNO").Value & " : " & .Fields("ENAME").Value

                ''MoveNext - Moves the cursor to the next record in the recordset
                .MoveNext
            Loop

            ''Close the recordset
            .Close
        'End If
   
    End With
   
    objConn.Close
    Exit Sub
   
ERR_MAIN:
    MsgBox Err.Number & " : " & Err.Description
End Sub
Read more

0 Opening Recordsets from within VBA

Opening Recordsets from within VBA
  1. Using DAO (Data Access Objects)
·         The DBEngine Object
-          The DBEngine object represents the Jet Engine (or ODBCDirect if you are connecting to a remote enterprise server). The DBEngine object is really only used to access Errors collection and the Workspaces collection.

·         The Workspace Collection
-          The Workspace Collection is usually used when Access database has multiple users. Each Workspace object represents a single instance of a user interacting with the database engine.

·         The Database Object
-          The Database object contains five collections
-          Containers – represents the collection of Container objects, each of which contains Documents collection. This collection is used when programmatically managing database security.
-          QueryDefs – represents the collection of currently open recordsets in the database (including internal queries created during table or report creation).
-          Recordset – represents the collection of currently open recordsets in the database.
-          Relations – represents the collection of relationships between tables in the database. This collection is rarely used.
-          TableDefs – represents the collection of table (including system tables).

·         The OpenRecordset Method
-     The Recordset object equal to the return value of the Database object’s
                  OpenRecordset method.


·         Navigating through the Recordset
o    MoveNext – moves the cursor to the next record in the recordset.
o    MovePrevious – moves the cursor to the previous record in the recordset.
o    MoveFirst - moves the cursor to the first record in the recordset.
o    Movelast - moves the cursor to the last record in the recordset.
o    Move <x> - moves the cursor forward x number of records. If x is positive, the cursor position moves forward that many records. If x is negative, the cursor position moves backward that many records. If x is zero, the underlying data for the current record is retrieved.
-          BOF - When the cursor is pointing at the beginning of the file (BOF), it is actually set before the first record.
-          EOF - the opposite of BOF. When the cursor is pointing at the end of the file (EOF), it is actually set after the last record.
·         The RecordCount Property
-          The RecordCount property is used to return the number of records in the recordset.


  1. Using ADO (Using ActiveX Data Objects)
-          ADO is the most current data access technology from Microsoft. ADO provides a common interface to data by developing an object model that is loosely formed and designed to provide as much or as little functionality that a data provider would need to expose to an application.

Note: For most everyday tasks within you Access-VBA applications, DAO will still have the edge, both in terms of performance and functionality. If you are working with Access tables and VBA and have no intention of upsizing to client-server database such as SQL Server, the staying with DAO is currently the best option. If you are using Access as a front-end to client-server database in an Access project, you should use ADO.

·         Data Providers
-          A data provider is a mechanism that connects to a physical data source. ADO, through OLE DB, has access to these data providers and thus we can connect to different types of data source.
-          The OLE DB providers supplied with ADO 2.1 include:
-          Microsoft Jet 4.0 OLE DB Provider – for Access 2000 databases
-          Microsoft OLE DB Provider for Oracle – for Oracle databases
-          Microsoft OLE DB Provider for SQL Server – for accessing SQL Server
         databases

·         The Connection Object
-          The connection object represents the connection to a data source.
-          The  ConnectionString property of the Connection object is used to specify the full connection information required to connect to a data source.

·         The Recordset Object
-          The Recordset object is used for manipulating a resultset from an OLE DB provider.


·         Navigating through the Recordset
o    MoveNext – moves the cursor to the next record in the recordset.
o    MovePrevious – moves the cursor to the previous record in the recordset.
o    MoveFirst - moves the cursor to the first record in the recordset.
o    Movelast - moves the cursor to the last record in the recordset.
o    Move <x> - moves the cursor forward x number of records. If x is positive, the cursor position moves forward that many records. If x is negative, the cursor position moves backward that many records. If x is zero, the underlying data for the current record is retrieved.
-          BOF - When the cursor is pointing at the beginning of the file (BOF), it is actually set before the first record.
-          EOF - the opposite of BOF. When the cursor is pointing at the end of the file (EOF), it is actually set after the last record.
Read more

Delete this element to display blogger navbar

 
© C.L.S.T.I. Tutorial | Design by Blog template in collaboration with Concert Tickets, and Menopause symptoms
Powered by Blogger