Oracle, Access...deployment issues.

[The_Grudge]

I have written a fairly large program in Visual Basic that writes data to an Oracle database.

This program will be installed on 2 computers. In order to get everything up and running I need to install the Oracle client on these 2 computers as well as my Visual Basic program. That part is easy.

Here's where it get's difficult.

The software I have written is scheduling software for clinics in a hospital. The 2 end-users populate this schedule. Yesterday I found out that mangement wants to collect a few more peices of information, such as late clinic start or late clinic finish.

In order to capture this data they want to have a form installed on each computer in the clinic wing (approx 10 computers) where another user can enter that a) this clinic did infact take place and b) it started on time.

My question is this...

These additional users are only adding two or three more fields to the existing data. Is it possible to have them do this without installing the Oracle client somehow?

I was thinking of putting a Microsoft Access form on a server that DID have an Oracle license. Then the additional users would just open this Access database from their own computer, find the record they want, and add the additional one or two fields of data.

It seems silly to write an entire VB program and install 10 damn Oracle lisences for the sake of an additional 3 fields!

Will my idea work...better yet, any other suggestions?

[Webtest]

Does the data from the new fields have to populate the Oracle database real time, or can it be accumulated ... say daily or weekly. If it can be accumulated, perhaps the extra 10 PCs can put the data in the Access database, and then the Access 'server' with the Oracle driver can be scheduled to do a periodic update to the Oracle database. Just a thought (from a NEWBIE!).

Fortunately all my Oracle work is READ-ONLY. It is working fairly well now. Good Learning and Good Programming!

[GaryMazzone]

You will need the Oracle Client instsalled on any machine that connects to the oracle db. Weither you do it in VB or Acess you still need the client installed and the TNSNames setup to connect to the DB. What was the Lic. bought oraginally? Conncurent or Named Users?

[The_Grudge]

I know that I need an Oracle license for every PC putting data into Oracle, but don't want to install the client on 10 computers if I don't have to. It's time consuming and a pain.

If I can channel these 10 computers through a centralized server then giddyup!

[GaryMazzone]

Yes you can install the Client on the Network and point to the comman share in the pathing. At least that worked when I used to do that in version 7. Never tried in versions above that.

[The_Grudge]

Gary, could you explain how to do that a little more in detail please?

Maybe a weblink even?

Thanks

[GaryMazzone]

What we did was install the Oracle Client on the network. For each user we then modified the path to include the network directory that oracle was placed in. Set the Oracle_Home enviorment variable to then Network path. That way all things point to the same Oracle_Home\bin and Oracle_Home\network paths. The bin hold all of the executables for oracle and the network folder holds the TNSNames.ora file. The other anvantage is that you don't have to modify a buch of TNSNames all the time and there is no need to set up an Oracle Name server. But remember I have not done this since version 7.3.4. Since that time I always installed the client on each machine. Just for info I am an Oracle DBA also.

[The_Grudge]

I'll have to check with our DBA and see what he says about this tomorrow. Hopefully it will work. If not, it may open doors to other ideas.

Thanks.

[Webtest]

As far as I know, the 'driver' for Oracle for Access is a freebie ??? (Don't quote me on that). I don't know the difference between "client" and "driver", if there is any. I think the licensed software resides mostly on the Oracle side, and that there is some optional Oracle software available on the Windows side that can be purchased separately from the 'driver'. I saw the driver available for download from the Oracle website, and I didn't notice any discussion of licensing fees. My IT dept. loaded everything on my machine, so I don't know what all licensing was involved.

Please correct me if I am wrong ... this is just my impression of the situation.

[The_Grudge]

After doing a GOOGLE for "Oracle ODBC Drivers" the following link comes up...

http://www.oracle.com/technology/sof...dbc/index.html

Looks like I can just install the ORACLE ODBC driver instead of the full client because my program uses the ODBC connection anyways. It makes sense that you wouldn't have to install the entire client just for the ODBC driver.

I'll have to look into licensing but that's minor...the less crap I have to install the better!!

[GaryMazzone]

I have done ODBC calls to Oracle DB before and still need the conectivity of SQL*Net to connect to the DB. The only way that is supplied is with the Oracle client. The only method I have seen that does not need the client installed is in the following:
1) Useing Java there is a class that is supplied to java from oracle that lets the connection happen with out use of SQL*Net.
2) Using an Oracle Names server to handle all connections to the DB

[Webtest]

Ah! Mazz1 ... that strikes a bell! Some of the Oracle people kept giving me Java lines to connect to their databases, which didn't do much for me. And ... Yes, I did need to create a "tnsnames.ora" list to identify the database connections. I have no idea what software uses this file ... are you saying that it is not the driver?

Incidentally, there is an Oracle Forum, and one of the sections deals with Windows connectivity:
http://forums.oracle.com/forums/foru...ID=145&start=0

[GaryMazzone]

That is correct. TNSNames is file that contains all the connection information for each Oracle database you want to connect to. If you do not use an Oracle Names Server (this is not the actual name in Oracle 10 I need to look that up again), each machine needs to have a copy of this file located in the Oracle_Home\network folder (or direcotry). The SQL*Net transport protocol will use that file to read the connection information of a database.

[The_Grudge]

I know that everyone in my department uses a tnsNames file to connect as well as SQL plus.

So I guess the next question is this...

What's the easiest way to hook up 10 computers so they can populate a measley 3 fields in an Oracle database?

Keep in mind, I know very little about java

[GaryMazzone]

What version of Oracle is in use? I believe that there is a lite client on the Oracle web site that does not need the full client install to work. I you don't want to do any install then thew only option is to write a Java app that will be installed on the required machines to connect to the DB and update whatever you need to. If that is the case you might as well write the whole app in Java.

[The_Grudge]

We are currently using...

Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production

At least that is what the following SQL executed in SQL+ says.

select * from v$version where banner like 'Oracle%';

I'll look into that light client. I can't believe it's such a pain to do this. This basically tells me that it's not worth it to use VB as a front-end when the need to connect to Oracle applies to more than one other user.

I KNEW they should have let me write this as a web-based application.

And what happens when they upgrade to the next version of Oracle? I suppose I'll have to re-install everything. What a damn farce!

[Webtest]

Grudge ... You're not pretty when you whine! It took me nearly 5 months from the time I started to finally make the first connection to a remote Oracle database! It took a couple of months to even discover from the smart IT people that I needed a special Oracle driver on my desktop in addition to installing the Oracle Library in Access!!! It took IT nearly a month to finally get it installed on my machine.

[The_Grudge]

Your even less pretty when you whine!


The days of development in VB are over for me.

Time to learn ASP.net...only because PHP is not standard at my workplace.

[GaryMazzone]

Sorry to say even if you use ASP.Net to connect to the Oracle DB you will still need the Oracle Cilent on the machine that is being used to create the connection. I would not recommened that you place that machine as the same one running the Oracle DB.

[randem]

The_Grudge,

I am a little confused with this discussion. Is all you want is to connect two or more new users to the Oracle database?

If so, and you don't want to use the client:

Have Access connect to your tables on the Oracle Server. Link the tables you need through Access to Oracle. Then have the new users connect to the Access database to access the tables.

You can get away with this as long as you do not use and Oracle specific codeing in your app.

The 2 or more users will connect to Access, and Access will only have one connection to Oracle. I.E one client licence.

[Webtest]

randem ...

That's what this thread is all about! In order to connect to a table on an Oracle Server using Access (as opposed to Java), you need the Access Oracle Library, an Oracle provided Driver installed on your desktop, an account on the Oracle server for your connection to the Oracle table(s), a "tnsnames.ora" file, and a Windows Data Source definition.

[randem]

Webtest,

No kidding... Re-read my post. He won't need any of that to do as I posted.

[Webtest]

How exactly do you get Access to create a Linked Table connected to an Oracle table without the support resources I listed? I tried for 2 months and had no success whatsoever.

Could you outline the steps required to do what you are suggesting?

[randem]

Webtest,

As I suggested, the Access database would be on the Oracle server (or any computer with the client installed there). In that way many users could connect to the access database that only needed one connection to the Oracle linked tables.

Since it is not for volume, it should work just fine.

[Webtest]

Ahh ... that was the missing piece ... having Access running directly on the Oracle Server! Now I understand. That wasn't clear in your post. Unfortunately, in my case the Oracle Server is hundreds of miles away, and the chances of putting ANYTHING on the Oracle Server is slim to none. Even if access were running there, I still couldn't do any maintenance or modifications on it. I was lucky enough to get read-only access to views of one table!

[randem]

Webtest,

You missed the whole point again...

