Writing back from recordset to table or query

Guinaba

Board Regular
Joined
Sep 19, 2018
Messages
232
Office Version
  1. 2016
Platform
  1. Windows
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?

VBA Code:
' 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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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:

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

to this:

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

and see if there's any improvement.
 
Upvote 0
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.
VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
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

IDstrNamestrColor
1JohnBlack
2FredGreen
3MaryYellow
4AlanBlue
5GillWhite
6ZoeGray


Code:

VBA 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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,222,246
Messages
6,164,802
Members
451,917
Latest member
WEB78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top