Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    55

    Unanswered: Saving Query Results to a String Variable

    First a little back ground... we have been having issues for the first time with Engineering Aides changes tolerences and things of that nature in our work order database. This db was build only using Access security so it has its leaks. What I'm trying to do is fortify the existing Access security. what I did was created a group WOEngAides and included all people in the EA category in this group. I can query the Access security tables to see who belongs to this group.

    Here is where I'm having trouble. I would like to query the Access security table and save the results to a string where I can parse out the names. That should save me the overhead of having to create and delete a table plus create the objects used to navigate the table.

    Now for the question: Is there any way to query a table through Visual Basic and have the query results get returned so I can save them to a string variable?

    E.g.
    Dim strQueyResults as String
    strQueyResults = Select * from Customer;

    Thanks in advance for any help.

    Animaul

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    I think what you would want to do is to loop through the recordset and append the names to a string. Something like.

    Dim rs as Recordset
    dim strTemp as String

    set rs = Currentdb.OpenRecordset("Select * From Customer")

    do until rs.EOF
    strTemp = strTemp & ", " & rs!CustomerName
    rs.MoveNext
    loop

    rs.Close

    That's not totally complete but should get you started.

    C

  3. #3
    Join Date
    Nov 2003
    Posts
    55
    Thanks for the quick reply. Unfortunately that is what I was trying to avoid. However, it may not be any worse then what I wanted to do. For example in tcl I can query a database, like in my example, and it will return the query results to me in string form. I might have found something in ADO called getString that may do wait I want.

    Thanks for the help.

    Animaul

  4. #4
    Join Date
    Jan 2005
    Posts
    31
    Quote Originally Posted by Animaul
    First a little back ground... we have been having issues for the first time with Engineering Aides changes tolerences and things of that nature in our work order database. This db was build only using Access security so it has its leaks. What I'm trying to do is fortify the existing Access security. what I did was created a group WOEngAides and included all people in the EA category in this group. I can query the Access security tables to see who belongs to this group.

    Here is where I'm having trouble. I would like to query the Access security table and save the results to a string where I can parse out the names. That should save me the overhead of having to create and delete a table plus create the objects used to navigate the table.

    Now for the question: Is there any way to query a table through Visual Basic and have the query results get returned so I can save them to a string variable?

    E.g.
    Dim strQueyResults as String
    strQueyResults = Select * from Customer;

    Thanks in advance for any help.

    Animaul
    Query the security tables? May I ask if there is some reason not to simply get the information from the DAO objects built for that purpose? I think it would be a lot easier than yanking information out of the security tables and parsing it out, etc.

    Do a search on your c: drive for "DAO360.chm" (assuming you have Access 2000 or later).

    The use of the Groups and Users collections and the Group and User objects is all in there, properties, methods, etc.

    -----------------------------------

    As for your direct question, in access, a query object generate a resultset, which is not, AFAIK, directly dumpable to a string object.

    canupus was right in suggesting a loop through a recordset to get what you want.

    Personally, I think using DAO would be a lot better.

  5. #5
    Join Date
    Nov 2003
    Posts
    55
    Thanks for the suggestion I will give it a try. I ended up using ado and getString. It is working well but there is some overhead. I'm up for trying anything that may work better.

    Let me know if you are interested in what I did with ADO and I will post it.

    Thanks,

    Animaul

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    seems to me it would more efficient to create a crosstab view and reference it when required instead of instantiating it as persistant. If you then index the view, your operating overhead becomes inconsequential in exchange for a tiny bit more physical structure.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •