Sort an access database, remove duplicates

[les.moodley]

Hi All,

I have created an access database from excel VBA but experiencing
problems with sorting. Once I sort the recordset, I can message box
the sorted table, but am not sure how to save the new sorted table?
Tried a few things but no luck. Basically I would like to update the
table. Please see code below. My second questions is how do you remove
duplicate lines in a access database from Excel VBA?

Public Sub sort_recordset()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sSQLQry As String
Dim dbPath As String
Dim dbConnectStr As String

'Generate the SQL query
sSQLQry = "SELECT * FROM table_piezo"

'Set database name here
dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
dbPath & ";"
'Open connection to the database
cnt.CursorLocation = adUseClient
cnt.Open dbConnectStr

'Open recordset based on Orders table
rst.Open sSQLQry, cnt

With rst
.Sort = "[Datetimex]"
Do While Not .EOF
MsgBox rst.Fields(0)
.UpdateBatch 'this does not work
.MoveNext
Loop
'.Save 'not sure how to use it

End With

With cnt
' sSQLQry = "UPDATE table_piezo"


End With
rst.Close

End Sub

Any help with this would be greatly appreciated, also if you can
recommend any good books/links on this subject it would be very much
appreciated.

Thanks,
Les

 

[Tim Williams]

There's no need to store the table in a sorted form - typically databases
are not used this way: you just sort the data on querying it.

How would you define a "duplicate" ?

Tim

 

[les.moodley]

There's no need to store the table in a sorted form - typically databases
are not used this way: you justsortthe data on querying it.

How would you define a "duplicate" ?

Tim

Hi All,

I have created an accessdatabasefrom excel VBA but experiencing
problems with sorting. Once Isortthe recordset, I can message box
the sorted table, but am not sure how to save the new sorted table?
Tried a few things but no luck. Basically I would like to update the
table. Please see code below. My second questions is how do you remove
duplicate lines in a accessdatabasefrom Excel VBA?

Public Sub sort_recordset()
   Dim cnt As New ADODB.Connection
   Dim rst As New ADODB.Recordset
   Dim sSQLQry As String
   Dim dbPath As String
   Dim dbConnectStr As String

'Generate the SQL query
    sSQLQry = "SELECT * FROM table_piezo"

'Setdatabasename here
   dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
dbPath & ";"
'Open connection to thedatabase
   cnt.CursorLocation = adUseClient
   cnt.Open dbConnectStr

'Open recordset based on Orders table
   rst.Open sSQLQry, cnt

   With rst
     .Sort= "[Datetimex]"
       Do While Not .EOF
           MsgBox rst.Fields(0)
           .UpdateBatch  'this does not work
           .MoveNext
       Loop
       '.Save  'not sure how to use it

   End With

   With cnt
      ' sSQLQry = "UPDATE table_piezo"

   End With
     rst.Close

End Sub

Any help with this would be greatly appreciated, also if you can
recommend any good books/links on this subject it would be very much
appreciated.

Thanks,
Les- Hide quoted text -

- Show quoted text -

Many thanks Tim for your response,

Sorry, I mean duplicate records. Since I export data from Excel to an
Access database it is possible to export the same data twice. Would
just like to implement some code prevent this from happening. I guess
one could query the database first to check if the exact record
already exists?

With regard to the my first question, I was hoping to save the data in
a sorted form. My reason is if someone wanted to stroll through the
database using Access, it would be easier to find something if it is
sorted by date. I saw that Access has a manual way to sort (Records/
Sort/Ascending), then you can save the table. Was hoping to have the
same functionality from VBA Excel. Is this possible?

Kind Regards,
Les

 

[Tim Williams]

Yes, just run a query before doing the insert: if it's EOF then it's
not a "repeat".

As for sorting the table in Access, I'm not familiar enough with the
application to comment.

Tim