topic Re: ACCESS vs. SQL Server - SQL statement... in VBA Forum

ACCESS vs. SQL Server - SQL statement...

Anonymous — Tue, 09 Jan 2001 13:31:34 GMT

i have created two UDL's, one for ACCESS and the other for SQL Server. my
SQL statement is:

xRS.Open "SELECT NAME FROM PEDESTAL GROUP BY NAME", db, adOpenKeyset,
adLockOptimistic

when i run the SQL statement attached through the ACCESS UDL, the result is
grouped properly with the record count set to the correct number of records.
when i run the same SQL statement in SQL Server attached through the SQL
Server UDL, i return only one record and the record count is set to "-1".
any ideas why the SQL Server is not returning the correct number of records?

(if i remove the "GROUP BY NAME" from the SQL statement, both queries return
the same number of records, as it should...)

--
sc
Mid-State Consultants
scarter@mscon.com

Re: ACCESS vs. SQL Server - SQL statement...

Anonymous — Tue, 09 Jan 2001 14:03:19 GMT

I suggest you check your err object.. I suspect that there is a problem in
the way you open the recordset in combination with the chosen driver.

Wouter

"Steve Carter" wrote in message
news:FD9DB81516719FC69756CD162F1F0835@in.WebX.SaUCah8kaAW...
> i have created two UDL's, one for ACCESS and the other for SQL Server. my
> SQL statement is:
>
> xRS.Open "SELECT NAME FROM PEDESTAL GROUP BY NAME", db, adOpenKeyset,
> adLockOptimistic
>
> when i run the SQL statement attached through the ACCESS UDL, the result
is
> grouped properly with the record count set to the correct number of
records.
> when i run the same SQL statement in SQL Server attached through the SQL
> Server UDL, i return only one record and the record count is set to "-1".
> any ideas why the SQL Server is not returning the correct number of
records?
>
> (if i remove the "GROUP BY NAME" from the SQL statement, both queries
return
> the same number of records, as it should...)
>
> --
> sc
> Mid-State Consultants
> scarter@mscon.com
>

Re:

Anonymous — Tue, 09 Jan 2001 20:00:27 GMT

i have reviewed the error count associated with his object, it was 0. i
have exhausted all the possibilities of the way the record set is being
opened, still no success. what did you mean by "driver"?

i am beginning to suspect that the "GROUP BY" is creating the problem. this
conclusion is due to other operators (like "ORDER BY") which work, but the
"GROUP BY" does not.

thanks for the help.

--
sc
Mid-State Consultants
scarter@mscon.com
"Wouter van Eck" wrote in message
news:EEEA39D36145AE3F5D6B0F95876133AF@in.WebX.SaUCah8kaAW...
> I suggest you check your err object.. I suspect that there is a problem in
> the way you open the recordset in combination with the chosen driver.
>
> Wouter
>
> "Steve Carter" wrote in message
> news:FD9DB81516719FC69756CD162F1F0835@in.WebX.SaUCah8kaAW...
> > i have created two UDL's, one for ACCESS and the other for SQL Server.
my
> > SQL statement is:
> >
> > xRS.Open "SELECT NAME FROM PEDESTAL GROUP BY NAME", db,
adOpenKeyset,
> > adLockOptimistic
> >
> > when i run the SQL statement attached through the ACCESS UDL, the result
> is
> > grouped properly with the record count set to the correct number of
> records.
> > when i run the same SQL statement in SQL Server attached through the SQL
> > Server UDL, i return only one record and the record count is set to
"-1".
> > any ideas why the SQL Server is not returning the correct number of
> records?
> >
> > (if i remove the "GROUP BY NAME" from the SQL statement, both queries
> return
> > the same number of records, as it should...)
> >
> > --
> > sc
> > Mid-State Consultants
> > scarter@mscon.com
> >
>

Re: ACCESS vs. SQL Server - SQL statement...

Anonymous — Wed, 10 Jan 2001 05:22:56 GMT

Try replacing GROUP BY NAME with GROUP BY 1, which is a column number for
the returned recordset. And try adding a semi-colon at the end of the
SELECT.
--
John Goodfellow
irtf'nm
use 'microtouch' in address to email

