Access Databse Connections in VBA

    From within VBA I need to open a database connection and issue a select and store the results in my spreadsheet. The issue is that I only JUST learned that you can do this and I dont know where I can look for instructions. Incase it matters, I am trying to connect to a Sybase DB. I have used perl, c# and a little bit of Java to do this but never from with in Basic (let alone basic within excel!) Any help/suggestions for where to look for informations would be greatly appreciated!


    ~Si~

    Re: DB Connections in VBA


    Have you tried using the import data wizard from the Data menu ?


    Data > Import External Data > New Database Query


    I don't know about Sybase database but it works with MS Access, Sage etc

    Re: DB Connections in VBA


    I hadn't but it did work (as I said, never worked with databases in excel). Also I was able to capture the process into a macro so I can use it as part of my program. thank you! you've saved me a huge headache!

    Re: DB Connections in VBA


    I am currently running on Excel 2000. When I use the method above where I record the macro, it does work. But my boss came back to me today with a modification he wants made.


    When I execute the macro, it brings up a box asking for the database name, user id/password, server name (but I dont think in that orderr, but you get the idea). She wants this box to not show up. In 2003, the box doesn't show up because we provided the macro with all of the data, but in 2000, it still brings up the inputbox.


    Any suggestions?

    Re: DB Connections in VBA


    What type of database are you querying?


    personally, I find ADO better than MSQuery. You can find examples of using ADO with Access & SQL Server in the Advanced Excel Integration forum. With SQL Server, using SSPI (Integrated Security) will get rid of the UID/Password issue

    Re: DB Connections in VBA


    I am using the Sybase ASE driver to query a Sybase database. Is it possible to use SSPI with that?

    Re: DB Connections in VBA


    I cannot rewrite the code into ADO, there is too much code relying on how it is currently written and I checked my connection string and it was correct but it still shows the box. Thank you for the effort though, but I think it is a "feature" of excel 2000 which they fixed in 2003.
    However, I was thinking, in VB6 you can send keystrokes into the system, thereby pressing enter from a program instead of a keyboard. I played around with that a little bit in High School for reasons I won't get into, however, I dont remember how to send keystrokes to the system, does anyone remember how to do it?

    Re: DB Connections in VBA


    Try Application.Sendkeys


    Example:


    Application.Sendkeys("mhabib~") will generate keystrokes
    mhabib followed by the Enter key.


    Be sure to follow up the SendKeys command with the DoEvents command to avoid unpredictable outcomes.


    Hence:


    Application.Sendkeys("mhabib~")
    DoEvents


    Symbols for special keys (ALT, TAB, etc.) can be found in the help section.


    HTH


    m

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login