(or any computer with the client installed there)

Reading is overrated anyway...

[Webtest]

Thank you for being so patient. I've only used weekly 'snapshots' of the Oracle database so far, with the results available to about 50 other 'regular' users.

[randem]

Webtest,

BTW: You don't need Access running on that computer, you only need the database to reside there and have a connection to the Oracle database from that computer.

[GaryMazzone]

Any place I have worked would have me shoot if I suggested place an MSAccess DB on the same server as the Oracle database. You must have a very kind and conciderate Oracle DBA. (If I was the DBA and a developer suggested it I would have them shoot.)

[randem]

GaryMazzone,

You are aware that is was one of many possible suggestion to a problem?

BTW: it's "have them Shot!!!"

[Webtest]

Gary ... the most important aspect of randem's post was his parenthetical comment: (or any computer with the client installed there). That means I do not have to take a snapshot RecordSet on my PC ... I can just create a Linked Table and I can do whatever I need (or anyone else needs!) through this table.

That's a great wakeup for me, because my date-ranged SQL snapshot was taking over an hour to run recently, and now I can just connect first thing in the morning and let it cook until I actually need the data. The only thing I've noticed is that Access only seems to fetch a hundred or so records ahead of the record being viewed. I'll have to be sure it continues fetching all 500,000 or so records without waiting for additional "prompts". Fortunately I just had my memory upgraded to 768 megs.

(Duuuh ... all I have to do is click the "Last Record" button) ... and WAIT!

Thanks again, randem ...

[Webtest]

Hey randem ... THANK YOU !!!

My SQL RecordSet query was taking an hour ... the same query function through a linked table only took a few minutes (I didn't time it exactly yet).

Also, to clear up another misconception on my part, the entire dataset evidently does not get stored on my local machine. Memory Useage has only bumped up a few megs over what it was before.

[randem]

Webtest,

You are welcome.

BTW: Why do you need to get to the last record? Are you adding to the table? If so you can just add to it with the AddNew keyword for ADO or just an SQL Insert statement (far easier and better for documentation).

[GaryMazzone]

Yes I was aware that it was one answer. My comment was that if some came and told me that wanted to place the MSAcess DB on the Oracle server I would not allow that to happen for security reasons.

[Webtest]

randem ...

Generally, the records I am fetching are the most recent records, which intuitively would be the last records in the table (depending on the sort, of course). I was just clicking the "Last Record" button to see how long it would take to get there. For my query, it took less than 5 minutes for 40,000 records ... a factor of TWO better than I have ever achieved before. However, I killed Access before it finished finding the end of the Linked Table (must be about 500,000 records). Part of the reason is that Windows 2000 Pro seems to be miserable for multi-tasking ... all sorts of strange and nasty hangups occur on my desktop while this stuff is running. It sure would be nice to have a dedicated machine!

[randem]

Webtest,

Do you have an index on your Oracle database for the field that you are querying on?

You should be able to get to those records in seconds not minutes.

[Webtest]

randem ... thanks for your gracious assistance.

No, there is no index for my data. There are 6 fields ... none are unique. There are 76 or 77 records for each form that is submitted by a user.

Submittal Date/Time Stamp for the Form;
Profile ID (one of 4 source forms);
Form ID (Sequential Submittal number for each Form);
Department/Sector ID (about 300 submitters for the forms);
Field ID (Name of the field on the source form);
Field Value(Value of the parameter in the field on the source form);

That is what I have to work with ... I didn't design it! Believe me, I'm ecstatic that I can get back my date-limited dataset ordered by Field ID in less than 5 minutes!!!

[randem]

Webtest,

See if you can have the admin put an index on that table. It will make your life much easier, In second not minutes....

[The_Grudge]

So what's the concensus here?

I'm going to have the Helpdesk here install the Oracle client on 13 machines?
Or am I putting an Access database on the Oracle server?

[Webtest]

Grudge ...

(C) "None of the above".

Put the Oracle Driver on ONE machine on your LAN ... it would probably be easiest if this machine has Access installed. Create the tnsnames.ora file and set up the ODBC Data Source on this machine. Test the connection in the ODBC Data Source application.

Create an Access database on this machine consisting of 1 Linked Table ... linked to the Oracle table.

Anyone on the LAN that can open the Access Linked Table database (and knows the Oracle password - if any) can operate on the database that actually resides on the Oracle Server.