473,372 Members | 746 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Access as Frontend to Oracle questions - Memo-to-CLOB? View vs. Query?

Hi All,

I started developing in Access, and people took notice and so we're
starting to migrate into our corporate's bigger Oracle system.

I'll still be using my developed Access front ends, but will be
migrating my back ends to Oracle ODBC.

1. Does anyone have recommendations for books or web resources for
general rules/guidelines/help on doing this? I haven't found a good
"narrower" term for searching usenet or the web that gets me to a
general guide.

2. Speed is an issue. I've been thinking that running stored Access
queries on our distributed network will take longer than simply
calling defined Views in Oracle running on the dedicated Oracle
servers. Any advice here?

3. I have lots of tables where I liberally used "Memo" fields. Some
of these, I'm realizing were just lazy, and I should have made text
columns of a certain size. However, a few, I need to be open ended
for people to type up reports/feedback/etc. Any guidance out there on
the proper equivalent in Oracle? CLOB's? Text with 4000 characters?
(Usage: The total number of these types of fields will be fairly
limited. I anticipate, at most, maybe 10,000 in 5 years at the most.
Many times, these will be blank or just have one or two sentences.
It's only wordy guys like me who'll use lots of space...)

Thanks,

Jon

Feb 1 '07 #1
5 7185
jonceramic wrote:

1. Does anyone have recommendations for books or web resources for
general rules/guidelines/help on doing this? I haven't found a good
"narrower" term for searching usenet or the web that gets me to a
general guide.
In my opinion (and Rick Brandt may disagree with me on this), even if
you exclusively use ODBC linked tables (against which you current Access
querydefs will work with no or very little modification needed), you
should still have an understanding of how Oracle SQL works.

Thus, while not a book on migration to Oracle from Access/Jet, the book
that I've had great success with is _Oracle SQL and PL/SQL Handbook_ by
John Adolph Palinski. Should be available via Amazon. There are
doubtless other great books out there, but this one is what is on my
desk (staring up at me, battered and with pages falling out of the well
used bindings).
2. Speed is an issue. I've been thinking that running stored Access
queries on our distributed network will take longer than simply
calling defined Views in Oracle running on the dedicated Oracle
servers. Any advice here?
My personal preference is pass through queries using Oracle syntax
versus using linked tables and Jet SQL syntax. In a well designed
Oracle database, simple to moderately complex queries in Access against
Oracle tables work just as well and even fractionally faster than
straight Oracle SQL via pass through queries. The key, here, though is
"well designed" Oracle tables, ie, properly indexed and PK/FK and other
constraints defined. Really, to properly design Oracle tables, you use
the same care and consideration you would to establish good Jet table
design.

However, sometimes, even in a well designed Oracle database, if you are
putting together a lot of tables in a Select statement, the ODBC
connection can start dropping whole columns of information and groaning
considerably.

In my experience, Select clause sub queries (indeed sub queries of any
kind) suck golf balls through a narrow urethra in a linked tables set
up. Not sure what Rck Brandt's comments on this might be.

In the situation described in the previous two paragraphs, I found pass
through queries were the cat's meow. Plus, they allowed me to better
talk to my Oracle admins when I was having problems or wanted them to
look at see why a table might be behaving unexpectedly.

The big disadvantage of pass through queries is that they are read only.
Thus if you have a continuous form in which you add records on the
fly, you can't do it the way you would if you had a Jet or linked table
Access query as a recordsource for the form. This leads to more
development consideration (routines for adding records, routines for
updating records, routines for deleting records) that those of us who
also develop in Jet find longer than what we're used to.

