using ADO recordset as recordsource

[Chris Hawkins]

Hi -

I am trying to use an ADO recordset that I create and load with values as
the recordsource for a form or control source for a combo box on a form.
I'm told that in A2K, you can set a recordsource to a recordset, but can't
get it to work with

form.recordsource = rstADO

Can someone please advise?

Thanks,
Chris Hawkins

--

[Philipp Stiefel]

Chris Hawkins schrieb kürzlich:

>I am trying to use an ADO recordset that I create and load
>with values as the recordsource for a form or control source
>for a combo box on a form. I'm told that in A2K, you can set a
>recordsource to a recordset, but can't get it to work with
>
>form.recordsource = rstADO

Try

form.recordset = rstADO

instead.

HTH
Phil

--
Ich beantworte keine technischen Fragen per Email
Richtig zitieren: http://www.afaik.de/usenet/faq/zitieren/
Mein Codekabinett: http://www.codekabinett.com/
Karl Donaubauer's Access-FAQ: http://www.donkarl.com

[Turtle MacDermott]

Yes, you definitely need recordset, not recordsource.

I'm not 100% sure but what you need to use Set as well.

HTH
- Turtle

Chris Hawkins wrote in message ...

[Jeff Lehn]

From the Microsoft KB:

Private Sub Form_Open(Cancel As Integer)
Dim rs As ADODB.Recordset

'Create a new ADO recordset, and disconnect it
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "SELECT * FROM Customers"
.Open
End With
Set Me.Recordset = rs
End Sub

Hope that helps.

Jeff

"Chris Hawkins" <chr...@harborside.com> wrote in message
news:tRiM5.114504$bI6.4...@news1.giganews.com...

[Jeff Lehn]

Slight mistake in my previous post...pay no attention to that comment
about the disconnected recordset...I took the code from an article on that
subject and hacked it down...hope I didn't confuse you. :-)

Jeff

"Jeff Lehn" <jl...@zoomtown.com> wrote in message
news:t04a4cc...@corp.supernews.com...

[Joe]

Hi Chris:

I may be a little late with this post but I understand your frustration. I
am currently developing a fairly large application using Access 2000 as the
front-end to a SQL Server 7 back-end database. We are not using any linked
tables at all. Editing is being done by "late binding" an ADO recordset to
the form as it opens. We have been very successful with regards to both
speed of development (as compared to VB) and overall application
performance. However, it took quite a bit of R&D to create a good
application framework that we can use as a standard for future A2K
development.

To my knowledge, the recordsource of a combobox still needs to be bound to a
local Access table/query object. If you find a way to dynamically link it
to an outside source, please share the wealth with the rest of us.

I have included a small code sample as well as the "nearly impossible to
find" connection parameters needed to successfully execute an updateable ADO
based form.

2 things that are absolutely imperative:

1. the connection provider must be MSDataShape when working against SQL
Server tables - I've only ever seen this mentioned once in the MSKB and this
one parameter took us the longest to uncover.
2. This type of recordset can only update one table at a time, you must use
.UniqueTable = "tablename" to have the recordset updateable. If your
recordset is based on a query that links several tables, only one can be
updated using this technique.

' ------------- variables in example, defined in a global module
gInstID - global variable containing key of record to edit
gCnn - global variable containing connection information to SQL Server 7

' ------------- connection definition
Set gCnn = New ADODB.Connection

With gCnn
.Provider = "MSDataShape"
.ConnectionString = "DATA PROVIDER=SQLOLEDB; DATA
SOURCE=SQLServerNameHere; DATABASE=DatabaseNameHere; UID=sa; PWD=password"
.CursorLocation = adUseServer
.ConnectionTimeout = 30
.Open
End With

' ------------ code sample (extracted form one of our working forms)
Private Sub Form_Load()
On Error GoTo Err_Form_Load

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
With rs

.Source = "SELECT * FROM Institution WHERE Institution_ID=" & gInstID
.ActiveConnection = gCnn

.CursorType = adOpenKeyset
.LockType = adLockOptimistic

.Open
End With
Set Me.Recordset = rs

Me.UniqueTable = "Institution"

...other Form_Load code here...

End Sub

If you have embedded forms on the main, use the exact same technique on the
Form_Load of the linked forms. It's been working like a charm for us!

Good Luck!

Joe Hadfield
Target Software, Inc.
www.targetsoftware.com

"Chris Hawkins" <chr...@harborside.com> wrote in message
news:tRiM5.114504$bI6.4...@news1.giganews.com...