Include Password in VBA for ODBC Connection

[Nevsky78]

Hi all,

I have a query that pulls from an external database via an ODBC Connection. This is the VBA for running and closing the query:

Function RunSalesandOrders()
On Error GoTo RunSalesandOrders_Err
    DoCmd.OpenQuery "Step 1", acViewNormal, acEdit
    DoCmd.Close acQuery, "Step 1"

RunSalesandOrders_Exit:
    Exit Function
RunSalesandOrders_Err:
    MsgBox Error$
    Resume RunSalesandOrders_Exit
End Function

In order to open the query it requires a password however. This is a password required to log on to the Progress OpenEdge Wire Protocol Driver.

My question is - does anyone know how to include the password in the VBA function? I can't use SetWarnings as the password is for an external source.

Many Thanks in advance.

 

[DCrake]

What does the query do?
What does the SQL statement look like?
Where is it getting its source from?
What is "Progress OpenEdge Wire Protocol Driver"?

 

[Nevsky78]

Hi David,

The query filters, pulls together and runs some calculations on sales data.
The SQL statement is below
The source is a Progress Database
The Progress OpenEdge Wire Protocol Driver is the driver used in the ODBC Connection to the Progress Database

SELECT PUB_ophead.kco, PUB_ophead.ledger, PUB_ophead.[pre-order], PUB_opdetail.[date-req], PUB_ophead.ordnum, PUB_ophead.ordtype, Trim([PUB_opdetail]![scode]) AS scode, IIf([PUB_ophead]![ordtype]="I",IIf([PUB_ophead]![date-inv]<=39965,0,1),1) AS Invoice, IIf([PUB_ophead]![ordtype]="C",IIf([PUB_ophead]![date-inv]<=39965,0,1),1) AS Credit_Note, IIf([PUB_ophead]![ordtype]="J" Or [PUB_ophead]![ordtype]="K",IIf([PUB_ophead]![date-inv]<=39965,0,1),1) AS J_K, IIf([PUB_ophead]![ordtype]="L",IIf([PUB_ophead]![date-req]<=39813,0,1),1) AS Delivery_Note, IIf([PUB_ophead]![ordtype]="O",IIf([PUB_ophead]![date-req]<=39813,0,1),1) AS [Order], IIf([PUB_ophead]![ordtype]="F",IIf([PUB_ophead]![date-req]<=39813,0,1),1) AS Forward_Order, [PUB_ophead]![accno] AS ophead_accno, PUB_opdetail.[qty-req], IIf([PUB_ophead]![ordtype]="I",IIf([PUB_ophead]![invno]<=155415,0,1),1) AS Invoice_LT_cutoff, IIf([PUB_ophead]![ordtype]="J",IIf([PUB_ophead]![invno]<=155415,0,1),1) AS Invoice_Cutoff2, IIf([PUB_ophead]![ordtype]="K",IIf([PUB_ophead]![invno]<=20406,0,1),1) AS CreditNo_LT_cutoff2, IIf([PUB_ophead]![ordtype]="L",[PUB_opdetail]![qty-alloc],[PUB_opdetail]![qty-req]) AS Picked, IIf([PUB_ophead]![ordtype]="D",[PUB_opdetail]![qty-alloc],[PUB_opdetail]![qty-req]) AS Delivered, IIf([PUB_ophead]![ordtype]="I",[PUB_opdetail]![qty-alloc],[PUB_opdetail]![qty-req]) AS Invoiced, IIf([PUB_ophead]![ordtype]="I",IIf([PUB_ophead]![invno]<=79067,0,1),1) AS Invoice_MT_cutoff_1, IIf([PUB_ophead]![ordtype]="J",IIf([PUB_ophead]![invno]<=79067,0,1),1) AS Invoice_MT_cutoff_2, IIf([PUB_ophead]![ordtype]="C",IIf([PUB_ophead]![invno]<=11127,0,1),1) AS Credit_MT_cutoff_1, IIf([PUB_ophead]![ordtype]="K",IIf([PUB_ophead]![invno]<=11127,0,1),1) AS Credit_MT_cutoff_2, ROOT_OPHEADVIEW.analcode1, ROOT_OPHEADVIEW.analcode3, ROOT_OPHEADVIEW.analcode5, ROOT_OPHEADVIEW.analcode7, IIf([PUB_ophead]![accno]="GPHK000","105",IIf([PUB_ophead]![accno]="GPGE000","105",IIf([ROOT_OPHEADVIEW]![analcode5]="106","102",[ROOT_OPHEADVIEW]![analcode5]))) AS [analcode5 calculation], [ROOT_OPHEADVIEW]![analcode7] AS [Agent Code], [ROOT_OPHEADVIEW]![analcode3] AS [Sub Group], PUB_opdetail.[disc-price], PUB_opdetail.line, PUB_opdetail.[qty-alloc], PUB_opdetail.reason, PUB_opdetail.[uom-qty], PUB_ophead.currcode, PUB_ophead.[date-created], PUB_ophead.[date-inv], PUB_ophead.[del-code], PUB_ophead.icount, PUB_ophead.invno, PUB_ophead.reason, PUB_stock.[item-flag], IIf([PUB_opdetail.reason]=0,[PUB_ophead]![reason],[PUB_opdetail.reason]) AS Reason_Code_0, IIf([Reason_Code_0]=3,0,IIf([Reason_Code_0]>24,1,IIf([Reason_Code_0]>9,0,1))) AS Reason_Code, IIf([PUB_ophead]![ordtype]="C",([PUB_opdetail]![qty-req]*[Reason_Code]),[PUB_opdetail]![qty-alloc]) AS Qty_allocated_2, IIf([PUB_ophead]![ordtype]="I" Or [PUB_ophead]![ordtype]="L" Or [PUB_ophead]![ordtype]="D",[PUB_opdetail]![qty-alloc],IIf([PUB_ophead]![ordtype]="C",-[Qty_allocated_2],IIf([PUB_ophead]![ordtype]="J",0,IIf([PUB_ophead]![ordtype]="K",0,[PUB_opdetail]![qty-req])))) AS Qty, [PUB_opdetail]![disc-price]*[PUB_opdetail]![uom-qty] AS Discount_Price, [Discount_Price]/[ExR] AS Discount_Price2, IIf([PUB_ophead]![accno]="WILK000","NAT",IIf([PUB_ophead]![accno]="INTH000","KEY",IIf([PUB_ophead]![accno]="ESDE000","KEY",IIf([PUB_ophead]![accno]="AUTO000","KEY",IIf([PUB_ophead]![accno]="PRPH000","KEY",[ROOT_OPHEADVIEW]![analcode1]))))) AS Account_Type, IIf([PUB_ophead]![ordtype]="C",-([PUB_opdetail]![qty-req]*[Discount_Price2]),IIf([PUB_ophead]![ordtype]="K",-([PUB_opdetail]![qty-req]*[Discount_Price2]),IIf([PUB_ophead]![ordtype]="O",([PUB_opdetail]![qty-req]*[Discount_Price2]),IIf([PUB_ophead]![ordtype]="F",([PUB_opdetail]![qty-req]*[Discount_Price2]),IIf([PUB_ophead]![ordtype]="J",([PUB_opdetail]![qty-req]*[Discount_Price2]),([PUB_opdetail]![qty-alloc]*[Discount_Price2])))))) AS Total_Line_Value, IIf([PUB_ophead]![accno]<>"TOYM000",[ROOT_OPHEADVIEW]![analcode7],IIf([analcode5 calculation]<>"101",[ROOT_OPHEADVIEW]![analcode7],IIf([ROOT_OPHEADVIEW]![analcode7]="000000","000001",IIf([ROOT_OPHEADVIEW]![analcode7]="ZZZZZ","000001",[ROOT_OPHEADVIEW]![analcode7])))) AS Toymaster_0, IIf([PUB_ophead]![accno]="FORS000" Or [PUB_ophead]![accno]="ONLI000" Or [PUB_ophead]![accno]="STAN000" Or [PUB_ophead]![accno]="WILK000","000000",[Toymaster_0]) AS Toymaster, PUB_ophead.batchno, [PUB_acctrans]![currgoods]/[PUB_acctrans]![basegoods] AS ExR
FROM (((PUB_ophead INNER JOIN PUB_opdetail ON (PUB_ophead.kco = PUB_opdetail.kco) AND (PUB_ophead.ledger = PUB_opdetail.ledger) AND (PUB_ophead.ordnum = PUB_opdetail.ordnum) AND (PUB_ophead.icount = PUB_opdetail.icount) AND (PUB_ophead.[pre-order] = PUB_opdetail.[pre-order])) INNER JOIN PUB_stock ON (PUB_opdetail.kco = PUB_stock.kco) AND (PUB_opdetail.scode = PUB_stock.scode)) INNER JOIN ROOT_OPHEADVIEW ON (PUB_ophead.kco = ROOT_OPHEADVIEW.kco) AND (PUB_ophead.ledger = ROOT_OPHEADVIEW.ledger) AND (PUB_ophead.ordtype = ROOT_OPHEADVIEW.ordtype) AND (PUB_ophead.ordnum = ROOT_OPHEADVIEW.ordnum) AND (PUB_ophead.icount = ROOT_OPHEADVIEW.icount)) INNER JOIN PUB_acctrans ON (PUB_ophead.kco = PUB_acctrans.kco) AND (PUB_ophead.ledger = PUB_acctrans.ledger) AND (PUB_ophead.accno = PUB_acctrans.accno) AND (PUB_ophead.batchno = PUB_acctrans.batchno) AND (PUB_ophead.docno = PUB_acctrans.docno)
WHERE (((PUB_ophead.kco)=1) AND ((PUB_ophead.ledger)="S") AND ((PUB_ophead.[pre-order])=False) AND ((PUB_opdetail.[date-req])<=#7/4/2011#) AND ((PUB_ophead.ordnum)>=100000) AND ((PUB_ophead.ordtype)<>"X" And (PUB_ophead.ordtype)<>"S") AND ((Trim([PUB_opdetail]![scode]))<>"1544" And (Trim([PUB_opdetail]![scode]))<>"1570") AND ((IIf([PUB_ophead]![ordtype]="I",IIf([PUB_ophead]![date-inv]<=39965,0,1),1))=1) AND ((IIf([PUB_ophead]![ordtype]="C",IIf([PUB_ophead]![date-inv]<=39965,0,1),1))=1) AND ((IIf([PUB_ophead]![ordtype]="J" Or [PUB_ophead]![ordtype]="K",IIf([PUB_ophead]![date-inv]<=39965,0,1),1))=1) AND ((IIf([PUB_ophead]![ordtype]="L",IIf([PUB_ophead]![date-req]<=39813,0,1),1))=1) AND ((IIf([PUB_ophead]![ordtype]="O",IIf([PUB_ophead]![date-req]<=39813,0,1),1))=1) AND ((IIf([PUB_ophead]![ordtype]="F",IIf([PUB_ophead]![date-req]<=39813,0,1),1))=1) AND (([PUB_ophead]![accno])<>"GBSW000" And ([PUB_ophead]![accno])<>"MENZ000") AND ((PUB_opdetail.[qty-req])<>0) AND ((IIf([PUB_ophead]![ordtype]="I",IIf([PUB_ophead]![invno]<=155415,0,1),1))=1) AND ((IIf([PUB_ophead]![ordtype]="J",IIf([PUB_ophead]![invno]<=155415,0,1),1))=1) AND ((IIf([PUB_ophead]![ordtype]="K",IIf([PUB_ophead]![invno]<=20406,0,1),1))=1) AND ((IIf([PUB_ophead]![ordtype]="L",[PUB_opdetail]![qty-alloc],[PUB_opdetail]![qty-req]))<>0) AND ((IIf([PUB_ophead]![ordtype]="D",[PUB_opdetail]![qty-alloc],[PUB_opdetail]![qty-req]))<>0) AND ((IIf([PUB_ophead]![ordtype]="I",[PUB_opdetail]![qty-alloc],[PUB_opdetail]![qty-req]))<>0) AND ((IIf([PUB_ophead]![ordtype]="I",IIf([PUB_ophead]![invno]<=79067,0,1),1))=1) AND ((IIf([PUB_ophead]![ordtype]="J",IIf([PUB_ophead]![invno]<=79067,0,1),1))=1) AND ((IIf([PUB_ophead]![ordtype]="C",IIf([PUB_ophead]![invno]<=11127,0,1),1))=1) AND ((IIf([PUB_ophead]![ordtype]="K",IIf([PUB_ophead]![invno]<=11127,0,1),1))=1) AND ((PUB_stock.[item-flag])="S") AND ((IIf([PUB_ophead]![ordtype]="C",IIf([PUB_ophead]![invno]<=20406,0,1),1))=1));

 

[DCrake]

When you created the ODBC link to the Progress database did it ask you for a password?

Are you using a DNSless connection or have you set one up. It may be that you need to provide the password in the connection string.

Only guessing never used Progress before.

 

[Nevsky78]

Hi David,

Yes, when I created Linked Table to the Database, it asked me for a Password.

It then asks me for a password every time I run the query. We are using a DNS Connection - but I'm afraid I don't know how or where to put the password in a connection string! Probably very simple but I'm still very much in the beginning stage!

 

[MagicPat]

Hi,
I'm having this concern too... Have you been able to solve it ? How did you proceed ?
Thks in advance,
MagicPat.