SQL Query Problem :(

[squirrelly1]

Ok, I have the following SQL Query in both Access and VB... In access, it pulls two records, just as it should, but in VB when I do a recordset.recordcount, it gives me "-1" and only opens one recordset into my datareport.

SELECT Company.Name, Company.Address, Company.City, 
Company.State, Company.Zip, Company.Telephone, [Units.Number], Units.Price, Units.CustomerNumber, 
Customers.Name, Customers.Address, Customers.City, 
Customers.State, Customers.Zip, Customers.Telephone, 
Customers.Balance, Customers.LastPayment FROM (Company INNER JOIN Units ON Company.Number = Units.Company) INNER 
JOIN Customers ON (Units.CustomerNumber = 
Customers.Number) AND (Company.Number = 
Customers.Company)

Like I said, It will work in Access, but vb doesn't want to do it...
oh, btw... I'm invoking it by DB.Execute

Thanx,
squirrelly1

[mendhak]

Working on Sunday?

Post all your code.

[brucevde]

The usual culprit. The cursor location and type do not support the Recordcount property. Try a client side cursor location and/or a static cursor.

[squirrelly1]

I don't have much choice. I'm supposed to get a beta of this program to my client tomorrow and I still have to get a few reports done... No hard stuff... well, it shouldn't be anyway.

how do i set the curser location and type when I'm using the .execute method of ADODB? A little code example would help. Thank you...

Squirrelly1

[brucevde]

You can't set the cursor type with the Execute method of the Connection object but you can set the location. By default this will result in an adOpenStatic cursor type, which is fine. On second thought it may depend on which provider you are using, so you better check or make life easier and use the Recordset.Open method.

Dim objCon as ADODB.Connection
Dim objRS as ADODB.Recordset
 
Set objCon = New ADODB.Connection
objCon.CursorLocation = adUseClient
objCon.Open "provider=..."
Set objRs = objCon.Execute(strSQL)
 
'or use the open method instead
Set objRs = New ADODB.Recordset
objRs.CursorLocation = adUseClient
 
objRs.Open strSQL, objCon, adOpenStatic, adLockReadOnly,adCmdText

[titan7262]

yeah! that would be the culprit made lots of error because of that one.