Accessing Oracle Tables from MS Access

[berj]

How difficult is this? I'm having a tough time getting a simple select to
work.

I am using MS Access 2002 and Windows XP.
What do I have to do to make a simple select statement like this work:

select id from emp
I have stumbled upon many websites although none straight to the point.
Any help in how to get this done, or pointing me in the right direction of a
book or website, would be greatly appreciated!
berj

[Steve Jorgensen]

The easiest way is to link the table in Access, then query the link. Is that
how you're trying to do it?

On Mon, 28 Feb 2005 21:25:36 -0800, "berj" <be**@adelphia.net> wrote:

How difficult is this? I'm having a tough time getting a simple select to
work.

I am using MS Access 2002 and Windows XP.
What do I have to do to make a simple select statement like this work:

select id from emp
I have stumbled upon many websites although none straight to the point.
Any help in how to get this done, or pointing me in the right direction of a
book or website, would be greatly appreciated!
berj

[berj]

Yes, I do have the table links working.

But I did want to run the select for such things as grabbing the next number
off of a sequence. As in:

select test.nextval from dual;
Where "test" is the name of an Oracle sequence.

berj
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:bp********************************@4ax.com...

The easiest way is to link the table in Access, then query the link. Is that how you're trying to do it?

On Mon, 28 Feb 2005 21:25:36 -0800, "berj" <be**@adelphia.net> wrote:

How difficult is this? I'm having a tough time getting a simple select towork.

I am using MS Access 2002 and Windows XP.
What do I have to do to make a simple select statement like this work:

select id from emp
I have stumbled upon many websites although none straight to the point.
Any help in how to get this done, or pointing me in the right direction of abook or website, would be greatly appreciated!
berj

[Steve Jorgensen]

Access does not deal directly with sequences, so you'll have to use a
pass-through query. You should be able to simply type your statement into the
pass-through query, set the query's connection property to point to the
server, and run it.

On Mon, 28 Feb 2005 21:48:41 -0800, "berj" <be**@adelphia.net> wrote:

Yes, I do have the table links working.

But I did want to run the select for such things as grabbing the next number
off of a sequence. As in:

select test.nextval from dual;
Where "test" is the name of an Oracle sequence.

berj
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:bp********************************@4ax.com.. .

The easiest way is to link the table in Access, then query the link. Is

that

how you're trying to do it?

On Mon, 28 Feb 2005 21:25:36 -0800, "berj" <be**@adelphia.net> wrote:

>How difficult is this? I'm having a tough time getting a simple selectto >work.
>
>I am using MS Access 2002 and Windows XP.
>
>
>What do I have to do to make a simple select statement like this work:
>
>select id from emp
>
>
>I have stumbled upon many websites although none straight to the point.
>
>
>Any help in how to get this done, or pointing me in the right directionof a >book or website, would be greatly appreciated!
>
>
>berj
>

[berj]

how?

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:li********************************@4ax.com...

Access does not deal directly with sequences, so you'll have to use a
pass-through query. You should be able to simply type your statement into the pass-through query, set the query's connection property to point to the
server, and run it.

On Mon, 28 Feb 2005 21:48:41 -0800, "berj" <be**@adelphia.net> wrote:

Yes, I do have the table links working.

But I did want to run the select for such things as grabbing the next numberoff of a sequence. As in:

select test.nextval from dual;
Where "test" is the name of an Oracle sequence.

berj
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:bp********************************@4ax.com.. .

The easiest way is to link the table in Access, then query the link. Is

that

how you're trying to do it?

On Mon, 28 Feb 2005 21:25:36 -0800, "berj" <be**@adelphia.net> wrote:

>How difficult is this? I'm having a tough time getting a simple

selectto

>work.
>
>I am using MS Access 2002 and Windows XP.
>
>
>What do I have to do to make a simple select statement like this work:
>
>select id from emp
>
>
>I have stumbled upon many websites although none straight to the

point. >
>
>Any help in how to get this done, or pointing me in the right

directionof a

>book or website, would be greatly appreciated!
>
>
>berj
>

[Tim Marshall]

berj wrote:

how?

Open a new query in design view.

Close the Show table window without picking any tables/queries

On menus, cLick on Query->SQL SPecific->PAss-through

The query is now in SQL view (the white background)

Assign the DSN connection: click View->Properties, if the properties
window is not visible

On the query properties window, there is aq property called ODBC COnnect
str. CLick the builder button and select the DSN just as you would for
making a linked table.

Now, type in Select Sequence_name.nextval from dual (do not use the semi
colon - Oracle is very picky about semi colons and they are not required
in select statements).

Run the query and you advance the sequence by one or whatever your
interval is.

YOu can also use the above method to run many SQL commands.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Grumpy Old Man]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote:

Oracle is very picky about semi colons and they are not required
in select statements

Presumably you mean just in Access SQL statements? They're certainly
needed in SQLplus.

[Tim Marshall]

Grumpy Old Man wrote:

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote:

Oracle is very picky about semi colons and they are not required
in select statements

Presumably you mean just in Access SQL statements? They're certainly
needed in SQLplus.

Actually Grumpy, for select, insert, delete, update statements, they are
not. 8)

They are only required in SQL Plus if you don't want to have to press
the / key to execute. The semi-colon will execute an SQL Plus (and
presumeably TOAD, though I've never played with TOAD - and no frog jokes
please!!! 8) ) on pressing enter. Otherwise, you need to execute the
statement with the back slash.

I've had difficulty when including a semi colon in a pass through query
because Oracle doesn't know what to do with it when the statement is
passed directly to it this way. It's been a few years, but IIRC, it
returned an invalid character error in Oracle trandsferred to Access.

My understanding is the semi-colon acts as the terminator for PL/SQL
code. I am pretty shaky on PL/SQL, though, but this is what I've
divined from my Oracle DBA and comp.databases.Oracle.*
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Grumpy Old Man]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote:

Actually Grumpy, for select, insert, delete, update statements, they are
not. 8)

They are only required in SQL Plus if you don't want to have to press
the / key to execute.

That was my understanding, thanks for the clarification.

Keith.