Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    3

    Unanswered: MS Access 2010 - VBA

    Recently, my user received an upgrade to Windows 7 and MS Office 2010. The MS Access application was previously executed using Win XP and MS Access 2003. For MS Access 2003 the user would receive only one prompt to her databasde credentials (user ID and password). With MS Access 2010, this is now occurring three times (once for the CurrentDb.TableDefs(sDatabase).RefreshLink statement and twice for the sMinimumDate = Trim("" & DMin("[ADJPOST]", "[IBNR_ADJTRAN_TEST]")) statement). The IBNR_ADJTRAN_TEST table is a linked table residing in DB2. I cannot find enough documentation on MS Access 2010 to assist me in resolving the problem. Any assistance would be appereciated. The code segment is below.


    If [Forms]![Main Switch Board].currentDatabase = "PRODUCTION" Then
    sDatabase = "IBNR_ADJTRAN_PROD"
    ElseIf [Forms]![Main Switch Board].currentDatabase = "TEST" Then
    sDatabase = "IBNR_ADJTRAN_TEST"
    Else
    sDatabase = "IBNR_ADJTRAN_UAT"
    End If
    CurrentDb.TableDefs(sDatabase).RefreshLink
    'Empty out BUFFER tables
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryADJTran_CLEAR_LHS", acViewNormal
    DoCmd.OpenQuery "qryADJTran_CLEAR_RHS", acViewNormal
    frmDEBIT.Requery
    frmCREDIT.Requery
    DoCmd.SetWarnings False
    If [Forms]![Main Switch Board].currentDatabase = "PRODUCTION" Then
    sMinimumDate = Trim("" & DMin("[ADJPOST]", "[IBNR_ADJTRAN_PROD]"))
    ElseIf [Forms]![Main Switch Board].currentDatabase = "TEST" Then
    sMinimumDate = Trim("" & DMin("[ADJPOST]", "[IBNR_ADJTRAN_TEST]"))
    Else
    sMinimumDate = Trim("" & DMin("[ADJPOST]", "[IBNR_ADJTRAN_UAT]"))
    End If

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you open the linked table residing in DB2 from Access without being asked for credentials?
    Have a nice day!

  3. #3
    Join Date
    Nov 2011
    Posts
    3

    MS Access 2010 - VBA

    When I use Tables directly from that inteface in Access the same thing occurs. Strangely enough, I am prompted three times once again for user credentials.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Did you try refreshing or perhaps recreating the link to the external table?
    Have a nice day!

  5. #5
    Join Date
    Nov 2011
    Posts
    3
    Yes, I had also converted the MS access database to 2010 format without any success. Tried refreshing the link (no difference) and also tried to create a new link (with a different name) to the same table. There seems to be something funky between the interface of ODBC and Access. When I tried to create a linked table using the Machine Data Source, the name appears clearly in the window that appears. Clicking on the MDS and supplying the user ID password the results in an error "[IBM] [CLI Driver] The database alias or database name " " could not be found. SQLSTATE=42705 (#-1013). Note that in the error the name is blank. When the DB2 command line processor (CLP) is used to view the environment all appears well (names and aliases). The ODBC administrative tools also displays the environment data without any problem.

Posting Permissions

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