Solved

Excel DAO to Oracle

Posted on 1998-11-20
4
351 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

829 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