Ultrasoft Money Link in with Microsoft Money and Windows 11

[joe dempsey]

I have a new PC with Windows 11 and have installed the Sunset Edition of Microsoft Money

All working correctly in Money

I also use Ultrasoft Money Link in Excel to extract data from my Money file to Excel

This had a declaration error in the VBA code so I had to add PtrSafe to the function declaration - this works

But now when I run the Getdata I get an error - the internally defined functions/classes in the xla are not register and so the link fails

Anyone any ideas?

Thanks

Joe

[Bill & Michelle Becker]

Did you also do the mnyob99.dll replacement?

--
You received this message because you are subscribed to the Google Groups "Microsoft Money" group.
To unsubscribe from this group and stop receiving emails from it, send an email to microsoft-mon...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/microsoft-money/b809f965-d9f6-4037-8489-1a27d05884e6n%40googlegroups.com.

[joe dempsey]

yes

[Dick Watson]

I don't know if this is relevant, nor do I know if this is how it fails, but if your Office install is 64-bit version--and that's the default install these days--the MoneyLink Add-In will not work. It's 32-bit only.

[joe dempsey]

Yes, I have 64-bit version of Office installed

When I ran it the first time, it said there was a compile error - one Declare Function statement in the code needed to be changed to Declare PtrSafe Function. When I made this change, the code compiled correctly and gave an error when I tried to Refresh All Money Data with an error message "Class not registered" for the SelectQuerySource which is one of the forms defined in the Moneylink XLA

I think the Add-in does not work because of missing something in the VBA code - I'm no expert so maybe I'll ask in a VBA forum

[Dick Watson]

The problem is not in the MoneyLink VBA. There's compiled code in the .XLA. It was never ported to 64-bit.

[joe dempsey]

Yes, I found that some of the classes in the USMNY.DLL are not available in Excel - I have reverted to exporting the data in Money via a report into .csv files and then accessing them in Excel via a Vlookup - not quite as automatic as Ultrasoft's MoneyLink, but it will have to do

[Tom Roberts]

Hi Joe

For what it's worth, here's my experience on possibly a related macro problem. 

I was having an issue with an excel macro when trying to convert csv to qif. I had been using the app xl2qif for a few years then got a new computer (64 bit operating system and x-65 based processor) and XL2QIF stopped working. I was directed to youtube by someone here (How to Automatically Convert a CSV file into to a QIF file Using a Microsoft Excel Add-In - YouTube) and in the comments section I found that someone had cracked the password for xl2qif by putting the command you used. I downloaded the cracked xl2qif and it works very well on my laptop. XL2QIF is old and likely was not designed for a 64 bit laptop.  

I'd also be interested to try it if you get the moneylink app to work. 

Good luck. 

--

You received this message because you are subscribed to the Google Groups "Microsoft Money" group.
To unsubscribe from this group and stop receiving emails from it, send an email to microsoft-mon...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/microsoft-money/9f77a5cf-cbc6-4632-8986-3ecc71070298n%40googlegroups.com.

[Dick Watson]

Joe, I sent you an email on this subject. Let me know if you don't get it. It has a .XLSM attached, so may get swallowed somewhere along the way.

Dick

[Tom Roberts]

I sent an email to ask about translating moneylink for 64 bit systems. 

It looks like the company Ultrasoft who created it is no longer in existence.

The gentleman, who is in India, said to send him moneylink and he'd have a look. 

He told me just to change the Long and Declare statements.

I plan to send him a link to the MS Money Plus shown on this page http://moneymvps.org/downloads/moneylink.aspx

If its not costly, it may be very easy for him to edit the older versions as well, in case anyone is interested. 

Will keep you posted. 

--

[Tom Roberts]

Some further info below.

On Thu, Jan 18, 2024 at 5:38 AM <ad...@exceldemy.com> wrote:

