Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    3

    Question Unanswered: Problem open recordset with db2 connection

    Hello all,
    I'm trying to open a recordset that has to get its data out of a DB2 table on the IBM Mainframe.
    I always get the error message :"-2147217865:[IBM][CLI Driver][DB2]SQL0204N "AMYON.VGEGD_EUY1P_V001" is an undefined name. SQLSTATE=42704"

    The problem is that the LoginID (AMYON) is combined with the table I refer to.

    Can someone help me out with this ?
    More information about de VBA sript you find hereafter.


    This message pops up when I run next command line:
    Rs_Chk.Open SqlChk, Db2con, adOpenForwardOnly, adLockReadOnly

    I use next code to open my ODBC connection:
    Public Sub Class_Initialize()

    'Define connection string to DB2 with DB2connect
    Db2Cnstr = "Data Source=" & odbcdsn & ";User ID=" & UserID & ";PWD=" & Passwd & ";"
    Set Db2con = New ADODB.Connection
    Db2con.ConnectionString = Db2Cnstr
    Db2con.Open

    SqlChk = "SELECT * FROM " & Table & ";"
    Set Rs_Chk = New ADODB.Recordset

    End Sub

  2. #2
    Join Date
    May 2009
    Posts
    258
    The most likely cause of this error message is that the table does not exist in the default schema, so you'll need to specify the schema in the SQL.

    To check the current schema, run the following SQL statement:
    Code:
    SELECT CURRENT SCHEMA FROM SYSIBM.SYSDUMMY1
    If your ODBC connection is not set up with the correct default library. You'll need to fully qualify your table name with its location in this case.

    Regards,

    Ax

  3. #3
    Join Date
    Jul 2009
    Posts
    3
    Ax,

    I do not understand your answer.
    How can I run your code.?
    Maybe I'm looking for the wrong solution.
    All the DB2 tables are already linked in the Access Database and I can use them in the queries, but I like to check upfront
    if the DB2 table is available for use. Sometimes a table is locked due to an update.
    Last edited by amduke; 07-09-09 at 10:50.

  4. #4
    Join Date
    May 2009
    Posts
    258
    OK, scratch all that then...

    If all your tables are linked in Access, you don't need to use the ODBC connection to query data from them, just use Access:
    Code:
    Dim rs as DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(LinkedTableName)
    If you need to deal with a query that fails to load because of locking, use error handling. Querying a locked table isn't going to be anything different than letting Access try it through the linked table.

    Ax

  5. #5
    Join Date
    Jul 2009
    Posts
    3
    Thank you Ax, but this is were it all started.
    When I do as you discribe, I have to login in manualy to the system.
    This is what I try to avoid and login automaticaly based on the parameters in my script.
    I try to make the tool running without manual interference of any kind. The job has to run at night hours.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •