UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Connection String For Oracle    
 
   
TomS
post Feb 6 2018, 06:20 PM
Post#1



Posts: 73
Joined: 29-January 03



I have linked Oracle tables which prompt for username and password when using the first time the database is opened. I am able to open the tables after entering username and password. I would like to set the connection to the oracle tables automatically when the database opens so users are not prompted to enter the username and password to open a table. I tried the following code but I get a connection failed 3151 an error


Public Function InitConnect(UserName As String, Password As String) As Boolean
' Description: Should be called in the application’s startup
' to ensure that Access has a cached connection
' for all other ODBC objects’ use.
On Error GoTo ErrHandler
Dim dbCurrent As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset


strConnection = "ODBC;DRIVER={Microsoft ODBC for Oracle};" & _
"Server=" & "servername" & ";" & _
"Database=" & "databasename" & ";"""

Set dbCurrent = DBEngine(0)(0)
Set qdf = dbCurrent.CreateQueryDef("")

With qdf
.Connect = strConnection & _
"Uid=" & UserName & ";" & _
"Pwd=" & Password
.SQL = "SELECT CUSTOMER.NO" & _
" FROM CUSTOMER" & _
" WHERE (((CUSTOMER.NO)="1"))"

Set rst = .OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)
End With
InitConnect = True
ExitProcedure:
On Error Resume Next
Set rst = Nothing
Set qdf = Nothing
Set dbCurrent = Nothing
Exit Function
ErrHandler:
InitConnect = False
MsgBox Err.Description & " (" & Err.Number & ") encountered", _
vbOKOnly + vbCritical, "InitConnect"
Resume ExitProcedure
Resume
End Function
Go to the top of the page
 
HairyBob
post Feb 7 2018, 02:40 AM
Post#2



Posts: 987
Joined: 26-March 08
From: London, UK


Are 'servername' and 'databasename' module-level or global identifiers? (they are not declared at function level). Whether you have declare Option Explicit or not, try removing the double-quotes from around these identifiers, together with the double double-quotes immediately after the semi-colon. Also, try removing the double-quotes round the '1' in the WHERE criteria (or if the data type of CUSTOMER.NO is text, try adding extra double-quotes so that it's ""1"") i.e:

CODE
...

strConnection = "ODBC;DRIVER={Microsoft ODBC for Oracle};" & _
"Server=" & servername & ";" & _
"Database=" & databasename & ";"

...

.SQL = "SELECT CUSTOMER.NO" & _
" FROM CUSTOMER" & _
" WHERE (((CUSTOMER.NO)=1))"        'or...  " WHERE (((CUSTOMER.NO)=""1""))"

...


HTH

Hairy.
This post has been edited by HairyBob: Feb 7 2018, 02:41 AM
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th July 2018 - 02:45 AM