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

Connecting to ODBC DB at startup

P: n/a
RLN
RE: Access 2003 using WinXP SP2

Problem: When I start up my app I double click either one of my two
Oracle tables in the table list, it asks for an id and pass. I need
them to be linked at startup automatically.

The Oracle db contains two tables I need.
I've already created an ODBC data source that contains the userid and
pass to link to the Oracle DB. It works.
When I go into linked table manager, I can link to them just fine
manually.
When the applications starts up I have a VBA module that runs when the
apps starts up. It simply connects to the Oracle database and does a
'select *' from one of the tables I need and returns the right value
from the recordset. Runs great.

The problem is when I go to run a query or do anything else in the app
later against either of these two Oracle tables, Access asks for a
userid and pass as though I am not connected to it. I thought that I
still had a good connection when the app started up. I did not close
the initial connection object when the app started up.

I'm thinking of as possible solution here as I type... is there a way to
do the following in VBA:
-bring up linked table manager
-select "ODBC databases"
-select my data source
-login
-find the two tables and link them to the db.
(all of this has to be done manually right now even though I was able to
get a good connection when the app launched.)

Any ideas on what I might be able to do here?
Thanks.

FWIW: Here is my initial connection function that runs at startup:

<begin code>
Public Function OracleConnect_FXP()

Dim cnConn As ADODB.Connection
Dim rsTemp As ADODB.Recordset
Dim strDB As String
Dim strLogin As String
Dim strTable As String
Dim strMsg As String
Dim strSQL As String
Dim strPass As String

strDB = "MyDB"
strLogin = "MyLogin"
strPass = "MyPass"
strTable = "MyTable"

Set rsTemp = New ADODB.Recordset
Set cnConn = New ADODB.Connection

'Using Microsoft Oracle driver
cnConn.ConnectionString = "Provider=MSDAORA;" & "Password=" & strPass &
"; User ID=" & strLogin & ";Data Source=" & strDB & "; Persist Security
Info=True"
cnConn.CursorLocation = adUseClient
cnConn.Open
strSQL = "SELECT Count(*) as results FROM " & strTable
rsTemp.Open "SELECT Count(*) as results FROM " &
strTable, cnConn, adOpenStatic, adLockReadOnly

'yields 1 row when doing a Select Count(*)
'yieldss '98' rows when doing a Select *"
strMsg = strMsg & vbCrLf & strDB & "/" & strLogin & "/" & strPass &
vbCrLf & _
"Select (*) Count--rsTemp.Recordcount: " & rsTemp.RecordCount & vbCrLf &
"Select (*) Count--rsTemp!Results: " & rsTemp!Results & "Connection
String: " & cnConn.ConnectionString

MsgBox strMsg, vbOKOnly, "RLNTest-DB Connection"

End Function

<end code>

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

*** Sent via Developersdex http://www.developersdex.com ***
Mar 8 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Creating an Oracle connection in an independent object
won't help your Access object at all. The code below
is irrelevant for linked tables.

Creating an Oracle connection in an independent object
won't help an other objects you create later. If you
want to maintain the object you have created, you need
a static object or an object with global scope.

The object in the code below does not seem to use
an ODBC DSN at all (I may have misread it). Putting
a user name and password into a DSN won't help if
you don't use the DSN.

Are you using ODBC? Are you using linked tables?
If so, what is the connect string for the linked
tables? Did you use the DSN when you created
the linked tables? Did you save the password when
you created the linked tables?

(david)

"RLN" <no****@devdex.com> wrote in message
news:X0***************@news.uswest.net...
RE: Access 2003 using WinXP SP2

Problem: When I start up my app I double click either one of my two
Oracle tables in the table list, it asks for an id and pass. I need
them to be linked at startup automatically.

The Oracle db contains two tables I need.
I've already created an ODBC data source that contains the userid and
pass to link to the Oracle DB. It works.
When I go into linked table manager, I can link to them just fine
manually.
When the applications starts up I have a VBA module that runs when the
apps starts up. It simply connects to the Oracle database and does a
'select *' from one of the tables I need and returns the right value
from the recordset. Runs great.

The problem is when I go to run a query or do anything else in the app
later against either of these two Oracle tables, Access asks for a
userid and pass as though I am not connected to it. I thought that I
still had a good connection when the app started up. I did not close
the initial connection object when the app started up.

I'm thinking of as possible solution here as I type... is there a way to
do the following in VBA:
-bring up linked table manager
-select "ODBC databases"
-select my data source
-login
-find the two tables and link them to the db.
(all of this has to be done manually right now even though I was able to
get a good connection when the app launched.)

Any ideas on what I might be able to do here?
Thanks.

FWIW: Here is my initial connection function that runs at startup:

<begin code>
Public Function OracleConnect_FXP()

Dim cnConn As ADODB.Connection
Dim rsTemp As ADODB.Recordset
Dim strDB As String
Dim strLogin As String
Dim strTable As String
Dim strMsg As String
Dim strSQL As String
Dim strPass As String

strDB = "MyDB"
strLogin = "MyLogin"
strPass = "MyPass"
strTable = "MyTable"

Set rsTemp = New ADODB.Recordset
Set cnConn = New ADODB.Connection

'Using Microsoft Oracle driver
cnConn.ConnectionString = "Provider=MSDAORA;" & "Password=" & strPass &
"; User ID=" & strLogin & ";Data Source=" & strDB & "; Persist Security
Info=True"
cnConn.CursorLocation = adUseClient
cnConn.Open
strSQL = "SELECT Count(*) as results FROM " & strTable
rsTemp.Open "SELECT Count(*) as results FROM " &
strTable, cnConn, adOpenStatic, adLockReadOnly

'yields 1 row when doing a Select Count(*)
'yieldss '98' rows when doing a Select *"
strMsg = strMsg & vbCrLf & strDB & "/" & strLogin & "/" & strPass &
vbCrLf & _
"Select (*) Count--rsTemp.Recordcount: " & rsTemp.RecordCount & vbCrLf &
"Select (*) Count--rsTemp!Results: " & rsTemp!Results & "Connection
String: " & cnConn.ConnectionString

MsgBox strMsg, vbOKOnly, "RLNTest-DB Connection"

End Function

<end code>

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

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

Mar 9 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.