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