"Steve Carter" wrote in message
news:FD9DB81516719FC69756CD162F1F0835@in.WebX.SaUCah8kaAW...
> i have created two UDL's, one for ACCESS and the other for SQL Server. my
> SQL statement is:
>
> xRS.Open "SELECT NAME FROM PEDESTAL GROUP BY NAME", db, adOpenKeyset,
> adLockOptimistic
>
> when i run the SQL statement attached through the ACCESS UDL, the result
is
> grouped properly with the record count set to the correct number of
records.
> when i run the same SQL statement in SQL Server attached through the SQL
> Server UDL, i return only one record and the record count is set to "-1".
> any ideas why the SQL Server is not returning the correct number of
records?
>
> (if i remove the "GROUP BY NAME" from the SQL statement, both queries
return
> the same number of records, as it should...)
>
> --
> sc
> Mid-State Consultants
> scarter@mscon.com
>

Re: ACCESS vs. SQL Server - SQL statement...

Anonymous — Wed, 10 Jan 2001 08:40:58 GMT

I'm guessing your problem is the adOpenKeyset. I'd have to go look but
isn't that opening the recordset for editing? Try a different option like
adOpenForwardOnly there and see if it works.

"Steve Carter" wrote in message
news:FD9DB81516719FC69756CD162F1F0835@in.WebX.SaUCah8kaAW...
> i have created two UDL's, one for ACCESS and the other for SQL Server. my
> SQL statement is:
>
> xRS.Open "SELECT NAME FROM PEDESTAL GROUP BY NAME", db, adOpenKeyset,
> adLockOptimistic
>
> when i run the SQL statement attached through the ACCESS UDL, the result
is
> grouped properly with the record count set to the correct number of
records.
> when i run the same SQL statement in SQL Server attached through the SQL
> Server UDL, i return only one record and the record count is set to "-1".
> any ideas why the SQL Server is not returning the correct number of
records?
>
> (if i remove the "GROUP BY NAME" from the SQL statement, both queries
return
> the same number of records, as it should...)
>
> --
> sc
> Mid-State Consultants
> scarter@mscon.com
>

Re: ACCESS vs. SQL Server - SQL statement...

Anonymous — Sun, 28 Jan 2001 21:16:04 GMT

Steve,

the problem that you get with SQL Server is probably not so much the
SQL Server options, but what object you are using to attach to the
server.

Are you using DAO for the Access stuff and ADO for the SQL Server
stuff?

When you query an SQL server, it doesn't return the number of records,
only whether the records returned are TRUE or FALSE. There are obvious
reasons for this, the simplest being that usually SQL Servers deal with
far larger data sets and you are very unlikely to sort through large
numbers of them.

The other thing you have to learn is that connecting to an SQL server
is not the same as connecting to an Access server, as the interface
changes because an SQL Server ALSO records the Username of the person
accessing the table. Note below I have two (2) dots between the
database name and the table name. That is telling the server that I am
going to use the DEFAULT user name.

Whenever I use a "<" or a ">"... that means that the things between
that are variables, and you replace that information WITH the symbols.
i.e. becomes YourDatabase

You can use a conditional loop at the after you have found the records
to sort through them if you require that.

SQLString1 = "DRIVER={SQL
Server};SERVER=;DATABASE=;UID=;PWD=;APP=;"
conCAD.Open SQLString1

SQLString1 = "SELECT * "
SQLString1 = SQLString1 & " FROM []..[]"
SQLString1 = SQLString1 & " WHERE
[]..[].[] = ''"
SQLString1 = SQLString1 & " ORDER BY
[]..[].[] DESC"

Set rsLSDumpStatus = New ADODB.Recordset
Call rsLSDumpStatus.Open(SQLString1, conCAD, adOpenDynamic)

If IsNull(rsLSDumpStatus.Fields(0)) = False Then
If rsLSDumpStatus.BOF = True And rsLSDumpStatus.EOF = True Then
' This means that no record is found

Else
' This means that some records ARE found

End If
End If

Steve Carter wrote:
>
> i have created two UDL's, one for ACCESS and the other for SQL Server. my
> SQL statement is:
>
> xRS.Open "SELECT NAME FROM PEDESTAL GROUP BY NAME", db, adOpenKeyset,
> adLockOptimistic
>
> when i run the SQL statement attached through the ACCESS UDL, the result is
> grouped properly with the record count set to the correct number of records.
> when i run the same SQL statement in SQL Server attached through the SQL
> Server UDL, i return only one record and the record count is set to "-1".
> any ideas why the SQL Server is not returning the correct number of records?
>
> (if i remove the "GROUP BY NAME" from the SQL statement, both queries return
> the same number of records, as it should...)
>
> --
> sc
> Mid-State Consultants
> scarter@mscon.com