sort ASC an .mdb file from visual basic

[juve11]

Hello,
I made a change to an Access file this way.

[CODE=vb]Dim myData As New DataSet()
'Dim mySelectQuery As String = _
'"SELECT * FROM D WHERE postoffice like ',MIHAI%'"
Dim myConnection As New OleDbConnection ( _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=c:\mihai .mdb")
Dim strSelect As String = "update D set postoffice=RIGH T(postoffice,9) where postoffice not like ',MIHAI%'"
Dim dsCmd As New OleDbDataAdapte r(strSelect, myConnection)
dsCmd.Fill(myDa ta)
myConnection.Cl ose()[/CODE]

The execution is OK, but I also want that the file c:\mihai.mdb to be sorted ASC at postoffice column.

Thank you.

[jamesd0142]

[code=vb]
Dim myData As New DataSet()
'Dim mySelectQuery As String = _
'"SELECT * FROM D WHERE postoffice like ',MIHAI%' order by postoffice ASC"
Dim myConnection As New OleDbConnection ( _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=c:\mihai .mdb")
Dim strSelect As String = "update D set postoffice=RIGH T(postoffice,9) where postoffice not like ',MIHAI%'"
Dim dsCmd As New OleDbDataAdapte r(strSelect, myConnection)
dsCmd.Fill(myDa ta)
myConnection.Cl ose()
[/code]

try this Added 'order by postoffice ASC'

[debasisdas]

You just need to add the ASC (for ascending) key word to the SQL string. ASC only sort the recordset, it does not affect the actual data in the database tables.

Just do as suggested in the previous post.

[juve11]

hi,thank you for you're replyes,but it doesnt works,no error but when i enter the mdb file they arent sorted ...

i also try "update D set postoffice=RIGH T(postoffice,9) where postoffice not like ',mol%' order by postoffice asc;" but it gives me this error (missing semicolon ; at the end of sql statement) wich i really dont get it..

[QVeen72]

Hi,

Why would you want to "Order By" an Update Statement...?
All the Records which Satisfies the "Where" Conditions are updated.
Remove Order By from Update statement..

Just use "Order By" for a Select Statement.


Regards
Veena

[debasisdas]

Remove the order by clause from the sql statment .

Order by is used only in select statments .

[juve11]

ok,but i want to sort the column POSTOFFICE of the file c:\mihai.mdb for permanent, i dont want to sort the data set...

[debasisdas]

ok,but i want to sort the column POSTOFFICE of the file c:\mihai.mdb for permanent, i dont want to sort the data set...

There is no command in sql to sort the database table data itself.

[QVeen72]

Hi,

Not very sure why would you want to sort the Column and save in Database...?
In Select statement, you can very well sort..

Regards
Veena

[juve11]

well beacause the database must be in order of the postoffices in order to be corectly processed by somebody else.

what if i show the database in a datagrid,sort there and after that save all the data grid into a new mdb file?

Hi,

Not very sure why would you want to sort the Column and save in Database...?
In Select statement, you can very well sort..

Regards
Veena

[QVeen72]

Hi,

Create an Index for "PostOffice s" field and Check...

Regards
Veena

[juve11]

what?i dont understand...

Hi,

Create an Index for "PostOffice s" field and Check...

Regards
Veena

[debasisdas]

well beacause the database must be in order of the postoffices in order to be corectly processed by somebody else.

what if i show the database in a datagrid,sort there and after that save all the data grid into a new mdb file?

To process the data you have to select it from database table ,right.
and always you can do that by sorting the recordset . In that case u don't have to bother about order of data in the database at all.

[Killer42]

In general, the database should be viewed as simply a big "bucket" of data. It is handed to you in the order you request. If you want the records to be physically stored in postcode sequence, you would have to load the records into a new table in the desired sequence.

However, you can probably get the effect you want just by making the postcode the primary key on the table. I believe Access returns records in primary key sequence unless told otherwise. If you need further help with this, I'd recommend you ask in the Access forum where you will get much more authoritative answers.

[juve11]

done :
[CODE=vb]
Dim oApp As Access.Applicat ion

oApp = CreateObject("A ccess.Applicati on")
oApp.OpenCurren tDatabase("E:\d atabase.mdb")


oApp.Visible = False
oApp.DoCmd.Open Table("E", AcView.acViewNo rmal, AcOpenDataMode. acEdit)

oApp.DoCmd.GoTo Control("PostOf fice")
oApp.DoCmd.RunC ommand(AcComman d.acCmdSortAsce nding)

oApp.CloseCurre ntDatabase()[/CODE]