Free Online Excel Help Forum - Get Help From ExcelDemy Team! Aniruddah Alam replied to a thread you are watching at Free Online Excel Help Forum - Get Help From ExcelDemy Team!. convert macro from 32 bit Excel to 64 bit Excel timbertom said: Hi I've read that all the Declare statements have to become "PtrSafe Declare" and the Long statements to become "LongPtr". I made these changes in the VBA modules and still get error messages. Has anybody here done conversions of XLA files before so they function on 64 bit Excel? Thank you. Dear Timbetom, Thank you for posting your query on our Exceldemy forum. Without seeing your code, it's difficult to diagnose the problem accurately. You're right that when converting code from 32 bits to 64 bits, all the Declare statements need to be updated to "PtrSafe Declare". Additionally, the data types in the Declare statement (parameters and return values) that store 64-bit quantities should be changed to LongLong for 64-bit integrals or LongPtr for pointers and handles (Source). Could you please share your code with us so that we can examine it closely and provide you with a solution? We look forward to hearing back from you. Best regards, View this thread Watched threads Please do not reply to this message. You must visit the forum to reply. This message was sent to you because you opted to watch the thread convert macro from 32 bit Excel to 64 bit Excel at Free Online Excel Help Forum - Get Help From ExcelDemy Team! with email notification of new replies. You will not receive any further emails about this thread until you have read the new messages. If you no longer wish to receive these emails, you may disable emails from this thread or disable all emails. Visit Free Online Excel Help Forum - Get Help From ExcelDemy Team!

[Tom Roberts]

FYI -  still waiting to hear back on this but sent a reminder today.  

[Phil Gilbert]

The guy who wrote the original add in is called Jan Mikorsky he mentions it in his website

https://jan.miksovsky.com/about

[Tom Roberts]

Are you sure ?  

He seems to be writing about Money on his site and not Money Link.

To view this discussion on the web visit https://groups.google.com/d/msgid/microsoft-money/4b3e7fb3-04fb-4113-a84c-5dcfa430bc3en%40googlegroups.com.

[Phil Gilbert]

This is the header of the Global Variable declaration in the VBA

'------------------------------------------------------------
' Microsoft Money Link to Excel
'
' Copyright(C) 2001. Ultrasoft Limited.
' Copyright(C) 1997-2000. Microsoft Corporation.
' All rights reserved.
'
'------------------------------------------------------------
'
' Global declarations and routines for Money Link add-in.
'
' Money Link for Excel by Jan Miksovsky and Manny Weiser
'
' Copyright Microsoft Corp., 1997.
'

[Tom Roberts]

I see... I suppose one could ask Jan Miksvosky if he has another version (ie. 64 bit) and refer to this user group as having an interest.

I wonder if people here would mind this group being referenced for that purpose ? 

To view this discussion on the web visit https://groups.google.com/d/msgid/microsoft-money/bf1976e9-5e48-4138-9df0-a2cec9bc087en%40googlegroups.com.

[Phil Gilbert]

I don't think it's likely that there will be a 64-bit version as the work pre-dates 64-bit versions of Excel.

And as somebody said above the problem might be in the associated .dll files that may not work with 64-biot Office.

However it might be interesting to find out what his ideas are on the way forward for the small group of die-hards that are still enjoying his MS Money experience?

[Dick Watson]

The guy's page says he worked on MSM UI design (not necessarily same as code) and, from description, sound like in the v2-

[Dick Watson]

Money 95 era. (Sorry about the break. Must have fat-fingered the wrong key.) That's a LONG ways removed from involvement in the Money side of Money Link, much less the Excel side. Don't forget, MS claims to have poured $1B down the MS Money rathole before they gave up. That's a LOT of software developers over a LONG time.

[joe dempsey]

I wouldn't take Microsoft's claim of $1B seriously - sure, they spent a lot on it but that is not a serious figure - remember this was 20 and more years ago

[Dick Watson]

Good find. I haven’t looked in that code since I reverse engineered the interface for my add-in dev and installed 64-bit Excel/uninstalled MoneyLink.

