Wednesday, December 23, 2009

Simple EJB Bean

Okay so let's create a simple EJB bean that just says "hello", after that we want to secure the bean using a JDBC Realm.

Let's start with your basic EJB.



//File: HelloBean.java
package com.blogger.ramen;

import javax.ejb.Stateless;

@Stateless(name="HelloBean")
public class HelloBean implements HelloRemote {

public String sayHello() {
return "Hello from EJB!";
}

}

and the interface,

//File: HelloRemote.java

package com.blogger.ramen;

import javax.ejb.Remote;

@Remote
public interface HelloRemote {

String sayHello();

}


See that wasn't that hard. Now let's write up the sun-ejb-jar.xml file so that we can let Glassfish know what to do with the EJB once we deploy it. Think of the xml descriptors as instructions for Glassfish on how it should expose your EJB, use your EJB, what resources are needed, and so forth. Some tags from the xml descriptors have been converted into annotations for your Java code, like @Stateless and @Remote.

One of the things that I usually put into the xml descriptor is the JNDI name. I'm just so use to doing it that way, but you are free to use the @Stateless annotation to give the JNDI. So let's look at my sun-ejb-jar.xml file, also note the sun that is in front of ejb-jar.xml. The ejb-jar.xml file is the standard xml descriptor and would be a good place for the JNDI but we need the sun specific one because we will need to secure our EJB later. We'll forgo the standard ejb-jar for the sun specific one.


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sun-ejb-jar PUBLIC "-//Sun Microsystems, Inc.//DTD Application Server 9.0 EJB 3.0//EN" "http://www.sun.com/software/appserver/dtds/sun-ejb-jar_3_0-0.dtd">
<sun-ejb-jar>
<enterprise-beans>
<ejb>
<ejb-name>HelloBean</ejb-name>
<jndi-name>ejb/Helloness/HelloThere</jndi-name>
</ejb>
</enterprise-beans>
</sun-ejb-jar>


As you can see I've given my EJB a global JNDI name of ejb/Helloness/HelloThere.
Now deploy to Glassfish.

Congrats you've written your first EJB, or maybe your second or so. However, the JAR is running all by itself on your Glassfish server. The crappy part about that is that you won't be able to use injection now. The reason is that for injection to work the JAR and the client must be running together. You can bundle them together in a Java Enterprise Application. Of course, if you deploy the JAR with a client in an EE Application, you now have two copies of your JAR on the server.

To avoid all of that, you can just use a context lookup for the JNDI name. However, the caveat of that is, your application client won't know anything about the JAR, ergo, it won't know the signature of the method, return type, etc... If you're in Netbeans, you'll get a real eye opener if you try to include the JAR file in your project. It seems like it knows nothing about the JAR you included. Mainly you'll get something like an ejb ref error or something of the sort.

The reason? The client application is running on it's own just like the JAR is. The two aren't talking to each other, except for the JNDI lookup. So basically you lookup a POJO but then you basically cast it to an unknown type. You need to let that type be known to your client, to do such a thing, simply copy and paste your interface file into your client project.

So let's write our client program, as you know we have our HelloRemote.java file copy and pasted into our project, so I'll just cover the main file.


//File: Main.java

package com.blogger.client;

import javax.naming.InitialContext;
import com.blogger.ramen.HelloRemote;

public class Main {

public static void main(String [] args) {

InitialContext ctx;
try {
ctx = new InitialContext();
HelloRemote hr = (HelloRemote) ctx.lookup("ejb/Helloness/HelloThere");
javax.swing.JOptionPane.showMessageDialog(hr.seyHello());
} catch (Exception ex) {
javax.swing.JOptionPane.showMessageDialog("Massive failure!");
}
}

}

And there you go. A full client to use our EJB bean. Deploy that to your Glassfish server and then use Java Web Start to start up your application. Presto! You have your client getting the implementation from your remote EJB.

