By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 1,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Link to Oracle tables with VBA

P: n/a
I have a Microsoft Access2002 database that needs to connect to an
Oracle Database. I need to map 2 tables from the Oracle DB to retrieve
the proper data.

I read somewhere (quite a while back) that if you use ADO in Access, you
can connect without the need for ODBC, TNSNAMES.ORA and the Oracle
client installed on the user's desktop.

If I use the Microsoft driver for Oracle, is it possible to connect to
an Oracle DB using straight VBA
code inside of Access and not having to mess with configuring the user's
workstations with an ODBC
data source, TNSNames.ORA file, etc.?

Any assistance is appreciated.

Using Visual Basic 6.0, below is the code I used in a simple VB app to
connect to the Oracle database,
and I just can't seem to get it translated to ADO in Access/VBA.

Here is the Visual Basic code that connects to my Oracle database
without the use of an ODBC data source:

<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

strDB = "MyUniqueDB"
strlogin = "MyUniqueLogin"
strpass = "MyUniquePass"
strTable = "MyUniqueTable"

Set rsTemp = New Recordset
Set cnConn = New Connection
cnConn.ConnectionString = "Provider=OraOLEDB.Oracle.1;" & "Password=" &
strpass & "; User ID=" & strlogin & ";Data Source=" & strDB & "; Persist
Security Info=True"
cnConn.CursorLocation = adUseClient

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

Debug.Print "rsTemp!Results are: " & rsTemp!Results
Debug.Print "rsTemp.Recordcount is: " & rsTemp.RecordCount

Set rsTemp = Nothing
End Sub

<end code>

After the DB connects, you would normally use Linked Table Manager to
link to the tables you need. Using VBA has anyone out there tried to
-interrogate to see if the application already has a link to TableX and
Table Y
-and if the table is not currently linked, use VBA code to link the
tables before the first form loads?

Thanks for the assistance.

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

*** Sent via Developersdex ***
Mar 1 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.