Maybe he’d have something to add to the discussion, but I still believe this issue is that the .DLL from MoneyLink would have to be ported for 64-bit operation and there is probably nobody who is in a position to do it with access to the original source and motivation or business case. The visible VBA is only a minor part of the MoneyLink puzzle. The heavy lifting is in the .DLL and, I’ve always suspected, an undocumented Money API so that Money could insulate MoneyLink, to some degree, from Money schema changes. One of the reasons I’ve suspected this is every Money version took a corresponding MoneyLink version update even when the MoneyLink functionality never changed.

David Kendall is the name I’ve been trying to remember that was associated with Ultrasoft while they were in this loop. I have no clue what happened to him or Ultrasoft. I don’t know whether Ultrasoft was sub to Microsoft in the original development, or if they were ex- Microsoft and permitted to take the product (and, from the Microsoft team’s probably POV, the headache) with them, or what.

[Dick Watson]

No way we’ll ever know, but I remember the struggle they were in with Intuit/Quicken, the amount of energy Microsoft put into the (basically failing) effort to get the FIs to play along, and how many free copies of Money they gave away for a number of versions in a row. So, I tend to believe it. That’s number was reported by news organizations (WSJ?) during the Microsoft-Intuit buyout attempt.

[Phil Gilbert]

I was nervous about using the old .dll, but I was running 16-bit Excel under Win10 with working money-link, so I don't know how/where the .dll restriction comes from.

[joe dempsey]

The dll doesn't work with the 64-bit Excel

[Dick Watson]

It's foundational in all x86 (32-bit addressing) vs x64 (64-bit addressing) code development. Here's one link on this narrow aspect, Office Add-Ins, of the problem:

https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2010/ee691831(v=office.14)?redirectedfrom=MSDN

[Phil Gilbert]

It's most likely that the money Link VBA app calls critical functions that are coded into the USMNY.dll.

These will be the functions which read/write to the proprietary .mny structures.

(If the functions were in the VBA we would be able to reverse engineer the .mny structures.)

Given that none of this has changed there is a chance that if the VBA was updated as per the guildelines, it might work.

I spent a few minutes adding if/then ..... PRTSafe to the opening declarations and it still didn't work but I lack the skills to debug it after that.

Of course there is an equal chance that there are calls inside the .dll that might not be 64-bit compliant, but Money isn't using new bigger 64-bit files so we can't be 100% certain that it will fail.

[Dick Watson]

The MoneyLink .DLL needs to be compatible with the Add-In interfaces of 64-bit Excel and it is not. Period. Full Stop.

The exposed VBA from MoneyLink has next to nothing to do with MoneyLink not working in Excel 64-bit. Play with PtrSafe and that code all you want. It won’t get you where you want to go.

IIRC, I have some VBA somewhere, that, when run from VBA in 32-bit Excel (curiously not from Word), gets data returned from a Money file by calling the MoneyLink .DLL API separate from anything to do with the MoneyLink exposed VBA. I played around with this a lot back in 2018 before I wrote my own VSTO add-in MoneyLink replacement. And, yes, as previously noted in this thread, I think we can quite safely assume that MoneyLink calls undocumented (and probably obscured and secured) APIs in USMNY for the very reason you note. The SQL to take data from Money tables and produce MoneyLink “Transactions” is considerable and I’m sure they would have wanted to decouple MoneyLink from some of those schema details and potential Money-driven changes thereto. I’m away from home, but I’ll see if I can extract the SQL and post it here. Sort of as a proof-of-life that I know whereof I speak on the subject.

[Dick Watson]