I'll be back later, maybe tomorrow, maybe after the holidays, to show how to secure the EJB using a JDBC realm. Of course if you want to see how to implement the JDBC Realm in Glassfish take a look at this post.

Cheers!

Tuesday, December 22, 2009

Holidays are fun, but they sure do sap the time from you.

Promise to post some EJB code examples tomorrow.

Monday, December 07, 2009

Sorry about the Holidays

Well, I knew that the Holidays would keep me pretty busy.  I haven't gotten around to posting anything with all the running around that I've been doing.  I'll go ahead and post something here just so I can keep my mind fresh about having a blog.

Hope everyone is having a wonderful holiday season!  Cheers!

Wednesday, November 25, 2009

Inkscape 0.47 is HERE!!!

OMFGRTFBBQ!!! Inkscape 0.47 is here! This is hands down the best tool for SVG work, period! You can read the release notes here.

Biggest item on my list is the Spiro tool. This has to be the coolest tool ever. Instead of storing the object as a set of bezier curves, it is stored as a set of points with curves where needed. Trying to get perfect curves are no longer a pain in the BBQ. Just change the arrangement of control points and the curves form automagically. Perfect raindrops, vines, and more await you.

The Knot tool looks to hold a lot of promise. I hadn't seen the knot tool in the trunk code so it's appearance is new to me. The hatches tool looks very cool but I'm not sure where I'll be able to use it. The ruler effect is one that I have used often in the trunk code. It does what it says, offers the ability to draw a ruler on a path. It is very useful if you need to add scale to a project.

I can't wait to get my hands on the new release. I know what I'm doing this Thanksgiving weekend!

Um...Being with my family and thankful for the wonderful life I have (uh, yeah that sounds like it...) Just kidding I love you all family.

Friday, November 20, 2009

LoginContext of ACC or more?

Many of the questions I see on Google are about how to setup a LoginContext and then use that context to login to the Glassfish server. First off, the server already creates a LoginContext for you, you do not need to make another one unless you have some real serious demands. Second, when you do make a new LoginContext you are making it outside of the Glassfish server.

JAAS uses a set of local files to determine which Java Objects (modules) to execute in order to make a login. Those configuration files also specify which of those modules are required to send back a "GO" to equal a full login. This is called a stacked login. You could have an application that requires not only a username and password, but also a smart card inserted. If either or both of them return a "NO-GO" then you are denied access.

The thing about JAAS is servers are not the only ones that can use it. You could make your normal Java 2 SE program use JAAS to authenticate smart cards or even a flat file that holds encrypted passwords stored on the local machine. Because anyone could have any given set of configuration files setup on their machine, when you use Glassfish to secure EJBs understand that Glassfish considers the server's configuration files to be the final say in the matter of logging on to access the EJB.

When you create your own LoginContext in your client application, JAAS has to consult the local configuration files. Since a JVM outside of the Glassfish JVM is running your client application, these JAAS configuration files have no say in authenticating your client to Glassfish.

Enter the ClientPasswordLoginModule. This module is a pretty neat little module. It takes in a client username and password and stores it. If you try to go somewhere that requires you to login, this module will take the information that it collected and pass it on to the module asking for login information.

