http://www.bygsoftware.com/examples/sql.html
Or you can get there from the "Excel with Access Databases" section o
page:
http://www.bygsoftware.com/examples/examples.htm

Okay, I found the old thread on Excel - Access and I downloaded thes
guys and they do work.

The problem is: When I try to use the code in my file it says that th
user-defined type "Database" is not defined. When I look in the hel
file I can't access the topics on the Databases Colletion etc.

I assume I need to install another feature of my Office but I woul
rather know which one than to install all 133 MBs worth. Also, th
examples create databases in an older format which is annoying. (I'
using office 2000 Pro)

Thank

--
TommySzalapsk
-----------------------------------------------------------------------
TommySzalapski's Profile: http://www.excelforum.com/member.php...fo&userid=2556
View this thread: http://www.excelforum.com/showthread.php?threadid=39072

i want to Connect Excel with oracle


"Gareth" wrote:

Hi,

You can connect to Access from Excel using ADO - it works very nicely
and is well documented in the VBA Help file. It's sometimes a little
tricky to find... but it's there.

Firstly, make a reference in your workbook to:

Microsoft ActiveX Data Objects 2.0 Library
[not necessarily 2.0 - there are other versions too but 2.0 works fine]

If you don't know how to do this, Select Tools\References when in the
VBE and select it from the list.

I've pasted below some of the code I use for dumping queries into a
variant - creating an array (not always two dimensional so watch for
that!). You can obviously make updates, delete records etc. If you need
code on this just post back.

[Warning: In my code, I leave the connection open until the Excel
session ends. In most other examples you will read the connection is
always closed following the DB activity. I don't do this because I am
reading and updating the DB frequently. (With Oracle at least it's
always a good idea to do this since fresh resources are allocated upon
each connection - even if it's the same user.) For a file based DB like
Access, it probably isn't necessary (although it might be minisculely
faster like this) but I like my code to be "hotswappable". So be aware
of this and change it if necessary. I am *not* an Access expert!]

TO use the below code, just pass your SQL string to ADO_RunQuery()
example below also.

HTH,
Gareth

Sub Test()
Dim x as variant
Dim i as long, j as long
Dim myLine as string

x= ADO_RunQuery("Select * from Table")

if not isarray(x) then
if not x then
msgbox "FAILED"
exit sub
end if
end if

'Do something with your array e.g.
for j = lbound(x,2) to ubound(x,2)
myline = ""
for i = lbound(x,1) to ubound(x,1)
myline = myline & "," & x(i,j)
next i
debug.print myline
next j

End Sub

'----------------code snippet begins---------
Private rs As ADODB.Recordset
Private cn As ADODB.Connection
Private cmd As ADODB.Command

Private Const myDBConnectionString As String =
_"Provider=Microsoft.Jet.OLEDB.4.0"
Private Const myDBLocation As String = "C:\myDB\myDB.mdb"

'################################################
'# SET UP DATABASE CONNECTION #
'################################################
'Once we've opened a connection we leave it open!
'This closed upon closing the workbook.
Private Function fcnConnectToDB() As Boolean

'Check whether the connection is already open.
If Not cn Is Nothing Then
If cn.State = 1 Then
fcnConnectToDB = True
Exit Function
End If
End If

'Open connection the database
Set cn = New ADODB.Connection
With cn
.Errors.Clear
On Error Resume Next
'.CursorLocation = adUseClient
.ConnectionString = myDBConnectionString
.Open myDBLocation
On Error GoTo 0
If .Errors.Count = 0 Then fcnConnectToDB = True
End With

End Function

'Returns False if there was a error
'Else returns array containing of returned records
Public Function ADO_RunQuery(mySQL As String) As Variant

'Check we're connected to the database
fcnConnectToDB

'Set the command text and execute
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = mySQL
.CommandType = adCmdText
.Execute
End With

'Open the recordset.
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Open cmd
End With

'Dump the record set into an array
If rs.RecordCount = 0 Then
ADO_RunQuery = False
Else
ADO_RunQuery = rs.GetRows
End If
rs.Close

'tidy up
Set cmd = Nothing
Set rs = Nothing
'(We leave the connection permanently open)

End Function

'----------------code snippet ends---------

TommySzalapski wrote:
http://www.bygsoftware.com/examples/sql.html
Or you can get there from the "Excel with Access Databases" section on
page:
http://www.bygsoftware.com/examples/examples.htm

Okay, I found the old thread on Excel - Access and I downloaded these
guys and they do work.

The problem is: When I try to use the code in my file it says that the
user-defined type "Database" is not defined. When I look in the help
file I can't access the topics on the Databases Colletion etc.

I assume I need to install another feature of my Office but I would
rather know which one than to install all 133 MBs worth. Also, the
examples create databases in an older format which is annoying. (I'm
using office 2000 Pro)

