Object Required when using ADO 1

[SwingXH]

I have a sub to open a table using ADO (part of the codes are shown below), but I got error "Object required". I added microdoft activex data recordset 2.5 library and microsoft activex data object 2.1 library. But could not find the control on the menu like other standard controls.
Is that the problem? Thannks.
swingXH

--------------------------------------------------------
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sql As String

Set conn = Server.CreateObject("ADODB.Connection")
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open "U:\ReferenceOnDatabase\ADO.mdb"
Set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Employees", conn
---------------------------------------------------------

 

[RoyVidar]

Should it be a Debug.Print in front of the rs("... thingies (isn't that some kind of equivalence to response.write?), to study the result in the immidiate pane (ctrl+g)? Or some assigning?

I think also if SwingXH took the time to try to explain what the process and purpose of this is, then we could assist more, either by explaining or link to relevant threads/faqs.

For instance
* are the tables within this database, are they (or could they be) linked, or are they "truly" external
* is the result supposed to be shown on a form (as the recordsource - bound), in a listbox control on the form, or something else

Roy-Vidar

 

[SwingXH]

OK, let me specify the problem.
Suppose I have three simplified tables, namely table1, table2 and table3.

Table1 (for general info)
ID Name Retired City
1 AAA Yes NYC
2 BBB No LA
3 CCC Yes LA
4 DDD No DC

Table2 (for those who retired)
ID Name Yearsworked YearRetired Sex
1 AAA 30 1990 M
3 CCC 25 2004 F

Table3 (for non-retired)
ID Name YearsStart Title Sex
1 AAA 1990 CFO M
4 DDD 1995 CEO F

In the main input form, I'll have a combol, let's say combolID, which is used to select or enter the ID for the employees.

My objectives are that
1, Once this ID is picked, I wish form1 show up which give the current record in Table1 that matches selected ID.
2, Creat a query to form a new recordset for those who are females and display data on Form2 in the structure of Table2 or Table3 depending on which table they originally belong to. Also show the total number of records that match the query.
3, Similar to 2, but I want the record to show on a new structured form3 which combines the structures of table2 and table3 without redundant fields.

Hope I explained clear enough this time and won't give you too much trouble. Look forward to your reply and thank you very much for your time and help in advance.

SwingXH

 

[RoyVidar]

I was more into thinking about the retrieval technique (ADO, tables/queries, sql strings) and where those should be assigned, than solving the actual "how to get the correct information", based on the challenges of the syntax. Don't know quite where to start.

Now if the forms are designed, the controls on the forms have their controlsources set to field names that will exist in the recordset/recordsource there are a lot of techniques to use.

If the tables are within this database, or linked:

Create stored queries (with criteria/parameters if needed) and set that as the forms recordsourse (in design view).

Use the stored query (or table if applicable) as recordsource, and assign the where condition when opening the form:

[tt]docmd.openform "form1",,,"ID = " & me("combolID").value[/tt]

Assign the forms recordsource dynamicly based on some selection:

[tt]me.recordsource="select * from ....where ID =" & me("combolID").value[/tt]

You can also, for instance in a forms on open or load event, assign an ADO recordset to the form:

[tt]set me.recordset=rs[/tt]

Using Access 2000, the form recordset won't be updateable (well, it might be, with some quirks, I'm sure cmmrfrds knows more about that)

For the first issue, I'd place the combo on a form having table 1 as recordsource, and use the combo wizard to create a "find record" thingie. Code would look something like this in the after update of the combo:

[tt]dim rs as dao.recordset ' if dao
set rs=me.recordsetclone
rs.findfirst "id=" & me("combolID").value
if not rs.nomatch then me.bookmark=rs.bookmark
set rs=noting[/tt]

For ADO form recordset
[tt]dim rs as adodb.recordset
set rs=me.recordsetclone
rs.find "id=" & me("combolID").value
if not rs.eof then me.bookmark=rs.bookmark
set rs=noting[/tt]

or, as above, just use a where condition when opening the form, which implicates that the form needs table1 as recordsource:

[tt]docmd.openform "form1",,,"ID = " & me("combolID").value[/tt]

