Excel - VBA SQL Array Order

[msign]

Hi, I am currently experiencing a weird issue with querying data from access into excel (through vba)

The first field of the access table is the primary key. In access I see records show up in numerical (1, 2, 3, 4, ...) order as it should be; when querying through vba i see the numbers at the end being moved to the beginning (something like 92, 93, 94, ... , 1, 2, 3, .... 91). Its pretty weird and at the moment the point at which the numbers get mixed up seem a bit arbitrary.

Anyone have any idea whats going on here? I imagine a quick fix would be to add the "ORDER BY ID" text to the query but that seems more like a hack than an actual cause+solution.

One possible reason is that I am also using vba to enter data as well but I have no idea why that would do it.

Thanks

[JohnM3]

Hi M

Could there be a WHERE clause OR JOIN clause OR sub Query
that is driving the results you are seeing?

In any event - you could use ADO command object ie

(in part)
CMD.COMMANDTYPE = ADCMDTABLE
CMD.COMMANDTEXT = tablename
set rs = CMD.EXECUTE

just to confirm the order (via code) of the records in the table.

regards
John

[JohnM3]

just thought of it ...... you could put test SQL like

select top 25 * from mytable and see if the record order is the same

[msign]

hey the select statement is pretty simple.

SELECT * FROM tablename

I query by saying something like

dim query as string
dim arows

query = "SELECT * FROM tablename"
Set rs = cn.Execute(query)
aRows = rs.GetRows()

I tried grabbing the top 25 and I still get the same results (92, 93, 94, ... , 117), I tried top 30 and it gave me (92, 93, 94, ..., 117, 118, 119, 1, 2, 3)

[JieJenn]

You need to insert Order by statement at the end

For example

SELECT TOP 25 * FROM tablename ORDER BY [field name]

[msign]

You need to insert Order by statement at the end

For example

SELECT TOP 25 * FROM tablename ORDER BY [field name]

I already mentioned that

I imagine a quick fix would be to add the "ORDER BY ID" text to the query but that seems more like a hack than an actual cause+solution.

this should work but Im more concerned with what is actually causing this to happen. In access the tables are ordered correctly by default.

[alansidman]

Access stores data in tables in the sequence that it was entered. Is your numerical order the same sequence? Are your numbers entered as text or as numbers (how is the field formatted in Access that contains the numbers?)

[msign]

I agree, in Access the data appears to be stored in the order that it was entered. this is evident by the primary key actually being in the correct order (while viewing it in access). The only numerical field in the table is the primary key which is set to Autonumber. The format row in design view is empty for this field.

I enter the data through an excel spreadsheet. Since the primary key is set to autonumber, I dont enter it when entering data. I simply write an INSERT INTO statement which states values for all of the fields except for the primary key. This might be an issue but I am not entirely convinced of that. I just am not sure what else may be causing the vba sql query to get the data in the wrong order.

[JohnM3]

Hi m

Run the SQL in Access. Is the result the same as you see in Excel?

regards
John

[msign]

Hi m

Run the SQL in Access. Is the result the same as you see in Excel?

regards
John

Hmmm I just tried SELECT * FROM table in acces and it gave me the result in proper order (id going from 1, 2, 3, and on) and then I tried SELECT top 30 FROM table in access and the result was the same incorrect order as excel gave ( 92, 93, ... 1,2 ..)

strange indeed

[JohnM3]

From out of a book by Allison and Berkowitz -

Using TOP (without an ORDER BY results in records being delivered in the SAME order
as they appear in the table.

You might consider.

(1) Get out of Access
(2) Make a FILE Copy of your ACCDB file.
(3) Rename the COPY to _whatever_.
(4) Open up _whatever_.
(5) From the BIG CIRCLE thingie (top left corner) CLICK -> MANAGE -> Compact and Repair
(6) Re-run your code pointing to _whatever_

I am NOT saying that the above is necessary. I _don't_ have a clue if there is a problem
and if there is - what it might be.

At the end of the day - to get the results you need - you will need to add an ORDER BY.

fwiw - I have _never_ thought of ORDER BY as a hack but that notwithstanding - I am not
certain why the records are not returned IN THE SAME ORDER as they are in the table.

regards
John

[msign]

From out of a book by Allison and Berkowitz -

Using TOP (without an ORDER BY results in records being delivered in the SAME order
as they appear in the table.

You might consider.

(1) Get out of Access
(2) Make a FILE Copy of your ACCDB file.
(3) Rename the COPY to _whatever_.
(4) Open up _whatever_.
(5) From the BIG CIRCLE thingie (top left corner) CLICK -> MANAGE -> Compact and Repair
(6) Re-run your code pointing to _whatever_

I am NOT saying that the above is necessary. I _don't_ have a clue if there is a problem
and if there is - what it might be.

At the end of the day - to get the results you need - you will need to add an ORDER BY.

fwiw - I have _never_ thought of ORDER BY as a hack but that notwithstanding - I am not
certain why the records are not returned IN THE SAME ORDER as they are in the table.

regards
John

Wow compact and repair worked! thanks so much. did you have a particular suspicsion or was it a shot in the dark?

In the end I think your right, I might have to do an ORDER BY. whatever caused the issue might keep coming back. The thing is, the spreadsheet that I use can query for multiple tables (each with its own primary key) so creating a logic for an order by will be difficult. Any idea how I can say something like "ORDER BY primary_key_of_table" or if such a thing is possible?

[JohnM3]

Hi m

It was a shot in the dark. I assume you know that Access "bloats" and (in some places) its an
ordinary course of business to Compact and Repair. It should always be done with caution ie
make a copy of the file and CR that file. I suggested it ONLY to bring things to some baseline
starting point .... and then ... try to figure out what was happening.

I only use ADO to get data from (database) to (Excel). And that requires - at least how I use it -
that SQL Statements be used. There is NOTHING significant about a field being a primary key or using
that field in a SQL Statement. eg

MyTable
-----------
Fields
-----------
MyId - Primary Key
LName -
FName -
State
Salary
Hire-Date
-------------
SQL Statement
---------------
Example 1 - With MyId in the SELECT and Order By
---------------
Select MyId, Lname, Fname from MyTable Order By MyID
---------------
Example 2 - MyID is _not_ in the Select but used in the Order By
---------------
Select Lname, Fname from MyTable Order By MyID

I really _don't_ know "HOW TO" query databases from Excel in the way that you are doing it
so perhaps someone else can guide you.

regards
John