VB6 Access2000 errors out on rs.Open PLEASE - URGENT

[RDC]

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim strConnection As String
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My2000HW.MDB;Persist Security Info=False"

Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.ConnectionString = strConnection
cn.Open

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
rs.ActiveConnection = cn

Const strSQL As String = _
"SELECT Salesmen.SalesFirst," _
& " Salesmen.SalesLast," _
& " Salesmen.SalesID," _
& " Customers.FirstName," _
& " Customers.LastName," _
& " Customers.Custno," _
& " Customers.SalesID" _
& " FROM Customers c INNER JOIN Salesmen s" _
& " ON c.SalesID = s.SalesID" _
& " WHERE c.State = 'CA'" _
& " ORDER BY c.SalesID"

rs.Source = strSQL
rs.Open
'Err.Number = -2147217904 = No value given for one or more required parameters.

'Never gets here
rs.MoveFirst
While Not rs.EOF
List1.AddItem rs![customers.SalesID]
rs.MoveNext
Wend

 

[harmmeijer]

The error must be in the sql string, are all the table and field names correctly spelled?

 

[harmmeijer]

I am not sure but I think this is wrong:
FROM Customers c INNER JOIN Salesmen s
should be:
FROM Customers as c INNER JOIN Salesmen as s

 

[RDC]

I know the SQL is good. I even put the SQL through the query analyzer and produced the exact results I was looking for. I have debug prints all the way through to the open. PLEAS SOMEONE..today is my deadline.. Thanks for trying. rdc

 

[harmmeijer]

Well you are right about the Customers as c you can allso use the Customers c.
But I get exactly the same error and error code when I deliberately misspell the field name like Salesmen.SalesLast.

Can you copy and paste the sqlstring ,then open access, make a new querry using this string(sql view) and open this query.
If one of the fields is misspelled access will ask some user input on opening the query.
Sorry I could not be of more help, I am going home now you should place this question here one more time.

 

[Fred]

The idea of the phrase (Customer c) or (Customer as c) is to create an alias for the table. Once you establish the alias it should be used as the prefix for each field name in the target list of your SQL statement.

Therefore you should reference each field of the Customer table with the c. prefix not the Customer. prefix.

Example c.FirstName, not Customer.FirstName

Hope this helps.

 

[AdaHacker]

Or you could just nix the 'c' and 's' aliases entirely, as they aren't strictly necessary. Just use the full table name, e.g. for the JOIN clause, just use

FROM Customers INNER JOIN Salesmen
ON Customers.SalesID = Salesmen.SalesID
WHERE Customers.State = 'CA'
ORDER BY Customers.SalesID

In this case, there doesn't seem to be any compelling need to use an alias, so you might do just as well to ignore it. Also, if SalesID is the only field common to both tables, you shouldn't even need the tablename.fieldname syntax, as just the fieldname should suffice.

I've had these errors too, and every time, it's been the SQL. Everything else looks good, so I don't know what else it could be.

Hope I could help!

 

[StewartGW]

Try it this way:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConnection As String
Dim strSQL as String

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\MY2000HW.mdb;"

Set cn = New ADODB.Connection
cn.Open strConnection

strSQL = "SELECT Salesmen.SalesFirst," _
& " Salesmen.SalesLast," _
& " Salesmen.SalesID," _
& " Customers.FirstName," _
& " Customers.LastName," _
& " Customers.Custno," _
& " Customers.SalesID"
strSQL = strSQL & " _
& " FROM Customers c INNER JOIN Salesmen s" _
& " ON c.SalesID = s.SalesID" _
& " WHERE c.State = 'CA'" _
& " ORDER BY c.SalesID"

'Set rs = cn.Execute(strSQL) OR

Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenForwardOnly

If Not rs.EOF Then
Do While Not rs.EOF
List1.AddItem [rs.customers.SalesID]
rs.MoveNext
Loop
End If

rs.Close
Set rs = Nothing
Set cn = Nothing

 

[RichieSimonetti]

Just a clue, try this:
..........
........
Set cn = New ADODB.Connection
cn.Open strConnection

if cn.state = adstateopen then
strSQL = "SELECT Salesmen.SalesFirst," _
& " Salesmen.SalesLast," _
& " Salesmen.SalesID," _
& " Customers.FirstName

.........
..........