none
trying to find syntax on getting return value of Access qry into Word. have two qry's in Access, trying to get their values to Word UserForm Code. RRS feed

  • Question

  • Only problem is passing the data from Access to Word so Word can use that data in Word's VBA code.

    I have two queries in Access 2013 64bit, Win7, VBA 7.1       Word 2013 64 bit, Win7 VBA 7.1

    One qry returns only one string.

    Second qry returns many more, current number of rows is 215 and growing and this qry has parameters to maybe narrow that number.

    I have been using the code below in a

    Word Document UserForm to call this Access database that holds the info.

    Dim MyPath As String

    Dim MyObject As Object

      MyPath = CStr("C:\Users\Mark\Documents\NameFinder2.accdb")

    Set MyObject = GetObject(MyPath)

    MyObject.Visible = True     'seems needs to be True to see form.

    MyObject.DoCmd.OpenForm "frmTblLands"     'this does open frmTblLands in the database.

    It is as though I opened Access from a Desktop short cut.  All features useable. 

    IF I could figure out anyone of these possible ways to get the info from Access to Word VBA I would almost be done.

    1. In Word VBA refer to a TextBox on a Access Form to get the single value, after Access runs a qry or other code.

    2. In Access declare a Public Variable1 in a standard module.  Then elsewhere in Access assign value to Variable1.  

                           Then Back in Word use that Same Public Variable1 in Word VBA code.

    3. If I can do # 2, then I figure I could Also declare in a standard module in Access, Public Arrary1, holding all the rows returned by QryManyRows in Access.  Elsewhere in Access, parameters passed to QryManyRows, then Arrary1 gets rows of qry. 


    Mark J

    Saturday, October 31, 2015 6:35 AM

Answers

  • Hi Mark J,

    Based on the description, you were retrieve the data from Access. There is not necessary to use Access object model. A more nature way is using DAO or ADO in VBA.

    Here is an helpful link for your reference:
    How To  Open ADO Connection and Recordset Objects

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by PuzzledByWord Monday, November 9, 2015 10:02 AM
    Monday, November 2, 2015 2:21 AM
    Moderator
  • Fei,

    Thanks for the link,

    Question 1. IS Data Programing with Microsoft Access 2010    EXACTLY the same as Microsoft Access 2013 ???

    I couldn't tell from the article if answer to "question 1" was yes or no.   I think it would be close, just not sure how close.

     

    I did notice link was for how to do in VB as in Visual Studio, NOT in Word VBA.  Except for one that uses

    Set db = CurrentDb()    No problem if I was in Access, I used that line often,

    but I am in Word VBA  IDE     I have No database to reference as Current Database in Word.

    And line above code sample is

    The following code example runs in Access VBA/VBE environment, on a current database.

    Problem is I am not in Access I am in Word, trying to get info from Access into Word.

    Still have problem of making an Access 2013 win7 64 bit Access accdb file THE CURRENT DATABASE in Word.

    In Access I have checked the references Microsoft Access 15.0 Object Library and Microsoft Office 15.0 Access Database Engine Object Library.

    The Word VBA IDE I am trying to make an Access Database the CurrentDb()  

    In Word I have the references checked, Word 15.0 Object Library, Office 15.0 Object Library, Access 15.0 Object Library, Microsoft Data Access Components Installed Version, Microsoft DAO 3.6 Object Library.  

    The version of Visual Studio I have, is the Free version,

    Visual Studio Community 2013, And Only Seems to permit, Under New Projects, Templates, Visual Basic, for Word and Excel (ones I am interested in, that is).   Add-in, Documents and Worksheets.

    I hope the added info helps make my question clearer.

    Mark J


    Mark J

    • Marked as answer by PuzzledByWord Monday, November 9, 2015 10:03 AM
    Friday, November 6, 2015 11:35 AM
  • Fei,

    Once I found problem was a line that looked perfect, but was not.

    Once I deleted that line, and retyped exact same string of characters, all worked great.

    Turns out the object variables weren't being set to correct value, even though the line of code looked prefect.

    That did explain why the rest of the code didn't work.

    how that line became bad, I couldn't tell, but deleting line and retyping exact same line, worked.

    The line with the error didn't show up as an error, to make it more fun.

    Mark J


    Mark J

    • Marked as answer by PuzzledByWord Monday, November 9, 2015 10:02 AM
    Monday, November 9, 2015 10:02 AM

