?
Solved

ODBC Auto Connect

Posted on 2003-02-19
6
Medium Priority
?
743 Views
Last Modified: 2012-08-13
I have a MS Access 97 DB that links Oracle tables via MS ODBC for Oracle driver. What happens now is whenever the user runs the Access DB a prompt comes up asking the user for loginID, password and server. I would like to have this connection established automatically.
Something like this in the form_load event:

Dim db As Database, strCon As String
strCon = "ODBC;DSN=mydsn;UID=myuserid;PWD=mypassword;SERVER=myserver;"
Set db = OpenDatabase("", False, True, strCon)

'btw the above does not work

any suggestions?
thanks in advance
0
Comment
Question by:melsrock
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 5

Accepted Solution

by:
KMAN earned 80 total points
ID: 7982885
The Oracle tables must be relinked with connect string you eluded to in your question.  Do it with code:

Here is a sub() I use...

Sub ConnectOutput(dbsTemp As Database, strTable As String, strConnect As String, strSourceTable As String)

    Dim tdfLinked As TableDef

    ' Create a new TableDef, set its Connect and
    ' SourceTableName properties based on the passed
    ' arguments, and append it to the TableDefs collection.
    Set tdfLinked = dbsTemp.CreateTableDef(strTable)

    tdfLinked.Connect = strConnect
    tdfLinked.Attributes = dbAttachSavePWD
    tdfLinked.SourceTableName = strSourceTable
    dbsTemp.TableDefs.Append tdfLinked
   
    Set tdfLinked = Nothing

End Sub


...and a usage example...

ConnectOutput CurrentDB, "accessTableName", _
"ODBC;DSN=MyORAODBCDSN;UID=user;PWD=password;LANGUAGE=us_english;", "oracleTableName"

Or use the linked Table Add-in, where you can refresh links as needed.

GL, K
0
 

Author Comment

by:melsrock
ID: 7983025
My fear is that somehow connecting to Oracle via ODBC and using MS access as a front end I can someone corrupt the tables in Oracle.
I don't really understand your code (Probably because I don't truly understand the use of ODBC) so I am scared to try it.
Maybe a simpler solution would be to try and return a recordset and set the connection.

0
 
LVL 5

Expert Comment

by:KMAN
ID: 7993369
Nothing to be scared of.  You are simply creating a linked table with the code.  The linked table will have stored in its attributes the UID and PWD, removing the need for a prompt. It doesn't read from the table, just creates a link.

My usage example will create a Linked Table called "accessTableName" from the Oracle table "oracleTableName" with attributes in Connect string.

As far as recordset, sure but then you lose built-in Access features that can be used on table objects.  Any damage you can do with a linked table can be done with a recordset, just as easiliy.

If you need help calling a subroutine or function, let me know.

K
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 

Author Comment

by:melsrock
ID: 8018612
Thank you KMAN your example works great.  But I have now decided to use pass-through SQL, it is much faster than linking tables.  Do you have a good site that can help me "translate" Access SQL to Oracle SQL?

For example here is an example of an Access SQL string that I would like to write in Oracle...

"SELECT MAXIMO_WORKORDER.WONUM, IIf(IsNull(Sum([REGULARHRS]+[OTHRS])),0,Sum([REGULARHRS]+[OTHRS]))/96 AS UTILIZATION, CDate(Format([STATUSDATE],"Short Date")) AS NEWSTATUSDATE
FROM MAXIMO_WORKORDER LEFT JOIN MAXIMO_LABTRANS ON MAXIMO_WORKORDER.WONUM = MAXIMO_LABTRANS.WONUM
GROUP BY MAXIMO_WORKORDER.WONUM, CDate(Format([STATUSDATE],"Short Date")), MAXIMO_WORKORDER.STATUS, MAXIMO_WORKORDER.EQNUM
HAVING (((MAXIMO_WORKORDER.STATUS) Like "COMP") AND ((MAXIMO_WORKORDER.EQNUM) Like "TF-HF*"))
ORDER BY CDate(Format([STATUSDATE],"Short Date"));
"

thanks again
0
 
LVL 5

Expert Comment

by:KMAN
ID: 8018965
MAX(), SUM(), MIN(), AVG(), etc... are all the same in Oracle and most RDBs.

CDate = to_date() in Oracle

Don't know about isNull, but you can try "= NULL"

'Like' I'm not sure of either.  I wish I had a site for you but try the Oracle area in EE, I'm sure they can help.

K
0
 
LVL 5

Expert Comment

by:KMAN
ID: 8018984
I love Pass-Through queries for Read-Only access.  That is the only caveat, is that they will only retrieve data.  You can use them to do SQL INSERTs, UPDATEs and DELETEs, but not while returning records.

K
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question