Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004

    Angry Unanswered: My Excel does not support Database Query?

    I was trying to prepare VBA functions to query an Oracle database via ODBC. However, when I was composing the program in Excel's VB Editor, the compiler prompted me error, saying the Database, Recordset datatypes were not defined.

    I found many examples, including the Excel Helps, that showed the usage of Recordset and Database types. So, I was wondering what I did wrong with my Excel program? My Excel is of version 2002 and the VB Editor is of version 6.0

    Would you please kindly show me the direction? Thank you in advance.

  2. #2
    Join Date
    Feb 2004
    Database access from Excel can be tricky. There are 3 ways you can access either by ODBC, DAO, or ADO. Either way you will need to have the database parameters, name, password etc. defined exactly and in the right format for the method of access your using. Even with ODBC there is different ways of defining and accessing the database. You must also have correctly structure your SQL Statements.

    Try using Microsoft Query Editor to see if you can access the database that way first. In Excel from a new workbook select 'Data->Import External Data->New Database Query...' This will open the query wizard and allow you to view the result in Excel or the Query Editor. There you can configure and Copy your SQL text to use in VB.

    If you can't work out the problem post back here and include an EXAMPLE of your code that you've tried. Most likely someone can look at it and give you some more specific suggestions.

    Good Luck


  3. #3
    Join Date
    Feb 2004

    My Excel does not support Database Query in VBA?

    Hi Bill,

    Thanks for your reply.

    I actually could query the database using your suggested method. The problem was that, when I tried to compose a VBA function to query the database, the compiler doesn't recognise the Database, Recordset, .... datatypes. So, I could not go further with the composition. The VB Editor supposes to suggest me with some command with its AutoComplete function, but it didn't suggest those Database commands, nor the Database datatypes.

    Would you please kindly suggest the workarounds? Thank you very much.


  4. #4
    Join Date
    Jan 2004
    Aberdeen, Scotland
    a quick thought would be to make sure you have the references required available before wrting your code
    i.e. for DAO you will need
    Microsoft DAO ?.? Object Library
    Where ?.? is the highest number you have on my version i have 3.6

    for ADO you will need
    Microsoft ActiveX Data Objects ?.? Library
    the highest i have on my machine is 2.7
    or for work with tables views, etc,
    Microsoft Ext. ?.? for DDL and Security

    Hope this helps

  5. #5
    Join Date
    Feb 2004

    Thumbs up It works now. Thanks!

    Thanks Dave. the problem solved after I included the Microsoft ActiveX Data Objects Recordset Library in the VB editor. Thanks a lot.

Posting Permissions

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