Connection String Using IBM ODBC Administration (1 Viewer)

[robertbwainwright]

Hi,

I would like to use a connection string to run queries that I have created in Access 2007. In the past the odbc link prompted the user for an ID and Password. I would like to just pass the ID and Password in VBA via a connection string, but I have never done this in the past, and I can not find info on this site or through searching the web on a tutorial on how to do so.

Currently I am using the following command to run my queries:

DoCmd.OpenQuery "qryStartTime"

From what I gather my connection string should look like this (copied from info I found):

Dim sConStr as String
sConStr = "DSN=MY400;UID=HARNER;PWD=SECRET;"

My problem is that I don't know where to use my string. Maybe I am using the wrong command to run my query? Sorry for my ignorance on this topic, but I appericate any help on this.

Robert

 

[pr2-eugin]

So what you want is a Pass through Query.. THIS Page, has some methods that would help you..

 

[robertbwainwright]

Thank you Paul for your reply, but I don't think this is what I am looking for.

 

[pr2-eugin]

So could you explain a little bit more what exactly that you are looking for?

 

[robertbwainwright]

I have a series of queries that I run using the docmd.openquery method. I have coded them to run on specific events (many many db's are running this way). When the event happens that triggers the query to run it will either prompt the user to sign in to IBM ODBC or if they have already signed in once and the pc has not been shut off it will directly run the query (it remembers the odbc log in). I would like to simply pass credentials to run queries via odbc in vba. I really don't want to write the SQL out for every query I have that is existing. Many of the queries are action queries that will write/update or append data. I would simply like to sign in to the ODBC driver on load of a form if possible.