Here are the heavy lifting SQL bits for a MoneyLink -like Transactions query from a Money file converted to .MDB via Jackcess/Jackcess Encrypt/Sunshiine Money Export. I have not tried to split the code for DAO/ADODB, so both codebases are shown here. I developed the queries in DAO/ADE in Access and use a tool and some data there to generate a code fragment, from which this is an edited extract, that I paste into the VB VisualStudio code. Note that this includes Transactions support beyond what MoneyLink does, including dumps of Class1/Class2, Flags, TaxYear, and the top transaction including Memo from split transactions. There may be some other inclusions beyond MoneyLink that I'm forgetting just now. If you stripped out, say, the DAO pieces here, and created Access stored Queries in a .MDB file dumped from the toolchain noted above, you could see how this produces a recordset with Money data that looks a lot like the Excel output of MoneyLink.

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
'
'   code auto-generated from Access
'
'   for:            codegen("SqlDefs","GetSql")
'
'   source:         mmd development support.accdb
'   last generated: 20240323 08:34
'
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

' SQL used in queries/subqueries for each query class, generated in
' inverse dependency order; i.e., most prerequisite subquery last

...

ElseIf qryClass = MmdQcls.Transaction Then

strName = "Transactions"
#If dbAPI = "DAO" Then
strSql = "PARAMETERS paramAcct Text ( 255 ), paramDtFrom DateTime, paramDtTo DateTime, paramBankCash Bit, paramIncludeParents Bit;" & vbNewLine
#ElseIf dbAPI = "ADODB" Then
strSql = "CREATE PROCEDURE Transactions (paramAcct Text ( 255 ), paramDtFrom DateTime, paramDtTo DateTime, paramBankCash Bit, paramIncludeParents Bit) AS " & vbNewLine
#End If
strSql &= "SELECT IIf(Len([TRN].[szid])>0,LTrim(Right([TRN].[szId],Len([TRN].[szId])-1)),Null) AS [Number], TRN.dt AS dtDate, ACCT.szFull AS Account, PAY.szFull AS Payee, IIf([TRN].[mFiStmtId] Is Null And [TRN_parent].[mfiStmtId] Is Null And [Trn_xfermFiStmtId].[mFiStmtId] Is Null,[TRN].[cs],3) AS CState, IIf([TRN].[ps]=1 Or [TRN].[ps]=5 Or [TRN].[ps]=6,[TRN].[ps],IIf((([grftt]\(2^19)) Mod 2)=1,7,IIf([Trn_InvDetails].[act]=1,8,IIf(Not [Trn_InvDetails].[act] Is Null,9,Null)))) AS SpecialTrans, ACCT_link.szFull AS LinkAccount, Trn_InvDetails.szFull AS Investment, IIf([TRN_parent].[PS]=1,[TRN].[ps],Null) AS PayTab, CRNC.szIsoCode AS CurrIsoCode, TRN.amt AS Amt, Cat.t1 AS Category, Cat.t2 AS Subcategory, Cls1.t1 AS Class1, Cls1.t2 AS Subclass1, Cls2.t1 AS Class2, Cls2.t2 AS Subclass2, TRN.mMemo AS [Memo], Trn_xbagTaxYears.TaxYear AS TaxYear, Not [Trn_xbagFlags].[lHtrn]" & vbNewLine
strSql &= "Is Null AS IsFlagged, Trn_xbagFlags.flagtext AS FlagText, Trn_xbagFlags.followup AS FollowUp, Trn_xbagAttachments.attachment AS URI, (([TRN].[grftt]\(2^8)) Mod 2)=1 AS IsVoid, [TRN].[grftt]\(2^5) Mod 2=1 AS IsParent, [TRN].[grftt]\(2^6) Mod 2=1 AS IsChild, IIf(Not [TRN].[hacctLink] Is Null And [ACCT_link].[at]=5,IIf([ACCT_link].[hacctRel] Is Null,False,[TRN].[hacct]=[ACCT_link].[hacctRel]),Null) AS LinkAcctIsAcctInvAcct, CRNC.lcid, Trn_InvDetails.sct AS id_sct, Trn_InvDetails.t1 AS id_Category, Trn_InvDetails.t2 AS id_Subcategory, Trn_InvDetails.act AS id_act, Trn_InvDetails.dPrice AS id_price, IIf([Trn_InvDetails].[htrnInvDetails] Is Null,Null,[Trn_InvDetails].[IsSplitPrice]) AS id_IsSplitPrice, Trn_InvDetails.szIsoCode AS id_CurrIsoCode, Trn_InvDetails.lcid AS id_lcid, Trn_InvDetails.qty AS id_qty" & vbNewLine
strSql &= "FROM (((((((((((((TRN LEFT JOIN ACCT ON TRN.hacct = ACCT.hacct) LEFT JOIN PAY ON TRN.lHpay = PAY.hpay) INNER JOIN CRNC ON TRN.lHcrncUser = CRNC.hcrnc) LEFT JOIN ACCT AS ACCT_link ON TRN.hacctLink = ACCT_link.hacct) LEFT JOIN Trn_CatCls AS Cls1 ON TRN.lHcls1 = Cls1.hcat) LEFT JOIN Trn_CatCls AS Cls2 ON TRN.lHcls2 = Cls2.hcat) LEFT JOIN Trn_CatCls AS Cat ON TRN.hcat = Cat.hcat) LEFT JOIN Trn_xbagTaxYears ON TRN.htrn = Trn_xbagTaxYears.lHtrn) LEFT JOIN Trn_xbagAttachments ON TRN.htrn = Trn_xbagAttachments.lHtrn) LEFT JOIN Trn_xbagFlags ON TRN.htrn = Trn_xbagFlags.lHtrn) LEFT JOIN TRN_SPLIT ON TRN.htrn = TRN_SPLIT.htrn) LEFT JOIN (SELECT htrn, ps, mFiStmtId FROM TRN)  AS Trn_parent ON TRN_SPLIT.htrnParent = Trn_parent.htrn) LEFT JOIN Trn_InvDetails ON TRN.htrn = Trn_InvDetails.htrnInvDetails) LEFT JOIN Trn_xfermFiStmtId ON TRN.htrn = Trn_xfermFiStmtId.htrnFrom" & vbNewLine
strSql &= "WHERE (((TRN.dt)>=[paramDtFrom]) AND (([TRN].[grftt]\2^14 Mod 2+[TRN].[grftt]\2^17 Mod 2+[TRN].[grftt]\2^18 Mod 2+[TRN].[grftt]\2^21 Mod 2)=0) AND ((IIf([TRN].[grftt]\(2^5) Mod 2,IIf([paramIncludeParents],True,False),True))=True) AND ((IIf([paramDtTo]<=#12/31/2200# And [TRN].[dt]<=[paramDtTo],True,IIf([paramDtTo]>#12/31/2200#,True,False)))=True) AND ((IIf([paramAcct]='*',True,IIf([ACCT].[szFull] Like [paramAcct] And (([paramBankCash] And [ACCT].[ast]=0) Or (Not [paramBankCash] And [ACCT].[ast]=2)),True,False)))=True) AND (([TRN].[hacct] Is Null Or [ACCT].[ast]=0 Or [ACCT].[ast]=2)=True))" & vbNewLine
strSql &= "ORDER BY TRN.dt, Right([TRN].[szId],10), IIf([paramIncludeParents],IIf([TRN_SPLIT].[htrnParent] Is Null,[TRN].[htrn],[TRN_SPLIT].[htrnParent]),[TRN].[htrn]), IIf([TRN_parent].[PS]=1,Choose([TRN].[ps]+1,0,Null,4,1,2),Null), IIf([paramIncludeParents],IIf([TRN].[grftt]\(2^5) Mod 2,-1,[TRN_SPLIT].[iSplit]),Null);" & vbNewLine
lst.Add(New SqlDef(strName, strSql))

