TheSwamp

Code Red => VB(A) => Topic started by: dr-j on November 16, 2005, 05:20:18 AM

Title: sort and loop access table in autocad
Post by: dr-j on November 16, 2005, 05:20:18 AM

Hi,
I'm working on a small calculating prog in Autocad.
It should take a record from a access database table, and calculate it.
I already got the calculating part going, but can anyone help me to sort the table according to a certain fieldname.
Also, if i want to loop the caltulation on every record till it finds a correct one, how do i do that?

This is the code that i have:

Code: [Select]

Public wksObj As Workspace
    Public dbsObj As Database
    Public tblObj As TableDef
    Public fldObj As Field
    Public rstObj As Recordset

Private Sub CommandButton5_Click()
'Berekeningen en toetsen.

Set dbsObj = DBEngine.Workspaces(0).OpenDatabase("D:\My Documents\project\7m535\access database\profielen.mdb")
Set rstObj = dbsObj.OpenRecordset("profielinfo", dbOpenTable)

'Ascending
Dim SQLString As String
SQLString = "profielinfo.* FROM profielinfo ORDER BY profielinfo.profielhoogte ASC;"

so the thing now is that it keeps picking the number 1 id record  instead of the first of the fieldname"profielhoogt" that should have been sorted in ascending order.

if someone can help me on this i'd be very grateful  :-)

Title: Re: sort and loop access table in autocad
Post by: Troy Williams on November 16, 2005, 08:24:35 AM

Quote from: dr-j on November 16, 2005, 05:20:18 AM

Code: [Select]

SQLString = "profielinfo.* FROM profielinfo ORDER BY profielinfo.profielhoogte ASC;"


How about this:

Code: [Select]

SQLString = "SELECT * FROM profielinfo ORDER BY profielinfo.profielhoogte ASC;"


Title: Re: sort and loop access table in autocad
Post by: Swift on November 16, 2005, 08:31:54 AM

I'm not sure and didn't test this, but try

Code: [Select]

SQLString = "[b]SELECT * FROM profielinfo[/b] ORDER BY profielinfo.profielhoogte ASC;"


Title: Re: sort and loop access table in autocad
Post by: Swift on November 16, 2005, 08:33:08 AM

good answer Troy  :-o

Title: Re: sort and loop access table in autocad
Post by: dr-j on November 16, 2005, 06:12:34 PM

Thanx a million  :-D

i'll start on it right away and will let you know how it went.

Title: Re: sort and loop access table in autocad
Post by: dr-j on November 27, 2005, 05:34:26 PM

ok i got it workin basically with making the order through vba.
the next thing is I made a query in access as i thought i should have something to compare it with.
but how can you open a query table through vba?

i thought it should be along the line of:

Set dbsObj = DBEngine.Workspaces(0).OpenDatabase("D:\My Documents\project\7m535\access database\profielen.mdb")
Set rstObj = dbsObj.OpenRecordset("Queryprofielhoogte", dbOpenQuery)

it just gives me a big errorr there so i know i as being a noob must do something terribly wrong.
can someone give me a hand here?

Title: Re: sort and loop access table in autocad
Post by: Jim Yadon on December 13, 2005, 03:24:33 PM

Try this one out...
http://www.w3schools.com/sql/default.asp

I do a fair bit of data crunching and have found SQL to be the smoothest way to handle the selection & transport of data once the connection is established.