Sort an access database, remove duplicates
 

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

Sort an access database, remove duplicates
 

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

wrote in message
...
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

Sort an access database, remove duplicates
 

On Jun 13, 6:08*am, "Tim Williams" <timjwilliams at gmail dot com
wrote:
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

wrote in message

...



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

Sort an access database, remove duplicates
 

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


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- Hide quoted text -

- Show quoted text -