Works in Access Query, not through OLEDB or ODBC 
Author Message
 Works in Access Query, not through OLEDB or ODBC

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

Quote:

> 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'
>     );



Wed, 18 Jun 1902 01:00:00 GMT  
 Works in Access Query, not through OLEDB or ODBC
Incredible!  That was it!  If you knew how little hair I had left after
wrestling with this one...

Anyway, I used the SQL Server compliant wildcard characters, that is % for
multiple and _ for single and that did the trick.

What's annoying to me is that the OLEDB provider for Access and SQL is
supposed to expose the proprietary features of those database backends as if
you were using them directly, whereas ODBC is supposed to standardize things
like wildcards in a particular driver (or that was my understanding).

Apparently, this is not the case, as Access wildcards do not work with the
Jet OLEDB provider...  Sigh.

Thanks again


Quote:


> 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'
> >    );



Wed, 18 Jun 1902 01:00:00 GMT  
 Works in Access Query, not through OLEDB or ODBC
I considered this work around.  If Jon Ashton hadn't solved my problem for
me here on the NG, I would have gone with that.

Thank you for your reply.

Tony


Quote:
> 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'
> >     );



Wed, 18 Jun 1902 01:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Works in Access Query, not through OLEDB or ODBC

2. Works in Access Query, not through OLEDB or ODBC

3. Query does not allow new records: OLEDB vs ODBC in Access 2K

4. OLEDB Access 2000 project view does not allows new records, whereas ODBC query does

5. ODBC - Call Failed - Query Works in Access 97 not in Access 2000

6. Parameters not working in Access 2000 ODBC query

7. Query works in Access but not with ASP/ODBC

8. Access, ODBC and SYBASE Queries to linked tables not working

9. Query problem when switching from ODBC to OLEDB

10. ACCESS'97 ODBC,OLEDB,ADO,DAO, SQL SRVR 7


 
Powered by phpBB® Forum Software © phpBB Group