Not related to questions, but if your organization is running Oracle,
you will have Oracle admins who will be of help in terms of setting up
table spaces, your schema and such.
3. I have lots of tables where I liberally used "Memo" fields. Some
of these, I'm realizing were just lazy, and I should have made text
columns of a certain size. However, a few, I need to be open ended
for people to type up reports/feedback/etc. Any guidance out there on
the proper equivalent in Oracle? CLOB's? Text with 4000 characters?
(Usage: The total number of these types of fields will be fairly
limited. I anticipate, at most, maybe 10,000 in 5 years at the most.
Many times, these will be blank or just have one or two sentences.
It's only wordy guys like me who'll use lots of space...)
I actually don't have experience with CLOBs, BLOBs or BFILE data types.
My major Oracle app is an older one and uses Longs (of which you may
only have one in any table or view - a "view" BTW, if you're not sure,
is actually the equivalent of a stored Access/Jet query. I do find the
varchar2 data type (which can be up to 4000 characters long) to be very
useful for most things.

I guess the thing to do is run a Jet totals query on numbers of current
record for the length of your larger field(s) and go from there.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
Feb 1 '07 #2
On Feb 1, 1:19 pm, Tim Marshall
<TIM...@PurplePandaChasers.Moertheriumwrote:
Thus, while not a book on migration to Oracle from Access/Jet, the book
that I've had great success with is _Oracle SQL and PL/SQL Handbook_ by
John Adolph Palinski. Should be available via Amazon. There are
doubtless other great books out there, but this one is what is on my
desk (staring up at me, battered and with pages falling out of the well
used bindings).
Our Developer had that on her lap this morning as I talked to her! :)
The big disadvantage of pass through queries is that they are read only.
Thus if you have a continuous form in which you add records on the
fly, you can't do it the way you would if you had a Jet or linked table
Access query as a recordsource for the form. This leads to more
development consideration (routines for adding records, routines for
updating records, routines for deleting records) that those of us who
also develop in Jet find longer than what we're used to.
Yeah, I think for my skill level at the moment, I'll have to stay away
from pass through queries and just stick with linked tables. I'd like
to do more, but projects have deadlines, and my mind is only so big...
Not related to questions, but if your organization is running Oracle,
you will have Oracle admins who will be of help in terms of setting up
table spaces, your schema and such.
Most definitely.
I do find the
varchar2 data type (which can be up to 4000 characters long) to be very
useful for most things.
In talking to my admin, varchar2 is definitely the way to go because
of the "var" part. No more worrying about space because of the "var"
part.

Many thanks, Tim.

Feb 2 '07 #3
jonceramic wrote:
Yeah, I think for my skill level at the moment, I'll have to stay away
from pass through queries and just stick with linked tables. I'd like
to do more, but projects have deadlines, and my mind is only so big...
That's how I started and it did very well for me, though I did have some
issues.

You asked about views and I didn't really comment on them. here's my
experience:

Views can be linked to as if they were Oracle tables. So if you find
something a bit sticky or doesn't quite give you the performance you
want in a bunch of linked tables set up in an Access query, you can talk
to your Oracle admin about setting up a view for you in Oracle that
gives you the results you want and then linking directly to the view.

In terms of speed - as I said, I use exclusively PTQs. I have found on
small to moderate size databases (less than a million records) that I
don't have a noticeable difference in speed when I constuct my oracle
SQL via VBA without using views from doing the same sort of queries
using views.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
Feb 2 '07 #4
Tim Marshall wrote:
jonceramic wrote:

>1. Does anyone have recommendations for books or web resources for
general rules/guidelines/help on doing this? I haven't found a good
"narrower" term for searching usenet or the web that gets me to a
general guide.

In my opinion (and Rick Brandt may disagree with me on this), even if
you exclusively use ODBC linked tables (against which you current
Access querydefs will work with no or very little modification
needed), you should still have an understanding of how Oracle SQL
works.
Thus, while not a book on migration to Oracle from Access/Jet, the
book that I've had great success with is _Oracle SQL and PL/SQL
Handbook_ by John Adolph Palinski. Should be available via Amazon. There
are doubtless other great books out there, but this one is what
is on my desk (staring up at me, battered and with pages falling out
of the well used bindings).
>2. Speed is an issue. I've been thinking that running stored Access
queries on our distributed network will take longer than simply
calling defined Views in Oracle running on the dedicated Oracle
servers. Any advice here?

My personal preference is pass through queries using Oracle syntax
versus using linked tables and Jet SQL syntax. In a well designed
Oracle database, simple to moderately complex queries in Access
against Oracle tables work just as well and even fractionally faster
than straight Oracle SQL via pass through queries. The key, here,
though is "well designed" Oracle tables, ie, properly indexed and
PK/FK and other constraints defined. Really, to properly design
Oracle tables, you use the same care and consideration you would to
establish good Jet table design.

