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.

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

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.

Regards,

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
Dave

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