All replies

  • Hi Mark J,

    Based on the description, you were retrieve the data from Access. There is not necessary to use Access object model. A more nature way is using DAO or ADO in VBA.

    Here is an helpful link for your reference:
    How To  Open ADO Connection and Recordset Objects

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by PuzzledByWord Monday, November 9, 2015 10:02 AM
    Monday, November 2, 2015 2:21 AM
    Moderator
  • Fei Xue,

    Thanks for the reply,

    The link was very helpful.

    One challenge I came up with was,

    How to I figure out what the correct "Connection String" should be.

    Some I figured out, like the default directory part.

    Others like  Uid=          and Pwd=

    Not sure if or what I am looking for Uid,  

    should it be Admin  to indicate Level of change,

    or should it be the user name of who is the Admin for this machine?

    And do I need to change part below 

    MORE than just Replace "mdb"   with "accdb"    ?

     Driver={Microsoft Access Driver (*.mdb)

    I did find LOTs of code referring to the "Connection String" BUT most used with C# code,

    I am learning VB, Have not started learning C#.   BUT this code is in VBA not VB or C# or .NET

    I did find Lots of code that SEEM to be using OBDC drivers to do this, and info that the drivers for my 64 bit machine, would most likely be stored in folder of    Windows \ System32

    still couldn't tell what to use in my case, I am using.

    Win 7 Ultimate, Office 2013, The calling code would be in Word 2013 64 bit, to get info from Access 2013 64 bit.

     I do also have Access 2003 32 bit files I would like to come up with a "connection string" for. That I could use in Word 2013 64 bit.  in VBA 7.1.

    Mark J


    Mark J

    Thursday, November 5, 2015 10:03 AM
  • Hi Mark J,

    The connection string depends on which data provider you choose to connect the database. There are various data providers.

    Here is an helpful links for your reference and there are also some demos in this link:
    Data Programming with Microsoft Access 2010

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 6, 2015 9:52 AM
    Moderator
  • Fei,

    Thanks for the link,

    Question 1. IS Data Programing with Microsoft Access 2010    EXACTLY the same as Microsoft Access 2013 ???

    I couldn't tell from the article if answer to "question 1" was yes or no.   I think it would be close, just not sure how close.

     

    I did notice link was for how to do in VB as in Visual Studio, NOT in Word VBA.  Except for one that uses

    Set db = CurrentDb()    No problem if I was in Access, I used that line often,

    but I am in Word VBA  IDE     I have No database to reference as Current Database in Word.

    And line above code sample is

    The following code example runs in Access VBA/VBE environment, on a current database.

    Problem is I am not in Access I am in Word, trying to get info from Access into Word.

    Still have problem of making an Access 2013 win7 64 bit Access accdb file THE CURRENT DATABASE in Word.

    In Access I have checked the references Microsoft Access 15.0 Object Library and Microsoft Office 15.0 Access Database Engine Object Library.

    The Word VBA IDE I am trying to make an Access Database the CurrentDb()  

    In Word I have the references checked, Word 15.0 Object Library, Office 15.0 Object Library, Access 15.0 Object Library, Microsoft Data Access Components Installed Version, Microsoft DAO 3.6 Object Library.  

    The version of Visual Studio I have, is the Free version,

    Visual Studio Community 2013, And Only Seems to permit, Under New Projects, Templates, Visual Basic, for Word and Excel (ones I am interested in, that is).   Add-in, Documents and Worksheets.

    I hope the added info helps make my question clearer.

    Mark J


    Mark J

    • Marked as answer by PuzzledByWord Monday, November 9, 2015 10:03 AM
    Friday, November 6, 2015 11:35 AM
  • Fei,

    Once I found problem was a line that looked perfect, but was not.

    Once I deleted that line, and retyped exact same string of characters, all worked great.

    Turns out the object variables weren't being set to correct value, even though the line of code looked prefect.

    That did explain why the rest of the code didn't work.

    how that line became bad, I couldn't tell, but deleting line and retyping exact same line, worked.

    The line with the error didn't show up as an error, to make it more fun.

    Mark J


    Mark J

    • Marked as answer by PuzzledByWord Monday, November 9, 2015 10:02 AM
    Monday, November 9, 2015 10:02 AM