ODBC Connection String

[suntsu]

Quick Query

I have set up a report to export itself to excel, attach to email and send off at the same time daily, now this all works but the pain is that I am prompted for my odbc login credentials each time.

the main underlying table linked through the query is an informix 5 table (if it makes any difference).

Thanks in advance for the wisdom

Sun Tsu

 

[jes]

Hi SunTsu,

Hope this helps, I'm not sure where I got it from, but it should do what you want. Call this routine when you start up.

Insert the DSN where I've put XXXX, and put connection string wher I've put YYYY.

Sub PreConnect(strUserName As String, strPassword As String)
Dim wrkRemote As Workspace, dbsRemote As Database
Dim strConnect As String

strConnect = "ODBC;DSN=XXXX;ConnectString=YYYY;" & _
"UID=" & strUserName & ";PWD=" & strPassword & ";"
Set wrkRemote = DBEngine.Workspaces(0)
Set dbsRemote = wrkRemote.OpenDatabase("", False, False, strConnect)
dbsRemote.Close ' Close database but keep connection.
End Sub

Let me know how it goes,

Jes

 

[suntsu]

Hi Jes,

Thanks for the help,
I copied the code and amended accordingly....

on the autoexec macro I inerted the selected RunCode:"Preconnect()" after changing the sub to a public function.

when the macro started running it would pass through your sub, then execute the report, unfortunately I was still prompted for login+pass to the odbc connection.

After that I checked one of the access system tables and viwed the connection string to the linked table (wouldn't let me add the login ID or Password).

After this I deleted the links, and recreated them selecting 'remember password' this worked a treat until I closed access. Now it prompts me again every time

Do you think it may be an issue with my odbc drivers ?
I am using the latest openlink (v4) drivers.
Possibly an access fault ?

Thanks,

David

 

[jes]

Hi SunTsu,

Did you call the function as preconnect XXXX,YYYY ? Alas, it does mean having the username and password somewhere in the code( or hidden table ) but should keep it safe from most users.

Other than that I can't really be of much assistance, but let me know - in the meantime I'll check out where I've used it,

Jes