However, sometimes, even in a well designed Oracle database, if you
are putting together a lot of tables in a Select statement, the ODBC
connection can start dropping whole columns of information and
groaning considerably.

In my experience, Select clause sub queries (indeed sub queries of any
kind) suck golf balls through a narrow urethra in a linked tables set
up. Not sure what Rck Brandt's comments on this might be.

In the situation described in the previous two paragraphs, I found
pass through queries were the cat's meow. Plus, they allowed me to
better talk to my Oracle admins when I was having problems or wanted
them to look at see why a table might be behaving unexpectedly.

The big disadvantage of pass through queries is that they are read
only. Thus if you have a continuous form in which you add records on
the fly, you can't do it the way you would if you had a Jet or linked
table Access query as a recordsource for the form. This leads to more
development consideration (routines for adding records, routines for
updating records, routines for deleting records) that those of us who
also develop in Jet find longer than what we're used to.

Not related to questions, but if your organization is running Oracle,
you will have Oracle admins who will be of help in terms of setting up
table spaces, your schema and such.
>3. I have lots of tables where I liberally used "Memo" fields. Some
of these, I'm realizing were just lazy, and I should have made text
columns of a certain size. However, a few, I need to be open ended
for people to type up reports/feedback/etc. Any guidance out there
on the proper equivalent in Oracle? CLOB's? Text with 4000
characters? (Usage: The total number of these types of fields will
be fairly limited. I anticipate, at most, maybe 10,000 in 5 years
at the most. Many times, these will be blank or just have one or two
sentences. It's only wordy guys like me who'll use lots of space...)

I actually don't have experience with CLOBs, BLOBs or BFILE data
types. My major Oracle app is an older one and uses Longs (of which
you may only have one in any table or view - a "view" BTW, if you're
not sure, is actually the equivalent of a stored Access/Jet query. I
do find the varchar2 data type (which can be up to 4000 characters
long) to be very useful for most things.

I guess the thing to do is run a Jet totals query on numbers of
current record for the length of your larger field(s) and go from
there.
I agree with everything you say here. Passthroughs will be better any time
you are joining two or more tables on the server. The use of links is
mostly when you need to edit the data. Querying a single table is when I
have found that an Access query on a link is usually just as fast as a
passthrough. I qualify that by adding that my experience has been with SQL
Server and UDB400. Oracle might be a completely different experience in
these areas.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Feb 2 '07 #5
There's also good free online documentation at:
http://www.oracle.com/pls/db102/homepage?remark=tahiti
You might also be interested in SQL*PLUS, the Oracle command line
utility that lets you execute SQL in a command line window for small
ad hoc queries.
I've been doing Access/Oracle for the last 6 months (after years of
Acces/Jet/SQL Server);
Oracle SQL is very powerful. PL/SQL is a great procedural language to
work in and to my mind much easier to use than T-SQL.
Good luck
Terry Bell

On Feb 2, 6:19 am, Tim Marshall
<TIM...@PurplePandaChasers.Moertheriumwrote:
jonceramic wrote:
1. Does anyone have recommendations for books or web resources for
general rules/guidelines/help on doing this? I haven't found a good
"narrower" term for searching usenet or the web that gets me to a
general guide.

In my opinion (and Rick Brandt may disagree with me on this), even if
you exclusively use ODBC linked tables (against which you current Access
querydefs will work with no or very little modification needed), you
should still have an understanding of how Oracle SQL works.

Thus, while not a book on migration to Oracle from Access/Jet, the book
that I've had great success with is _Oracle SQL and PL/SQL Handbook_ by
John Adolph Palinski. Should be available via Amazon. There are
doubtless other great books out there, but this one is what is on my
desk (staring up at me, battered and with pages falling out of the well
used bindings).
2. Speed is an issue. I've been thinking that running stored Access
queries on our distributed network will take longer than simply
calling defined Views in Oracle running on the dedicated Oracle
servers. Any advice here?

My personal preference is pass through queries using Oracle syntax
versus using linked tables and Jet SQL syntax. In a well designed
Oracle database, simple to moderately complex queries in Access against
Oracle tables work just as well and even fractionally faster than
straight Oracle SQL via pass through queries. The key, here, though is
"well designed" Oracle tables, ie, properly indexed and PK/FK and other
constraints defined. Really, to properly design Oracle tables, you use
the same care and consideration you would to establish good Jet table
design.

