sql action qry in vba not matching same qry in design view

  • Thread starter Thread starter Drew
  • Start date Start date
D

Drew

Can anyone help me understand under what conditions an
action query (dynamically built sql statement with no
wildcards) executed in vba using ado produces different
results than copying this string from the immediate
window and pasting it into SQL view? Im trying a 'SELECT
INTO' or 'INSERT INTO'.

thanks
 
Can anyone help me understand under what conditions an
action query (dynamically built sql statement with no
wildcards) executed in vba using ado produces different
results than copying this string from the immediate
window and pasting it into SQL view? Im trying a 'SELECT
INTO' or 'INSERT INTO'.
Click to expand...

String delimiters are different: ado needs single quotes, access can use
double or single;

Wildcard characters may differ: access will handle the old DOS * and ?,
while ADO (I think) uses the ISO standard % and _;

Access' expression interpreter will pick up all references to forms,
controls, VBA functions and other GUI stuff that are not visible to ADO/
VBA;

Later versions of ADO have a fuller implementation of SQL than Access/DAO,
but the main differences are in the DDL.

That is all I can think of from the top of my head, but I may have missed
some. What specifically did you have difficulty with?

B Wishes


Tim F
 
Thanks Tim,

specifically im having problems with 'SELECT DISTINCT'
and 'GROUP BY'. When I run it in vba, i end up with fewer
records than when I run it from design view.

Here's the sql statement: SELECT DISTINCT
DOM_OAG.AIRLINE, DOM_OAG.LEAVE_AIRPORT,
DOM_OAG.ARRIVE_AIRPORT, DOM_OAG.EQUIPMENT,
DOM_OAG.FLIGHT_NUMBER, DOM_OAG.MONDAY, DOM_OAG.TUESDAY,
DOM_OAG.WEDNESDAY, DOM_OAG.THURSDAY, DOM_OAG.FRIDAY,
DOM_OAG.SATURDAY, DOM_OAG.SUNDAY, DOM_OAG.LEAVE_TIME,
DOM_OAG.ARRIVE_TIME, DOM_OAG.DAY_ADJ_AMOUNT INTO
Apr_Temp1 FROM DOM_OAG;

The Monday - Sunday fields, and the Day_Adj_amount fields
can be null. Could that be causing a problem?
 
i have new info that brings me closer to a solution (i
hope). when i check 'Sql Server Compatible Syntax (ANSI
92)' in Tools/Options/TablesQueries, and I run this query
in the db window, I get the same results that ado is
giving. So this tells me when you launch a query in ado,
it interprets it using ANSI 92. So my next question is
how do i construct the SQL statement in ANSI 92 to give
me the results that default access gives me -- maybe i
need to post in a different category.
 
So this tells me when you launch a query in ado,
it interprets it using ANSI 92. So my next question is
how do i construct the SQL statement in ANSI 92 to give
me the results that default access gives me -- maybe i
need to post in a different category.
Click to expand...

That was pretty much my understanding too. I don't see why "constructing" a
SQL command is harder in one dialect than another -- you just stick the
string together. If you need specific help, then the gurus on
m.p.a.adp.sqlserver are pretty hot.

As to the query you posted (I've relaid it so that it's readable) --

SELECT DISTINCT
AIRLINE,
LEAVE_AIRPORT,
ARRIVE_AIRPORT,
EQUIPMENT,
FLIGHT_NUMBER,
MONDAY,
TUESDAY,
WEDNESDAY,
THURSDAY,
FRIDAY,
SATURDAY,
SUNDAY,
LEAVE_TIME,
ARRIVE_TIME,
DAY_ADJ_AMOUNT
INTO Apr_Temp1
FROM DOM_OAG;

I can see very obvious design problems here, but nothing that should cause
any kind of hiccup in ISO or Jet. What differences are you seeing?

B Wishes


Tim F
 
Back
Top