Thanks Jack -- it must have been the parenthesis.  Great stuff, this Jet
SQL!

Pete

-----Original Message-----
From: Jack [mailto:jdunstan7@h...]
Sent: Thursday, September 26, 2002 9:46 AM
To: professional vb
Subject: [pro_vb] Re: A little SQL help, please?


Pete,

Running this in Access works for me - I did have to change the "value" field
to something else (i chose 'textvalue'), so this should be working.  Also
ran in vb project so you should be able to do this.

You do need to get rid of the "(  )"'s around your select statement.

   mCn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & msQualifiedDatabaseName
    mCn.CursorLocation = adUseClient
    mCn.Open
    lsSQL = "INSERT INTO d_username ( Textvalue )" _
             & " SELECT DISTINCT [Owner]FROM Tasks;"

    mCn.Execute lsSQL


hth
Jack Dunstan


----- Original Message -----
From: "Peter N. Kipe" <pkipe@c...>
To: "professional vb" <pro_vb@p...>
Sent: Thursday, September 26, 2002 2:05 AM
Subject: [pro_vb] Re: A little SQL help, please?


> Berkay,
>
> Changing the column name from 'value' to 'user_name' had no effect on the
> problem.  I think it's a bug  -- there were several references to similar
> (but not exact) problems in the knowledgebase.
>
> I changed it to use two recordsets, loading the first with my distinct
set,
> and using the second to AddNew into the new table for each row in the
> distinct set.  It works, even if it isn't elegant.
>
> Thanks anyway,
>
> Pete
>
> -----Original Message-----
> From: Berkay Mese [mailto:berkaymese@y...]
> Sent: Wednesday, September 25, 2002 9:38 PM
> To: professional vb
> Subject: [pro_vb] Re: A little SQL help, please?
>
>
> Pete,
> You're trying to insert using the unique keyword and
> property clause "Value" as a field name descriptor.
> This does not get any confusion in SQL Server Engine ,
> but in Access you have to change this name in the
> table;
> ex: "d_Value"  'do not use 'VALUE'
>       lsSQL = _
>           "INSERT INTO D_UserName (d_Value) " & _
>           "(SELECT DISTINCT owner " & _
>           "FROM tasks);"
>
> Berkay
>
> --- Pete Kipe <pkipe@c...> wrote:
> > With an Access 2000 database and an ADO connection,
> > I'm trying to populate
> > a newly-defined table with the distinct set of
> > values from another table.
> > The table being populated consists of two columns,
> > an AutoNumber column,
> > and a column (value) defined as adVarWChar, 40.
> > Here's the connection and
> > the insert code:
> >
> >     mCn.ConnectionString = _
> >         "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> >         "Data Source=" & msQualifiedDatabaseName
> >     mCn.CursorLocation = adUseClient
> >     mCn.Open
> >
> >     lsSQL = _
> >         "INSERT INTO D_UserName (value) " & _
> >         "(SELECT DISTINCT owner " & _
> >         "FROM tasks);"
> >     mCn.Execute lsSQL
> >
> > I'm getting the error, "Syntax error in INSERT INTO
> > statement," with no
> > further explanation.  I've done this many, many
> > times in SQL Server --
> > does anyone know why it's failing here?
> >
> > Thanks,
> >
> > Pete
> >
> >
> > ---
> > Visual C# - A Guide for VB6 Developers
> > This book will make it easy to transfer your skills
> > from Visual Basic 6 to C#, the language of choice
> > of the .NET Framework.
> >
> http://www.wrox.com/ACON11.asp?ISBN=1861007175&p2p0059
> >
>
>
> __________________________________________________
> Do you Yahoo!?
> New DSL Internet Access from SBC & Yahoo!
> http://sbc.yahoo.com
>
>
> ---
> Visual C# - A Guide for VB6 Developers
> This book will make it easy to transfer your skills
> from Visual Basic 6 to C#, the language of choice
> of the .NET Framework.
> http://www.wrox.com/ACON11.asp?ISBN=1861007175&p2p0059
>
>
>
>
> ---
> Visual C# - A Guide for VB6 Developers
> This book will make it easy to transfer your skills
> from Visual Basic 6 to C#, the language of choice
> of the .NET Framework.
> http://www.wrox.com/ACON11.asp?ISBN=1861007175&p2p0059
>
>


---
Visual C# - A Guide for VB6 Developers
This book will make it easy to transfer your skills
from Visual Basic 6 to C#, the language of choice
of the .NET Framework.
http://www.wrox.com/ACON11.asp?ISBN=1861007175&p2p0059