Quick DoCmd.OpenQuery Question

[Kosmos]

I should probably know this, but I have a query that I'm opening with VBA:

DoCmd.OpenQuery "SortTableClientCN_ASC", acViewNormal, acEdit

the query is the following:

SELECT tblClientCN.ClientCN
FROM tblClientCN
ORDER BY tblClientCN.ClientCN;

I simply need to order this column in ascending or descending order (doesn't matter) - however I want to edit the actual table with the ordered results and was not sure if there was simple way to save the results of the query to the table?

I don't have any primary keys

Thanks!

[jaxjagfan]

I should probably know this, but I have a query that I'm opening with VBA:

DoCmd.OpenQuery "SortTableClientCN_ASC", acViewNormal, acEdit

the query is the following:

SELECT tblClientCN.ClientCN
FROM tblClientCN
ORDER BY tblClientCN.ClientCN;

I simply need to order this column in ascending or descending order (doesn't matter) - however I want to edit the actual table with the ordered results and was not sure if there was simple way to save the results of the query to the table?

I don't have any primary keys

Thanks!

For the most part records and entries are saved in the order they are entered. If we need ordered views for whatever reason that is normally accomplished with queries.

Is there a reason to reorder the table after making entries?

[Kosmos]

Yes. I'm trying to find duplicate records and the number of times they occur...gotta go to a meeting but will be back. I guess I could just output to another table I'm trying to make this a bit more of a simplified process. Thx

[jaxjagfan]

Yes. I'm trying to find duplicate records and the number of times they occur...gotta go to a meeting but will be back. I guess I could just output to another table I'm trying to make this a bit more of a simplified process. Thx

What is the table structure? What field or combination of fields are you looking to count?

"SortTableClientCN_ASC" is this an acsii file you are importing?

FYI - If you select "New Query" and choose "Find Duplicates", Access wizards will help you build a find dupes query as longs as the data is not too complex.

If the wizard can't help you out, give us some more details on the table and what you are trying to accomplish. I'm sure we can come up with a solution.

[Kosmos]

Alright well I appreciate your help! Unfortunately, for this project, I have to be careful about what I post. Anyways...I have already found a solution to the original way I intended to handle this. As you may have seen from some of my earlier posts...I do like to post a lot of code so that I can give back to the community.

Here was my original problem which I tried to work around as I had thought that Access did not support it:

recSet1.Sort = "ClientCN ASC"

Problem:
In addition to opening my recordset in the usual way, I also had to add the following line of code:

recSet1.CursorLocation = adUseClient before my recSet1.open declaration

For further explanation on the root cause of this problem:
http://support.microsoft.com/kb/253248

Thx!

[NeoPa]

I appreciate your attitude Kosmos. Feedback is always good.
I'd be grateful if you'd try to remember to use the code tags when posting code in future though. With over a hundred posts you should know the drill by now ;)

As to storing the data, you mention you don't have a Primary Key - well I would suggest from this request that indexes are exactly what you need. As Jax says, the order of the records physically in the table is undefined (generally the order entered but not reliably so I believe). If any order is required then it should be defined either by an index or by using a sorted query.

I hope this helps for future situations.

[Kosmos]

Hey Neo,

thanks for the info...as always! I remember when I was just starting out in programming and you had less than 1000 posts because some error had reset everything but you're already at 7,000+ so yeah I'm glad to help out

anyways...I appreciate your advice about using primary keys but I tend to avoid using primary keys where possible as I use a lot of vb scripting that I will be able to remove from access sometime down the road...the intention is that some of the programming I'm doing here will be implemented outside of access sometime in the future

Anyways thanks again!

[NeoPa]

That's a while back Kosmos :)

Just remember that unindexed data is pretty well random when retrieved in Access.