Weird situation---VBA union SQL Query bringing back fewer records than Created Query Union Query

[CodeLiftSleep]

I'm not exactly sure what's happening or why it's occurring...



Background: I have 3 tables on SharePoint linked to the frontend of a Access solution I am building. I then run a Query to Union these tables together because I need to look at all of them as a group and the SQL queries to do this while they were 3 separate tables was getting a little out of hand.

I am currently running it in the Access open AutoExec Macro via VBA and it brings back 14,765 records. However, if I create a query for it and then run it, the same SQL brings back 15650 records or something around there(it's between 800-1000 records more).

Anyone know what might be happening here? Does the DoCmd.RunSQL have a limit to how many records it can bring back?

I'm assuming I can just build a query and have that run upon opening the same way, but I prefer the VBA solution. Any ideas? Do I need to do something special to run it as a created query or just called QueryDefs from VBA?

[ranman256]

Could one be bringing UNIQUE values?

[June7]

If the query is just a SELECT, why do you use RunSQL? RunSQL is to execute SQL actions (DELETE, UPDATE, INSERT). Just Open the query or use it in a report.

Why do you have 3 separate tables? Why not 1 table with another field for a category value?

Are you sure the two SQL statements are identical?

[CodeLiftSleep]

If the query is just a SELECT, why do you use RunSQL? RunSQL is to execute SQL actions (DELETE, UPDATE, INSERT). Just Open the query or use it in a report.

Why do you have 3 separate tables? Why not 1 table with another field for a category value?

Are you sure the two SQL statements are identical?

I didn't design the SharePoint tables nor do I have the ability to change them. Apparently there are too many items to fit in one table.

Yes I'm sure, I copy and pasted it into the query designer and ran it.

[June7]

"too many items to fit in one table" - too many fields? Sounds like non-normalized data structure.

You copy/pasted the SQL from VBA?

Again, I don't understand using RunSQL for a SELECT query. Are you running an INSERT action?

Post your VBA code.