How would the first query look? That's really not my strength, but from what I see, you're going to have some problems due to the redundancy. I think I'd work on the structure a bit. That said, creating a query where the sequence of the fields mirrors which table they “belong” to, might perhaps be done thru a union query:

[tt]select ID, [Name], Yearsworked, YearRetired, Sex
from table2
where sex="F"
UNION
select ID, [Name], YearsStart, Title, Sex
from table3
where sex="F"[/tt]

Or to "spread" the columns a bit:

[tt]select ID, [Name], Yearsworked, YearRetired, "" as fld1, "" as fld2, Sex
from table2
where sex="F"
UNION
select ID, [Name], "" as fld3, "" as fld4, YearsStart, Title, Sex
from table3
where sex="F"[/tt]

To open this as a recordset, replace the double quotes with single quotes ('), and enclose the string in double quotes.

Don't know it this is what you're after, but this is what I'm able to do based on the information, and my assumptions.

Roy-Vidar

 

[SwingXH]

RoyVidar, you are great! This is very helpful to me.
I tried some of the codes, these two worked great.
---------------------------------------------------------
docmd.openform "form1",,,"ID = " & me("combolID").value

me.recordsource="select * from ....where ID =" & me("combolID").value
---------------------------------------------------------


When I tried codes with DAO,
--------------------------------------------------
dim rs as dao.recordset ' if dao
set rs=me.recordsetclone
rs.findfirst "id=" & me("combolID").value
if not rs.nomatch then me.bookmark=rs.bookmark
set rs=noting
---------------------------------------------------
I had to remove the last row of code, "set rs=noting", to make it work. Otherwise there was a run-time error 424, Object required, with "Set rs = noting" got highlighted.


When I tried codes with ADO,
------------------------------------------
dim rs as adodb.recordset
set rs=me.recordsetclone
rs.find "id=" & me("combolID").value
if not rs.eof then me.bookmark=rs.bookmark
set rs=noting
------------------------------------------
There's a run-time error "13", Type mismatch, with
Set rs = Me.RecordsetClone
got highlighted.

I will try the rest of them. Thanks so much!
SwingXH

 

[PHV]

Keep on with DAO, but replace this:
set rs=noting
By this:
Set rs = nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SwingXH]

Thanks, that worked. A typo.
In ADO part, I also used FindFirst instead of Find, then it worked too.

SwingXH

 

[cmmrfrds]

rs.find "id=" & me("combolID").value
change to.
rs.filter "id=" & me("combolID").value

The filter is the preferred method in ADO it allows multiple select criteria and does NOT requery the recordset on each find as the Find method does.

 

[SwingXH]

cmmrfrds,
when I use
rs.filter "id=" & me("combolID").value

There's an error saying "Operation is not supported by this type of object".
Did I miss something?

Thanks,
SwingXH

 

[RoyVidar]

Form recordsets in Access are DAO unless you use an ADP or you've explicitly set the form recordset to an ADO recordset.

In your case, since DAO works, it means you're working with either native access tables, or linked tables -> form recordsets are DAO, and only DAO methods can be used.

