L
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
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