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    
post Feb 6 2018, 06:20 PM

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
" WHERE (((CUSTOMER.NO)="1"))"

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

Posts: 895
Joined: 26-March 08
From: London

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:


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


" WHERE (((CUSTOMER.NO)=1))"        'or...  " WHERE (((CUSTOMER.NO)=""1""))"



This post has been edited by HairyBob: Feb 7 2018, 02:41 AM

If it was easy, It wouldn't be fun!
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    23rd February 2018 - 09:27 PM