How do I assign an ADO recordset to an Access form

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How do I assign an ADO recordset to an Access form

Author

Topic

Lin100
Yak Posting Veteran

70 Posts

Posted - 2006-10-03 : 19:12:31

Access 2002 and SQL 2000 Server.
Access ADP file.
Compile error: Type mismatch

Forms!Forms2.RecordSource = rs <-- YELLOW HIGHLIGHT

-----------------------------------------------------------------
Private Sub Assigning_Data_To_A_Recordset_Click()
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset

strConnect = "Provider=sqloledb; Data Source=Win-2000-Server;" & _
"Initial Catalog=pubs; Integrated Security=SSPI"
cnn.ConnectionString = strConnect
cnn.Open

cmd.ActiveConnection = cnn
cmd.CommandText = "Retrieve_RecordSet_Titles"
cmd.CommandType = adCmdStoredProc 'Tell Access this is a stored procedure

Set rs = cmd.Execute
Forms!Forms2.RecordSource = rs <-- YELLOW HIGHLIGHT
Forms!Forms2.Requery
End Sub

------------------------------------------

CREATE PROC Retrieve_RecordSet_Titles AS
SELECT * FROM titles
GO

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-10-03 : 19:45:54

You should just be able to pass the stored proc name as the RecordSource instead of creating a recordset.

You can try this by opening the form in design view and editing the RecordSource property (you get a list of possible sources including stored procs)

pootle_flump

1064 Posts

Posted - 2006-10-04 : 05:56:01

Probably a client side cursor required for this if you stick with the recordset.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-04 : 05:58:33

cnn.Execute "Retrieve_RecordSet_Titles"

Peter Larsson
Helsingborg, Sweden

Lin100
Yak Posting Veteran

70 Posts

Posted - 2006-10-05 : 11:51:04

Hi everybody.

To timmy. I did as you have suggested and it work.

To pootle_flump. Can you show me what you mean because I did do that already.

To Peso. I need to reassign a new recordset to a form. So I did as you have suggested.
The code belows causes Access to flag as a syntax error, and all the words turned red.
Forms!Form2.RecordSource = cnn.Execute "Retrieve_RecordSet_Titles" <--- RED TEXT
Forms!Form2.Requery

pootle_flump

1064 Posts

Posted - 2006-10-05 : 18:12:39

quote:
Originally posted by Lin100

To pootle_flump. Can you show me what you mean because I did do that already.

Nope - you don't. To create a client side recordset you need to instantiate a new recordset object and set the cursor location rather than set the recordset to the output of another objects method which sets the curosr location to server side - the default.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart01/html/sa01l8.asp
Look towards the bottom for "Assigning recordsets to controls". I am fairly sure the method you need to use is just about the same.

HTH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources