Open Acces form

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • GinnyP
    New Member
    • Sep 2006
    • 17

    Open Acces form

    Hi,
    I am new to this. How do I track if my question is answered? do I get an email?

    My Question,

    I have a database form with a command button that opens a data entry form. The problem is that on some user machines it opens fine, but on others it does not open? Any ideas on what could be the problems?

    Thanks
    Ginnyp
  • PEB
    Recognized Expert Top Contributor
    • Aug 2006
    • 1418

    #2
    Is there an error message?

    What is the code?

    :)

    Comment

    • GinnyP
      New Member
      • Sep 2006
      • 17

      #3
      Originally posted by PEB
      Is there an error message?

      What is the code?

      :)
      Hi,
      Thanks for the quick reply. So far I have figured out that some of my linked tables are linked to a "Z:" drive instead of using the generic name. Those people that do not have a z drive cannot access. I have just taken over maintaining this database. How would I have these linked tables point to the name of the drive instead of a letter???

      Thanks

      Comment

      • GinnyP
        New Member
        • Sep 2006
        • 17

        #4
        Originally posted by GinnyP
        Hi,
        Thanks for the quick reply. So far I have figured out that some of my linked tables are linked to a "Z:" drive instead of using the generic name. Those people that do not have a z drive cannot access. I have just taken over maintaining this database. How would I have these linked tables point to the name of the drive instead of a letter???

        Thanks
        I think I figured out. There is a module that remaps all the linked tables. Two of my tables were not remapped, So a added and what do you know....It works :).

        Thanks for the support

        Comment

        • PEB
          Recognized Expert Top Contributor
          • Aug 2006
          • 1418

          #5
          Pleasure for me :)

          :)

          Best regards!

          :)

          Comment

          • GinnyP
            New Member
            • Sep 2006
            • 17

            #6
            Originally posted by PEB
            Pleasure for me :)

            :)

            Best regards!

            :)
            Hi new question. I run a report in cognos, save as excel and import to access. I would like to run in the background from access. I can start cognos running but need to know how to do the following. Open the file to run, enter userid and password, save to excel, I know how to do the import part. I have tried to rewrite code in Access but keep blowing up on space. The tables that I read from are 200thousand plus. Below is the code. Any suggestions.

            Thanks for your support


            GinnyP

            select

            T1.HANDLING_OFF ICE, T2.CUR_ADJUSTOR _NO, T3.ADJUSTOR_NAM E, T2.BRANCH_CD,

            T2.CASE_NO, T2.SYMBOL, T2.ABSTRACT_DT, T2.CLM_CREATE_D T, T2.CLMNT_NM,

            T1.LOSS_DT, T1.LOSS_DESC_1, T1.LOSS_CATAS_N O, T1.CLM_STAT, T2.IND_OS,

            T2.IND_PD, T2.LEG_PD, T2.ADJ_PD, (((T2.IND_OS + T2.IND_PD) + (T2.LEG_PD + T2.ADJ_PD))

            + (T2.RECOVERY_RE C + T2.SALVAGE_REC) ) + T2.SUBRO_REC as INCURRED, T2.RECOVERY_REC ,

            T2.SALVAGE_REC, T2.SUBRO_REC, T4.POL_NO, T4.INSD_NM, T4.EFF_DT, T2.MAJOR_CLASS_ CD,

            T2.MAJOR_CLASS_ TYPE, T5.MAJOR_CLASS_ DESC, T6.DIVISION_CD, T6.DEPARTMENT,

            T6.SECTION_CD, T6.PROFIT_UNIT, T7.CLAIM_ST_ALP HA, T2.INJURY_DAMAG E_1,

            T1.LOSS_LOCATIO N, T2.CLM_CLOSE_DT , T2.LOSS_DT_REPO RTED, T2.IND_REOPEN_D T, T8.ZIP_1_TO_5, t8.addr_ln_1, t8.addr_ln_2, addr_ln_3,

            min(t9.fin_proc ess_dt) as Date_First_Ind_ Pmt, min(T10.ASSIGNM ENT_OPEN_DT) as Vars_Assign_Dat e

            from

            {oj CQLYLRPP.TCLAIM T2 LEFT OUTER JOIN CQLYLRPP.TFINAN CIALS T9

            on T2.BRANCH_CD = T9.BRANCH_CD and T2.CASE_NO = T9.CASE_NO and T2.SYMBOL = T9.SYMBOL

            and t9.resv_typ = 'I' and t9.paid_or_resv _am <> 0},

            CQLYLRPP.TREF_A DJUSTOR T3, CQLYLRPP.TPOL T4,

            CQLYLRPP.TREF_M AJOR_CLASS T5, CQLYLRPP.TCOVG_ KEY_EXTRACT T6,

            CQLYLRPP.TREF_S TATECD T7, {oj CQLYLRPP.TCASE T1 LEFT OUTER JOIN ANDDLMSP.CLM_NM S T8

            on T1.BRANCH_CD = T8.BRANCH_CD and T1.CASE_NO = T8.CASE_NO and t8.use_cd = 'LLA'},

            {oj CQLYLRPP.TCLAIM T11 left outer join CQLYLRPP.TASSIG NMENT_REPORT T10 on

            T11.BRANCH_CD = T10.BRANCH_CD AND

            T11.CASE_NO = T10.CASE_NO AND T11.SYMBOL = T10.SYMBOL_no}

            where T2.BRANCH_CD NOT in (676, 677, 678) and

            T4.POL_NO <> 9999999 and T4.POL_NO <> 999999999 and T1.CLM_STAT <> 'M' and

            T6.DIVISION_CD in (66, 74, 93, 28, 92, 97) and

            T1.LOSS_CATAS_N O in ('04605', '04705', '04905', '05005', '05105', '05405') and

            T7.CLAIM_ST = T1.LOSS_ACC_ST_ PROV and

            T2.SYMBOL = T6.SYMBOL and T2.CASE_NO = T6.CASE_NO and

            T2.BRANCH_CD = T6.BRANCH_CD and T2.MAJOR_CLASS_ CD = T5.MAJOR_CLASS_ CD and

            T1.POL_NO = T4.POL_NO and T1.MODULE = T4.MODULE and

            T1.ANN_STMT_CO = T4.ANN_STMT_CO and

            T3.ADJUSTOR_NO = T2.CUR_ADJUSTOR _NO and T1.CASE_NO = T2.CASE_NO and

            T1.BRANCH_CD = T2.BRANCH_CD and

            T11.branch_cd = T2.branch_cd and T11.CASE_NO = T2.CASE_NO and t11.symbol=t2.s ymbol and

            case when t1.handling_off ice = 869 and (t2.symbol = 2 or t2.symbol = 3 or t2.symbol = 4 or t2.symbol = 5 or t2.symbol = 6 or t2.symbol = 7 or t2.symbol = 8 or t2.symbol = 9) then 'flag' end is null

            group by

            T1.HANDLING_OFF ICE, T2.CUR_ADJUSTOR _NO, T3.ADJUSTOR_NAM E, T2.BRANCH_CD,

            T2.CASE_NO, T2.SYMBOL, T2.ABSTRACT_DT, T2.CLM_CREATE_D T, T2.CLMNT_NM,

            T1.LOSS_DT, T1.LOSS_DESC_1, T1.LOSS_CATAS_N O, T1.CLM_STAT, T2.IND_OS,

            T2.IND_PD, T2.LEG_PD, T2.ADJ_PD, T2.RECOVERY_REC ,

            T2.SALVAGE_REC, T2.SUBRO_REC, T4.POL_NO, T4.INSD_NM, T4.EFF_DT, T2.MAJOR_CLASS_ CD,

            T2.MAJOR_CLASS_ TYPE, T5.MAJOR_CLASS_ DESC, T6.DIVISION_CD, T6.DEPARTMENT,

            T6.SECTION_CD, T6.PROFIT_UNIT, T7.CLAIM_ST_ALP HA, T2.INJURY_DAMAG E_1,

            T1.LOSS_LOCATIO N, T2.CLM_CLOSE_DT , T2.LOSS_DT_REPO RTED, T2.IND_REOPEN_D T, T8.ZIP_1_TO_5, t8.addr_ln_1, t8.addr_ln_2, addr_ln_3

            Comment

            • PEB
              Recognized Expert Top Contributor
              • Aug 2006
              • 1418

              #7
              Hi,

              So this is a bit strange for SQL in Access!

              Can you tell me what is the meaning of {oj in the SQL! This is really the first time that I see it!

              With this SQL you want to treat your data from Excel or what?

              And what is this cognos? Isn't possible to introduce data directly from cognos to Access without passing by Excel?

              Sorry about the questions but I really need more info to know what to say!

              :)

              Originally posted by GinnyP
              Hi new question. I run a report in cognos, save as excel and import to access. I would like to run in the background from access. I can start cognos running but need to know how to do the following. Open the file to run, enter userid and password, save to excel, I know how to do the import part. I have tried to rewrite code in Access but keep blowing up on space. The tables that I read from are 200thousand plus. Below is the code. Any suggestions.

              Thanks for your support


              GinnyP

              select

              T1.HANDLING_OFF ICE, T2.CUR_ADJUSTOR _NO, T3.ADJUSTOR_NAM E, T2.BRANCH_CD,

              T2.CASE_NO, T2.SYMBOL, T2.ABSTRACT_DT, T2.CLM_CREATE_D T, T2.CLMNT_NM,

              T1.LOSS_DT, T1.LOSS_DESC_1, T1.LOSS_CATAS_N O, T1.CLM_STAT, T2.IND_OS,

              T2.IND_PD, T2.LEG_PD, T2.ADJ_PD, (((T2.IND_OS + T2.IND_PD) + (T2.LEG_PD + T2.ADJ_PD))

              + (T2.RECOVERY_RE C + T2.SALVAGE_REC) ) + T2.SUBRO_REC as INCURRED, T2.RECOVERY_REC ,

              T2.SALVAGE_REC, T2.SUBRO_REC, T4.POL_NO, T4.INSD_NM, T4.EFF_DT, T2.MAJOR_CLASS_ CD,

              T2.MAJOR_CLASS_ TYPE, T5.MAJOR_CLASS_ DESC, T6.DIVISION_CD, T6.DEPARTMENT,

              T6.SECTION_CD, T6.PROFIT_UNIT, T7.CLAIM_ST_ALP HA, T2.INJURY_DAMAG E_1,

              T1.LOSS_LOCATIO N, T2.CLM_CLOSE_DT , T2.LOSS_DT_REPO RTED, T2.IND_REOPEN_D T, T8.ZIP_1_TO_5, t8.addr_ln_1, t8.addr_ln_2, addr_ln_3,

              min(t9.fin_proc ess_dt) as Date_First_Ind_ Pmt, min(T10.ASSIGNM ENT_OPEN_DT) as Vars_Assign_Dat e

              from

              {oj CQLYLRPP.TCLAIM T2 LEFT OUTER JOIN CQLYLRPP.TFINAN CIALS T9

              on T2.BRANCH_CD = T9.BRANCH_CD and T2.CASE_NO = T9.CASE_NO and T2.SYMBOL = T9.SYMBOL

              and t9.resv_typ = 'I' and t9.paid_or_resv _am <> 0},

              CQLYLRPP.TREF_A DJUSTOR T3, CQLYLRPP.TPOL T4,

              CQLYLRPP.TREF_M AJOR_CLASS T5, CQLYLRPP.TCOVG_ KEY_EXTRACT T6,

              CQLYLRPP.TREF_S TATECD T7, {oj CQLYLRPP.TCASE T1 LEFT OUTER JOIN ANDDLMSP.CLM_NM S T8

              on T1.BRANCH_CD = T8.BRANCH_CD and T1.CASE_NO = T8.CASE_NO and t8.use_cd = 'LLA'},

              {oj CQLYLRPP.TCLAIM T11 left outer join CQLYLRPP.TASSIG NMENT_REPORT T10 on

              T11.BRANCH_CD = T10.BRANCH_CD AND

              T11.CASE_NO = T10.CASE_NO AND T11.SYMBOL = T10.SYMBOL_no}

              where T2.BRANCH_CD NOT in (676, 677, 678) and

              T4.POL_NO <> 9999999 and T4.POL_NO <> 999999999 and T1.CLM_STAT <> 'M' and

              T6.DIVISION_CD in (66, 74, 93, 28, 92, 97) and

              T1.LOSS_CATAS_N O in ('04605', '04705', '04905', '05005', '05105', '05405') and

              T7.CLAIM_ST = T1.LOSS_ACC_ST_ PROV and

              T2.SYMBOL = T6.SYMBOL and T2.CASE_NO = T6.CASE_NO and

              T2.BRANCH_CD = T6.BRANCH_CD and T2.MAJOR_CLASS_ CD = T5.MAJOR_CLASS_ CD and

              T1.POL_NO = T4.POL_NO and T1.MODULE = T4.MODULE and

              T1.ANN_STMT_CO = T4.ANN_STMT_CO and

              T3.ADJUSTOR_NO = T2.CUR_ADJUSTOR _NO and T1.CASE_NO = T2.CASE_NO and

              T1.BRANCH_CD = T2.BRANCH_CD and

              T11.branch_cd = T2.branch_cd and T11.CASE_NO = T2.CASE_NO and t11.symbol=t2.s ymbol and

              case when t1.handling_off ice = 869 and (t2.symbol = 2 or t2.symbol = 3 or t2.symbol = 4 or t2.symbol = 5 or t2.symbol = 6 or t2.symbol = 7 or t2.symbol = 8 or t2.symbol = 9) then 'flag' end is null

              group by

              T1.HANDLING_OFF ICE, T2.CUR_ADJUSTOR _NO, T3.ADJUSTOR_NAM E, T2.BRANCH_CD,

              T2.CASE_NO, T2.SYMBOL, T2.ABSTRACT_DT, T2.CLM_CREATE_D T, T2.CLMNT_NM,

              T1.LOSS_DT, T1.LOSS_DESC_1, T1.LOSS_CATAS_N O, T1.CLM_STAT, T2.IND_OS,

              T2.IND_PD, T2.LEG_PD, T2.ADJ_PD, T2.RECOVERY_REC ,

              T2.SALVAGE_REC, T2.SUBRO_REC, T4.POL_NO, T4.INSD_NM, T4.EFF_DT, T2.MAJOR_CLASS_ CD,

              T2.MAJOR_CLASS_ TYPE, T5.MAJOR_CLASS_ DESC, T6.DIVISION_CD, T6.DEPARTMENT,

              T6.SECTION_CD, T6.PROFIT_UNIT, T7.CLAIM_ST_ALP HA, T2.INJURY_DAMAG E_1,

              T1.LOSS_LOCATIO N, T2.CLM_CLOSE_DT , T2.LOSS_DT_REPO RTED, T2.IND_REOPEN_D T, T8.ZIP_1_TO_5, t8.addr_ln_1, t8.addr_ln_2, addr_ln_3

              Comment

              • GinnyP
                New Member
                • Sep 2006
                • 17

                #8
                Originally posted by PEB
                Hi,

                So this is a bit strange for SQL in Access!

                Can you tell me what is the meaning of {oj in the SQL! This is really the first time that I see it!

                With this SQL you want to treat your data from Excel or what?

                And what is this cognos? Isn't possible to introduce data directly from cognos to Access without passing by Excel?

                Sorry about the questions but I really need more info to know what to say!

                :)
                Thanks,

                I guess I did'nt word the question well. I can start cognos running from access, but I need to "specify what file to open and the userid and password to use", in addition specify to save to excel speadsheet". Can this information be executed through access?

                Thanks for your help
                Virginia

                Comment

                • PEB
                  Recognized Expert Top Contributor
                  • Aug 2006
                  • 1418

                  #9
                  Virginia,

                  If your program cogno can update those parameters as command line

                  Use

                  i=Shell("Cognos -UserId -pwd myfile",1)

                  And this is sure that U can save a file from Access to Excel programatically !

                  :)

                  Comment

                  • GinnyP
                    New Member
                    • Sep 2006
                    • 17

                    #10
                    Originally posted by PEB
                    Virginia,

                    If your program cogno can update those parameters as command line

                    Use

                    i=Shell("Cognos -UserId -pwd myfile",1)

                    And this is sure that U can save a file from Access to Excel programatically !

                    :)
                    Again thanks for the quick response.
                    I have exported data to excel spreadsheet using access, what I need to do is direct cognos to save as excel. I can then import from access. I think I will try at command line, as you suggested. I just need to figure out the syntax.

                    Thanks for you help

                    GinnyP
                    :)

                    Comment

                    • PEB
                      Recognized Expert Top Contributor
                      • Aug 2006
                      • 1418

                      #11
                      If You explain me more about the direct cognos I'll be more usefull!

                      However my English isn't native ant it's very poor... If everybody that has native english understant what is the cognos i can't!

                      Pls be gentle and if u have a time explain me what is this cognos! I'll be greatfull to you!

                      And if I understand better maybe can be more usefull!

                      :)

                      Thanks!

                      :)

                      Comment

                      • GinnyP
                        New Member
                        • Sep 2006
                        • 17

                        #12
                        Originally posted by PEB
                        If You explain me more about the direct cognos I'll be more usefull!

                        However my English isn't native ant it's very poor... If everybody that has native english understant what is the cognos i can't!

                        Pls be gentle and if u have a time explain me what is this cognos! I'll be greatfull to you!

                        And if I understand better maybe can be more usefull!

                        :)

                        Thanks!

                        :)
                        Hi,

                        Thanks again for your input. Cognos is another type of reporting application, that is being used to extract data from a very large DB2 database. The SQL I sent you is from Cognos. I tried to recreate in access, but it has not been a success. So I thought it might be better just to have Cognos run report and save as excel and then I can import. As I emailed earlier I would like this to be automatic. Does this help?

                        Thanks
                        GinnyP

                        Comment

                        • PEB
                          Recognized Expert Top Contributor
                          • Aug 2006
                          • 1418

                          #13
                          So Cognos exports the results from DB2 always in Excel isn't it?

                          To treat this files further so you can import or link them in Access!

                          Thanks for the information!

                          Vladimir :)

                          Comment

                          Working...