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
---------------------------------------------------------

 

[CajunCenturion]

The only reference that you need is the Microsoft ActiveX Data Objects 2.? Library. I would remove the Recordset library reference.

Now, if you're getting the error at run-time, but not at compile time, then it's possible that the CreateObject function is failing.

I also, with respect to the code shown, have to ask what is the Server object? That may the object that is causing the problem.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[cmmrfrds]

The objects are being created dynamically like would be necessary in an ASP app. In Access they are normally declared at compile time an probably easier to catch the errors that way. Example.

Dim cn As New adodb.Connection
Dim rs As New adodb.Recordset
Dim connString As String
Dim sql1 As String
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\atestdir\adotestnew.mdb;" & _
"Persist Security Info=False"

cn.ConnectionString = connString
cn.Open connString

sql1 = "select * from yourtable"
rs.Open sql1, cn, adOpenStatic, adLockOptimistic

 

[SwingXH]

After your code, I used the following operation, but got an error: "Variable Not Defined" for Response. How to define it?
Thanks.

Do While Not rs.EOF R
Response.write Recordset("ID")
Response.write Recordset("Title")
Response.write "<br>"
Response.MoveNext
Loop

rs.Close
Set rs = Nothing
Connection.Close
Set Connection = Nothing

 

[CajunCenturion]

Are you sure that the Variable Not Defined is for Response and not for Recordset. I would try using rs instead of Recordset since rs is the name of the recordset.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[amorous]

Response.MoveNext is wrong...

it should be

rs.MoveNext


-VJ

 

[SwingXH]

Thank you. Once I changed Response to rs, the error became "Method or data member not defined" for "rs.write Recordset("ID").
What is wrong?
Thanks,

SwingXH

 

[amorous]

OK OK...

Let me give the whole code...

Do While Not rs.EOF
Response.write rs("ID")
Response.write rs("Title")
Response.write "<br>"
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Connection.Close
Set Connection = Nothing

-VJ

 

[SwingXH]

Sorry, I still got error "variable not defined", hightlighted on Response.
Here are the whole codes,

Private Sub Command8_Click()
Dim cn As New adodb.Connection
Dim rs As New adodb.Recordset
Dim connString As String
Dim sql1 As String
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\ADO.mdb;" & _
"Persist Security Info=False"

cn.ConnectionString = connString
cn.Open connString

sql1 = "select ID, Title from employees"
rs.Open sql1, cn, adOpenStatic, adLockOptimistic

Do While Not rs.EOF
Response.write rs("ID")
Response.write rs("Title")
Response.write "<br>"
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

 

[SwingXH]

Also If I want the query results show on a form/or a new table on a form, how to set up the connection?
Thanks,
SwingXH

 

[amorous]

The code looks ok to me...can please specify the line on which you are getting "Variable not defined" error

Also try to make this small change

Private Sub Command8_Click()
Dim cn As New adodb.Connection
Dim rs As New adodb.Recordset
Dim connString As String
Dim sql1 As String
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\ADO.mdb;" & _
"Persist Security Info=False"

cn.ConnectionString = connString
cn.Open connString

sql1 = "select ID, Title from employees"
rs.Open sql1, cn, adOpenStatic, adLockOptimistic

Do While Not rs.EOF
Response.Write rs.Fields("ID")
Response.Write rs.Fields("Title")
Response.write "<br>"
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub


And if you want to show the query results in a new form or a in a new table...you have to have the same kind of connection which you have on this page...

-VJ

 

[SwingXH]

Now "Response.Write rs.Fields("ID")" got hightlighted, say run time error: Object required.
Thanks a lot,
SwingXH

 

[SwingXH]

anf here is my table

ID FirstName Last Name Title
69 Jerry Barnes Shipping Manager
70 Scott Beckom Human Resource Director
71 Mac Grimes General Manager
72 Neal Grimes CEO
78 Stephen Grimes Sales

 

[amorous]

Let me stop here and ask you something...

Response.Write is used in a server side scripting...your code looks like a VB code and its client side...

How are you actually calling this sub?

-VJ

 

[amorous]

I am confused with your code...

But anyways give this a shot...

do until rs.EOF
for each x in rs.Fields
Response.Write(x.ID)
Response.Write(x.Title)
next
Response.Write("<br />")
rs.MoveNext
loop

-VJ

 

[SwingXH]

Yes, it's VBA in Access. I have a command button for this sub. I am trying to define the recordset for the table and take information from this table to display in another form or table based on the query. I know I am new to this field, please comment and help.
My final objective is to obtain infomation based on several queries from several tables and then form a new table/form.
Thank you very much.
SwingXH

 

[SwingXH]

what is x stands for?
Thanks,
SwingXH

 

[amorous]

I suggest you to post your question in VB Forum to get better replies...

Thanks

-VJ

 

[SwingXH]

Thanks a lot for your time anyway!
SwingXH

 

[cmmrfrds]

It seems things are getting confused since the first example looked like vb scripting associated with an ASP page. Since that is NOT the case and this is an Access program using vba then, this syntax is wrong.
Response.Write rs.Fields("ID")
Response.Write rs.Fields("Title")
Response.write "<br>"

Should be.
Do While Not rs.EOF
rs.Fields("ID")
rs.Fields("Title")
rs.MoveNext