Unbound form + Vba + Ado + Rowsource

egovernment
egovernment used Ask the Experts™
on
I need urgent an example with
Unbound form + Vba + Ado + Rowsource
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Here you go ... and I just used it this week!
Has list/combo box rowsource, forms ... VERY COOL.

http://www.databasedevelopment.co.uk/examples.htm

Scroll down to :

"Bind Any Data to an Access Form by Generating Recordsets (Download RecordsetBind.zip)
Creating ADO recordsets from scratch allows a developer a great deal of freedom.
Fill the recordset with whatever you want - live data and extra fields you've added too - perhaps to use as a selection aid.
This example allows binding that recordset to a form (Access 2002+) - for editing and appending to the disconnected data.
(Example Implementations "RecordsetBind Example.zip")"

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
And check out the other samples on that Leigh's site also ... some truly GREAT stuff.

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
This is what I needed and tweaked his code to do ...
Open external db.
Get records
Set RowSource of List Box (could be Form Record Source just as well).
Close External db.

RowSource (or RecordSource) persists, ready for use!

 = = =

    Dim rs As New ADODB.Recordset
    Dim sSQL As String, sServerDMXPath As String
    sSQL = CurrentDb.QueryDefs("qryDXM").SQL
    sServerDMXPath = "\\abc\workgroup\CSBU2\BPO-CSBU\Mass AOR Desk\zzzzBetaTest\DXM\DXMData.mdb;"
    'Set rs = New ADODB.Recordset
   
    With rs
        '.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = " & sServerDMXPath
        .CursorLocation = adUseClient   'very important - maintains
        '.CursorType = adOpenStatic 'adOpenKeyset
        '.LockType = adLockBatchOptimistic
        '.Source = sSQL  ' "SELECT ID, NameField, DescriptionField FROM tblDXM"
        .Open sSQL, "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = " & sServerDMXPath, adOpenForwardOnly, adLockBatchOptimistic
        Me.lstList = Null   'do this so nothing is selected - for testing only.
        Set Me.lstList.Recordset = rs   ' Could set a Form's RecordSource here
        Set .ActiveConnection = Nothing
        ' .Close '  dont need this
    End With
    'Set rs = Nothing
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
I need urgent direct example Unbound form + Vba + Ado + Rowsource
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Please see the sample MDB at that link.  The examples are very simple and ready to use.  Exactly what you need.    And/or look as my example above.  

I've attached the sample mdb for you.

mx
RecordsetBindExample.zip

Author

Commented:
DatabaseMX where Rowsource in your examples ?

Author

Commented:
I'm using in back-end SQL Server not Access

Author

Commented:
DatabaseMX where is the Rowsource

Please if you don't know you should not answer

Author

Commented:
There no fast and direct answer ?

Author

Commented:
Where the experts ?

Why I should wait long time to get answer and not correct ?

Any body here !!!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"Please if you don't know you should not answer"
I do know and I did answer.  Please look at the examples.  There are several.  Choose the on that is closest to what you need.

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
I gave you a correct answer, as well as I posted a sample.  If you don't want to look at the sample db, then I really can't help you, sorry.

Also, your question is VERY vague.

Author

Commented:
Please give me direct example I don't want to search so if I want to search I should search by google
Top Expert 2011

Commented:
<,... direct example Unbound form + Vba + Ado + Rowsource>>

Direct example of what? You need to be a lot more specific on what you need.

Rowsource for what object?  (Combo box, list box, etc) Please be specific.

Do you mean an ADO recordset not rowsource?

Note: When using ADO the it does not really matter if you are using a SQL server back end or a jet back end. The ADO code is basically the same.

I feel DatabaseMX  gave you a good answer based on your lack of specifics.  You have been giving examples of Unbound form, Vba, Ado with a recordset, and  a Rowsource.  

We can only give you what you ask for. We no nothing about your project or homework.  If you make a very vague request like "I need urgent an example with Unbound form + Vba + Ado + Rowsource" you will get very general answers. If you want a specific example the you have to give use the specific detail of what you need.

If this is truly urgent then you need to take the time to be specific about exactly what you need.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Ado + Rowsource
That would imply List/Combo box, but I suspect the OP means Recordsource for Form ...

mx

Author

Commented:
Hi TheHiTechCoach

I'm using ADO to get data from SQL Server as back-end so I'm using the Access forms to display the data from SQL Server therefore I want to fill combobox with data using Rowsource.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"your project or homework"

And if this is Homework, we are not allowed to do that for you either.

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"I'm using the Access forms to display the data from SQL Server therefore I want to fill combobox with data using Rowsource."

That is what I posted @ http:#a35764923

mx

Author

Commented:
    vr_ConnString = "driver={SQL Server};" & _
    "server=MONEY-41\SQLEXPRESS;uid=;pwd=;database=Test"
    vr_Conn.ConnectionString = vr_ConnString
    
    vr_Conn.Open
       
    vr_RdSet.Open "SELECT [Emp Name] FROM [Employeers]", vr_Conn, _
    adOpenStatic, adLockReadOnly
    If Not vr_RdSet.EOF Then
        With Combobox            .
            Set .RowSource = vr_RdSet
        End With
    End If
    vr_RdSet.Close
    Set vr_RdSet = Nothing

Open in new window

Author

Commented:
Where the Rowsource property ??????????????????

 Dim rs As New ADODB.Recordset
    Dim sSQL As String, sServerDMXPath As String
    sSQL = CurrentDb.QueryDefs("qryDXM").SQL
    sServerDMXPath = "\\abc\workgroup\CSBU2\BPO-CSBU\Mass AOR Desk\zzzzBetaTest\DXM\DXMData.mdb;"
    'Set rs = New ADODB.Recordset
    
    With rs
        '.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = " & sServerDMXPath
        .CursorLocation = adUseClient   'very important - maintains
        '.CursorType = adOpenStatic 'adOpenKeyset
        '.LockType = adLockBatchOptimistic
        '.Source = sSQL  ' "SELECT ID, NameField, DescriptionField FROM tblDXM"
        .Open sSQL, "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = " & sServerDMXPath, adOpenForwardOnly, adLockBatchOptimistic
        Me.lstList = Null   'do this so nothing is selected - for testing only.
        Set Me.lstList.Recordset = rs   ' Could set a Form's RecordSource here
        Set .ActiveConnection = Nothing
        ' .Close '  dont need this
    End With
    'Set rs = Nothing 

Open in new window

Database Architect / Application Developer
Top Expert 2007
Commented:
It's Recordset, not RowSource when using ADO like this

Set Me.lstList.Recordset = rs  

mx

Author

Commented:
Ok fast and direct answer thanks alot

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial