> Greetings

> Hope I'm posting to the right place.  If not, feel free to "tell me where to
> go..." :)

> The insert statement following this message works if I execute it from SQL
> view in a Microsoft Access 97 Query.  When I click the exclamation mark (!),
> Access displays the message "You are about to append 48 row(s)... blah,
> blah... Yes, No."  I click "Yes" and the rows are appended.  I verify they
> have been appended by looking at the table.

> It does not work if I execute it either from MS-Query 8.00 (ODBC - driver
> version 4.00.4202.00) or through "Microsoft.JET.OLEDB.3.51" (ADO, Visual
> Basic).

> MS-Query says "Executed SQL Statement Successfully".  ADO does not return an
> error (either through an exception, a return value or through the connection
> object errors collection.)  In both cases (ADO and MS-Query), no records are
> inserted.  I verify there are no records inserted by looking at the table.

> Anyone have any idea what the problem is?

> Thanks
> Tony

> ' From a MS Access module or from VB (doesn't work)
> Sub MySub()
>    Dim sTemp As String
>    Dim ocnTemp As Object
>    Set ocnTemp = CreateObject("adodb.connection")
>    ocnTemp.Provider = "Microsoft.JET.OLEDB.3.51"
>    ocnTemp.ConnectionString = "c:\users\aevans\vsn4.mdb"
>    ocnTemp.Open
>    sTemp = "Insert Into 00000001_0000_tblsysLock( ORGCREATED, USERCREATED,
> DATECREATED, " & _
>       "ORGMODIFIED, USERMODIFIED, DATEMODIFIED, TableName, RecordId ) Select
> 1, " & _
>       "'aevans', #10/17/1999 2:40:00 PM#, 1, 'aevans', #10/17/1999 2:40:00
> PM#, 'T1', CONTROL " & _
>       "From 00000001_0000_T1 Where (Name Like('some*')) And CONTROL Not In
>  Select RecordId " & _
>       "From 00000001_0000_tblsysLock Where TableName = 'T1' And USERCREATED
> = 'aevans'); "
>    ocnTemp.Execute sTemp
>    ocnTemp.Close
>    Set ocnTemp = Nothing
> End Sub

> In MS-Query (doesn't work) or an Access QueryDef (works).

> Insert Into 00000001_0000_tblsysLock
> (
>     ORGCREATED,
>     USERCREATED,
>     DATECREATED,
>     ORGMODIFIED,
>     USERMODIFIED,
>     DATEMODIFIED,

>  TableName,
>     RecordId
> )
>     Select
>         1,
>         'aevans',
>         #10/17/1999 2:40:00 PM#,
>         1,
>         'aevans',
>         #10/17/1999 2:40:00 PM#,
>         'T1',
>         CONTROL
>     From
>         00000001_0000_T1
>     Where
>     (
>         Name Like('some*')
>     ) And
>     CONTROL Not In

>         Select
>             RecordId
>         From
>             00000001_0000_tblsysLock
>         Where
>             TableName = 'T1' And
>             USERCREATED = 'aevans'
>     );

>Greetings

>Hope I'm posting to the right place.  If not, feel free to "tell me where to
>go..." :)

>The insert statement following this message works if I execute it from SQL
>view in a Microsoft Access 97 Query.  When I click the exclamation mark (!),
>Access displays the message "You are about to append 48 row(s)... blah,
>blah... Yes, No."  I click "Yes" and the rows are appended.  I verify they
>have been appended by looking at the table.

>It does not work if I execute it either from MS-Query 8.00 (ODBC - driver
>version 4.00.4202.00) or through "Microsoft.JET.OLEDB.3.51" (ADO, Visual
>Basic).

>MS-Query says "Executed SQL Statement Successfully".  ADO does not return an
>error (either through an exception, a return value or through the connection
>object errors collection.)  In both cases (ADO and MS-Query), no records are
>inserted.  I verify there are no records inserted by looking at the table.

>Anyone have any idea what the problem is?

>Thanks
>Tony

>' From a MS Access module or from VB (doesn't work)
>Sub MySub()
>   Dim sTemp As String
>   Dim ocnTemp As Object
>   Set ocnTemp = CreateObject("adodb.connection")
>   ocnTemp.Provider = "Microsoft.JET.OLEDB.3.51"
>   ocnTemp.ConnectionString = "c:\users\aevans\vsn4.mdb"
>   ocnTemp.Open
>   sTemp = "Insert Into 00000001_0000_tblsysLock( ORGCREATED, USERCREATED,
>DATECREATED, " & _
>      "ORGMODIFIED, USERMODIFIED, DATEMODIFIED, TableName, RecordId ) Select
>1, " & _
>      "'aevans', #10/17/1999 2:40:00 PM#, 1, 'aevans', #10/17/1999 2:40:00
>PM#, 'T1', CONTROL " & _
>      "From 00000001_0000_T1 Where (Name Like('some*')) And CONTROL Not In
> Select RecordId " & _
>      "From 00000001_0000_tblsysLock Where TableName = 'T1' And USERCREATED
>= 'aevans'); "
>   ocnTemp.Execute sTemp
>   ocnTemp.Close
>   Set ocnTemp = Nothing
>End Sub

