ADO Compile Error

[tschilbach]

I am trying to populate my listbox from an access database and I seem to get this compile error whenever I go to preview it:

Compile error:

user-defined type not defined

Here is an example of the code I am using:


'Connecting to an Access Database using ADO
option Explicit
Dim cnn as ADODB.Connection
Dim rs as ADODB.Recordset

private Sub Form_Load()
set cnn = new ADODB.Connection
set rs = new ADODB.Recordset

' Open the database connection and recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;" & _
"Persist Security Info=false"
rs.Open "Select * from Customers", cnn, adOpenStatic, adLockOptimistic

' Place values in the comboBox control
Do While rs.EOF = false
Combo1.AddItem rs!CompanyName
rs.MoveNext
Loop
End Sub




Any ideas as to why I may be getting this error?


Timothy H. Schilbach
Alpha Omega Design Inc.
[email protected]

[Cakkie]

Check your refferences to see if there's a refference to ADO. The error is typically for missing refferences, and since you are only refferencing ADO, I don't think we need to look any further.
Go to the project>refferences menu, and see if 'Microsoft ActiveX Data Objects 2.X' is selected (X is the version, currently, 2.7 is the latest)

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Cimperiali]

may be it is the same, but I remember I read something about performances with different sintax when reading data. It may be better:

Combo1.AddItem rs("CompanyName")

instead of

Combo1.AddItem rs!CompanyName

Cesare


Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

[tschilbach]

I found the problem to be that I did not have teh ADO lib refrenced. When I did refrence the library, I got a new message:

Compile Error:

Wrong number of arguments or invalid poperty assignment

I useed both of these methods and this is where VB says the problem is:


' I am populating the combo box until the end of the database
Do While rs.EOF = false
Combo1.AddItem rs("CompanyName")
rs.MoveNext
Loop




Any ideas?


Timothy H. Schilbach
Alpha Omega Design Inc.
[email protected]

[Iouri]

Tom,
Is ADO2.7 is the latest version?
I thought that ADO2.6 SP1 is the latest. I just want to confirm if I missed the latest version.

Iouri Boutchkine
[email protected]

[sotoasty]

You might try the full reference to the field object like...


rs.fields("CompanyName").value

[tschilbach]

I only have the 2.6 version and I have SP5 loaded for Visual Studio. Its supposed to have the latest ADO in it.


Timothy H. Schilbach
Alpha Omega Design Inc.
[email protected]

[tschilbach]

No good so far, now I get a 3001 Runtime error that states the object is a wrongt type of in conflict of another object. This object seems to be the one its talking about:


rs.Open "select * from customers", con, adOpenStatic, adLockOptimistic




Here is my whole source code:

option Explicit
Dim con as ADODB.Connection
Dim rs as ADODB.Connection

private Sub Form_Load()

set con = new ADODB.Connection
set rs = new ADODB.Connection

' I am establishing a database connection
con.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;" & _
"Persist Security Info=false"

' I am setting my querry
rs.Open "select * from customers", con, adOpenStatic, adLockOptimistic

' I am populating the combo box until the end of the database
Do While rs.EOF = false
Combo1.AddItem rs.Fields("CompanyName").Value
rs.MoveNext
Loop

End Sub



Any suggestions as to what may be causing this?


Timothy H. Schilbach
Alpha Omega Design Inc.
[email protected]

[Cakkie]

Well, ADO 2.7 comes with .Net Beta 2 or was it Whistler, so it might not be available yet for the public. I haven't tested with it yet.

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Cimperiali]

Solution is:
Combo1.AddItem rs.Fields("Company Name") 'a space between Comany and Name: you have to match exact name of fields in DB!

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

[tschilbach]

I Tried separating the name like you said:

("Company Name") instead of ("CompanyName")

I am still getting the same errors. Have you tried executing the code I had cut and paste earlier? I am racking my brain as to what is happening. Its almost as if it doesnt recognize the methods for Combo1.Additem.

Any other ideas would be awesome.


Timothy H. Schilbach
Alpha Omega Design Inc.
[email protected]

[d.paulson]

If Company Name actually does have a space in it, you must enclose it within the square brackets.

Combo1.AddItem rs![Company Name]

David Paulson

[tschilbach]

Ok, looks like we are getting somewhere. I added the following code and I now get:

Compile Error:

Wrong number of arguments or invalid property assignment.


' I am populating the combo box until the end of the database
Do While rs.EOF = false
Combo1.AddItem rs!["CompanyName"]
rs.MoveNext
Loop



it seems to do this at the RS! part. Ok so lets try this another way:


' I am populating the combo box until the end of the database
Do While rs.EOF = false
Combo1.AddItem rs.Fields["CompanyName"].Value
rs.MoveNext
Loop




Now it gives me this error:

Compile error:
Expected end of statement

I get the same error with this code too:


' I am populating the combo box until the end of the database
Do While rs.EOF = false
Combo1.AddItem rs["CompanyName"]
rs.MoveNext
Loop




Any last suggestions?




Timothy H. Schilbach
Alpha Omega Design Inc.
[email protected]

[tschilbach]

Oh yeah, I meant to type Company Name with the spaces in them in the post I just put up. Same result.


Timothy H. Schilbach
Alpha Omega Design Inc.
[email protected]

[d.paulson]

When you use the bang operator '!' , you do not use the quotation marks.

Combo1.AddItem rs![Company Name]

David Paulson