Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    64

    Unanswered: Using ADO to fill a recordset

    OK, I have the following code, and the SQL statement does not pull in any information. This is really my first attempt at trying to fill a recordset this way, so I am learning as I go.. Any help would be appreciated.
    Code:
    Private Sub Form_Open(Cancel As Integer)
      Dim rstAction As ADODB.Recordset
      
      'Stop
      
      Set rstAction = New ADODB.Recordset
      With rstAction
      	.ActiveConnection = CurrentProject.Connection
      	.CursorType = adOpenKeyset
      	.CursorLocation = adUseClient
      	.LockType = adLockOptimistic
      	.Source = "SELECT * from cust_info"
      	.Open
      	Me.Recordset = rstAction
      '.Close
      End With
      Me.Recordset = rstAction
      'Set rstAction = Nothing
      End Sub

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Here's an example of using ADO recordsets ...

    Code:
    Function RetrieveMatchingPOs() As Integer
        On Error GoTo Err_RMPO
        
        Dim FormsConnect As ADODB.Connection
        Dim TrgRecSet As ADODB.Recordset
        
        Set FormsConnect = New ADODB.Connection
        Set TrgRecSet = New ADODB.Recordset
        
        FormsConnect.CursorLocation = adUseClient
        FormsConnect.Open "DSN=Billing Forms;"
        
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
        ' Clear existing records (if any)
        SQLString = "DELETE FROM " & RptTbl & ";"
        FormsConnect.Execute SQLString, , adCmdText
        
        ' Retrieve the Supplier Name
        SQLString = "SELECT Name FROM Suppliers WHERE(SupplierID=" & SupplierID & ");"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SupplierLbl.Caption = MyRecSet.Fields(0).Value & ""
        End If
        MyRecSet.Close
        
        ' Retrieve the PO's
    'SELECT Receipts.[Tracking #], [Purchase Orders].[PurchaseOrder #], Suppliers.Name, Receipts.[Olimpic #], Receipts.ShippedDate, Receipts.ReceivedDate, Left(UserBillingSettings.FirstName,1) & ' ' &  UserBillingSettings.LastName AS Clerk, Receipts.LastModified, Receipts.[Invoice Total]
    'FROM (Suppliers INNER JOIN (Receipts INNER JOIN [Purchase Orders] ON Receipts.[Tracking #] = [Purchase Orders].[Tracking #]) ON Suppliers.SupplierID = [Purchase Orders].SupplierID) INNER JOIN UserBillingSettings ON Receipts.UserID = UserBillingSettings.UserID;
        SQLString = "SELECT Receipts.[Tracking #], [Purchase Orders].[PurchaseOrder #], Receipts.[Invoice #], Receipts.[Olimpic #], Receipts.ShippedDate, Receipts.ReceivedDate, Left(UserBillingSettings.FirstName,1) & ' ' &  UserBillingSettings.LastName AS Clerk, Receipts.LastModified, Receipts.[Invoice Total]"
        SQLString = SQLString & " FROM (Suppliers INNER JOIN (Receipts INNER JOIN [Purchase Orders] ON Receipts.[Tracking #] = [Purchase Orders].[Tracking #]) ON Suppliers.SupplierID = [Purchase Orders].SupplierID) INNER JOIN UserBillingSettings ON Receipts.UserID = UserBillingSettings.UserID"
        SQLString = SQLString & " WHERE ([Purchase Orders].SupplierID=" & SupplierID & ");"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM " & RptTbl & " WHERE (1=0);"
            TrgRecSet.Open SQLString, FormsConnect
            While MyRecSet.EOF = False
                TrgRecSet.AddNew
                ' Tracking #
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value
                ' P.O. #
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value & ""
                ' Invoice #
                TrgRecSet.Fields(2).Value = MyRecSet.Fields(2).Value & ""
                ' Olimpic #
                TrgRecSet.Fields(3).Value = MyRecSet.Fields(3).Value
                ' Shipped Date
                TrgRecSet.Fields(4).Value = MyRecSet.Fields(4).Value
                ' Received Date
                TrgRecSet.Fields(5).Value = MyRecSet.Fields(5).Value
                ' Clerk
                TrgRecSet.Fields(6).Value = MyRecSet.Fields(6).Value & ""
                ' Date Entered
                TrgRecSet.Fields(7).Value = MyRecSet.Fields(7).Value
                ' Invoice Total
                TrgRecSet.Fields(8).Value = MyRecSet.Fields(8).Value
                TrgRecSet.Update
                MyRecSet.MoveNext
            Wend
            TrgRecSet.Close
        End If
        MyRecSet.Close
        
        FormsConnect.Close
        Set FormsConnect = Nothing
        Set TrgRecSet = Nothing
        
        LoadingDelay
        
        RetrieveMatchingPOs = 0
    Exit_RMPO:
        Exit Function
        
    Err_RMPO:
        
        MsgBox Err.Number & ": " & Err.Description
        RetrieveMatchingPOs = 0
        Resume Exit_RMPO
    End Function
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jul 2004
    Posts
    64
    Thanks, I will have a look at that.. Does that produce an updateable recordset?

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Your query will define if the recordset is updateable or not ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •