Refresh link to Querydefs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to refresh the link programatically to a Querydef that
already exists. I runs a stored procedure on the SQL Server. I want to
eliminate any prompt for the user to provide the User name and Password
because this is to be an automated process. Here is my code that I thought
would establish the link and thus allow the use of the Querydef:

sConnect = "ODBC;Description=Data Warehouse;DRIVER=SQL
Server;SERVER=D1PROD01;DATABASE=Applications;UID=dwro;Password=dwro;Trusted_Connection=yes"
Set db = CurrentDb
Set qdfSp = db.QueryDefs("spQuery")
qdfSp .Connect = sConnect
Set qdfSp = Nothing
set db - nothing

Any help is appreciated.

Mr B
 
If you are using a trusted connection, you shouldn't need to enter a UID and
PWD. I'm not sure if your code actually is in error but you have a couple
spaces prior to your .Connect.
 
Plus if the UID & PWD are in the connect string, why would the prompt be
presented to begin with?
 
The spaces do not exist in the actual code. The code seems to run without
error, however, after running the code, any attempt to run a make table query
that has the Querydef as one of the sources for the query will cause the
login prompt to the sql server.

I really need this to be automated and have been successful in connecting to
other tables in another system, using an ODBC connection. After making the
connection, opening the tables or running queries does not cause any login.

My quertion is how can I get my code to run without the login? I have tried
several things.

Your assistance is appreciated.

Mr B
 
Duane,

Thanks for the reply, but Yes I did try changing the Trusted Connection
property to "No" and still did not have any positive results.

However,

I was able to come up with a better way to get the connection to complete
and then not prompt for the user ID and password. Here is what I wound up
with:

'establish a connection to the sql server
UserID = "dwro"
PsWd = "dwro"
strConn = "ODBC;"
strConn = strConn & "DSN=D1PROD01.dsn;"
strConn = strConn & "APP=Microsoft Access;"
strConn = strConn & "DATABASE=Applications;"
strConn = strConn & "UID=" & UserID & ";"
strConn = strConn & "PWD=" & PsWd & ";"
strConn = strConn & "QUERYDEF=spQuery"
Set db = CurrentDb
Set qryDef = db.QueryDefs("spQuery")
qryDef.Connect = strConn
Set rst = qryDef.OpenRecordset(dbReadOnly)
rst.Close
Set rst = Nothing
Set qryDef = Nothing
Set db = Nothing

All variable were appropriately declared.

This has solved my problem.

Mr B
 
Back
Top