The .Find method and .Filter property are ADO method and properties, and cannot be used on DAO objects (cmmrfrds point on using .Filter in stead of .Find. is something I tend to forget unless i'm doing "multicolumn find", thanks for the reminder, but again, applies only on ADO recordets).

Just another little issue, regarding opening ADO recordset (which you'll probably do sooner or later). If this is against native or linked tables, you can use the connection of the currentproject in stead of opening a connection against the current database, perhaps something like this (using cmmrfrd previous example)

[tt]Dim rs As adodb.Recordset
Dim sql1 As String

sql1 = "select * from yourtable"
set rs = new adodb.recordset
rs.Open sql1, currentproject.connection, adOpenStatic, adLockOptimistic[/tt]

Roy-Vidar

 

[SwingXH]

Roy-Vidar,
First of all thank you very much!
I used the following codes to show the desired records on a form and it worked OK.
------------------------------------------------------
Private Sub cmdADO_Click()
Dim rs As adodb.Recordset
Dim sql1 As String

sql1 = "select * from Table1 where ID=" & Me("combolID").Value
Set rs = New adodb.Recordset
Me.RecordSource = sql1
End Sub
------------------------------------------------------
I know, in codes above, I am not using ADO at all and I can remove the definiton of rs.


When I used
rs.Open sql1, currentproject.connection, adOpenStatic, adLockOptimistic
Dim rs As adodb.Recordset
Dim sql1 As String

sql1 = "select * from Table1"
Set rs = New adodb.Recordset
rs.Open sql1, CurrentProject.Connection, adOpenStatic, adLockOptimistic
and
rs.Find "ID=" & Me("combolID").Value

Instead of Me.RecordSource = sql1
nothing happened.
I guess I missed some codes to open it?

SwingXH

 

[SwingXH]

When I tested the codes below, it worked and deleted all the records. Seems ADO works now. But how to let the form show the records for certain query? Which command should I use?
Thanks,
SwingXH

------------------------------------------------------
Dim rs As New ADODB.Recordset
Dim cnthisconnect As ADODB.Connection
Set cnthisconnect = CurrentProject.Connection
rs.Open "Table1", cnthisconnect, adOpenKeyset, adLockOptimistic, adCmdTable
Do Until rs.EOF
rs.Delete
rs.MoveNext
Loop
rs.Close

Set rs = Nothing
Set cnthisconnect = Nothing
----------------------------------------------------------

 

[RoyVidar]

Assign an sql string to a form:

[tt]Private Sub cmdAssignSQL_Click()
Dim sql1 As String
sql1 = "select * from Table1 where ID=" & Me("combolID").Value
Me.RecordSource = sql1
End Sub[/tt]

Assign an ADO recordset to a form (your recordset, here only showing the assigning, and using .Filter in stead of .Find)

[tt] rs.Filter = "ID=" & Me("combolID").Value
set me.recordset=rs[/tt]

I think most of this is covered in the replies above.

Roy-Vidar

 

[SwingXH]

I created a form and several text fields to hold records to test ADO codes.
The subs I used are listed below and it worked fine. However, when I added these two lines before rs.close

rs.MoveNext
Call LoadControls(rs)


when it calls Sub LoadControls(rs), an error showed up
"The value you entered isn't valid for this field".
Don't know why.
SwingXH

========================================================
Sub cmdAssignADOtoForm_Click()
Dim rs As New ADODB.Recordset
Dim cnthisconnect As ADODB.Connection
Set cnthisconnect = CurrentProject.Connection
rs.Open "Employees", cnthisconnect, adOpenKeyset, adLockOptimistic, adCmdTable
rs.Filter = "ID=" & Me("combolID").Value
Call LoadControls(rs)
rs.Close
Set rs = Nothing
Set cnthisconnect = Nothing
End Sub

Private Sub LoadControls(rs)
Me.Text0.Value = rs.Fields("ID")
Me.Text2.Value = rs.Fields("FirstName")
Me.Text4.Value = rs.Fields("LastName")
Me.Text6.Value = rs.Fields("Title")

End Sub
===========================================================

 

[PHV]

Have tried to replace this:
Private Sub LoadControls(rs)
By this:
Private Sub LoadControls(rs As ADODB.Recordset)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SwingXH]

Sorry, it did not work.
SwingXH

 

[cmmrfrds]

Should always check for an empty recordset before acting on the recordset.

rs.Filter = "ID=" & Me("combolID").Value
''- the filter may not have found any records.
If rs.EOF then
msgbox "no records"
''- do not call the loadcontrols.
End if
Call LoadControls(rs)

To reset the filter
rs.Filter = adFilterNone
''-- please verify the above constant name.

 

[SwingXH]

Thank you cmmrfrds. That'a good idea.
It worked if I reset the filter, but rs.movenext always pointed to the second record. If I don't reset the filter, same error as before will occur as "The value you entered isn't valid for this field". Maybe it's not a good idea to put rs.movenext to this sub, for now I just want to test these codes and see how ADO works.

Thanks,
SwingXH

 

[cmmrfrds]

Sequence should be.

1st time set rs.filter to the value.
next check to see if EOF.
next if not EOF then call the sub until EOF with next rs.
next reset filter.
Loop back.
set rs.filter to the new value
repeat until there are no more values.