ADO Help. Using Query in recordset source? 2

[AppStaff]

This line produces error 3709: The connection cannot be used to perform this operation. It is either closed or invalid in this context. I suspect it has to do with the strQ variable that stores the name of a query. however, the name produced from the debug window is a valid query object. The query object is a selection based on another query and uses distinct rows. Not sure if this matters.

I'm not very versed in ADO and kinda lost. Anyone see what I'm doing wrong? Thanks for any responses.

"rstTL.Open strQ, cnn1, adCmdTable, adLockReadOnly"
Code
-----------------------------------------------------
Private Sub btnDefects_Click()
Dim rstDIS As ADODB.Recordset
Dim rstTL As ADODB.Recordset
Dim cnn1 As ADODB.Connection
Dim strQ As String
Dim strTL As String
Dim strPath As String

strPath = "C:\Terry\Defects\Defects.mdb"
Set cnn1 = New ADODB.Connection
With cnn1
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source").Value = strPath
'.Open
End With


' Define Selection.
strQ = "qry_SEL_DCEXP_N"

Set rstTL = New ADODB.Recordset
Set rstDIS = New ADODB.Recordset

rstTL.Open strQ, cnn1, adCmdTable, adLockReadOnly
rstDIS.Open "tblDiscrepancies", cnn1, adCmdTable, adLockBatchOptimistic

'write records to Discrepancies table
Do While Not rstTL.EOF

'set variable for team_lead
strTL = rstTL!Team_Lead
rstDIS.AddNew
rstDIS!DefectNumber = DMax("DefectNumber", "tblDiscrepancies") + 1
rstDIS!Originator = Me.cmbOrig
rstDIS!Admin = Me.cmbAdmin
rstDIS!Owner = strTL
rstDIS!SubmitDate = Now()
rstDIS!ActionType = "Defect"
rstDIS!Analysis = "Current Express Phase Missing from these open projects."
rstDIS!Request = "TL to update RTM."
rstDIS.Update

'Write records to Discrepancy Detail
'Write Records to CR Detail

rstTL.MoveNext
Loop
Set rstTL = Nothing
Set rstDIS = Nothing

rstTL.Close
rstDIS.Close

 

[SkipVought]

Hi,

You never OPEN a connection -- it's commented out...

        With cnn1
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Data Source").Value = strPath
        [b][red]'.Open[/red][/b]
        End With

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[VBslammer]

You haven't given us any details as to what your doing, but if you are already working in the database you can use:

[tt]Set cnn1 = CurrentProject.Connection[/tt]

Of course, if you are working in that database you could make a reference to the "Microsoft DAO 3.x Object Library" and just use DAO - much easier. But I suppose you're using ADO because the database is external to your app, so disregard this if that's the case...

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[AppStaff]

Thanks Skip. I noticed that comment after I posted this

Thanks slammer. I am actually working in the same database so that's very useful info to me. My impression was that DAO is going away so I'm trying to use ADO whenever I need to use recordsets so that I learn it. I do find it much harder to use than DAO though.

Does ADO only make sense when working with external data? Is there some steadfast way of deciding which way to go?

 

[AppStaff]

Bleh.. couldn't get the active connection string to work. Redoing it in DAO as slammer suggested.

 

[cmmrfrds]

Here is an example of opening a connection to another mdb file.

Dim cn As New ADODB.Connection, sqlString As String
Dim rs As New ADODB.Recordset, connString As String

connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\bigtuna\Databases\Bank.mdb;" & _
"Persist Security Info=False"

cn.ConnectionString = connString
cn.Open connString

Here is a typical open statement with an updateable recordset.
Dim sql1 as String
sql1 = "select * from sometable"
rs.Open sql1, cn, adOpenStatic, adLockOptimistic

The problem with your statement is that you are telling ADO that it should be using a table NOT the sql string that you are providing. The default is a string i.e. select statement, that is why it is not included on the example above.