ADO How to Insert at a specific place in a table?

[dmassey]

Hi

I am using ado to add records to an access97 table using rs.addnew and rs.update

Is there any way to insert the new row at a specific location in the recordset instead of at the end?

Thanks, Derek

 

[SkipVought]

Hi,

Record position is not important in a table. Why is this important to you?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[BNPMike]

As SkipVought says, a relation by definition does not have order, unless of course you apply an order by clause. If you have 2 rows - A and C - and you insert B at the end of recordset derived from these two rows, then B gets inserted not at the end but between A and C. Of course you know that I'm being disingenuous - in fact where the row is inserted in the recordset is meaningless as there is no order. When you ask for the recordset again -providing the underlying query is ordered on the relevant field - lo and behold there is B right bewteen A and C! Just what you wanted.

The actual answer to your question is never use a programming algorithm that is sensitive to where an inserted record physically is.

 

[dmassey]

Thanks for the replies

I realize that what I want to do here is kind of unusual. I am trying to optimize some queries on some huge tables through slow connections and it would have been nice to avoid the slowdown caused by the ORDER BY in the query.

What I am trying to do is mirror an AS400 database table in an access database. My strategy is to open the two tables at the same time and move through the recordsets simultaneously comparing the keyfields. When I get to two rows that don't match, then I either insert or delete a row from the access table. This works very well except that the SQL Statement has an have to be sorted the same before I can compare them. If I could insert at a specific position in the access table, then I could make the default order match the order in the as400 table and I wouldn't have to sort either table which would make it very, very fast. Doing it this way even with the sort is about 80 times as fast as using updated queries and append queries.

Derek

 

[CajunCenturion]

Have you tried putting an Index on your Access table the matches your OrderBy clause?

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dmassey]

Hi CajunCenturion

I have tried putting an index in the access table but it seems like if I open an ado recordset with orderby in the sql statement it gives a different sort order than if I open the recordset with ado an then use a sort on the recordset itself.

ie

ppprs.Open "Select * From ITEMASA_ALL", cnPPP, adOpenDynamic, adLockOptimistic
ppprs.Sort = "ITNBR ASC"

gives a different sort order than

ppprs.Open "Select * From ITEMASA_ALL ORDER BY ITNBR", cnPPP, adOpenDynamic, adLockOptimistic

Derek

 

[CajunCenturion]

I can see where the DB engine and the .Sort method could produce two different sequences, but only different within a given ITNBR. If you have 10 different records with the same ITNBR, then I would think that all ten would be together if both cases, but the ordering of those specific 10 may vary between the two approaches. Adding a secondary sort key could resolve that issue.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dmassey]

Hi CajunCenturion

An example of two part numbers that would be sorted backwards is the following

"000103 " and "000-701886 "

These part numbers are text fields with trailing spaces as shown.

It's not a big deal to use the client side cursors and sort everything the same way so don't lose any sleep over it.

This was using Jet 3.51 and ADO 2.6

Derek

 

[CajunCenturion]

Which one sorted backwards - the ORDER BY or the .Sort method?

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dmassey]

rs.Open "Select ITNBR From table1 ORDER BY ITNBR"

Puts 000-701886 before 000103

and

rs.Open "Select ITNBR From table1", cn, adOpenDynamic, adLockOptimistic
rs.Sort = "ITNBR ASC"

puts 000103 before 000-7001886

I don't really know which is backwards, access97 sorts like the first example "000-701886" before "000103"


Derek

 

[dmassey]

I just tested it using access 2000 and access2000 puts the 000103 before the 000-701886

I wasn't aware that access 2000 had a different sort order from 97. Maybe MDAC 2.6 isn't really meant to be used with access 97.

Derek

 

[CajunCenturion]

000-701886 should come before 000103 because in the 4th position, a dash (Asc = 45) comes before a 1 (asc = 49). It appears that the .Sort method is the culprit.

The only suggestion that I have to to update your ADO from 2.6 to the latest version (which may include a fix to the .Sort method). The lastest version is at least 2.8, but I haven't checked recently.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[willir]

Derek

Regardless of the retrieved order, you can not specifically control where a record tacks on a record.

For example, BTrieve will a record into a "tree" using an algorythym to "balance" the number of records in the top, mid and bottom of the branching tree. I seem to recall this is an effective approach for moderate to high amount of data, but has a lot of overhead for small databases.

Others, may just tack on the record at the end. This is a simple approach which suffers when the number of records increases.

I am not sure which approach the Jet Engine uses.

Regardless, the Jet Engine will insert the record per its designed mechanism.

...Moving on
Next is the actual data on the hard drive. With a large database, the data can become segmented. This can really hurt performance, especially on operating systems that have no native or third-party defragmentation programs.

Okay, a novell approache...
Use an append / create table query that inserts the records from the source table into a new table using the sort order you want to use. This may help. (You will have to break the relations and rejoin them on the new table.)

We used to use this approach on large databases on Unix systems - pretty drastic - backup the database, delete the database, and then restore the database - to minimize fragmentation.

Richard

 

[BNPMike]

My understanding is Jet always put insertions at the end of the tablespace which is why you have to compact the database often. Also, as far as I know, Jet will insert records in the order it receives them. What happens when you compact, I'm not sure. Quite possibly it re-sorts the records in primary key order.