Run Time Error ‘3001’

[Trudye]

HiVb'ers:

I am receiving Error Msg:
Run Time Error ‘3001’
Arguments are the wrong type, are out of acceptable range, or are in conflict with one another.

When I looked it up (in MS Knowledge Base) this is what I read. But because I am new to VB 6 I don’t know which one applies or how to fix it. I placed the code at the bottom.

SYMPTOMS
Setting a Recordset's ActiveConnection property to a recordset object causes either a Dr. Watson or a general protection fault (GPF) error to occur. For example:
Set rst.ActiveConnection = rst

STATUS
Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

This problem has been fixed in MDAC version 2.1 Service Pack 2.

Under MDAC versions 2.1 Service Pack 2 and later, you will receive the following trappable error as expected:
Run-time error '3001': Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
MORE INFORMATION
It is not recommended to set the ActiveConnection property to a recordset object. It is recommended that the Recordset's ActiveConnection property be set to an actual Connection object or connection string. The proper behavior is for Visual Basic to respond with a trappable error because ActiveX Data Objects (ADO) is expecting a connection string or object.


Since I don't know what the heck I'm doing this also seems plusible:
SYMPTOMS
When you try to specify a connection string to use the persist provider to open from a valid ADO stream object, you receive the following error message:

Run-time error '3001': Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

CAUSE
When a stream is used to open a recordset, there should be no parameters specified other than the source parameter of the open method.

RESOLUTION
Remove any parameter other than the source of the recordset as follows:
rs.Open stm ' no other parameters should be specified

Thanks again
Trudye

Public Function Open_the_DataBase()

On Error GoTo dbErrors

'-Create a new connection
Set adoConnection = New ADODB.Connection
'Create a new Recordset
Set adoRS1 = New ADODB.Recordset
Set adoRS2 = New ADODB.Recordset
Set adoRS3 = New ADODB.Recordset

'-Build our connection string to use when we open the connection
adoConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\VB Practice\zGDS_Data_V6_1_2k.mdb;" _
& "Mode=ReadWrite|Share Deny None;Persist Security Info=False"

adoConnection.Open
'Test DB Open
If adoConnection.State = adStateOpen Then
Else
MsgBox "Connection for zGDS_Data_V6_1_2k.mdb Failed " & connectString
End If

'Open individual Tables via Recordset keyword
adoRS1.Open "select * from Family_Eligibility", _
adoConnection, adOpenDynamic, adLockOptimistic, adCmdText
adoRS2.Open "select * from IncomeA", _
adoConnection, adOpenDynamic, adLockOptimistic, adCmdText
adoRS3.Open "select * from Tracking", _
adoConnection, adOpenDynamic, adLockOptimistic, adCmdText

Open_the_DataBase = True
gCurrentState = Now_Loading
Exit Function

dbErrors:
Open_the_DataBase = False
MsgBox (Err.Description)
End Function

NOW HERE COMES THE REAL QUESTIONABLE CODE

Public Sub LoadTab0()
Dim H_ID As Integer
Dim adorsFEE As ADODB.Recordset
Dim sqlFee As String 'Family_Elig - Fee - Tab0
Call ClearTab0
If gCurrentState = Now_Loading Then
adoRS1.MoveFirst
End If
‘gScccc_ID is a global variable
sqlFee = "SELECT * FROM adoRS1"
sqlFee = sqlFee & " Where adors1!scccc_id = “ & gScccc_ID

Set adorsFEE = adoConnection.OpenRecordset(sqlFee)

H_ID = adorsFee!scccc_id
txtFamily_size = adorsFee!family_size
txtTot_Fam_Income = adorsFee!tot_fam_Income
txtFull_Rate = adorsFee!full_rate
txtPart_rate = adorsFee!part_rate
txtPrivate_Fee = adorsFee!Private_fee
cboRate_Type = adorsFee!rate_type

End Sub


Thanks much
Trudye

 

[jebenson]

First, there is no OpenRecordset method of the ADODB.Connection object. I think you are confused by the way the methods of the ADODB.Connection object are displayed in the Help files. There are 2 listings for Open: Open (Connection) and Open (Recordset) - those are 2 different methods for each of the objects. When you execute the code

Set adorsFEE = adoConnection.OpenRecordset(sqlFee)

Vb is expecting there to be a recordset object on the right side of the equation. Instead it finds some text that it can't interpret correctly in the context, so it throws the error.

That said, I think you need to do this:

sqlFee = "SELECT * FROM Family_Eligibility" 'note same table as adoRS1
sqlFee = sqlFee & " Where scccc_id = " & gScccc_ID

Set adorsFEE = New ADODB.Recordset

adorsFEE.Open sqlFee, adoConnection, adOpenDynamic, adLockOptimistic, adCmdText


Let me know if this works. Good luck.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Trudye]

OOOOOkay I get it(I think)!

There are two points:
First I don't need to open it during initialization, I can wait until the tab form is called. But won't that slow down processing? Not that I care for this practice pgm but just FYI.

Second I was trying to treat it as an already opened recordset which ADODB does not support. I have to treat it as a NEW recordset whenever I access it.

Did I totally miss it or am I at least close to what you where trying to impart?

OBTW it worked perfectly. Now I can go by that wig to cover up the spots where I pulled out my hair (LOL).

Thanks so much
Trudye

 

[jebenson]

As to your first point...well, you could use the adoRS1 recordset youopen in the initialization and do a Find() on the recordset for the record you want. However, this will only work well if scccc_id is unique.. Otherwise, I really don't see any way around opening the recordset and selecting the record(s) you want when the tab form is called. Anybody else know a better way?

Second, its not that adorsFEE was not open when you tried to fill it with data from the adoRS1 recordset, its that you tried to treat the adoRS1 recordset as a table that the Connection object could query. It is my understanding that this cannot be done.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Trudye]

Thanks Jebenson for everything.

Trudye