Access can be used as a front end for oracle tables, in fact I use it all the time.
First you need the Oracle Driver.
Then you need to have an ODBC datasource that connects to your Oracle database.
Next just open a database, and go to the add table wizard.
Choose the link tables option.
When it asks you where you want to link the tables from change "Files of type" to "ODBC Databases."
Choose your datasource, and click OK, you may be prompted for a log on.
Select your Oracle tables from the list, and they will be linked to your Access database.
You can also use an SQL Passthrough query to connect.
After you have linked a table you can go to the table properties, and grab the connect string, go to the Query properties, and paste the connect string in, then paste your SQL statement in the SQL section of the query.
If you have any more questions let me know, and I can walk you through it.
I am linking my oracle backend DB to my ACCESS frontend DB. I can view my data in tables and views, so evething goes well so far.
Problems occur when I try adding or updating data. The problem is that I can't seem to insert records in my oracle table if I have made my primary key of the type number. Nore can I change the value of the primary keys. I keep getting the following error message:
ODBC--insert on a link table 'table name' failed.
[Microsoft][ODBC driver for ORACLE][ORACLE].ORA-01722:
invalid number (#1722)
Now if I insert the same data using SQL*PLUS I have no problems
I have tried everything: queries, passthrough, manually in the table,...
Nothing works with numbers
I have an other existing database in ORACLE. I tried doing the same thing with this one and guess what it worked. Now I am trying to figure out what I did wrong when I created the database, tables, users,...
Is it possible that I (the user I log on whith) need an extra privilige. I have given the user all the roles and priviliges of the sys. So I think I have got it covered.
Also I created a table which has only varchar2 column and now I was able to add rows using ACC.
I am a bit dazled!!
Thanks a lot for the help so far!!
I really appreciate it!!
If I might respond to your Access versus VB question.
As far as I am concerned, there is no discernable performace difference between Access and VB, when used as a front-end.
An Access application may take longer to launch, initially, to the first screen. But we are talking about, at the most, a single second.
Once loaded, there is no performace difference between the two products.
Each product has it pluses and minuses.
Access requires a larger footprint of disk space and memory.
Access requires a greater effort to hide your program's architecture.
Access does not handle third-party controls very well.
Access installations are vulnerable to changes in their environment, namely upgrades to Access.
On the plus side, and we are getting in to the great Chevy vs. Ford, PC vs. Mactinosh debate here . . .
. . . in a normal business environment, there is nothing you cannot do in Access that you can do in VB. Development in Access is simpler and faster because Access provides you with a more complete environment of development tools, right out of the box.
Access has always suffered from the fact that is practically given away with the Office package and is available for everyone to play with, even though they may not have any programming experience. Hence, most people dismiss Access because "if it doesn't require a guru to program, it must not be a very good programming environment."
As I have mentioned in other threads here, I currently have a customer running almost 2,000 CONCURRENT users on an Access front-end, SQL Server back-end application. The exact number, last week, was 1,811 users. And this is not a trivial application. It involves over 60, complex forms and almost 500 queries, etc., etc. I think that that is a pretty solid testiment to the ability of Access to handle the task.
Of course you can do this in VB, but it will cost you in having to have a programmer skilled in developing SQL syntax (Access doesn't require that), skilled in developing in CrystalReports (Access has it's own, internal report writer that, I think, is better, in many ways, than Crystal Reports), etc., etc.
Again, VB is a great environment for doing certain things. But as long as you are aware of Access' low spots, and are willing to accept and work aroung them, Access is completely up to the task of dealing with the development requirements of most small- to medium-sized businesses.
I'm late, PracticalProgram beat me to the answer , but I concur with everything he has said
I would also like to add that from a development stand point working with databases through access is alot faster and easier then trying to code everything out in VB. And as PracticalProgram mentioned reporting is a lot easier to do in Access so if your front-end is going to have alot of reports then access is definatly the way to go. I could be a bit biased though . I have created about 10 databases in the past year and I am constantly pressed for new features so I just don't have the time to code everything out in vb. Access is a life saver.
If you have Access 2000 or greater you can also use data access web pages (which are very cool).
The only real downside to access is the larger disk and memory footprint on the clients computers as PracticalProgram mentioned, but using it will save you a lot of programming time
If you e-mail me firstname.lastname@example.org I can give you input on the best road to take based on exactly what you are doing, as well as try to help you with any questions you may have.
now i need to create an access as fornt end with oracle as backend but i dun relaly know how to go about doing it...
do someone had a sample or a simple example to let me see?? if hav, do u all mind email to email@example.com.. specifying the subject as maybe like "access link to oracle" so that i won't accidentally delete the email...