473,729 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Need to Connect to Oracle DB with no ODBC

RLN
I have an Access2002 database that needs to connect to an Oracle
Database. I connected to my Oracle DB in a simple VB6 app using no ODBC
data source. How do I do the same thing using VBA in Access?

Below is the code I used in the VB app to connect to the Oracle
database, and I just can't seem to get it translated to ADO in
Access/VBA.

Any assistance is appreciated.

Here is the VB code that connects to Oracle with no OBDC:
<begin code>
Private Sub Form_Load()
Dim cnConn As Connection
Dim rsTemp As Recordset
Dim strDB As String, strTable As String, strMsg As String, strSQL As
String

strDB = "MyUniqueDB "
strlogin = "MyUniqueLo gin"
strpass = "MyUniquePa ss"
strTable = "MyUniqueTa ble"

Set rsTemp = New Recordset
Set cnConn = New Connection
cnConn.Connecti onString = "Provider=OraOL EDB.Oracle.1;" & "Password=" &
strpass & "; User ID=" & strlogin & ";Data Source=" & strDB & "; Persist
Security Info=True"
cnConn.CursorLo cation = adUseClient
cnConn.Open

strSQL = "SELECT Count(*) as results FROM " & strTable
rsTemp.Open "SELECT Count(*) as results FROM " &
strTable, cnConn, adOpenStatic, adLockReadOnly

Debug.Print "rsTemp!Res ults are: " & rsTemp!Results
Debug.Print "rsTemp.Recordc ount is: " & rsTemp.RecordCo unt

rsTemp.Close
Set rsTemp = Nothing
End Sub
<end code>

RLN

----------
"Perseveran ce - there is no substitute for hard work."
- Thomas Alva Edison

*** Sent via Developersdex http://www.developersdex.com ***
Feb 24 '06 #1
4 19852
"RLN" <no****@devdex. com> wrote:
I have an Access2002 database that needs to connect to an Oracle
Database. I connected to my Oracle DB in a simple VB6 app using no ODBC
data source. How do I do the same thing using VBA in Access?

Below is the code I used in the VB app to connect to the Oracle
database, and I just can't seem to get it translated to ADO in
Access/VBA.


It sounds like you want to use ADO and oraOLEDB together?
I haven't done this, but a Google search turned up the following
which you might find helpful:

http://msdn.microsoft.com/library/de...asp?frame=true

Here is a "tiny URL" link to the same MSDN page:

http://tinyurl.com/8ct4k

-Mark
Feb 24 '06 #2
RLN wrote:
I have an Access2002 database that needs to connect to an Oracle
Database. I connected to my Oracle DB in a simple VB6 app using no ODBC
data source. How do I do the same thing using VBA in Access?


You're using ADO in your code, something with which I'm not familiar.

My understanding is you cannot connect without ODBC (but see comments in
last para, below). You can, in ADO, connect without a DSN, however.
But be warned - the ADO methods used to make such a connection are such
that you cannot use the reports in an mdb.

For DSNless connections, see
http://www.carlprothman.net/Default.aspx?tabid=81

This also has OLE DB provider connections, something I once investigated
a long time ago for connecting to Oracle via VB using Oracle's 0034 or
something OLE connector. Not sure if this can be done in Access, maybe
some testing with connection on the string examples. I would love to
hear if you're successful with it.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Feb 24 '06 #3
Access is VB. Access has a different forms engine,
but it uses the same language engine.

I don't see anything wrong with the code you posted.
It looks like ordinary VB. It looks like it would
work in VB4,VB5,VB6,VBA 5,VBA6.

The code you have posted does not require translation.
If it works in an VB environment, it works in VBA6,
which is bound into Access 2002.

Perhaps, you have not set your project references
correctly? What project references are you using
in the project where the code does work?

(david)

"RLN" <no****@devdex. com> wrote in message
news:_z******** *******@news.us west.net...
I have an Access2002 database that needs to connect to an Oracle
Database. I connected to my Oracle DB in a simple VB6 app using no ODBC
data source. How do I do the same thing using VBA in Access?

Below is the code I used in the VB app to connect to the Oracle
database, and I just can't seem to get it translated to ADO in
Access/VBA.

Any assistance is appreciated.

Here is the VB code that connects to Oracle with no OBDC:
<begin code>
Private Sub Form_Load()
Dim cnConn As Connection
Dim rsTemp As Recordset
Dim strDB As String, strTable As String, strMsg As String, strSQL As
String

strDB = "MyUniqueDB "
strlogin = "MyUniqueLo gin"
strpass = "MyUniquePa ss"
strTable = "MyUniqueTa ble"

Set rsTemp = New Recordset
Set cnConn = New Connection
cnConn.Connecti onString = "Provider=OraOL EDB.Oracle.1;" & "Password=" &
strpass & "; User ID=" & strlogin & ";Data Source=" & strDB & "; Persist
Security Info=True"
cnConn.CursorLo cation = adUseClient
cnConn.Open

strSQL = "SELECT Count(*) as results FROM " & strTable
rsTemp.Open "SELECT Count(*) as results FROM " &
strTable, cnConn, adOpenStatic, adLockReadOnly