However, sometimes, even in a well designed Oracle database, if you are
putting together a lot of tables in a Select statement, the ODBC
connection can start dropping whole columns of information and groaning
considerably.

In my experience, Select clause sub queries (indeed sub queries of any
kind) suck golf balls through a narrow urethra in a linked tables set
up. Not sure what Rck Brandt's comments on this might be.

In the situation described in the previous two paragraphs, I found pass
through queries were the cat's meow. Plus, they allowed me to better
talk to my Oracle admins when I was having problems or wanted them to
look at see why a table might be behaving unexpectedly.

The big disadvantage of pass through queries is that they are read only.
Thus if you have a continuous form in which you add records on the
fly, you can't do it the way you would if you had a Jet or linked table
Access query as a recordsource for the form. This leads to more
development consideration (routines for adding records, routines for
updating records, routines for deleting records) that those of us who
also develop in Jet find longer than what we're used to.

Not related to questions, but if your organization is running Oracle,
you will have Oracle admins who will be of help in terms of setting up
table spaces, your schema and such.
3. I have lots of tables where I liberally used "Memo" fields. Some
of these, I'm realizing were just lazy, and I should have made text
columns of a certain size. However, a few, I need to be open ended
for people to type up reports/feedback/etc. Any guidance out there on
the proper equivalent in Oracle? CLOB's? Text with 4000 characters?
(Usage: The total number of these types of fields will be fairly
limited. I anticipate, at most, maybe 10,000 in 5 years at the most.
Many times, these will be blank or just have one or two sentences.
It's only wordy guys like me who'll use lots of space...)

I actually don't have experience with CLOBs, BLOBs or BFILE data types.
My major Oracle app is an older one and uses Longs (of which you may
only have one in any table or view - a "view" BTW, if you're not sure,
is actually the equivalent of a stored Access/Jet query. I do find the
varchar2 data type (which can be up to 4000 characters long) to be very
useful for most things.

I guess the thing to do is run a Jet totals query on numbers of current
record for the length of your larger field(s) and go from there.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

Feb 3 '07 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: matt bourke | last post by:
Hi, Can you please tell me what the advantage is of using an access backend with a VB frontend OVER access as a frontend and backend. M
24
by: deko | last post by:
I have an mdb (2002 file format) that uses memo fields extensively. I've read a lot about how problematic memo fields can be and that avoiding them is a good idea. But I'm stuck with them and was...
1
by: AquilaV | last post by:
Ciao a tutti, ho un problema che non riesco a risolvere nonostante abbia girato mille newsgroup... Ho creato un'applicazione con access divisa in frontend e backend. Dall'interfaccia principale...
3
by: bclegg | last post by:
Hi, Does anybody know if it possible to force Oracle to release cursors that have been generated when servicing a vb.net stored procedure call? ie My app makes a call that populates a field in a...
0
by: softwareengineer2006 | last post by:
10000 Interview Questions And Answers(C,C++,JAVA,DOTNET,Oracle,SAP) I have listed over 10000 interview questions asked in interview/placement test papers for all companies between year 2000-2005...
0
by: softwareengineer2006 | last post by:
Oracle,C#,JSP,.NET,ASP,J2EE,Networking,Java Interview questions and answers I have listed over 1000 Oracle,C#,JSP,.NET,ASP,J2EE,Networking,Java interview questions and answers in my website...
49
by: Allen Browne | last post by:
If you are looking for opinon on what's useful in Access 2007, there's a new article at: http://allenbrowne.com/Access2007.html Covers what's good (useful features), what's mixed (good and bad),...
0
by: Dilruba | last post by:
I want retrieve data from Ms Access 2000 database by using ASP and data type memo and after that i want e-mail that data automatically
0
by: SePp | last post by:
Hello all, I'm looking for the pro's and con's for an MS Access Application which has an Oracle backend (Oracle Express). Especially how it is about the autocommit ( I believe it is off ), the...
2
by: Michael Shilokovsky | last post by:
Srki wrote: Hi. I've got Oracle 8.1.7 EE for Solaris x86 if you interested. Rbear. Please contact me directly if any questions
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.