Solved

Excel DAO to Oracle

Posted on 1998-11-20
4
345 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
  • 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 150 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Outlook Free & Paid Tools
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

757 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

22 Experts available now in Live!

Get 1:1 Help Now