Debug.Print "rsTemp!Res ults are: " & rsTemp!Results
Debug.Print "rsTemp.Recordc ount is: " & rsTemp.RecordCo unt

rsTemp.Close
Set rsTemp = Nothing
End Sub
<end code>

RLN

----------
"Perseveran ce - there is no substitute for hard work."
- Thomas Alva Edison

*** Sent via Developersdex http://www.developersdex.com ***

Feb 25 '06 #4
>>You can, in ADO, connect without a DSN, however. <<
I don't think so.

Currently I have a connection string that works in code w/ ADO.
However when I made one little change to the TNSNames file then the
connection bombed. Same happened when I would tweak the ODBC data
source. So unless I'm missing something here, connecting to Oracle via
ADO requires ODBC & a TNSNames.ORA file and cannot be done with just
VBA code alone.

My goal here is simple. Just trying to get away from having to load
and configure a TNSNames.ORA file and an ODBC data source on each
user's workstation. I understand that the Oracle 8i client has to be
loaded on the user's workstation though.

I'm looking to just simply deploy my Access application to each
workstation minus the TNSNames/ODBC headaches.

Feb 28 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2890
by: simianphile | last post by:
OK, I had a problem that I've now fixed but I can't really understand what was causing it in the first place. I have an intranet site that uses basic authentication to allow users to view and update information from an Oracle DB (ver 8.1.6 -- ancient, I know). Anyway, the site worked fine as long as the machine accessing the ASP page was running Win 2k but when an XP machine tried to view the page, the client got an error: Microsoft...
0
3227
by: Surly | last post by:
Both the Microsoft and the Oracle ODBC driver will take an outer join with the "{oj....}" ODBC syntax and Put the (+) on one column as determined by order of the columns in the "Left Outer Join" portion of the query What the heck do I mean by that? For instance: select foo.col1 foo, nvl(bla.col1, 'NULL') bla from {oj foo left outer join bla on FOO.col1 = BLA.col1};
2
5075
by: Miran Kopmajer | last post by:
I need Oracle ODBC Driver to convert database from MS access to Oracle 9i. Can anyone help me. Thx,Miran
0
2270
by: Spiggy Topes | last post by:
Hi I have a Visual Basic application that uses ADO to access an Oracle database. I have the latest 9.x ODBC drivers installed, and SQL*Plus tells me I'm looking at Oracle 8i release 8.1.7.4.1 through an Oracle client version 9.2.0.1.0. I'm assuming that these should be compatible... The application has, until now, been running on the MS ODBC drivers for Oracle, but we'd like to get away from using LONGs, and use the
1
3859
by: Dave | last post by:
I'm trying to connect to an Oracle db on our network, through either SQL Server 2000 or Access 2000. Do I have to have Oracle client software loaded on my machine in order to do this or can I just download drivers alone?
5
102553
by: SerGioGio | last post by:
Hello, I am going nuts. I am trying to connect to my local ORACLE instance using ODBC. It used to work few weeks ago, but it fails now. Connection with: - SQL*plus: connection works! - DataDirect 5.0 Oracle Wire protocol (3rd party ODBC driver): connection works when I hit the driver's "test connection"
2
7970
by: jmev7 | last post by:
Any way to avoid having to manually enter my user name & password for the Oracle login box? I normally run a query on an attached Oracle table and have to enter the un/pw before the query will run. There's no place to enter this information in. Later, if I run subsequent queries on the table in question, I don't need to enter un/pw again. I can leave my PC on 24x7, but that's not really a solution. I need to know if I can log in to the...
2
9950
by: Crazy Cat | last post by:
Hi all, I am having trouble getting linked Oracle 9 server in MS SQL Server 2005 Express to work properly. My machine is running Windows XP. The Microsoft and Oracle OLE DB Providers have problems dealing with Oracle's Numeric Data Type, so I decided to use Microsoft's OLE DB for ODBC Provider and an Oracle ODBC source. When using the Microsoft ODBC for Oracle Driver in my ODBC source I have inconsistent behavior. Sometimes my queries...
0
1751
by: aparnats | last post by:
Hi, I need to connect to Oracle database from SQL Server. And for this, I was told to install Oracle ODBC driver. I downloaded Oracle ODBC Driver 9.0.1.8.0 and installed it (the installation was successful.). Then, I was trying to create DSN and couldn't find the driver in the list and I am stuck here and don't know what to do.. Am I doing anything wrong ? Or do I need to do anything else ? Please let me know if anybody has any ideas. The...
11
16328
by: funky | last post by:
hello, I've got a big problem ad i'm not able to resolve it. We have a server running oracle 10g version 10.1.0. We usually use access as front end and connect database tables for data extraction. We have been using oracle client 10.1.0.2 with it's odbc for a while without problem. The problem arose when we decided to reconnect all the tables and save password. Some query became suddenly very slow. Then I've discovered that the tables...
0
8925
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9288
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9206
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9154
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8156
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6722
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6026
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3240
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2692
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.