Open Acces form

[GinnyP]

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]

Is there an error message?

What is the code?

:)

[GinnyP]

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

[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

[PEB]

Pleasure for me :)

:)

Best regards!

:)

[GinnyP]

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

[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!

:)

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

[GinnyP]

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

[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 !

:)

[GinnyP]

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
:)

[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!

:)

[GinnyP]

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

[PEB]

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 :)