Here is where some people get confused. They assume that the local JAAS needs to use the same module as the Glassfish module. No. Becuase there is no way the Glassfish server can be sure that your local result would be the same result with the Glassfish configuration. If our Application Client used a jdbcModule in the local JAAS configuration, we could (in theory) use a local MySQL to say (Oh yeah, he's who he says he is.) Instead, we have to pass username/password information on to the Glassfish server and the Glassfish server runs it through its jdbcModule, not our local jdbcModule. ClientPasswordLoginModule simply passes that information on to whoever asks for it.

You may also have heard of a ClientCertificateLoginModule, this allows a client to send a X509 certificate to login to a system. Usually your appclient is setup to use both ClientPasswordLoginModule and ClientCertificateLoginModule. The ClientCertificateLoginModule doesn't really do much if you haven't signed the application. If you have, this module makes sure that only the application that was signed is being used to access the resources. This keeps rouge applications from accessing your data. If you want IIMOP over SSL that's a whole other thing.

Remember, when you create a LoginContext inside you Java Client Application it will read in the local JAAS configurations. Those configurations should be setup to pass information back to Glassfish. However, my advice is to not even create your own LoginContext. The ACC on Glassfish is good enough for most needs, you shouldn't have to create your own LoginContext. I'll cover a method of how to get a bit more control over user logins with EJBs on Glassfish with your plain ol' Java Application Client.

Thursday, November 19, 2009

What's wrong with me?

Okay now a little bit of Java code to get into the mindset of using Glassfish.

What is wrong with this code?

File: SimpleMessageRemote.java


package com.blogspot.ramenboy.logintest;

import javax.ejb.Remote;

@Remote
public interface SimpleMessageRemote {

String sayWorld();

}


File: SimpleMessage.java


package com.blogspot.ramenboy.logintest;

import javax.annotation.security.DeclareRoles;
import javax.annotation.security.RolesAllowed;
import javax.ejb.Stateless;

@DeclareRoles("AUser")
@Stateless
public class SimpleMessage implements SimpleMessageRemote {

@RolesAllowed("AUser")
public String sayWorld() {
return "World!!";
}
}


File: Main.java


package logintest01;

import com.blogspot.ramenboy.logintest.SimpleMessageRemote;
import javax.swing.JOptionPane;
import javax.ejb.EJB;

public class Main {

@EJB
private static SimpleMessageRemote s;

public static void main(String[] args) {
JOptionPane.showMessageDialog(null, "Hello... " + s.sayWorld());
}
}


The problem with this code is (we are assuming that all the XML descriptors are in order) that we are injecting an EJB that is protected. Now there is nothing wrong with injecting a protected EJB but we shouldn't do this in our Main method. Injecting secure EJBs should be done once we have established the user as belonging to the system.

Why?

If the user fails to login properly (mistyped something or what-have-you). The Injection fails and the end result is an unusable object. The object being the whole freaking program, since this is the main method.

Ergo, don't do this unless you are just writing a simple test. This isn't really production grade programming to inject secure beans all over the place. A failed injection will bring your application client to a grinding halt with a very confusing error.

Pretty Print Test

Okay so I chickened out and started using the Google CSS for syntax highlighting. Here is a test.



package foo;

public class Testing {

public static void main(String[] args) {
javax.swing.JOptionPane.showMessageDialog(null,"Hello, World!");

System.exit();
}
}


So I hope this works.

Tuesday, November 17, 2009

Chest Colds are the worst

I think the title says it all.  This would be the seventh time this year that I have gotten sick.  I can not seem to do anything in my personal time other than get sick and try to recover.  I swear, I'm going to post some python code soon.  I just keep getting sick.

Tuesday, November 10, 2009

Using the built in login versus ProgrammaticLogin

Yeah, I figure I'd cover a bit of Glassfish and JavaEE nuances. Today's gem is logging in.

When you think of logging into something you may have a sudden urge to think, "Yeah it's just a database with users and passwords stored in it."

To an extent you are correct. The broad 10,000 feet view of logging in is that you present something and that something proves you are who you say you are.  In some cases it is a password, some cases it may be a USB stick, and in some cases it may be something on you like your thumbprint.  However, the fun thing about being a programmer is that you not only get to deal with the details of where, what, and how idenities are matched with proof, you also deal with the way it is prosented to the user, interfaced with that presentation, handled during transport, how to transport, and so forth.  Basically you'll leave wondering how the API actually helped you.

But I'm getting ahead of myself, short end is that no matter what framework you use; be ready to do a bit of leg work when it comes to logging people in.

Now on to the topic at hand.  In JavaEE there is a set way of how to handle logging in to an AS.  This method is known as JAAS (say it with me: Jazz).  Without going into what that means (you can safely assume that the J stands for Java) JAAS is the standard built-in method for logging into an AS.  Now JAAS is great and all but it was mostly intended for web based applications, so if you are doing a lot of web based apps then just sticking with the default JAAS won't do you wrong.

Therein is the problem, if you are doing Client Application programming (thick clients) you may seek to have a bit more control over the login process.  JAAS is an okay solution, and you can work around some of it's limitations, but after a point you're just boiler plating and you need to stop.  One thing about JAAS is that it is specific to the Application Control Container (ACC), that's not to say the code isn't cross platform, it means that the ACC handles login, not you, you have to keep poking at the ACC for information about the current state of the login.  This simplifies things at an amazing rate.  You can have a databased back login module in less than five minutes.  In fact you'll spend most of your time with SQL.  The problem is things like making sure the person provides a valid login add complexity because the ACC will toss an Exception at you and your client will receive a very cryptic error message about RMI-IIOP.

This is all because when something bad happens you can't trap the ACC (that's a good thing security-wise) and therefore authentcation errors blow up your client.  You have to write EJBs that force the system to log you in and then before actually using that login check to make sure it is okay.  This can add a bit of overhead in Client Applications...

