Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ODBC Auto Connect

Posted on 2003-02-19
6
Medium Priority
?
758 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
  • 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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

571 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