Writing back from recordset to table or query

[Guinaba]

Hi guys,

I am learning how to handle recordsets in access, below I am opening a recordset and deleting all null values in the table. Using the sql below everything is ok
Set rs = db.OpenRecordset("SELECT * FROM tblTempColesForecast", dbOpenDynaset)

However, when I tried to add a sort or where condition on it, to organize the data better my loop code doesn't work anymore.
Set rs = db.OpenRecordset("SELECT * FROM tblTempColesForecast WHERE (((tblTempColesForecast.F2)<>""Vendor Description""))", dbOpenDynaset)

Not sure what I am missing?

' Set data
    strTableName = "tblTempColesForecast"
    strFileName = VarFileName
    blnHasHeadings = True
    
    'Delete all data from temp table and temp query before importing again
    Dim SQLDelete As String
        
    SQLDelete = "Delete * From tblTempColesForecast"
    DoCmd.RunSQL SQLDelete
    
    ' Import excel data into tblTempSys35
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTableName, strFileName, blnHasHeadings, "Data Extract Forecast!"
    
  '*************Adjusting the data format in the table************************
  
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rsSort As DAO.Recordset
    Dim inx As Long

    'Set the DAO database to current Access db
     Set db = CurrentDb

    'Open the table as the recordset
     Set rs = db.OpenRecordset("SELECT * " & _
     "FROM tblTempColesForecast", dbOpenDynaset)
     
    'Test to make sure there are rows
     If Not (rs.EOF And rs.BOF) Then

     'Loop through the records
        Do Until rs.EOF
           For inx = 0 To rs.Fields.Count - 1   'numbering for members of a collection begins with zero, code loops starting with the zero member and ending with the value of the Count property minus 1
                If IsNull(rs.Fields(inx).Value) Or Len(Trim(rs.Fields(inx).Value)) = 0 Then
             Else: Exit For
            End If
           Next

        If rs.Fields.Count = inx Then
            rs.Delete
        End If
         rs.MoveNext
      Loop
           
       rs.Close 'Cleanup work

     Set db = Nothing
     Set rs = Nothing

     End If

 

[JonXL]

It would help to know exactly what isn't working and whether you're getting an error (and where), etc.

But without that I can suggest you change this:

(((tblTempColesForecast.F2)<>""Vendor Description""))

to this:

(((tblTempColesForecast.F2) <> 'Vendor Description'))

and see if there's any improvement.

 

[Micron]

There is no visible attempt at sorting the recordset so I don't get this. All that's needed is an ORDER BY clause in the sql? But the stated problem doesn't seem to match the thread title? I might write the code this way (some comments removed to make my post smaller). Note: either you don't use Option Explicit, which you should, or some of your variables are not declared explicitly (table and file name variables) or they are, but at the top of the module.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim strTableName As String, strFileName As String

strTableName = "tblTempColesForecast"
strFileName = VarFileName
    
DoCmd.RunSQL "Delete * From tblTempColesForecast"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTableName, strFileName, True, "Data Extract Forecast!"
  
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tblTempColesForecast ORDER BY SomeField", dbOpenDynaset)
     
If Not (rs.EOF And rs.BOF) Then
  Do Until rs.EOF
    For inx = 0 To rs.Fields.Count - 1
     If Nz(rs.Fields(inx),"" = "") Then rs.Delete
    Next
    rs.MoveNext
  Loop
End If         

 rs.Close 'Cleanup work
 Set db = Nothing
 Set rs = Nothing

 

[Guinaba]

Thanks @JonXL @Micron! I have rewrote the code again and now it is ok, as I mentioned before I am still learning so have to spend hours trying different options, but thanks for your comments as you both mentioned the best way is to use the sql statement to sort the data.

 

[Guinaba]

Hi @Micron and @JonXL

Sorry guys, just one more question, I am able to sort the data in memory but how can I refresh the table back in Access? I have created a simple code to show the logic I am using:

table1: before sorting by strName

ID	strName	strColor
1	John	Black
2	Fred	Green
3	Mary	Yellow
4	Alan	Blue
5	Gill	White
6	Zoe	Gray

Code:

Option Compare Database
Option Explicit

Sub OrderDB()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim mySortedRS As DAO.Recordset

    'Set the DAO database to current Access db
     Set db = CurrentDb

    'Open the table as the recordset
     Set rs = db.OpenRecordset("SELECT * FROM Table1 order by strName", dbOpenDynaset)
     
    rs.Sort = "strName"
    Set mySortedRS = rs.OpenRecordset
    
   'Process the rows
    Do
    
      Debug.Print mySortedRS!strName
      mySortedRS.Edit
      mySortedRS.Update
      mySortedRS.MoveNext
      
    Loop Until mySortedRS.EOF
    
  
    'Cleanup
    rs.Close
    Set rs = Nothing
    Set mySortedRS = Nothing
    
End Sub

 

[JonXL]

What do you mean by refreshing the table? Is the whole point of this code to "resort" your table?

If so, that is going to be a no go. There is no order to data as stored in a table and not really any way to force one.

If you want your data ordered, you put that in the query as you're using the data. It always has to be explicit at the point of using the data (query, report, etc.) and can't be stored in the dataset itself.

 

[Micron]

You can manually sort a table using the ribbon but there's really not much point. Users should not be allowed to interact with tables - only forms and reports, so table sorting doesn't matter. I would not rely on a sorted table either because I'm pretty sure it is only a "sorted view" of the data. If you try to use such "sorted" records you might get unwanted results because of the way data can be scattered on disc, just like any other file.

So if the purpose of that code is to sort a table, it won't work, nor is the whole exercise of any value I'm afraid as what you do to a recordset is lost as soon as the code is finished. A rs exists only in memory.

 

[Guinaba]

Thanks a lot @JonXL and @Micron! Learned a good lesson today! I’ll do the sorting using a query instead. Still a lot to learn hehe!