Sql Server and Un-Bound Forms

[Dataman]

I am very new to SQL Server.

Sql server 2005 and Access 2003.

I would like to use a stored procedure or view as a recordsource for a
un-bound subform. The database will contain no linked tables.

The subform does not have to be updateable as I am only using it as a
listbox type form. I want to narrow down the records to those that start
with a selected alpha key. I might also mention that the subform fields are
not bound either.

The stored procedure is called "AlphaKeys"

SELECT @PLastName = RTRIM(@PLastName ) + '%';

SELECT PLastName + ', ' + PFirstName as
PatientName,DOB,MedicalRecNumber,PatientID

FROM dbo.tblPatients

WHERE PLastName LIKE @PLastName ;

=====================================================================================
THIS IS WHAT I HAVE SO FAR
=====================================================================================
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim intPatID As Integer
Dim ctl As Control
Dim Choice As String
Dim strChoice As String
Dim strRecordset As String


cn.Open "Provider=SQLOLEDB;data Source=DATACORP-SERVER;Initial
Catalog=MySQLServer;User Id=sa;Password=sa"

'Choice returns one of the alpha characters
Choice = Choose([OptKeys], "A", "B", "C", "D", "E", "F", "G", "H", "I", "J",
"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y",
"Z", "*")

If Not IsNull(Me.OptKeys) And Not Me.OptKeys = "" Then
strChoice = Choice
'Instantiante a Command Object
Set cmd = New ADODB.Command
With cmd
.CommandText = "AlphaKeys"
.CommandType = adCmdStoredProc
.ActiveConnection = cn
'Append a parameter containing the Choice
.Parameters.Append .CreateParameter("@PLastName", adVarChar,
adParamInput, 20, strChoice)
Set rst = .Execute
End With
=====================================================================================
HERE IS WHERE THINGS GO WRONG
=====================================================================================

Set Me.sbfPatientList.Form.Recordset = rst
Me.sbfPatientList.Requery
Set rst = Nothing
Set cn = Nothing
End If

Thanks for any help

Kurt

 

[Banana]

Strictly speaking, you aren't working with unbound forms. You're in fact
binding a ADO recordset to form which is very legitimate thing to do and
if the conditions are met, it can be updatable as well so if you really
don't want it to be updatable, then you need to set the form/query
accordingly.

That said, I don't see cursors type/location being specified. The safest
setting is to coerece the cursor to client-side static cursor. With SQL
Server, you can use server-side dynamic, if I remember it right. Also,
you normally would hold the recordset until the form's close rather than
disposing it in the same event handler that created it.

Google for a KB article about "binding ADO recordset to MS Access form"
to get a good explanation and code samples.

HTH.

I am very new to SQL Server.

Sql server 2005 and Access 2003.

I would like to use a stored procedure or view as a recordsource for a
un-bound subform. The database will contain no linked tables.

The subform does not have to be updateable as I am only using it as a
listbox type form. I want to narrow down the records to those that start
with a selected alpha key. I might also mention that the subform fields are
not bound either.

The stored procedure is called "AlphaKeys"

SELECT @PLastName = RTRIM(@PLastName ) + '%';

SELECT PLastName + ', ' + PFirstName as
PatientName,DOB,MedicalRecNumber,PatientID

FROM dbo.tblPatients

WHERE PLastName LIKE @PLastName ;

=====================================================================================
THIS IS WHAT I HAVE SO FAR
=====================================================================================
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim intPatID As Integer
Dim ctl As Control
Dim Choice As String
Dim strChoice As String
Dim strRecordset As String


cn.Open "Provider=SQLOLEDB;data Source=DATACORP-SERVER;Initial
Catalog=MySQLServer;User Id=sa;Password=sa"

'Choice returns one of the alpha characters
Choice = Choose([OptKeys], "A", "B", "C", "D", "E", "F", "G", "H", "I", "J",
"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y",
"Z", "*")

If Not IsNull(Me.OptKeys) And Not Me.OptKeys = "" Then
strChoice = Choice
'Instantiante a Command Object
Set cmd = New ADODB.Command
With cmd
.CommandText = "AlphaKeys"
.CommandType = adCmdStoredProc
.ActiveConnection = cn
'Append a parameter containing the Choice
.Parameters.Append .CreateParameter("@PLastName", adVarChar,
adParamInput, 20, strChoice)
Set rst = .Execute
End With
=====================================================================================
HERE IS WHERE THINGS GO WRONG
=====================================================================================

Set Me.sbfPatientList.Form.Recordset = rst
Me.sbfPatientList.Requery
Set rst = Nothing
Set cn = Nothing
End If

Thanks for any help

Kurt

 

[Dataman]

Thanks for the heads up.

Kurt

Strictly speaking, you aren't working with unbound forms. You're in fact
binding a ADO recordset to form which is very legitimate thing to do and
if the conditions are met, it can be updatable as well so if you really
don't want it to be updatable, then you need to set the form/query
accordingly.

That said, I don't see cursors type/location being specified. The safest
setting is to coerece the cursor to client-side static cursor. With SQL
Server, you can use server-side dynamic, if I remember it right. Also, you
normally would hold the recordset until the form's close rather than
disposing it in the same event handler that created it.

Google for a KB article about "binding ADO recordset to MS Access form" to
get a good explanation and code samples.

HTH.

I am very new to SQL Server.

Sql server 2005 and Access 2003.

I would like to use a stored procedure or view as a recordsource for a
un-bound subform. The database will contain no linked tables.

The subform does not have to be updateable as I am only using it as a
listbox type form. I want to narrow down the records to those that start
with a selected alpha key. I might also mention that the subform fields
are not bound either.

The stored procedure is called "AlphaKeys"

SELECT @PLastName = RTRIM(@PLastName ) + '%';

SELECT PLastName + ', ' + PFirstName as
PatientName,DOB,MedicalRecNumber,PatientID

FROM dbo.tblPatients

WHERE PLastName LIKE @PLastName ;

=====================================================================================
THIS IS WHAT I HAVE SO FAR
=====================================================================================
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim intPatID As Integer
Dim ctl As Control
Dim Choice As String
Dim strChoice As String
Dim strRecordset As String


cn.Open "Provider=SQLOLEDB;data Source=DATACORP-SERVER;Initial
Catalog=MySQLServer;User Id=sa;Password=sa"

'Choice returns one of the alpha characters
Choice = Choose([OptKeys], "A", "B", "C", "D", "E", "F", "G", "H", "I",
"J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W",
"X", "Y", "Z", "*")

If Not IsNull(Me.OptKeys) And Not Me.OptKeys = "" Then
strChoice = Choice
'Instantiante a Command Object
Set cmd = New ADODB.Command
With cmd
.CommandText = "AlphaKeys"
.CommandType = adCmdStoredProc
.ActiveConnection = cn
'Append a parameter containing the Choice
.Parameters.Append .CreateParameter("@PLastName", adVarChar,
adParamInput, 20, strChoice)
Set rst = .Execute
End With
=====================================================================================
HERE IS WHERE THINGS GO WRONG
=====================================================================================

Set Me.sbfPatientList.Form.Recordset = rst
Me.sbfPatientList.Requery
Set rst = Nothing
Set cn = Nothing
End If

Thanks for any help

Kurt