Re: DSN Behaving Badly

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 data*** 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=test123;"
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 ***
.