Jim Lawrence
accessd at shaw.ca
Wed Jan 25 23:38:32 CST 2006
Hi John:
Do not use pass-through (ODBC) queries, I have found them to be notoriously
unstable and very slow. (Refer to Doris's email a couple of days ago.) Below
is an example that I use to fill list and combo boxes. I sent the same code
sample to Jim Moss and though the pasted together code chunks very carefully
checked I apologies if there is any errors or omissions.
You can probably encapsulate the whole process in a much more sophisticated
set of classes.
Here it is. All the parts should be here:
1. Set the ADO objects
Within an Access module:
Public gstrConnection As String
Private mobjConn As ADODB.Connection
Public rsMyRecordset As ADODB.Recordset
....
2. Create an ADO connection.
' To test that the connection is made. Note windows authentication is being
used. I usually attach a Windows user group to the MS SQL security section;
add a login form and everything is rock solid.
gstrConnection = "Provider=SQLOLEDB; _
Initial Catalog=MyMSSQLDatabse; _
Data Source=MyMSSQLServer; _
Integrated Security=SSPI"
' Test connection string
Set mobjConn = New ADODB.Connection
mobjConn.ConnectionString = gstrConnection
mobjConn.Open
' It should connect with no errors...
...
3. Create a SP on the MS SQL server (2005 version... same as SQL 2000) that
will accept parameters.
ALTER PROCEDURE [FindMatchingEmployees]
@intEmpNumber INT
AS
SELECT RED.EmpNum
FROM RegisteredEmployeesDetail as RED
WHERE RED.EmpNum=@intEmpNumber
4. Set up a recordset to received data from the SP.
Public Function FillEmployeeDetail(lngEmployeeNumber as long) As Boolean
Dim objCmd As ADODB.Command
On Error GoTo Err_FillEmployPositions
FillEmployeeDetail = False
' It is as easy to pass twenty parameters as one.
' Each must be fully named.
' values can also be return from the SP.
Set objCmd = New ADODB.Command
With objCmd
.ActiveConnection = gstrConnection
.CommandText = "FindMatchingEmployees"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@intEmpNumber", adInteger,
adParamInput, , lngEmployeeNumber)
End With
' Refresh and cleanup
If Not rsEmployeeDetail Is Nothing Then rsEmployeeDetail.close: Set
rsEmployeeDetail = Nothing
Set rsEmployeeDetail = New ADODB.Recordset
With rsEmployeeDetail
.CursorLocation = adUseClient
' There are a number of different ways this can be configured on
' how you want the connection to be handled... same as DAO. I tend
' to use disconnected recordsets and do a comparison between a changed
' local record and the same server record... under code.
.Open objCmd, , adOpenDynamic, adLockOptimistic
If .BOF = False Or .EOF = False Then .MoveLast End With
FillEmployeeDetail = True
Exit_ FillEmployeeDetail:
Set objCmd = Nothing
Exit Function
Err_ FillEmployeeDetail:
ShowErrMsg " FillEmployeeDetail "
Resume Exit_ FillEmployeeDetail
End Function
5. When the recordset is populated use its data to fill the combo/list box
Populate a combo box:
Section A:
Public Function FillEmployeeList(ctlBox As Control, id As Variant, _
row As Variant, col As Variant, _
CODE As Variant) As Variant
Dim mvReturnVal As Variant
Dim Status as Boolean
'Common Combo and List box fill function.
'Assumes rsEmployees recordset is the supplied data and has 'equal or more
fields than required in the control.
On Error GoTo Err_ FillEmployeeList
Status = false
mvReturnVal = Null
' Populate recordset using a form field and subsequently combobox/list from
If rsMyRecordset Is Nothing Then
Status = FillEmployeeDetail(Form.Employee.text)
End if
With rsMyRecordset
Select Case CODE
Case acLBInitialize ' Initialize.
If Status = true then
If .BOF = False Or .EOF = False Then
.MoveFirst
mvReturnVal = .RecordCount
Else
mvReturnVal = 0
end if
else
mvReturnVal = 0 'error no records
End If
Case acLBOpen ' Open.
mvReturnVal = Timer ' Generate unique ID for control.
gvComboTimer = mvReturnVal
Case acLBGetRowCount ' Get number of rows.
mvReturnVal = .RecordCount
Case acLBGetColumnCount ' Get number of columns.
mvReturnVal = ctlBox.ColumnCount
Case acLBGetColumnWidth ' Column width.
mvReturnVal = -1 ' -1 forces use of default width.
Case acLBGetFormat ' Get format
mvReturnVal = -1
Case acLBGetValue ' Get data.
.MoveFirst
.Move (row)
mvReturnVal = .Fields(col)
End Select
End With
FillEmployeeList = mvReturnVal
Exit_FillEmployeeList:
Exit Function
Err_ FillEmployeeList:
'Handles error situation caused an apparent unrelated error(s) 'generated
in other modules. (It loses its brains...) If Err.Number <> 91 Then
ShowErrMsg "FillEmployeeList"
End If
Resume Exit_FillEmployeeList
End Function
Section B:
On the form in question a combo box or list named "cmbEmployeeList" in the
"Row Source Type" property is the name of the populating function,
"FillEmployeeList"
I think I have added all the pieces though some of the clean up and
simplification may have moved or removed some minor pieces. There is a lot
more detail but this should get you started. A system similar to this was
used by over a hundred user network extending across the province (state).
To handle reports view my article on populating reports at our DataBase
Advisors site:
http://www.databaseadvisors.com/newsletters/newsletter112003/0311UnboundRepo
rts.htm
HTH
Jim
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: January 25, 2006 8:08 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Moving some object's recordsource to SQL Server
Jim,
Please do send code samples. Let's just discuss this for a minute though.
I am getting a bunch of different answers from build "pass through queries"
to "open ADO record sets" (with painfully little detail).
If I were to do a pass through query, what in the heck is a pass through
query? These are the kinds of answers that I find frustrating, because they
just don't provide enough substance to do anything with. If I knew what a
pass through query was, then I would not even be having this conversation.
How do I build one? What does it look like? How do I feed it parameters?
How do I reference it in a form or combo?
If I am going to use a recordset, I at least understand this idea, but can I
(for example) build a function that returns an ADO recordset, then set the
rowsource of the form / combo to "=MyADORs()"? Do I need to set a "form
global" recordset object, get that set, then poke that RS object into the
form/combo's property?
When I build a stored procedure in SQL Server, how do I make that visible in
Access? Does it "look like" a linked table, visible in the table tab? Is
it something similar except in the query window? Is it just a connection
string in code somewhere?
Does anyone have any links to professionally written articles out on the
internet that show the code, the stored procedures, graphics of the
properties dialog for the object using the recordset/pass through query?
Understand that I am a FE/ JET BE kinda guy up to this point and any answer
that goes something like "use a pass through query" (as the old "microsoft
joke" goes) while perhaps technically correct is simply useless.
Thanks for the offer of details, please do send them my way.
John W. Colby
www.ColbyConsulting.com
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim
Lawrence
Sent: Wednesday, January 25, 2006 1:41 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Moving some object's recordsource to SQL Server
Hi John:
1. Set the ADO objects
2. Create an ADO connection.
3. Create a SP on the SQL that will accept parameters.
4. Set up a recordset to received data from the SP.
5. When the recordset is populated use its data to fill the combo/list box
(use the old combo box object if you do not have it I will send you a
working copy but I think you do.)
Every time a change is made in the request (filter) processes from 1 to 5
are invoked. If you need code samples I will be glad to send then to you.
Jim
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: January 24, 2006 9:10 PM
To: 'Access Developers discussion and problem solving';
dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Moving some object's recordsource to SQL Server
I need a "step by step" if you will of moving some object to SQL Server -
combo or form - where the Access query was filtered and the filter won't
work in SQL Server. For example a reference to a control on a form.
I know that I have to create a SP in SQL Server that accepts a parameter.
How do I "connect" to that query from Access? Is it a n ODBC (or other?)
link, just like a table? Then how do I feed the parameters to the SP out in
SQL server.
I am getting ready to do this and am way under prepared for this.
Environment:
SQL Server Express 2005
Access (Office) XP or 2003
Fes now talking to the SQL Server BE using the standard (ODBC I think)
"links" that the upsize wizard created when it moved the tables.
The FE seems to work as it did before except slower. Now if I can start
replacing the slow "Access Queries over ODBC" with "SQL Server SPs with
passed params????" I might be able to see what this is capable of. I just
haven't a clue how to move to that "SP with Params".
John W. Colby
www.ColbyConsulting.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com