QRe: Legacy Access 2 VB 4 app, Word 11 merge on XP Pro SP2 client

From: ITJRW (justice_at_community.nospam)
Date: 01/29/05

  • Next message: John Nurick: "Re: QRe: Legacy Access 2 VB 4 app, Word 11 merge on XP Pro SP2 client" 
    Date: Sat, 29 Jan 2005 14:47:52 -0600

    "John Nurick" <j.mapSoN.nurick@dial.pipex.com> wrote in message
    news:i3lnv09t9l446chs5kpp88mjvvpkv7manp@4ax.com...
    > On Sat, 29 Jan 2005 11:15:51 -0600, "ITJRW" <justice@community.nospam>
    > wrote:
    >
    >>Let me first say, I really appreciate the depth of your response--both in
    >>the questioning and the suggestions. Thanks.
    >
    > A pleasure: it's an interesting problem.

    Indeed. I find it a challenge. I would simply write a short interim app as
    a temporary merge from DSN-less SQLConnect() (which I've been able to
    successfully connect to the .mdb files and access the tables) into a cursor
    containing only the data desired. However the data is collected from
    several tables via over 24 records of steps in a "Logic" table. I suspect
    it may be placing them into a cursor long enough to create the .dat file but
    don't know for sure. I just know that it uses three of these logic record
    sets to retrieve the data. I would suspect that I may not have any
    restriction on doing this if I make the cursor read-only with Sequal Pass
    Trough approach. I guess I could locate the data in the original records of
    the various tables and create a SQL statement to retrieve all of these
    values and add my own he/she his/her values based on a "Sex" field value.

    Bottom line here is that I am developing a replacement application with new
    interfaces and tables and will ultimately bring these records into my own
    structures since the lady who developed the app is unwilling to update it
    and the client has asked me to develop a new app with more features anyway.
    I've been working on it alone since last August. It's mighty robust.

    >
    >>As to the questions, I may not be able to answer all the questions without
    >>some more direction. However, let me give them a try. But before I
    >>address
    >>the DDE, OLE, Jet issue, let me say that the workstations do not have any
    >>Access version installed on them. The legacy application was developed
    >>and
    >>compiled (with and .exe and several .dlls) using Access 2 (.mdb, .ldb,
    >>etc.)
    >>and Visual Basic 4. There are no macros that I know of that were
    >>interacting with DDE (as I'm familiar with at my office where legacy
    >>UNIX/Universe Data Base apps do merge via DDE and recent versions of Word.
    >>I suppose that would suggest the Jet database engine is what the Access
    >>2.0
    >>app is using.
    >
    > If the workstations don't have some version of Access installed on them
    > and the legacy app runs at all, it *cannot* be an Access application,
    > but is almost certainly a VB4 app that uses the Jet database engine. (It
    > makes no difference if Access was used to *create* the mdb files in the
    > first place.)
    >
    > Are you sure that when you install the legacy app it doesn't instal a
    > run-time version of Access 2? I'm labouring this point because if Access
    > isn't installed on the workstation this isn't an Access problem at all,
    > rather one involving a VB4 app failing to control Word 2003.
    >

    Where would I look for the runtime and what is it called? I assume it is a
    dll. Since I have Access 11 and the Access 2 converter on my development
    machine, do you believe I could identify it with certainty without going to
    the next county about an hour away for me?

    >>A brief description of how the merge takes place might help you know
    >>better
    >>than I what it is using. By the way, I have the legacy application
    >>installed on my development machine (OS=XP Pro SP2 and Office 2003 Pro --
    >>so
    >>I do have Access 11 installed and have downloaded and installed the Access
    >>2
    >>converter) and have reproduced the problem here as well. That is, in
    >>fact,
    >>the way I developed the macro to use as a work around. Now, the brief
    >>description of how the app merges:
    >>The user clicks a command button on the legacy app which presents them
    >>with
    >>a form selection window containing the path of the document to merge and
    >>the
    >>path of the word processing app (standard path to winword.exe has been
    >>verified and tested with a "Run" feature on the "File" pulldownmenu). A
    >>form is selected by number or by it's form descrition and when the user
    >>clicks an OK command button, they are presented with a window containing a
    >>textbox to enter the keycode (primary key) of the case record they want
    >>the
    >>merged document to contain. The click OK on this message window and are
    >>asked on another window to enter a primary key value for a specific person
    >>involved in the case. When they click on OK the error windows appear as
    >>described in the previous post (see below). In case it was not clear in
    >>the
    >>previous post, the application _does_ create the
    >><sometimes-short-merge-document-filename>.dat file using the name of the
    >>merge document the user selected.
    >
    > I don't understand what you mean by
    > <sometimes-short-merge-document-filename>.dat

    I mean that if the name of the merge document is longer than the old 8.3
    standard the .dat file is given the short file name. Examples:
    "Indictment.doc" named merge document will cause the app to create
    "Indict~1.dat" and "Motrev.doc" named merge document will cause the app to
    create "Motrev.doc" I hope that is a bit clearer.

    >
    >>Is it probable that, if DDE is being used, all of the processes are coded
    >>in
    >>the exe or dlls? Or, could there be a version issue with the Jet engine
    >>that could be dealt with?
    >
    > If the rest of the app is working it's unlikely to be a Jet issue.
    > That's why I'm focussing on the DDE or whatever else the VB app may be
    > using to control Word.
    >

    The application is performing exactly as it did on Windows 98 machines and
    NT 4.0 server in all respects except the merge doesn't even open MS Word 11,
    much less merge. The application used to open Word with the merge document
    merged into a new document with the data source (.dat file) providing the
    data for the merge fields. When they upgraded to OS XP Pro and Office 2003
    Standard the merge was "broken." Therefore, you must be right.

    >>I will post as you suggest to the other newsgroup.
    >>

    I have posted to several Word newsgroups as well.

    >>I'm not real sure how to go about what you are suggesting but I think you
    >>may have a great idea here. Let me see if I understand correctly. I
    >>replace the string to the path of winword.exe with another application
    >>executable which would simply receive the messages from the app and flush
    >>them down the "DDE sink" (itself) and then call Word 11 with arguments to
    >>run my macro at startup. I would modify my macro to use the filename that
    >>is passed to the dummy to open as the master merge document and do the
    >>stuff
    >>I'm already doing to associate the data source file the legacy app created
    >>with the merge file and do the merge. If that's correct, wow! I wish I'd
    >>thought of that.
    >
    > I'd envisaged having the "dummy" app receive and parse the messages and
    > then use (modern) automation to launch Word and perform the merge, with
    > the code running in the app rather than in a Word macro. I feel this
    > approach gives you more control than simply launching Word with
    > command-line arguments (particularly when it comes to trapping errors
    > and UI matters generall). But if you've got the command line approach
    > working, you may be able to stick with it.
    >

    Your concept would, indeed, be preferrable.

    >>The only problem is that I don't have a clue how to impersonate Word 6 in
    >>VB6 (I still have Visual Studio 6.0 installed, however I'm more
    >>comfortable
    >>in Visual FoxPro or Visual Studio.net and either VB.net or C#.net using
    >>the
    >>.net framework. Would it be possible for you to point me to some sample
    >>code or document that might guide me down the path or could you respond
    >>with
    >>a few lines of pseudocode?
    >
    > The first thing is to find out how the VB4 app is communicating with
    > Word 6. I never really used VB4 and can't remember where it stood in
    > relation to OLE, but the fact that the app wants to know the location of
    > winword.exe suggests that it may just be building a command line and
    > passing it to the shell. In that case, the impersonation is merely a
    > matter of setting the path to your dummy app in the legacy app's setup.
    > You could test that by setting the path to some innocuous executable
    > like notepad.exe instead of that to winword.exe; if an attempt to merge
    > from the app then launches Notepad, you'll know it's using the
    > shell+command line approach.

    >
    > Otherwise, things will be more difficult and are beyond my experience. I
    > guess it'd be a matter of installing some sort of snooper software to
    > see what's happening (www.sysinternals.com ?). After that, you'd
    > presumably have to spoof some registry entries.
    >
    > If it's a command line, just about any language should do the job
    > including your favourite scripting language. If DDE or OLE it will have
    > to be something that has the necessary libraries.
    >

    I researched the install.log of the application and found many "errors" such
    as the following.
    The following files were not installed:
    C:\Windows\Systeme32\Vb5db.dll
    ------same path-------- \Stdole.dll
    ------same path-------- \Stdole2.tlb
    ------same path-------- \P2sodbc.dll
    ------same path-------- \Olepro32.dll
    ------same path-------- \Oleaut32.dll
    ------same path-------- \Odbctl32.dll
    ------same path-------- \Odbcjt32.dll
    ------same path-------- \Odbcji32.dll
    ------same path-------- \Odbc32.dll
    ------same path-------- \Ms*.dll including jet35, and Msvbvm50
    Also Mfc42.dll and Mfc40.dll and many ODBC*.dll, .exe .cpl, .cnt.

    Most of the above I've checked and they were'nt installed due to newer
    versions being on the machine.

    In addition the install would not self register the following:
    vbajet32.dll, vb5db.dll, stdole2.tlb, p2sodbc.dll, several ODBC??32.dlls,
    msjter35.dll and msjter35.dll most of these because they could not be copied
    to the <system>\System32\folder. I have administrator rights and so did the
    users when the application was installed on their machines (now they don't
    since I installed a W2K3 server and improved security matters).

    I believe this problem is a typical "dll hell" problem from what I'm seeing.

    I don't know if this would give you any insights into what the application
    is using since there are dlls for ODBC, OLE, and Jet.

    > Good luck; even though it's probably not an Access issue I'd be
    > interested to hear how you get along.

    Thanks again for jumping in even though it may not be an Access problem.

    >
    >>In any case, you have been extremely helpful in pointing me to the right
    >>direction. Thanks again.
    >>
    >>Justice
    >>
    >>"John Nurick" <j.mapSoN.nurick@dial.pipex.com> wrote in message
    >>news:0jgmv098t36covq4asonvfcq50q0s38loa@4ax.com...
    >>> Hi Justice,
    >>>
    >>> I'm not quite clear what the situation is. It sounds as if the
    >>> application was developed in VB4 and automated Word 6 via DDE (or OLE?)
    >>> for this mailmerge. You mention Access 2.0; does that mean that the
    >>> application also automated Access 2.0 or simply that it uses the Jet
    >>> database engine (i.e. .mdb database files)?
    >>>
    >>> Do the new computers have Access 2.0 installed on them, or Access 11, or
    >>> both?
    >>>
    >>> Either way, there has been significant change in the Word mailmerge
    >>> object model since Word 6.x (contemporary with Access 2.0), and my first
    >>> suspicion is that this is the cause.
    >>>
    >>> It would be worth posting this question in one of the Word newsgroups in
    >>> case there's a known solution there.
    >>>
    >>> Failing that, it might be possible to improve your work-round along the
    >>> following lines. It sounds from what you say that the legacy app is
    >>> trying to use DDE to control Word.
    >>>
    >>> 1) Build a "dummy" application (maybe in VB6) that impersonates Word 6
    >>> to the legacy application. You'd include the dummy's filename in place
    >>> of "winword.exe" in the legacy app's setup settings. In the
    >>> proof-of-concept version, the dummy would simply be activated by Windows
    >>> when the legacy app tries to activate Word, accept any messages it
    >>> received, and quit at the end of the conversation; i.e. it would be a
    >>> sort of "DDE sink".
    >>>
    >>> This should allow the legacy app to create its .dat file without
    >>> producing any error messages.
    >>>
    >>> 2) If you can get that working, the next stage would be to modify the
    >>> dummy so it parses the .dat file name and any other necessary data out
    >>> of the messages it receives, fires up Word 11 and performs the merge.
    >>>
    >>>
    >>> On Fri, 28 Jan 2005 22:57:54 -0600, "ITJRW" <justice@community.nospam>
    >>> wrote:
    >>>
    >>>>I have a network where all of the client machines were recently replaced
    >>>>and
    >>>>are now running XP Pro SP2. Office 2003 is also on the new machines.
    >>>>
    >>>>The legacy application was written in VB 4 and I have no source code
    >>>>available. The Access version is 2.0. The application works as
    >>>>expected
    >>>>and as it did before the upgrade with the exception of Microsoft Word
    >>>>merging capabilities. The application creates a proper ".dat" merge
    >>>>document but is unable to do the link. The .dat file contains a
    >>>>properly
    >>>>generated header with the field names tab separated and the data from
    >>>>the
    >>>>requested record (by entering the keycode value in a message box) tab
    >>>>deliminted as well. The data is what it should contain.
    >>>>
    >>>>The setup settings are properly directing the application to the correct
    >>>>directory path and winword.exe file name. Word does not open and three
    >>>>errors occur in succession according to the messages that appear:
    >>>>In the title bar of the message box: "Merge Error (Word) #1056 and in
    >>>>the
    >>>>center of the message box: "Not a valid filename."
    >>>>Clicking on the OK command button brings up another error message box
    >>>>with
    >>>>the title bar showing "31031 (0)" and the text in the message box being
    >>>>"Invalid Source for link"
    >>>>Clicking in the OK command button brings up another error message box
    >>>>with
    >>>>the title bar showing "31004(0)" and the text in the message box being
    >>>>"No
    >>>>object."
    >>>>
    >>>>The document is a valid document and the path is correct. I have
    >>>>written
    >>>>a
    >>>>workaround macro in VBA of Word to allow them, after responding OK to
    >>>>the
    >>>>three messages in the legacy Access app, to press Alt-M in an open,
    >>>>no-document Word application which presents them with the directory
    >>>>containing the master merge document and containing the newly created
    >>>>data
    >>>>source .dat file containing the header and record data as requested in
    >>>>the
    >>>>legacy app. After selection of the requested file, I test the file name
    >>>>of
    >>>>the .doc file and create a DataSourceFileName for .dat files over 8.3
    >>>>and
    >>>>then do the merge of the document to a new file with the .dat file
    >>>>created
    >>>>and given as the source for the master document. This process proceedes
    >>>>without a problem. I suspect that there may be some ODBC connectivity
    >>>>problems between the legacy apps that don't know OLE and that are
    >>>>looking
    >>>>for another type of data source other than ".dat" files. This was the
    >>>>old
    >>>>way of providing a data source from which to merge via ODBC (to fake it
    >>>>out)
    >>>>and the older DDE application connectivity.
    >>>>
    >>>>I do not have the old app source code, the developer are not at all
    >>>>interested in updating to anything later than Access 97 (believing that
    >>>>Bill
    >>>>Gates is out to destroy them <bg>. Is there anything I can do, other
    >>>>than
    >>>>writing a new application, to allow the users to merge with this old
    >>>>application to Word v. 11? I have no problem accessing the tables via a
    >>>>DSN-less SQLConnect() statement but the record comes through some 24
    >>>>steps
    >>>>in a "Logic" table in a different database than the actual data. I have
    >>>>been doing database programming for 17 years but have little, brief
    >>>>experience with Access but quite a bit with VBA, Visual FoxPro, SQL, and
    >>>>C.
    >>>>
    >>>>Any suggestions would be greatly appreciated? The work around is
    >>>>getting
    >>>>the users to the goal line but it's taking too many downs to do make the
    >>>>touchdown.
    >>>>
    >>>>Justice
    >>>>
    >>>
    >>> --
    >>> John Nurick [Microsoft Access MVP]
    >>>
    >>> Please respond in the newgroup and not by email.
    >>
    >
    > --
    > John Nurick [Microsoft Access MVP]
    >
    > Please respond in the newgroup and not by email.

  • Next message: John Nurick: "Re: QRe: Legacy Access 2 VB 4 app, Word 11 merge on XP Pro SP2 client"