Using MS-Access with Informix
A newcomer asked whether MS Access can be used as a front end (GUI, reports, ad-hoc analysis) against an Informix database running 4GL apps on an HP 9000, and how Informix compares with MS SQL Server. Respondents confirmed it works via ODBC, with caveats: each linked table needs a unique index or Access behaves oddly, large inserts are slow unless pass-through queries are used, and locking/cursor/isolation behaviour differs. They also noted Informix is cross-platform, largely SQL-92 compliant, and that linked tables risk letting users edit raw data. No single fix was needed; the questioner was satisfied.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello all; I am totally new to Informix - it is being used by a potential employer. My question is, is there any reason that I can not utilize MS-Access in creating gui's and simple/complex data analysis queries with Informix. I am extremely comfortable working with Access and sadly ignorant of SQL query programming. Also, can someone explain to me in simple language what the difference/advantages are between Informix and SQL Server. Sorry if these questions are dumb to y'all however my responsibilities with this new position would be to know how to create quueries, design reports and do sales data analysis ie. user vs. developer. Thanks Robert
I should mention that the company has said that their computer system resides on HP 9000 system and that applications are written using an INFORMIX 4GL development language on top of SQL database technology. Any help/advice/direction would be greatly appreciated! Robert
On Wed, Mar 3, 2010 at 6:20 PM, ROBERT INGLES <robert_ingles@telusplanet.net > wrote: > Hello all; > > I am totally new to Informix - it is being used by a potential employer. My > question is, is there any reason that I can not utilize MS-Access in > creating > gui's and simple/complex data analysis queries with Informix. I am > extremely > comfortable working with Access and sadly ignorant of SQL query > programming. > It works. If it's the best way to do it... Possibly not. MS-Access, as expectable should be design to work better with MS SQL Server. But the principle is more or less the same. You'll need a driver to connect, and then you know. Browse through tables etc. You may hit differences in behavior, like locking, cursor handling etc... > Also, can someone explain to me in simple language what the > difference/advantages are between Informix and SQL Server. > For that, and to give you a complete and honest answer we would need to know in deep both products. Here most of the people will know Informix very well. MS SQL Server not so much (probably). One obvious difference is that Informix runs on any platform. It's not restricted to Windows. You'll find differences in replication technologies, internal architecture, many SQL functions, database extensibility tools etc. Note that I avoided to use the word "advantage"... Regards -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e6db2b150c72590480e9c87b
Hi I have been working on Informix for last 6 years also worked on ms access. Its like this they are two ends of the DBMS strings available now. Informix there is nothing as a GUI like msaccess or sqlserver both are basically window based DBMS. Informix is rarely used in Windows, at least i have not seen any. Its more like Oracle if you have any oracle user exp. Not in architecture or anything , just the experience of using sqls. I think You should probably go through Informix Unleashed. Its a very easy to understand starting point. Informix everything is a tool. You have to know the usage of these tools. there is possibility you are going to work on ODBC connections. Then anyway you dont have to worry too much. Regards Debadatta On 03-03-2010 23:55, ROBERT INGLES wrote: > I should mention that the company has said that their computer system resides > on HP 9000 system and that applications are written using an INFORMIX 4GL > development language on top of SQL database technology. > > Any help/advice/direction would be greatly appreciated! > > Robert > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
Informix 4gl is character based and is very much like any other 3gl except it incorperates SQL statements and functions not avilable to SQL. It has the typical program structures for looping and testing for truth. If you dont't have much experience with programming then 4GL will be a challenge. Some of the vendors listed on this site have GUI tools to help the character challenged. Informix 4GL has not been updated for several years so I'm not sure where thats going; GUI I guess. Informix as a database is very robust and scales really well. Gud luck.
It works, as already told. All you always need is a unique index on every table you access with ACCESS. If you don´t have such an index, you need some combined indexes that are more (or less) unique. If you don´t have any index on a table (which I don´t believe with Informix) you can get very funny responses inside Access. I tested it from Access 97 to 2007. Speed: It´s fine if passthrough is possible. Large inserts into access or to Informix (> 50,000 records) tend to be very slow because Access trys to do some sort of log mechanism. Only DB Access does not do this is SQL Server.... Regards, Joerg Volz ----------------------------------------------------------------------------- -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ROBERT INGLES Sent: Wednesday, March 03, 2010 7:26 PM To: ids@iiug.org Subject: Re: Using MS-Access with Informix [19184] I should mention that the company has said that their computer system resides on HP 9000 system and that applications are written using an INFORMIX 4GL development language on top of SQL database technology. Any help/advice/direction would be greatly appreciated! Robert ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. IT Handel und Beratung Jörg Volz Bernhard-Früh-Str. 7 77855 Achern GERMANY Tel: +49 (0)7841-681651 Fax: +49 (0)7841-681654 Mobil: +49 (0)170-2989757 VAT-ID: DE201383541 http://www.it-volz.de
On Wed, 2010-03-03 at 12:38 -0600, Fernando Nunes wrote: > On Wed, Mar 3, 2010 at 6:20 PM, ROBERT INGLES <robert_ingles@telusplanet.net > > wrote: > > > Hello all; > > > > I am totally new to Informix - it is being used by a potential employer. My > > question is, is there any reason that I can not utilize MS-Access in > > creating > > gui's and simple/complex data analysis queries with Informix. I am > > extremely > > comfortable working with Access and sadly ignorant of SQL query > > programming. > > > > It works. If it's the best way to do it... Possibly not. MS-Access, as > expectable should be design to work better with MS SQL Server. > But the principle is more or less the same. You'll need a driver to connect, > and then you know. Browse through tables etc. > You may hit differences in behavior, like locking, cursor handling etc... > > > Also, can someone explain to me in simple language what the > > difference/advantages are between Informix and SQL Server. > > > > For that, and to give you a complete and honest answer we would need to know > in deep both products. Here most of the people will know Informix very well. > MS SQL Server not so much (probably). > One obvious difference is that Informix runs on any platform. It's not > restricted to Windows. > You'll find differences in replication technologies, internal architecture, > many SQL functions, database extensibility tools etc. > Note that I avoided to use the word "advantage"... > Regards > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --0016e6db2b150c72590480e9c87b > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > I can attest that it works with varying degree's of success. We have used access in house for several years, not without issues of course. The drivers are a little more complex to setup but not overly. The big question to checkup on is how your potential employer does authentication and what degree of access they give their employee's to the database. Access with linked tables gives you the potential and the means to modify raw data in the tables if you are not very very careful. That is more than likely not an issue with 4gl living on their HP9000 box. Chris S.
Yes, you can use Access - or any ODBC client - as a front-end to an Informix database. The differences between MS SQL-Server and IBM's Informix Dynamic Server (IDS) from a technological perspective are many, but you need to know from a user/client perspective. The answer there is "not much". IDS understands most of SQL-92 and most SQL written for SQL Server will run without change. There are some paradigms that are SQL Server specific or were inherited by SQL Server from its parent database server Sybase. Many of these are not supported. An example would be selecting detail rows and aggregate rows in the same query. Under the rules of SQL, to which IDS adheres, this is not permitted and so not supported in IDS. If you are writing serious OLTP applications, there are semantics of the way IDS handles record locking that you need to be aware of in order to avoid concurrency issues. These are easily handled by writing correctly structured applications that, for example, use optimistic locking protocols and/or take advantage or IDS's isolation level known as COMMITTED READ ... LAST COMMITTED. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 3, 2010 at 1:20 PM, ROBERT INGLES <robert_ingles@telusplanet.net > wrote: > Hello all; > > I am totally new to Informix - it is being used by a potential employer. My > question is, is there any reason that I can not utilize MS-Access in > creating > gui's and simple/complex data analysis queries with Informix. I am > extremely > comfortable working with Access and sadly ignorant of SQL query > programming. > > Also, can someone explain to me in simple language what the > difference/advantages are between Informix and SQL Server. > > Sorry if these questions are dumb to y'all however my responsibilities with > this new position would be to know how to create quueries, design reports > and > do sales data analysis ie. user vs. developer. > > Thanks > Robert > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0ce0480c7bd7260480ead8e5
If you know Access and Basic, 4GL will not be uncomfortable for you to code in. Most code is procedural BASIC-like code except for forms handling and report writing modules which are event driven and very easy to get used to writing. Enjoy, if this new job works out, you will have joined the ranks of the most fanatical database devotees in the world and will be using the most advanced and best performing database server available. Have fun. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 3, 2010 at 1:25 PM, ROBERT INGLES <robert_ingles@telusplanet.net > wrote: > I should mention that the company has said that their computer system > resides > on HP 9000 system and that applications are written using an INFORMIX 4GL > development language on top of SQL database technology. > > Any help/advice/direction would be greatly appreciated! > > Robert > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00163646dbdcd6de630480eae1ac
Thanks everyone, the reason I like Access is that it allows me to develop dashboards and GUI's for non technical executives. I want them to run standard queries by the push of a button to check stock levels, order histories and comparatives via user entered parameters.