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

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

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

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 -