vba code to query sql db from excel

Posted on 2008-11-04
Last Modified: 2012-05-05
I have an excel sheet with 2 rows of information. the first row is numbers/id's. I have a sql db with more information for each id. I want to query the db and for each id, to return the rest of the info into excel. So Select where id=value in the cells in the first column. I can conect to the db so that part is working but I can't / do not know how to fit in the where properly. so here is the way i think it has to be. I get the id in cell one, search through the whole datatbase for that id and return the results in the next columns. then i move on cells 2 and so on. any help is appreciated
Question by:j8547
    1 Comment
    LVL 8

    Accepted Solution

    I would hesitate about making a function connect to a database because you will be opening and closing connections and causing quite a mess.  I would in a separate spreadsheet connect to the database and pull in a range of records through ODBC (or ado if you don't want to use a hard connection).  Then I would use Vlookup to retrieve the records from the page you just pulled in.  You could hide the page from the end  user, and refresh the data when the workbook is opened.  It would be much quicker that way.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Suggested Solutions

    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now