Else
Debug.Fail("enexpected qryClass requested")
SharedState.AppLog.LogBug("enexpected qryClass requested")
End If

...

If qryClass = MmdQcls.Transaction Then

strName = "Trn_xbagAttachments"
#If dbAPI = "DAO" Then
strSql = "SELECT XBAG.lHobj AS lHtrn, XBAG.szMemo AS attachment" & vbNewLine
#ElseIf dbAPI = "ADODB" Then
strSql = "CREATE PROCEDURE Trn_xbagAttachments AS SELECT XBAG.lHobj AS lHtrn, XBAG.szMemo AS attachment" & vbNewLine
#End If
strSql &= "FROM XBAG" & vbNewLine
strSql &= "WHERE (((XBAG.bt)=13) AND ((XBAG.tbl)=-4036));" & vbNewLine
lst.Add(New SqlDef(strName, strSql))

End If

If qryClass = MmdQcls.Transaction Then

strName = "Trn_xbagFlags"
#If dbAPI = "DAO" Then
strSql = "SELECT XBAG.lHobj AS lHtrn, XBAG.szMemo AS flagtext, IIf([dtFollowup]<#12/30/9999#,[dtfollowup],Null) AS followup" & vbNewLine
#ElseIf dbAPI = "ADODB" Then
strSql = "CREATE PROCEDURE Trn_xbagFlags AS SELECT XBAG.lHobj AS lHtrn, XBAG.szMemo AS flagtext, IIf([dtFollowup]<#12/30/9999#,[dtfollowup],Null) AS followup" & vbNewLine
#End If
strSql &= "FROM XBAG" & vbNewLine
strSql &= "WHERE (((XBAG.bt)=0) AND ((XBAG.tbl)=-4036));" & vbNewLine
lst.Add(New SqlDef(strName, strSql))

End If

If qryClass = MmdQcls.Transaction Then

strName = "Trn_xbagTaxYears"
#If dbAPI = "DAO" Then
strSql = "SELECT XBAG.lHobj AS lHtrn, XBAG.grf AS TaxYear" & vbNewLine
#ElseIf dbAPI = "ADODB" Then
strSql = "CREATE PROCEDURE Trn_xbagTaxYears AS SELECT XBAG.lHobj AS lHtrn, XBAG.grf AS TaxYear" & vbNewLine
#End If
strSql &= "FROM XBAG" & vbNewLine
strSql &= "WHERE (((XBAG.bt)=11) AND ((XBAG.tbl)=-4036));" & vbNewLine
lst.Add(New SqlDef(strName, strSql))

End If

If qryClass = MmdQcls.InvTransaction Or qryClass = MmdQcls.Transaction Then

strName = "Trn_xfermFiStmtId"
#If dbAPI = "DAO" Then
strSql = "SELECT TRN_XFER.htrnFrom, IIf(Not [Trn_parent].[mFiStmtId] Is Null,[TRN_parent].[mFiStmtId],[TRN].[mFiStmtId]) AS mFiStmtId" & vbNewLine
#ElseIf dbAPI = "ADODB" Then
strSql = "CREATE PROCEDURE Trn_xfermFiStmtId AS SELECT TRN_XFER.htrnFrom, IIf(Not [Trn_parent].[mFiStmtId] Is Null,[TRN_parent].[mFiStmtId],[TRN].[mFiStmtId]) AS mFiStmtId" & vbNewLine
#End If
strSql &= "FROM ((TRN_XFER INNER JOIN TRN ON TRN_XFER.htrnLink = TRN.htrn) LEFT JOIN TRN_SPLIT ON TRN.htrn = TRN_SPLIT.htrn) LEFT JOIN TRN AS Trn_Parent ON TRN_SPLIT.htrnParent = Trn_Parent.htrn" & vbNewLine
strSql &= "WHERE ((Not (IIf(Not [Trn_parent].[mFiStmtId] Is Null,[TRN_parent].[mFiStmtId],[TRN].[mFiStmtId])) Is Null));" & vbNewLine
lst.Add(New SqlDef(strName, strSql))

End If

...

If qryClass = MmdQcls.Bill Or qryClass = MmdQcls.Transaction Then

