Distinct not working in VBA recordset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Distinct not working in VBA recordset

Author

Topic

sql_wedge
Starting Member

18 Posts

Posted - 2005-08-26 : 12:43:02

I am having problems with executing a sql query using distinct keyword

I open a recordset for a form(frm_retrievesupp)(see below)

My problem is that the distinct doesnt work -- the query produces the records but as if there is no distinct!Is there a problem with using this keyword within an access framework?

Dim rs As New ADODB.Recordset
On Error GoTo Form_Error
Call dbConnectionOpen

rs.ActiveConnection = ModConnection

select distinct datasourceref,organisation,prefix,firstname,lastname,jobtitle,address1,address2,address3,address4,town,county,postcode,email,telephone,mobile,fax , deceasedFlag, goneawayFlag, dataProtectionFlag from dbo.extraction

If rs.BOF Or rs.EOF Then ClosingStatus = "norecords" Else ClosingStatus = ""
If rs.BOF Or rs.EOF Then DoCmd.OpenForm ("frm_Crossroad") Else DoCmd.OpenForm "frm_retrievesupp", , , "username=" & CRMod_user & "", acFormEdit

Fatalsniper
Starting Member

45 Posts

Posted - 2005-08-26 : 19:19:08

Why don't you create a View?

sql_wedge
Starting Member

18 Posts

Posted - 2005-08-31 : 04:21:15

Can you give an example of a view

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-31 : 04:24:17

Distinct will return distinct records only if all the specified columns of two or more rows are same. Post some sample data

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources