Solved

Excel DAO to Oracle

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

626 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