Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel DAO to Oracle

Posted on 1998-11-20
4
Medium Priority
?
365 Views
Last Modified: 2012-06-21
Part 1. I have Excel 97 SP2, I want to query data from Oracle database and populate sheet and/or particular cells. Part 2. I tried to create procedure but "Dim db as database", "Database" was not recognize as a data type, Why?
0
Comment
Question by:gmoore
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 

Expert Comment

by:timjc
ID: 1614571
Have you tried recording a macro to do this. As far as I know to do this, under data menu, choose Get External Data... where the following steps should be self explanatory.

Hope this helps.

The reason why it doesn't recognise  Database as an object is you might not have all the add-ins installed. Look for ODBC and Oracle add-ins.
0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1614572
...some words from Excel Help: "To use DAO, you must first create a reference from your workbook to the DAO library. To create this reference, activate a Visual Basic module, click References on the Tools menu, and then select Microsoft DAO 3.0 Object Library in the Available References box." - after this Database will be recognized datatype. And you can get data from external database with code like that:
Sub MyQ()
  Dim dbs As database, qDef As QueryDef, rs As Recordset
  Set dbs = Workspaces(0).OpenDatabase("d:\my1.mdb")
  MsgBox "The " & dbs.Name & " database is now open"
  Set qDef = dbs.CreateQueryDef("TEST")
  qDef.SQL = "SELECT * FROM DIVISION;" ' any your SQL query
  Set rs = dbs.OpenRecordset("TEST")
  numberOfRows = Sheets("Sheet1").Cells(1, 1).CopyFromRecordset(rs)
  dbs.Close
End Sub
I've test it and it works (Excel 95 - I hope, '97 too). Check it, maybe for Oracle and '97 will be some differences. Good luck!
0
 
LVL 2

Accepted Solution

by:
pjmaze earned 600 total points
ID: 1614573
Hi,
here it is :

Sub OpenConnectionX()
    Dim work_ODBC As Workspace
    Dim con_oracle As Connection

    Set work_ODBC = CreateWorkspace("NewODBCWorkspace", _
        "username", "password", dbUseODBC)
       
    work_ODBC.DefaultCursorDriver = dbUseODBCCursor

    Set con_oracle = work_ODBC.OpenConnection("con_oracle", _
        dbDriverComplete, False, "ODBC;DSN=IN_OR7")

    con_oracle.Close
    work_ODBC.Close
End Sub

If you dont suply the fields 'username' and 'password' ODBC will prompt them.
The DSN is the name of the ODBC driver that you use, see Control Panel->ODBC

if you need some more help ask, and i will comment it !

see ya
0
 
LVL 2

Expert Comment

by:pjmaze
ID: 1614574
I forgot to say that you must open VBA editor (alt+F11) go to Tools->references and Check Microsoft DAO 3.5

see ya...
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

670 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