Enter ProgrammaticLogin.  The purpose is pretty stright forward, handle logins programmatically.  Two problems with this approach, this will lock your code (once you start coding for ProgrammaticLogin you are locked into Glassfish with you code sans a major rewrite); second, you must handle everything about logging in yourself.  ProgrammaticLogin is lock-in for every platform, yes that's right, everyone has a ProgrammaticLogin (JBoss, Oracle, IBM, etc...), every single one of them makes it look the same (takes two to four parameters, passes information on to EJBs, etc...) but they all do it very differently per AS platform.  Judging by history, this makes it a good candidate for inclusion in the next version of JavaEE (???).

I guess I'll cover a bit about logging into an AS next time, I'll cover JAAS first.

Monday, November 02, 2009

Things not to do on Blogger when at work.

I'll try to keep updating this list as I go.
  1. Click the Next Blog link.
  2. Go to a blog that I know is going to have background music.
Holy smokes! Did you know it was November already?! Crap I've got so many car related things to get done it's not even funny.
  • Fix my car.  It's got a stuck open thermostat that makes the computer send P0126.  That's a you fail emissions error.
  • Inflate my freaking tires!  I usually am on top of this but the blasted air machines taunt me by accepting only quarters when I have a ton of dimes!  This also, by-the-way, sends a you fail emissions error.  In fact, every error on my car is a fail emissions error except low oil and low freon.  WTF?!
  • I turn an age evenly divisible by five this year.  That means that I have to pay to continue to keep my drivers license.  I don't really understand the point of this.  They don't require that you come in and have your eyes checked, your ability to drive checked, or anything that would ensure that you are still able to drive.  In lieu of all of that safety stuff they just want you to mail in a check and a form that ASSERTS that you still feel able to drive.
  • My son will be turning, an age that is a single number in binary, years old very soon.  I've so got to get planning and sending out invites done like it was yesterday.
  • I am so carpet bombing my friend's place.  He has consistently held the pieces of wood that I am using to make shelves for ransom.  He has until this Friday to return them, or I am getting 2,4,6-trinitrotoluene on his rear end.
See, this is what happens as you get older.  As a kid you loose track of time on a smaller scale, say minutes or even hours.  As an adult the same thing happens just on a larger scale, say days, months, seasons...  I guess it all just comes down to simply forgetting what year, century, millennium your currently in once you're ready to retire.

"What?! OH?!  Why are you grabbing me by the arms and dragging me out?!  What do you mean I retired seven years ago?! Let go of me right... oh forget it, I need a nap."

