Working with Access and Oracle/SQL Server 3

[DolphinFan]

Does anyone have experience using <b>Access97/Access 2000</b> as a front-end for a database in <b>Oracle or SQL Server</b>? I have read that you can link tables into Access throgh ODBC, but I have personally not implemented any solutions in this manner. Could some one give me an insight into the feasibility of this set up?<br>
<br>
Are there any performance/compatibility issues associated with using the Jet Engine in association with other databases? Your help is greatly appreciated.<br>
<br>
Prasad

 

[elizabeth]

Re: the Oracle connection, I don't have any experience with this yet, but if you plan to use Oracle you may be interested in a book I just bought yesterday, &quot;Microsoft Access 2000 Development Unleashed&quot; (Forte, Howe, Ralston). It appears to be the most complete written source of information on this subject. It has a 40-page chapter on using Access with Oracle.

 

[JimHorton]

Dolphin,<br>
I do this with Oracle, not SQL server, but the basics are the same. The ODBC link is the method to use. At the simplest level of explanation, you link the tables or views, and do the bulk processing via pass-thru queries, which either run straight SQL or call Stored Procedures. A query in the Access qbe will, if it's fairly simple (not too many joins), run on the server, just as if it's a pass-thru, and you can very quickly put together quick & dirty bound forms with almost the same ease as a Jet table. <br>
<br>
For real robustness and control, though, you want to use unbound forms and manually fetch/update the data--bound ODBC forms are quirky. Also, for recordset operations I don't use DAO or ADO, I write PL/SQL and call the procedure, DAO and ADO are just too slow for that. <br>
<br>
There are too many other detail issues to go into here, but in general, you need to deal with concurrency/locking issues, which is different than Jet, security which is also different. The good news is that once you link the tables you can get off to a quick start.<br>
<br>
--Jim

 

[Guest_imported]

Jim,<br>
I'm looking-- perhaps like Prasad-- at getting pretty heavily involved in developing Access and VB applications accessing Oracle and SQL Server back ends (mostly Oracle for now).<br>
<br>
I've been looking for newsgroups, discussions, books, courses that go into more detail on exactly the issues you mention: DAO/ADO and Oracle, bound vs. unbound control programming, integration of VB and PL/SQL and stored procedures, locking etc., etc.<br>
<br>
Can you suggest any good sources to help get us up to speed fairly quickly? Also, since you seem to have experience doing pretty much what I need to learn to do, would you mind if I picked your brain once in a while?<br>
<br>
Thanks, <p> Lee<br><a href=mailto: lee.meinhardt@smna.com> lee.meinhardt@smna.com</a><br><a href= > </a><br>

 

[JimHorton]

No problem, my brain is ready to be picked! As far as books, there is one (I don't have it here, but I'll get the exact title and post it) I've used I think it's VB Progamming with Oracle. I also have Oracle, the Complete Reference, and Oracle Beginners guide, and Oractl PL/SQL Reference. In addition, the Jet DB Engine Programmers guide has a good section on Client/Server.<br>
--Jim

 

[MikeAngelastro]

Hi,<br>
<br>
If one must use unbound forms, etc., in Access to interact with Oracle or SQL Server, would there be any advantage to using Access? Why not use VB6 if it is available?<br>
<br>
Just asking. <br>
<br>
Mike

 

[JimHorton]

Well, there are a few reasons...<br>
VB of course is the obvious choice for security reasons. But I can still set Access security to keep the majority of casual and even wannabe serious-hackers out. On a mission-critical app I would not use Access as a front end. That said, I think Access Forms have a better Event model (or maybe just more familiar) than VB and in general Access is much easier and faster to develop in. Also, I believe access has a far superior report writer than Crysal, which is what VB would most likely use, and in so many apps, reporting is not only crucial, sometimes it's everything.<br>
<br>
Moving on, my apps typically will end up a mix, ie, some bound, some unbound based on the use, but also I take static data tables and keep in in local mdb tables--ie, a States table, which hasn't changed since 1959 and won't soon, is a good canditate to keep local. On all forms that use it, we don't have to hit oracle for a list of 50 states to load a combo box every time the form opens. That's an obvious example, but doing an analysis of the data and it's frequency of updates can mark many tables to mirror, saving alot of bandwidth, ie, in one app I had a Parts table that was several thousand records, and was only updated weekly via an import process of new parts, changes, etc--(no direct user updates). <br>
<br>
I keep a table called DirtyLocalTables On oracle that has a list of locally mirrored tables, with flags that are set on triggers in the Server copy of these mirrored tables. Upon opening Access it's read and any tables with updates/deletes/additions are refreshed accordingly. This all would be more difficult with VB--and an .mdb would still be needed anyway. So, I guess you could call part of it lazyness , but seriously, there are lots of other little things (better, easier combo boxes, for another) that add up to make Access a good choice--even if you're going the unbound C/S route.<br>
--Jim

 

[elizabeth]

Thanks for the summary

 

[MikeAngelastro]

Thanks from me too.

 

[web]

I have tried to connect VB6 with Acess2000 as the backend but I get an error message that reflects that Acess does not recognise this connection. Has anybody else has a similar experience?

 

[elizabeth]

Is your question related to this Oracle thread? If not, maybe you'd like to post a new thread.

 

[tsyrell]

Has anyone run into the situation where an oracle table can't be linked within Access 97, but can be linked within Access 2000? We receive &quot;Invalid field definition 'SYS_NC00103$' in definition of index or relationship&quot;. There doesn't appear to be anything unique to the table in terms of field definitions or relationships. Any help would be greatly appreciated.

 

[JimHorton]

Off the top of my head, access 2000 I think supports some different data types that access97 doesn't, this might be it, possibley the $ sign in the name might not be allowed in 97.
Sorry I can't be of much more help. Back when this thread was fresh, I was working with Oracle daily, but lately I've been doing sql7 and db2 projects--for the last 2 years or so--so my Oracle skills have gotten rusty.
--Jim

 

[tsyrell]

Thanks anyway Jim. It was worth a shot...Tom

 

[pearlyn]

anyone have try how to link the sequence number from oracle to access?
or how do to autonumber for link table?
(after u link table, u cannot chnage the properties of the table)

 

[Isadore]

For what its worth don't forget that you can get by up to a point (smaller web site = 500 per day?) using OLE DB - Access. I built an ASP.NET site using Access tables via OLE DB. The entire site was built in Notepad. You can view it at:

http://frontpage.auburn.edu/icaae/index.aspx

Question: How does one decide to go either SQL Server or ODBC Oracle, etc...? We are getting ready to shift to SQL, but have never really &quot;weighed&quot; Oracle...