Connecting via VBA to AS400

[Soniaski]

Hi everyone!

Our Access databases currently use the windows Data Sources ODBC connection to link to AS400 tables. I am running into a problem where my modules are using a few of these tables and the module will prompt the user for a login and password. While it's fine to have this pop up once during a module, it pops up every time that the module touches one of the AS400 tables.

I started looking into using connection strings in VBA code to link to tables. I can get a SQL string working perfectly, but I am stumped at how to link to the AS400? I can't find any good examples that show how to do the connection and then how to USE the connected table.

Any thoughts?

Thank you for your input

 

[mdlueck]

Exactly what kind of a connection are you talking about: Linked Table Objects, DAO.QueryDef with ODBC DSN, ADO objects, etc...

I use DAO.QueryDef objects to issue Pass-Through queries to an AS/400 (iSeries) running DB2 database. Oh and that is right...

Public Property Get JDEODBCConnectString() As String
  'LOC for a FileDSN connection
  JDEODBCConnectString = "ODBC;FILEDSN=" & strJDEdbFILEDSN & ";"
End Property

I could not get it to accept a DSN'less connection, so I ended up using a file based DSN.

That DSN file goes in this directory, on Windows XP:
%SystemDrive%\Program Files\Common Files\ODBC\Data Sources\ConnectionName.dsn

And should have the following inside it:

[ODBC]
Driver=iSeries Access ODBC Driver
System=hostname
UID=userid
PWD=password

The Driver= entry value must match the name of the ODBC driver that came with your IBM connection software.

 

[Soniaski]

Thank you for replying mdlueck! Shown below is what I'm using to test using with SQL. I'd like to do the same thing but using AS400 data. I apologize for not knowing the proper definition of what I'm trying to do .

I want to be able to link to the AS400 table(s) programatically and not have it prompt for a login/password. This is used by multiple users so I'm hoping for a way to do it without touching everyone's computer.

I am using these linked AS400 tables to join to other linked tables (Access, FoxPro, Excel, etc..) and perform a variety of tasks.

Is that as clear as mud? :banghead: Sorry for putting the banging head smilie, but I think it's so cute . I'm not actually at that point yet.

Yes, this code is copied and modified from somewhere out on the world wide web
'''''''''''''''''''''''''''''''
Private Sub MYTEST()
AttachDSNLessTable "mytest", "vw_base_quotecomponentsfile", "MUNMPAKDB777", "QuoteSystemtwo", "", ""
End Sub
''''''''''''''''''''''''''''''''''''''''
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
Dim td As TableDef
Dim stConnect As String

stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"

Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)

''the following creates the linked table
CurrentDb.TableDefs.Append td
End Function

 

[mdlueck]

Yes, this code is copied and modified from somewhere out on the world wide web
'''''''''''''''''''''''''''''''
Private Sub MYTEST()
AttachDSNLessTable "mytest", "vw_base_quotecomponentsfile", "MUNMPAKDB777", "QuoteSystemtwo", "", ""
End Sub

Like I said... I could not get DSN'less to work to connect to the AS/400 iSeries running DB2, however I am able to get DSN'less working perfectly to connect to SQL Server 2008 R2.

I gave up trying to get DSN'less working to the AS/400 and went with a FileDSN which I was able to get working.

 

[Soniaski]

Thank you Michael... Can you use the FileDSN to create linked tables, or do you just use pass through queries with it?

 

[mdlueck]

Can you use the FileDSN to create linked tables, or do you just use pass through queries with it?

You should be able to use them for both.

Either way you build the same ODBC string in VBA, referencing the FileDSN, and pass it to the object.

Oh, and thus how to refresh the ODBC string inside of a Linked Table Object?...

Linked Table Saved Password ADOX code
http://www.access-programmers.co.uk/forums/showthread.php?t=226468#post1155700

So for Linked Table Objects you need to use that solution to update the connection string. Once that VBA code is run, users will not be prompted for credentials. The FileDSN will be used for credentials.

 

[Soniaski]

Thank you Michael! Let me give that a try today. Standby.....

 

[Soniaski]

I couldn't get it to work. Had problems with the real names to use within the DSN file, as well as the actual connection string.

I did speak with the AS400 lead, and he helped me set up the connections so that we didn't need to use a connection string.

Thank you for your time Michael. I do appreciate it.

 

[mdlueck]

I was able to get a DSN'less connection going to the iSeries / AS/400 server running DB2/400. The correct syntax was as follows:

  'LOC for a DSN'less connection
  JDEODBCConnectString = "ODBC;Driver=iSeries Access ODBC Driver;System=" & strJDEdbServer & ";UID=" & strJDEdbUID & ";PWD=" & strJDEdbPWD & ";[COLOR=Blue][B]MGDSN=0;[/B][/COLOR]"

The critical part was the MGDSN=0; switch which I can not find documentation as to what that switch does. Without it, I receive an error as follows:

Date: 20121023 Time: 17:04:38 UserID: c_mlueck
AppErrorMsg: Module: modshared_dbutils, Function: dbutils_RefreshLocalTmpTbl(), strQueryAPIName: clsObjJDEItemMasterTbl_RefreshLocalTmpTbl
Error Source: DAO.QueryDef
Error Number: 3000
Error Description: Reserved error (-7778); there is no message for this error.
MessageText: The specified print monitor is unknown.

This finally lead me to this page...
http://www.connectionstrings.com/as-400
And the suggestion to try the MGDSN=0; switch.

Note: That example showed {} around the driver name, which did NOT work for me.

 

[Soniaski]

That's great Michael!

Now I have to go and play with it again just to see if I can get it to work... Thanks a lot!!!

 

[mdlueck]

You are very welcome, Soniaski.