MS Access and the Gettoness that it is.

Why on Earth would you have a cross-tab query with no "non-VBA" method of making a report?  Oh well OpenOffice.org is guilty of the same crime.  C'mon, if I'm doing this with GUI magic in the query editor, why can't I do this with the report editor.

Well off I go to write about 400 lines of VBA code that no one will notice.  Nah, just kidding, it's only like 40 lines.

Code and Pre Tags

Hello.

Usually when I do some code, I just really place it between some <pre> tags. If it will have a lot of < and > symbols (like C++ code). I'll do the code really quickly in gedit and replace them with the entities that correspond to them.

However I was on Microsoft's web site and I really liked the idea of how they surround their code with a little blue box with a bigger top border.

Now using Firebug to look at the CSS for this, it is a really simply addition to what I was doing.


This is what I was doing...
<pre style="border:1px solid #8888FF">



However, as you can see in the above example, I'm using a similar usage of the MS CSS except I've made it green as such:


This is my new take.
<pre style="border-style: solid; corder-color: rgb(192,231,192); border-width: 10px 1px 1px;white-space: pre-wrap;word-wrap: break-word;">



Also I've been trying to understand how ScribeFire works with <br />. I am still working on it. Also, I will try to get my own CSS written and uploaded to Blogger when time permits.

Sunday, November 01, 2009

VirtualBox I still love you

Well it seems I'm running into a problem. 64-bit guest won't seem to go into long mode on a 64-bit host when AMD-V or Intel VT isn't available. That's okay because VBox still rocks.

Thursday, October 29, 2009

Getting LDAP Ready.

If you've never installed a LDAP server don't worry, its very easy. I like to install via source instead of grabbing packages from repos. You can find the directions for getting started here.


Just so you know, the rootdn (owner the root user for LDAP) can be named anything, in fact I suggest you use something other than cn=Manager, since a lot of people look for that in new LDAP installs.


Also please read up on the Access Control chapter before you go off leaving your LDAP server running for any length of time. Understanding the ACL of LDAP is a little tricky at first so go over it a couple of times just to make sure you have it. It should be understood that most users will need to connect to your server to get login information. Crazy enough, LDAP supports a type of permission called "auth". This allows an unknown client to query the LDAP server but only receive a connection or nothing from the server. This way a client can transmit a query with user name and password and hope that a connection is returned.


If the idea of having unknown people query your LDAP server bugs you, you can always setup a Kerberos server to handle the authentication of users, I'll get to that when I cover SASL. If fact, in some contract jobs with the government, you are legally required to have an external authentication service.


Well I'll fire up VirtualBox and begin doing some LDIF entries into LDAP and then some python code.

What is LDAP and Friends

LDAP stands for the Lightweight Directory Access Protocol, you can read more about it here.


Barring that, I'll give you a really quick run down of the history and uses of LDAP. LDAP is the Internet version of the X.500 Directory Access Protocol (DAP). In the beginning there was DAP, and no one used it (except maybe a couple of companies and the government, oh and Outlook as well.) DAP used the full OSI protocol stack which, in short, is seven layers thick. The Internet runs on a slightly lower calorie protocol, weighing in at four layers. Now one can transition between the two quite easily.


(NOTE: When people hear OSI they think OSI Model, there are also a ton of ITU-T papers that actually define a real set of protocols, you can check them out here.)