>In MS-Query (doesn't work) or an Access QueryDef (works).

>Insert Into 00000001_0000_tblsysLock
>(
>    ORGCREATED,
>    USERCREATED,
>    DATECREATED,
>    ORGMODIFIED,
>    USERMODIFIED,
>    DATEMODIFIED,

> TableName,
>    RecordId
>)
>    Select
>        1,
>        'aevans',
>        #10/17/1999 2:40:00 PM#,
>        1,
>        'aevans',
>        #10/17/1999 2:40:00 PM#,
>        'T1',
>        CONTROL
>    From
>        00000001_0000_T1
>    Where
>    (
>        Name Like('some*')
>    ) And
>    CONTROL Not In

>        Select
>            RecordId
>        From
>            00000001_0000_tblsysLock
>        Where
>            TableName = 'T1' And
>            USERCREATED = 'aevans'
>    );

> Tony,

> There are subtle diffences between Access SQL and "Proper" SQL as run by
Oracle, SQL
> Server, etc.

> One of these differences is the use of wildcard characters in queries.
Access uses * for
> multiple characters and ? for single characters.  "Proper" SQL uses ? for
multiple (I
> think) and underscore _ for single.

> "So What?" I hear you say - I fell foul of this problems a couple of
months back
> connecting to an Access database through ADO and only resolved the problem
when switching
> to ? and _ (underscore) characters in the queries.  Either ADO or the
underlying DB layer
> (ODBC/OLE-DB) uses "Proper" SQL.

> I mention this because I spotted the following line in your code: -

>       "From 00000001_0000_T1 Where (Name Like('some*')) And CONTROL Not In

> If I'm right the  Like('some*')  should read  Like("some?").

> Try it & see what happens

> Cheers, Jon

> >Greetings

> >Hope I'm posting to the right place.  If not, feel free to "tell me where
to
> >go..." :)

> >The insert statement following this message works if I execute it from
SQL
> >view in a Microsoft Access 97 Query.  When I click the exclamation mark
(!),
> >Access displays the message "You are about to append 48 row(s)... blah,
> >blah... Yes, No."  I click "Yes" and the rows are appended.  I verify
they
> >have been appended by looking at the table.

> >It does not work if I execute it either from MS-Query 8.00 (ODBC - driver
> >version 4.00.4202.00) or through "Microsoft.JET.OLEDB.3.51" (ADO, Visual
> >Basic).

> >MS-Query says "Executed SQL Statement Successfully".  ADO does not return
an
> >error (either through an exception, a return value or through the
connection
> >object errors collection.)  In both cases (ADO and MS-Query), no records
are
> >inserted.  I verify there are no records inserted by looking at the
table.

> >Anyone have any idea what the problem is?

> >Thanks
> >Tony

> >' From a MS Access module or from VB (doesn't work)
> >Sub MySub()
> >   Dim sTemp As String
> >   Dim ocnTemp As Object
> >   Set ocnTemp = CreateObject("adodb.connection")
> >   ocnTemp.Provider = "Microsoft.JET.OLEDB.3.51"
> >   ocnTemp.ConnectionString = "c:\users\aevans\vsn4.mdb"
> >   ocnTemp.Open
> >   sTemp = "Insert Into 00000001_0000_tblsysLock( ORGCREATED,
USERCREATED,
> >DATECREATED, " & _
> >      "ORGMODIFIED, USERMODIFIED, DATEMODIFIED, TableName, RecordId )
Select
> >1, " & _
> >      "'aevans', #10/17/1999 2:40:00 PM#, 1, 'aevans', #10/17/1999
2:40:00
> >PM#, 'T1', CONTROL " & _
> >      "From 00000001_0000_T1 Where (Name Like('some*')) And CONTROL Not
In
> > Select RecordId " & _
> >      "From 00000001_0000_tblsysLock Where TableName = 'T1' And
USERCREATED
> >= 'aevans'); "
> >   ocnTemp.Execute sTemp
> >   ocnTemp.Close
> >   Set ocnTemp = Nothing
> >End Sub

