I would like to connect to an Oracle 9.2 table from an Access form. What is
the format for the connection string using the Microsoft Driver for Oracle? I
do not believe I have a TNSNames file on my machine. Also, I would the user
to enter his password for the app and not be prompted for an oracle password.
Hey Joe,
This is the way I did it:
I had our Oracle dba create a user account to be used only by my application.
Below is a generic record writing function. I pass a SQL statement and a
connection string to it. Ignore the sDocName parameter. I use that in my error
trap for writing an error log. As always, watch for line wrap and, while this
code has worked in my app for well over a year, use at your own risk.
This is my connection string, substitute your own data source name, user ID and
password.
"PROVIDER=MSDAORA; DATA SOURCE=DataSourceName; USER
ID=userID;PASSWORD=password;"
<code>
Option Compare Database
Option Explicit
Function fWriteRecord(sSql As String, sConn As String, sDocName As String) As
String
' This function's only job is to take a SQL statement and a connection
' and write a record to the database
On Error GoTo ErrorHandler
Dim cnn As New ADODB.Connection
Dim vRet As Variant
cnn.Open sConn
cnn.Execute sSql
fWriteRecord = "True"
ExitPoint:
Set cnn = Nothing
Exit Function
ErrorHandler:
' Error handling stuff goes here
fWriteRecord = "False"
Resume ExitPoint
End Function
</code>
HTH,
RD
