Connecting to ODBC DB at startup

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 ***
.

Relevant Pages

  • Re: Query Oracle from Excel
    ... You will need the Oracle client sofware installed on the PC you're running ... What exactly is DBDataProvider and DBDataSource? ... Global prmUsername As String ... Dim adoConn As New ADODB.Connection ...
    (microsoft.public.excel.programming)
  • RE: Query Oracle from Excel
    ... Ron, thanks for your help...this is REALLY helping me!!!! ... Now, let's say that Oracle resides on the LAN/WAN somewhere, and I don't ... Global prmUsername As String ... Dim adoConn As New ADODB.Connection ...
    (microsoft.public.excel.programming)
  • Re: System.Net.WebException
    ... My network hasnt a proxy buy I m working with the idea.... ... Why do you think that the ASP.NET app can connect and the conolse/windforns ... >> at Proceso.Proceso.MiRequest(String Method, String URL, String ... >> Dim res As HttpWebResponse ...
    (microsoft.public.dotnet.framework)
  • Active Directory gives up group info for only SOME users
    ... Our ASP.NET app is secured with forms authentication and validates users ... against Active Directory. ... Private _filterAttribute As String ... Dim entry As DirectoryEntry = New DirectoryEntry(_path, domainAndUsername, ...
    (microsoft.public.dotnet.security)
  • AD only gives up group (role) information for SOME users
    ... Our ASP.NET app is secured with forms authentication and validates users ... against Active Directory. ... Private _filterAttribute As String ... Dim entry As DirectoryEntry = New DirectoryEntry(_path, domainAndUsername, ...
    (microsoft.public.dotnet.framework.aspnet.security)