Bypassing ODBC Security on linked queries

[Shake412]

Does anybody know how to hard code the username and password on a query using ODBC linked databases.

I'm using CreateQueryDef on some embedded SQL, then the '.Execute' method to run the query and would like the user to not have to enter the security information to access the linked tables.

I've tried relinking the tables using a connection string but this takes too long and I sometimes get a 'No Installable ISAM' error.

Any Ideas?

 

[BillPower]

The syntax is something like this:

"ODBC;DRIVER={Microsoft ODBC for Oracle};SERVER=Finance;UID=BILLP;PWD=1234"

 

[Shake412]

Thanks, But how do I fit that into a QueryDef?

 

[BillPower]

Can you post your code, SQL. Also ODBC details, Driver, DSN, Server etc.

 

[BillPower]

Have you got linked tables, do you want to keep on using these linked tables. Your question is a little confusing.

What are the Tables that you want to link to/query, MS SQL, Oracle Other?

 

[Shake412]

Sorry for any confusion.

Basically I've got a linked table in an Oracle DB, which ask for UID and Password everytime it's access through a query.

The queries I run are constructed in a string called strSQL, then created and run as queries using the following code...

Set qdf = db.CreateQueryDef(strQryName, strSQL)
db.QueryDefs.Refresh
qdf.Execute

I was wondering if the Username and Password could, in some way, be hard coded so that it wouldn't have to be entered every time.

Thanks for your help.

 

[BillPower]

I think you'll hopefully find this method will be easier and faster for you.

To get the correct connection string, put this behind a button on a form:

Dim CurDB As Database, tdfLinked As TableDef, tbl
Set CurDB = CurrentDb
For Each tbl In CurDB.TableDefs
If tbl.Attributes And dbAttachedODBC Then
Debug.Print tbl.Name & ", " & tbl.Connect
End If
Next
Set CurDB = Nothing

This assumes that you are linked to 1 Oracle DB. To use it have the immediate window open. Click the button, your connection string should be visible in the immediate window.

To reconnect your ODBC tables at startup add this to the on load event of a startup form that's not immediately bound to any linked tables. Test it first behind a button:

Dim CurDB As Database, tdfLinked As TableDef
Set CurDB = CurrentDb()
For Each tdfLinked In CurDB.TableDefs
If tdfLinked.Attributes And dbAttachedODBC Then
If Len(tdfLinked.Connect) > 0 Then
tdfLinked.Connect = "ODBC;Description=TestSQL;DRIVER=SQL Server;SERVER=oemcomputer;APP=????????sC;WSID=OEMCOMPUTER;DATABASE=NorthwindCS;JoeBloggs=sa;PWD=1234"
tdfLinked.RefreshLink
End If
End If
Next tdfLinked
Set CurDB = Nothing

Replace everything after tdfLinked.Connect = with a string copied from the immediate window. Add to the end of the string ;UID=xxxx;PWD=xxxx just as I have done above. This should seamlessly refresh your tables each time a user logs on.

Let me know how you get on.

 

[Shake412]

Excellent! It works a treat. Thanks very much.