strName = "Trn_InvDetails"
#If dbAPI = "DAO" Then
strSql = "SELECT IIf([TRN_XFER].[htrnFrom] Is Null,[TRN_XFER_1].[htrnFrom],[TRN_XFER].[htrnLink]) AS htrnInvDetails, TRN.act, SEC.sct, SEC.szFull, Trn_CatCls.t1, Trn_CatCls.t2, TRN_INV.dPrice, IIf(CLng([SEC].[dtSplit])=2958524,False,[TRN].[dt]<[SEC].[dtSplit]) AS IsSplitPrice, CRNC.szIsoCode, CRNC.lcid, TRN_INV.qty" & vbNewLine
#ElseIf dbAPI = "ADODB" Then
strSql = "CREATE PROCEDURE Trn_InvDetails AS SELECT IIf([TRN_XFER].[htrnFrom] Is Null,[TRN_XFER_1].[htrnFrom],[TRN_XFER].[htrnLink]) AS htrnInvDetails, TRN.act, SEC.sct, SEC.szFull, Trn_CatCls.t1, Trn_CatCls.t2, TRN_INV.dPrice, IIf(CLng([SEC].[dtSplit])=2958524,False,[TRN].[dt]<[SEC].[dtSplit]) AS IsSplitPrice, CRNC.szIsoCode, CRNC.lcid, TRN_INV.qty" & vbNewLine
#End If
strSql &= "FROM (((((TRN LEFT JOIN TRN_INV ON TRN.htrn = TRN_INV.htrn) INNER JOIN SEC ON TRN.hsec = SEC.hsec) LEFT JOIN Trn_CatCls ON TRN.hcat = Trn_CatCls.hcat) INNER JOIN CRNC ON SEC.hcrnc = CRNC.hcrnc) LEFT JOIN TRN_XFER ON TRN.htrn = TRN_XFER.htrnFrom) LEFT JOIN TRN_XFER AS TRN_XFER_1 ON TRN.htrn = TRN_XFER_1.htrnLink" & vbNewLine
strSql &= "WHERE ((Not (IIf([TRN_XFER].[htrnFrom] Is Null,[TRN_XFER_1].[htrnFrom],[TRN_XFER].[htrnLink])) Is Null));" & vbNewLine
lst.Add(New SqlDef(strName, strSql))

End If

...

If qryClass = MmdQcls.Bill Or qryClass = MmdQcls.InvTransaction Or qryClass = MmdQcls.Transaction Then

strName = "Trn_CatCls"
#If dbAPI = "DAO" Then
strSql = "SELECT CAT.hcat, IIf(([CAT].[hct]=0 And [CAT].[nLevel]=2) Or ([CAT].[hct]<>0 And [CAT].[nLevel]=1),[CAT_parent].[szFull],IIf(Not ([CAT].[hct]=0 And [CAT].[nLevel]=0),[CAT].[szFull],Null)) AS t1, IIf(([CAT].[hct]=0 And [CAT].[nLevel]=2) Or ([CAT].[hct]<>0 And [CAT].[nLevel]=1),[CAT].[szFull],Null) AS t2" & vbNewLine
#ElseIf dbAPI = "ADODB" Then
strSql = "CREATE PROCEDURE Trn_CatCls AS SELECT CAT.hcat, IIf(([CAT].[hct]=0 And [CAT].[nLevel]=2) Or ([CAT].[hct]<>0 And [CAT].[nLevel]=1),[CAT_parent].[szFull],IIf(Not ([CAT].[hct]=0 And [CAT].[nLevel]=0),[CAT].[szFull],Null)) AS t1, IIf(([CAT].[hct]=0 And [CAT].[nLevel]=2) Or ([CAT].[hct]<>0 And [CAT].[nLevel]=1),[CAT].[szFull],Null) AS t2" & vbNewLine
#End If
strSql &= "FROM CAT LEFT JOIN CAT AS CAT_parent ON CAT.hcatParent = CAT_parent.hcat;" & vbNewLine
lst.Add(New SqlDef(strName, strSql))

End If

...

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>