Query failing

[Pete Davis]

I have the following code that I call shortly after calling a ADO
Connection.Execute.

Public Function GetMaxID(TableName As String, FieldName As String) As
Integer
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "Select Max(" + FieldName + ") from " + TableName
GetMaxID = CInt(rst.Fields(0))
rst.Close

End Function


The Connection.Execute works fine. I never close the connection, but I call
this function immediately after the connection.execute (which happens in
another function), and it fails on the Recordset.Open saying: "The
connection cannot be used to perform this operation. It is either closed or
invalid in this context."

Any ideas?

Pete

 

[Peter Hoyle]

Set conn = CurrentProject.Connection

Set rst = New ADODB.Recordset
rst.Open "Select Max(" + FieldName + ") from " + TableName

Doesn't - rst.Open - need to refer to the connection?

strSQL = "Select Max(" + FieldName + ") from " + TableName
rst.Open strSQL, conn

Cheers,
Peter

 

[Pete Davis]

Yes, that was the problem. I tried to post a reply several times after I
posted the original message that I had found the problem, but I wasn't
receiving the original message. Sorry for the bother

Pete