So at any rate, X.500 defined this whole suite of protocols that allowed globally unique messaging and directory services (basically the precursor to email and a global phone book for all those email accounts.) Needless to say this was a pretty lofty goal. Moving on, some companies actually implemented OSI and started using X.400 (mail) and X.500 (directory) for company mail. Then along came the Internet and suddenly DAP and all related technology found itself isolated. Enter LDAP, whereas DAP was made to use a seven layer protocol, LDAP was made to use TCP/IP. This includes IPv4 and IPv6 and TCP and UDP variations. It is important to note that LDAP runs only on TCP/IP, so if you are using an IPX/SPX network then you will be using something like NDS (Novell eDirectory) which is a X.500 implementation on IPX/SPX (NOTE: NDS doesn't do everything X.500 does, but neither does LDAP).


Since LDAP was made to use the protocol of the Internet, and the Internet has become so wildly popular, LDAP has become a very popular replacement for X.500 installations. The rest of the history of LDAP isn't all that fascinating so I'll leave at that.


So what is LDAP used for? Well it can be used as a limited gateway to X.500 directories (and for a lot of large companies that is exactly what it is used for) but also it can be it's own trimmed down X.500 directory. Trimmed down because LDAP doesn't implement the parts of X.500 that rely on the OSI protocol. So what is this directory? Well it is basically a database that houses user information. Usually the LDAP database is optimized for many reads and few writes and usually doesn't implement things in a relational manner like an RDBMS. Instead, LDAP is tree like and the database is optimized to think that way as well. LDAP is also good at storing user information that can be used on the application side. A RDBMS usually has data types that you store into it, an LDAP has a schema that is more role based as opposed to data based.


As an example, an RDBMS would have a user name and password field for user logins. LDAP would have a user schema that stores user name and password. Not much difference really but the schema is mostly role based, as you can imagine you can find a role that fits your needs from the IANA and add that to your LDAP server to fulfill that role. It makes a lot of sense when you get more complicated examples like a FedEx Account Contact role versus (FULLNAME, ADDRESS1, CITY, STATE, ZIP, ACCOUNT, ...) like you would find in a RDBMS.


Also, LDAP is tree like in nature. To implement the same ability in a RDBMS you need at least two tables, one to hold the normalized data and the other to add the structure you need. So you may have a list of contacts: in a LDAP directory each branch may separate that contacts role in your company; in a RDBMS you would need another table describing the roles and who belongs to them. I know it's not a big difference but it makes all the world of difference when you try to make optimizations and LDAP generally requires less administration and complex layouts.


So what role does LDAP play in the real world? Usually it stores user information, logins, contacts, and so on. Basically the same kind of information you might find in a phone book sans the whole computer aspect of it. This is really important in the real world as it allows people to centralize user information. Allowing only a few trusted sources to edit the data and allow the rest of the users to use it while conducting business. Think of it as a company wide Roladex. Because it stores contact information it can also store login information (basically a list of contacts that work for the company). The LDAP server can tell the difference between the role of "Standard-Contact" and "Employee-Contact". The standard contact might just have the regular information you'd expect, but the Employee contact may have everything a standard contact has and also have a password and username field within it.


I hope this gives you a better idea of how LDAP works in the real world. LDAP stores user information and anything that you might be interested in storing for that user (notes, bookmarks, appointments, etc...) It's not a replacement for a RDBMS, but it is a good solution for the purpose it was written for. If you find yourself building an application that doesn't need RDBMS but needs millions of reads and very few writes, LDAP would be a good solution for you.

Wednesday, October 28, 2009

LDAP and Python

I have no idea why I am so attracted to LDAP. At any rate, I'll be over the next couple of weeks posting some LDAP python work that I'm doing. Here is the python library that I'll be (and most people) using. I figure it was time that someone made Yet Another LDAP GUI Tool (YALGT).


Nah I take that back, that sounds way too much like YAST from Novell.

ScribeFire

I love the web proxy at my company. Basically it is useless. At any rate, since I can not use the web interface on Blogger, I've started using ScribeFire at work to publish post. Right now my views about it are mixed. I just have a funny feeling about it because it has the little logo in the top right corner.

Why is that a big deal?! Well ever since we left the era of Netscape Navigator, I haven't seen the logo in the top right corner in some time, except for adware. Therein lays the point. The add-on reminds me of adware. So I'm a little cautious about it.

Also the <br /> in ScribeFire acts weird and sometimes it tries to add a <div> that adds a invisible tracking pixel to my blog. (Which reminds me I've got to go through and make sure I removed them all).

Anyone know this person?

Don't know anyone per se in Canada, but it is at least worthwhile to re-post this. Please help find this person's family

Pitfalls of AS400 and MS Access

Where I work we have an AS400 system that provides our DB.  However, our IT admins decided to use a rather uncommon naming scheme. Our Libraries "folders if your looking for a Windows equal" have a period in their name.

Well you may, or may not, know that the period is how the AS400 system tells the difference between a library and a member when using the *SQL naming scheme. So usually you have something like this:


FNTUSR.ACCNUMB


Then you have the FNTUSR library and the ACCNUMB member of that library. But if you have something like this:


CHK.ENTR.BILLNO


You basically have garbage, at least as far as ODBC goes. The problem actually lies within the ODBC driver that IBM includes. It doesn't follow the same resolution method as the actual AS400 system, maybe because it is local and ODBC is remote but details aren't really that important. The ODBC driver goes out and yanks the CHK member during the resolution and asks for the BILLNO member, skipping completely the ENTR part of the name.

Needless to say this may prevent you from using ODBC to connect to the AS400 system. If there was only a way to pass the name as one chuck so that the ODBC driver would stop parsing the second period and placing the resulting token in the garbage.

Oh wait, there is...

The wonderful world of Microsoft allows you to pass a chuck of information into the ODBC parser, bypassing the tokenizer, by surrounding it with quotes! Hooray! But wait! In addition to the wonderful-ness that is Microsoft. None of their GUI offerings allow you to give quotes (except Excel, go figure). So if you are wanting to connect to an AS400 system that is administer by half witted admins that place extra periods all over the place in an AS400 system (look *SYS != *SQL, you can not use *SYS when you are brainwashed into thinking that it is the same as *SQL). You will need to go the VBA route (hooray... please shoot me now).

For those of you not really familiar with the VBA way of doing things, it involves a lot of pain. Please observe (I've named the DSN as400_connect):

Public wspAS400 as Workspace
Public cntAS400 as Connection
Public dbAS400 as Database

Public Function IWroteThisFunctionBecauseOurAdminsAreDumb()

Dim qryDef as QueryDef
Dim rs as RecordSet
Dim SQLString as String

On Error GoTo ConnectionError
Set wspAS400 = DBEngine.CreateWorkspace("idiot", "", "", dbUseODBC)
Set dbAS400 = wspAS400.OpenDatabase("idiot_db", , , "ODBC;DSN=as400_connect;DATABASE=SM456J12")
Set cntAS400 = wspAS400.Connections(0)

On Error GoTo QueryError
SQLString = "SELECT * FROM ""CHK.ENTR""/BILLNO"
Set qryDef = cntAS400.CreateQueryDef("", SQLString)
Set rs = qryDef.OpenRecordset

DoCmd.SetWarnings False

'Insert you records into you MS Access DB here

DoCmd.SetWarnings True

ConnectionError:
MsgBox "Something got F***ed up!"
GoTo CleanUp

QueryError:
MsgBox "Something got F***ed up!"
GoTo CleanUp

CleanUp:

On Error Resume Next
rs.Close
qryDef.Close

Set rs = Nothing
Set qryDef = Nothing

cntAS400.Close
wspAS400.Close

Set cntAS400 = Nothing
Set dbAS400 = Nothing
Set wspAS400 = Nothing

End Function


That should get you started on getting crap out of the AS400 system and into your MS Access database. Notice that I am using the *SYS naming scheme. Try both the *SYS and *SQL naming scheme (library/member, library.member) to see which one your AS400 system is using (or at least will resolve). You can change that bit of information in the DSN entry on your system under ODBC Administration in Windows.

PS: Please make sure you have the damn ODBC driver from IBM before you even try setting up the DSN.