467,161 Members | 895 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,161 developers. It's quick & easy.

Problem sorting tables in visual basic

Hi,

I am using visual basic in access 2003 and am having a problem with sorting tables which I can't seem to find a solution to, and was hoping that someone might be able to help.

I have a table containing policy numbers and various bits of information about each policy. It is necessary to go through the table in policy order and do various calculations to each row.

So, I have used an SQL query to create a copy of the table sorted by policy number as follows:

Dim STR As String

STR = " SELECT Policy.* INTO PolicyCopy FROM Policy ORDER BY PolicyNumber; "
DoCmd.RunSQL (STR)


I now open the table PolicyCopy as follows:

Dim db As Database
Dim PolicyRecordset As Recordset

Set db = CurrentDb
Set PolicyRecordset = db.OpenRecordset("PolicyCopy", dbOpenTable)


Then I go through the PolicyRecordset by using Movefirst and Movenext's.

However, sometimes the program will crash because the PolicyCopy table is not in the correct order. Sometimes the top few rows, usually about a couple of hundred, will be dumped into the middle of the table. I wondered if anyone knew how to get around this problem

Many thanks
Nov 11 '06 #1
  • viewed: 1203
Share:
3 Replies
1GB
Hi,

I am using visual basic in access 2003 and am having a problem with sorting tables which I can't seem to find a solution to, and was hoping that someone might be able to help.

I have a table containing policy numbers and various bits of information about each policy. It is necessary to go through the table in policy order and do various calculations to each row.

So, I have used an SQL query to create a copy of the table sorted by policy number as follows:

Dim STR As String

STR = " SELECT Policy.* INTO PolicyCopy FROM Policy ORDER BY PolicyNumber; "
DoCmd.RunSQL (STR)


I now open the table PolicyCopy as follows:

Dim db As Database
Dim PolicyRecordset As Recordset

Set db = CurrentDb
Set PolicyRecordset = db.OpenRecordset("PolicyCopy", dbOpenTable)


Then I go through the PolicyRecordset by using Movefirst and Movenext's.

However, sometimes the program will crash because the PolicyCopy table is not in the correct order. Sometimes the top few rows, usually about a couple of hundred, will be dumped into the middle of the table. I wondered if anyone knew how to get around this problem

Many thanks
Hi. You have the answer really. Just need to put the two statements together.

Set PolicyRecordset = db.OpenRecordset("SELECT * FROM Policy ORDER BY PolicyNumber")

The table type recordset is default.

And away you go :)
Nov 11 '06 #2
Hi. You have the answer really. Just need to put the two statements together.

Set PolicyRecordset = db.OpenRecordset("SELECT * FROM Policy ORDER BY PolicyNumber")

The table type recordset is default.

And away you go :)

Thats wonderful, thanks very much
Nov 11 '06 #3
I Think into Statement Does Not Allow Order By Class
So please remove Order by policyNumber statement

STR = " SELECT * INTO PolicyCopy FROM Policy "

Dim db As Database
Dim PolicyRecordset As Recordset
Set db = CurrentDb
Set PolicyRecordset = db.OpenRecordset("select * from policycopy order by policynumber")

Thanking U
R.Jayaprakash
Nov 12 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by jwedel_stolo | last post: by
7 posts views Thread by Foxster | last post: by
7 posts views Thread by manning_news@hotmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.