I have created a file DSN on my PC, and am using it to provide one of
the two connections required for an Access query.

(The other connection uses the PostGRE SQL connection and works as
expected.)

When I created the DSN, I specified that it should use SQL Server
Login as the authentication method, prompting the user for
credentials.

This all works and I get a successful test in the ODBC Microsoft SQL
Server Setup window.

However, when I attempt to run the query, I get a "Connection Failed"
error and I can see that the connection is attempting to log into the
database using my trusted (LAN) login.

I can then tell it to stop trying to to log into the database using
trusted login and do what I said in the first place. If it was just
me, I'd probably keep doing that, annoying as it is.

Problem is, users with the runtime version of Access never have the
option to tell the DSN to behave.

Anyone know what's going wrong with my DSN?

I have rebuilt the connection and the query and the DB twice, but to
no avail.

How do I force the connection to behave the way it was instructed to
when I built it?

Thanks,

Patrick

Hi Patrick,

I was never the biggest fan of ODBC for interfacing between Access and
Sql Server, but over the years I have come to learn that ODBC is capable
of performing all the operations that DSNless constructs (like ADO) can
perform. But my gripe with ODBC is that it is quite difficult to
control as in your scenario. One option would be to use spaghetti API
code to delete and recreate the connection. This has worked for me,
except that you have to supports several lines of spaghetti API code.

For me, an easier solution was to use ADO against the sql server (which
is what ADO was primarily designed for anyway).

Dim cmd As New ADODB.Command, RS As New ADODB.Recordset

'--trusted connection
cmd.ActiveConnection = "Provider=SQLOLEDB; Data " _ &
"Source=myServer;Database=myDB;Trusted_Connection= Yes"

'--or ID/Password connection
cmd.ActiveConnection = "Provider=SQLOLEDB; Data " _
& "Source=myServer;Database=myDB;UserId=myID;Passwor d=myPassword;"

cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandTimeout = 600
cmd.CommandType = adCmdText
cmd.CommandText = "Select count(*) from tbl1"
Set RS = cmd.Execute
Debug.Print RS(0)
cmd.ActiveConnection.Close



Rich

*** Sent via Developersdex http://www.developersdex.com ***

Rich,

Thanks very much for your response. Assuming for the moment I can
stretch my brain enough to plug my variables into your (graciously
supplied) code, would I put the code in a module and call it via an
Autoexec macro?

If not, how do I "run" it?

Thanks again,

Patrick

Good morning,

Hmmm,

With ADO, you can have either a global connection string, global command
object -- if you are only dealing with one Sql Server database, or you
can just create the ADODB objects as you need them in your procedures.
Myself, I just create them as I need them because global objects (in
Access) can be hard to manage.

So the catch then, is that you would have to rewrite the procedures in
your application which use data from the sql server. The payoff,
however, is increase reliability and ease of maintenance. It is a lot
easier to debug issues with ADO than issues with ODBC.

So if you have procedures in forms which use data from queries on ODBC
tables you can modify those procedures within the form to use ADO.

Example: you have a subform which displays data in datasheet view based
on a selection from a combobox on the mainform. The recordsource for
the subform is a query which is based on ODBC tbl1.

With ADO, you would still have tbl1, but it would be a local table (for
an mdb), and you populate tbl1 as follows:

A table on the server is called tbl1, then you create a table on the
local app which I called tblSubscr - 3 fields, then I do the following
from a command button on the main form which populates tblSubscr which
is the recordsource for a subform on the mainform and displays the data
in datasheetview Note: the connection string below is the correct
connection string which I tested. UID (not userID) and PWD (or pwd):

Private Sub Command1_Click()
Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
Dim DB As DAO.Database, RS1 As DAO.Recordset, i As Integer

cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourSvr;Database=yourDB;UID=123;password=te st123;"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandTimeout = 600
cmd.CommandType = adCmdText
cmd.CommandText = "Select RecordID, CoID, SubscrID From tb1 Where CoID =
'3M Company'"

Set RS = cmd.Execute
Set DB = CurrentDb
Set RS1 = DB.OpenRecordset("tblSubscr")
Do While Not RS.EOF
RS1.AddNew
For i = 0 To RS.Fields.Count - 1
RS1(i) = RS(i)
Next
RS1.Update
RS.MoveNext
Loop
RS.Close
RS1.Close
cmd.ActiveConnection.Close
Me.Requery
Me.Refresh
End Sub

Now the subform will display the data you just retrieved from tbl1 on
the server. The only problem I have with this though, is that there is
some looping involved to load the data into the local table. This isn't
really a problem. I just don't like looping (.Net has fixed that).


Rich

*** Sent via Developersdex http://www.developersdex.com ***

Thanks for the explanation, Rich.

In this DB I only have a single Access query and a dozen tables - 10
linked via the connection that is behaving badly, and one linked via a
connection that uses the PostGRE SQL connection and works as expected.

So given that, do I modify my query in some way to use ADO?

Sorry if I'm asking basic questions. I've been relying on things
working the "simple" way - and now I'm getting bitten.

Patrick