> >In MS-Query (doesn't work) or an Access QueryDef (works).

> >Insert Into 00000001_0000_tblsysLock

> >    ORGCREATED,
> >    USERCREATED,
> >    DATECREATED,
> >    ORGMODIFIED,
> >    USERMODIFIED,
> >    DATEMODIFIED,

> > TableName,
> >    RecordId
> >)
> >    Select
> >        1,
> >        'aevans',
> >        #10/17/1999 2:40:00 PM#,
> >        1,
> >        'aevans',
> >        #10/17/1999 2:40:00 PM#,
> >        'T1',
> >        CONTROL
> >    From
> >        00000001_0000_T1
> >    Where
> >    (
> >        Name Like('some*')
> >    ) And
> >    CONTROL Not In

> >        Select
> >            RecordId
> >        From
> >            00000001_0000_tblsysLock
> >        Where
> >            TableName = 'T1' And
> >            USERCREATED = 'aevans'
> >    );

> I don't know that this is the problem but what immediately somes to mind
is that
> Oracle and Access have very different syntax for SQL. I don't know where
the SQL
> is being executed in your code (Server or Client)

> I am doing some stuff where I import Oracle tables into an Access DB
(using the
> TransferDatabase method), run some queries on the Access instance of the
table
> and then drop the Access instance of the table and it seems to work fine.

> You may want to try something along those lines and see what happens.

> Hope this helps, Good Luck

> Mark

> > Greetings

> > Hope I'm posting to the right place.  If not, feel free to "tell me
where to
> > go..." :)

> > The insert statement following this message works if I execute it from
SQL
> > view in a Microsoft Access 97 Query.  When I click the exclamation mark
(!),
> > Access displays the message "You are about to append 48 row(s)... blah,
> > blah... Yes, No."  I click "Yes" and the rows are appended.  I verify
they
> > have been appended by looking at the table.

> > It does not work if I execute it either from MS-Query 8.00 (ODBC -
driver
> > version 4.00.4202.00) or through "Microsoft.JET.OLEDB.3.51" (ADO, Visual
> > Basic).

> > MS-Query says "Executed SQL Statement Successfully".  ADO does not
return an
> > error (either through an exception, a return value or through the
connection
> > object errors collection.)  In both cases (ADO and MS-Query), no records
are
> > inserted.  I verify there are no records inserted by looking at the
table.

> > Anyone have any idea what the problem is?

> > Thanks
> > Tony

> > ' From a MS Access module or from VB (doesn't work)
> > Sub MySub()
> >    Dim sTemp As String
> >    Dim ocnTemp As Object
> >    Set ocnTemp = CreateObject("adodb.connection")
> >    ocnTemp.Provider = "Microsoft.JET.OLEDB.3.51"
> >    ocnTemp.ConnectionString = "c:\users\aevans\vsn4.mdb"
> >    ocnTemp.Open
> >    sTemp = "Insert Into 00000001_0000_tblsysLock( ORGCREATED,
USERCREATED,
> > DATECREATED, " & _
> >       "ORGMODIFIED, USERMODIFIED, DATEMODIFIED, TableName, RecordId )
Select
> > 1, " & _
> >       "'aevans', #10/17/1999 2:40:00 PM#, 1, 'aevans', #10/17/1999
2:40:00
> > PM#, 'T1', CONTROL " & _
> >       "From 00000001_0000_T1 Where (Name Like('some*')) And CONTROL Not
In
> >  Select RecordId " & _
> >       "From 00000001_0000_tblsysLock Where TableName = 'T1' And
USERCREATED
> > = 'aevans'); "
> >    ocnTemp.Execute sTemp
> >    ocnTemp.Close
> >    Set ocnTemp = Nothing
> > End Sub

> > In MS-Query (doesn't work) or an Access QueryDef (works).

> > Insert Into 00000001_0000_tblsysLock
> > (
> >     ORGCREATED,
> >     USERCREATED,
> >     DATECREATED,
> >     ORGMODIFIED,
> >     USERMODIFIED,
> >     DATEMODIFIED,

> >  TableName,
> >     RecordId
> > )
> >     Select
> >         1,
> >         'aevans',
> >         #10/17/1999 2:40:00 PM#,
> >         1,
> >         'aevans',
> >         #10/17/1999 2:40:00 PM#,
> >         'T1',
> >         CONTROL
> >     From
> >         00000001_0000_T1
> >     Where
> >     (
> >         Name Like('some*')
> >     ) And
> >     CONTROL Not In

> >         Select
> >             RecordId
> >         From
> >             00000001_0000_tblsysLock
> >         Where
> >             TableName = 'T1' And
> >             USERCREATED = 'aevans'
> >     );