Re: ODBC for access7

From: mark tomlinson <marktoml_at_gdi.net>
Date: Thu, 09 Apr 1998 18:45:32 GMT
Message-ID: <352d16e7.19143396@newshost.us.oracle.com>

Presuming that you have a working ODBC DSN called ORA32:

This type of data import is performed via an Excel macro. Follow these steps:

1. Click on the TOOLS option on the menu bar
2. Click on the CUSTOMIZE option on the menu dropdown list
3. Check the FORMS option in the options list
4. Create a button on the spreadsheet using the "Forms" toolbar
5. This will create a default macro named Buttonx_Click
6. In the Visual Basic editor, go to the MENU->TOOLS->REFERNECES and check MS RDO 2.0
7. Copy the following code in the Buttonx_Click macro to finish the sample:

' Rdo Sample Code
Sub Button1_Click()

Dim RdoEnvironment As RdoEnvironment
Dim RdoDatabase As rdoConnection
Dim RdoRecordset As rdoResultset

Dim connstr As String
Dim sqlstmt As String

connstr = "DSN=ORA32;UID=SCOTT;PWD=TIGER;" sqlstmt = "select * from emp"

Set RdoEnvironment = rdoEnvironments(0) Set RdoDatabase = RdoEnvironment.OpenConnection("", rdDriverNoPrompt, False, _
  connstr)
Set RdoRecordset = RdoDatabase.OpenResultset(sqlstmt, _   rdOpenDynamic, rdConcurRowver)

r = 10
c = 1
While Not RdoRecordset.EOF

  ActiveSheet.Cells(r, c + 0) = RdoRecordset("EMPNO").Value
  ActiveSheet.Cells(r, c + 1) = RdoRecordset("ENAME").Value
  ActiveSheet.Cells(r, c + 2) = RdoRecordset("JOB").Value
  ActiveSheet.Cells(r, c + 3) = RdoRecordset("MGR").Value
  ActiveSheet.Cells(r, c + 4) = RdoRecordset("HIREDATE").Value
  ActiveSheet.Cells(r, c + 5) = RdoRecordset("SAL").Value
  ActiveSheet.Cells(r, c + 6) = RdoRecordset("COMM").Value
  ActiveSheet.Cells(r, c + 7) = RdoRecordset("DEPTNO").Value

  RdoRecordset.MoveNext
  r = r + 1
Wend

End Sub Received on Thu Apr 09 1998 - 13:45:32 CDT