change/swap order of two records in access

[rey]

How can I efficiently swap order of two records sorted by an autonumber.

Access db. Primary key is an autonumber. I'm given a record ID corresponding
with the primary key.
I need to swap all record content of that record ID with either the previous
record or next record.

My first thoughts (of a novice mind you) are to select the two records in
one query and temporary hold
the content in variables or an array, and then update each record with said
variables/array using some for loop.

Can I select two or more records within a number range in one query?
Can I select, temporarily store, and loop update in one open
connection/execute? (I may be confusing terms in this question, I don't mean
the initial connection to db)

Any suggestion how to tackle this?

Thanks,
Rey

[rey]

Note, primary key may not be consecutive due to deletions.

Thanks,
Rey

[Ray at]

While I'm curious as to why you need to do this, I guess it's not my
concern. Here's one way, that is pretty much what you were saying.

YourConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\path\file.mdb;"
Dim oADO, oRS1, oRS2
Dim iRec1, iRec2
iRec1 = 1
iRec2 = 2
Set oADO = Server.CreateObject("ADODB.Connection")
oADO.Open YourConnectionString
Set rs = oADO.Execute("SELECT [ID],[SomeData] FROM [SomeTable] WHERE [ID]
IN (" & iRec1 & "," & iRec2 & ") ORDER BY [ID]")
If Not rs.EOF Then
aVals = rs.GetRows()
rs.Close : Set rs = Nothing
oADO.Execute "UPDATE [SomeTable] SET [SomeData]='" & aVals(1,0) & "' WHERE
[ID]=" & aVals(0,1)
oADO.Execute "UPDATE [SomeTable] SET [SomeData]='" & aVals(1,1) & "' WHERE
[ID]=" & aVals(0,0)
End If
oADO.Close
Set oADO = Nothing


If you use a method such as this, you should read this.
[url]http://www.asp101.com/articles/chris/asptransactions/default.asp[/url]

Ray at home



"rey" <dojocre8@yahoo.com> wrote in message
news:cf6dnUm7Jbpn5TCiRVn-jw@comcast.com...

> How can I efficiently swap order of two records sorted by an autonumber.
>
> Access db. Primary key is an autonumber. I'm given a record ID

corresponding

> with the primary key.
> I need to swap all record content of that record ID with either the

previous

> record or next record.
>
> My first thoughts (of a novice mind you) are to select the two records in
> one query and temporary hold
> the content in variables or an array, and then update each record with

said

> variables/array using some for loop.
>
> Can I select two or more records within a number range in one query?
> Can I select, temporarily store, and loop update in one open
> connection/execute? (I may be confusing terms in this question, I don't

mean

> the initial connection to db)
>
> Any suggestion how to tackle this?
>
> Thanks,
> Rey
>
>
>
>