Connection to AS400 with ODBC

[a2knovice]

I am an A2K novice and I need some help with connecting to an AS/400.

I have my ODBC data source created and it works well. I'm comfortable with the SQL syntax I'll need in A2K to fetch my AS/400 data. I have no idea what is necessary in the way of VB code in A2K to do the connection. Can somebody point me in the right direction where I can find some code samples? I searched this board prior to submitting this thread but came up empty. Time is ticking on this project.

All help is greatly appreciated.

 

[KeithG]

If you want to view the data in Access you could you a Passthrough query. You will need to create a DSN for the connection but after that just type in you SQL statement and you will be ready to go.

 

[a2knovice]

Don't I need some kind of connection string first? My goal is to fetch data from the AS/400 database and store in access; I'll do this via SQL statements. But, don't I need some kind of connection string in a module of some sort?

 

[KeithG]

I do a similar process here at work. The connection information will be in the DSN that you create for you passthrough query. Once you make your passthrough query you will need to make an Append query from the passthrough query to Append the info to a database table in Access.

 

[KeithG]

I do a similar process here at work. The connection information will be in the DSN that you create for you passthrough query. Once you make your passthrough query you will need to make an Append query from the passthrough query to Append the info to a database table in Access.

 

[a2knovice]

I don't know what you mean by passthrough query and append query. Are you talking about creating some sort of MS Access query? This is going to be realtime fetching of data, wouldn't the SQL statement in a function be faster?

I'm not an Access programmer, so forgive me if I sound lame. Just looking for the best approach so I can finish this project.

 

[Robert88]

Hi a2knovice,

A pass through query is a type of MS Access query, found at the menu item Query, SQL specific, pass through.

An append query is also a ytype of MS Access query, found at the menu item Query, Update Query.

Robert88

 

[KeithG]

So you want to do all this with VBA? You are going to need to define the conection and then use the Execute method of the connection to send an SQL statement to AS400 to retrieve the data

Dim myConn as New ADODB.Connection

myConn.ConnectionString="Connection String"
myConn.Open
myConn.Execute("SQL Statement")

If you create a DSN file you can use that in your connection string otherwise you must specify the password, driver, and username in for the connection in the connectionstring property.To create an DSN file goto the control panel and click on ODBC connection. The ODBC driver that I use is called Client Access ODBC Driver {32 bit}. Also if you create a DSN file you can link a table from AS400 as well. To link the table ...

Click File/Get External Data/Link Table

Then change the file type to ODBC Database. Then another box will pop up promting for your DSN file for the connection info. After that you can choose the table that you want to link to. You can't link to any AS400 table with over 255 columns becuase of Access limitations. I don't know much about your project but I think linking the table would be the way to go if you need real time data.

Also I wanted to tell you that a passthrough query is an Access query that uses a specified driver instead of the jet database engine to retieve data. That is why you can use a passthrough query with AS400

 

[a2knovice]

Almost there...I think. Here is my test scenario:

1) I have a Client Access ODBC User DSN that I created to allow a "Read Only" Connection Type for safety reasons.

2) I figured out that I needed to create a Function in a Module for the VB code required for the connection string and SQL statement.

3) I execute this Function on a click event on the form.

4) My TEST A2K table has two fields(columns), item number and description.

5) I want to populate my TEST A2K table with the item number and description fetched from a Physical File on the AS400 based on the item number entered on the form.

Everything seems to be working except the INSERT to the A2K table, tblTEST, abends with "Connection violates access rule: Connection is set to read only."

Why would this occur since I'm trying to INSERT into the A2K table, not a file on the AS400?

Here is my VB code:

Function getItemDescription()

Dim myDSN As String
Dim myPWD As String
Dim myUSER As String
Dim myConn As New ADODB.Connection
Dim sConnectString As String
Dim sSQL As String

myDSN = "ODBCName"
myUSER = "UserName"
myPWD = "PassWord"

sConnectString = "DSN=" & myDSN & ";UID=" & myUSER & ";PWD=" & myPWD & ";LOGINTIMEOUT=100"

sSQL = "INSERT INTO tblTEST (ItmNbr, Desc) SELECT AS400FldA, AS400FldB FROM AS400FileName WHERE AS400FldA = txtItmNbr"

myConn.ConnectionString = sConnectString
myConn.Open
myConn.Execute ((sSQL))

End Function


All help is appreciated.

 

[KeithG]

I don't think you can reference a local table in your SQL statement because you are using the ODBC driver and not the Jet Database engine. That is why you should try and link the AS400 table to your database. If you had your DSN set to Read/Write you would probably get an error stating that it could not find the specified table.

 

[a2knovice]

Then how does one go about getting realtime data from the AS400 and storing it in an Access table?

 

[KeithG]

Link the table. Read my last post, I edited it after I posted.

 

[a2knovice]

You are correct; I changed the DSN to Read/Write and it was looking for the Access Table on the AS400.

OK, so I linked the AS400 file to the Access Table, where do I go from here? I am absolutely lost at this point.

I appreciate all the help you have provided me.

 

[Robert88]

Hi a2knovice,

Your original post indicated you wanted connection to the AS/400 data, which you have.

The table that is linked form AS/400 is now like any other table.

What is it you were wanting to do in Access with the data now?

Robert88

 

[a2knovice]

Hello Robert88,

Point 5 of my rather long winded post 6 occurrences ago pretty much tells the story.

I was able to muddle my way through creating the Pass Through and Append queries as suggested by KeithG. I tested by manually running the Append query and it worked as designed.

Now I need to figure out how to run the Append query after someone enters an Item Number in a field on a form. The Item Number entered on the form needs to be part of the WHERE clause in the SQL statement.

How would I run this query from a click event?

 

[Robert88]

Hi A2KNovice,

I have in the past used unbound forms, which is the type of prgramming you are probably used to. Check this link to help you;

http://www.access-programmers.co.uk/forums/showthread.php?t=102707&highlight=Robert88

However as you will read in the link it is better to use bound forms, saves a lot of work.

Good Luck with it.

Robert88

 

[Robert88]

Hi a2knovice,

Code on_click event;

Private Sub CmdAppendQuery_Click()
On Error GoTo Err_CmdAppendQuery_Click

    Dim stDocName As String

    stDocName = "qryAppendQuery"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_CmdAppendQuery_Click:
    Exit Sub

Err_CmdAppendQuery_Click:
    MsgBox Err.Description
    Resume Exit_CmdAppendQuery_Click
    
End Sub

Hope it helps

Robert88

 

[KeithG]

You will have to modify the SQL statement after the button is clicked. I wish a could post a similar database but company regulations won't allow that. You will have to use the query def object to change the SQL statement and then you can run the query write from the query def.

 

[a2knovice]

I'm sorry, KeithG, I have no idea what you just said in you last post.

 

[KeithG]

The problem you are facing is that you need to put a parameter in your SQL statement in your passthrough query so that it retrieves the correct record, I am correct? The problem is that you can not use parameters in a passthrough query. So you have to hard code the value into your SQL statement. To modify a queries SQL statement you must create a query def object. I will post some sample code


dim qryDef as QueryDef

set qryDef=currentdb.querydefs("NameOfQuery")

qryDef.sql="Enter your modified SQL statement for the passthrough query"
qryDef.save
qrydef.close