Thanks

Just as easy - there's plenty of documentation out there on that. Set it
up just as you would connecting to Access using ADO. Obviously you need
to have the Oracle drivers installed on your machine.

This is a connection string I've used in the past for Oracle:

Function fcnConnectToMyOracleDB(cn As ADODB.Connection) As Boolean

''######## SET UP DATABASE CONNECTION #########
'connect to the Database
Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.Errors.Clear
On Error Resume Next
cn.Open _
"Provider=OraOLEDB.Oracle.1;Password=mypassword;Pe rsist Security " _
& "Info=True;User ID=myuserid;Data Source=TESTDB"
On Error GoTo 0

If cn.Errors.Count = 0 Then fcnConnectToMyOracleDB= True

End Function




reneabesmer wrote:
i want to Connect Excel with oracle


"Gareth" wrote:


Hi,

You can connect to Access from Excel using ADO - it works very nicely
and is well documented in the VBA Help file. It's sometimes a little
tricky to find... but it's there.

Firstly, make a reference in your workbook to:

Microsoft ActiveX Data Objects 2.0 Library
[not necessarily 2.0 - there are other versions too but 2.0 works fine]

If you don't know how to do this, Select Tools\References when in the
VBE and select it from the list.

I've pasted below some of the code I use for dumping queries into a
variant - creating an array (not always two dimensional so watch for
that!). You can obviously make updates, delete records etc. If you need
code on this just post back.

[Warning: In my code, I leave the connection open until the Excel
session ends. In most other examples you will read the connection is
always closed following the DB activity. I don't do this because I am
reading and updating the DB frequently. (With Oracle at least it's
always a good idea to do this since fresh resources are allocated upon
each connection - even if it's the same user.) For a file based DB like
Access, it probably isn't necessary (although it might be minisculely
faster like this) but I like my code to be "hotswappable". So be aware
of this and change it if necessary. I am *not* an Access expert!]

TO use the below code, just pass your SQL string to ADO_RunQuery()
example below also.

HTH,
Gareth

Sub Test()
Dim x as variant
Dim i as long, j as long
Dim myLine as string

x= ADO_RunQuery("Select * from Table")

if not isarray(x) then
if not x then
msgbox "FAILED"
exit sub
end if
end if

'Do something with your array e.g.
for j = lbound(x,2) to ubound(x,2)
myline = ""
for i = lbound(x,1) to ubound(x,1)
myline = myline & "," & x(i,j)
next i
debug.print myline
next j

End Sub

'----------------code snippet begins---------
Private rs As ADODB.Recordset
Private cn As ADODB.Connection
Private cmd As ADODB.Command

Private Const myDBConnectionString As String =
_"Provider=Microsoft.Jet.OLEDB.4.0"
Private Const myDBLocation As String = "C:\myDB\myDB.mdb"

'############################################### #
'# SET UP DATABASE CONNECTION #
'############################################### #
'Once we've opened a connection we leave it open!
'This closed upon closing the workbook.
Private Function fcnConnectToDB() As Boolean

'Check whether the connection is already open.
If Not cn Is Nothing Then
If cn.State = 1 Then
fcnConnectToDB = True
Exit Function
End If
End If

'Open connection the database
Set cn = New ADODB.Connection
With cn
.Errors.Clear
On Error Resume Next
'.CursorLocation = adUseClient
.ConnectionString = myDBConnectionString
.Open myDBLocation
On Error GoTo 0
If .Errors.Count = 0 Then fcnConnectToDB = True
End With

End Function

'Returns False if there was a error
'Else returns array containing of returned records
Public Function ADO_RunQuery(mySQL As String) As Variant

'Check we're connected to the database
fcnConnectToDB

'Set the command text and execute
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = mySQL
.CommandType = adCmdText
.Execute
End With

'Open the recordset.
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Open cmd
End With

'Dump the record set into an array
If rs.RecordCount = 0 Then
ADO_RunQuery = False
Else
ADO_RunQuery = rs.GetRows
End If
rs.Close

'tidy up
Set cmd = Nothing
Set rs = Nothing
'(We leave the connection permanently open)

End Function

'----------------code snippet ends---------

TommySzalapski wrote:

http://www.bygsoftware.com/examples/sql.html
Or you can get there from the "Excel with Access Databases" section on
page:
http://www.bygsoftware.com/examples/examples.htm

Okay, I found the old thread on Excel - Access and I downloaded these
guys and they do work.

The problem is: When I try to use the code in my file it says that the
user-defined type "Database" is not defined. When I look in the help
file I can't access the topics on the Databases Colletion etc.

I assume I need to install another feature of my Office but I would
rather know which one than to install all 133 MBs worth. Also, the
examples create databases in an older format which is annoying. (I'm
using office 2000 Pro)

Thanks