ADO Open command fails on multiple SQL functions
 

Hello All:

About a dozen users are independently updating separate Access tables
in the same db. The following Excel front end code is designed to
return the list of tables that are then passed to a SQL command that
runs three functions: Count, Sum and Max to determine the row counts,
when the table was updated, etc.

The code works fine with only one function, but it crashes at the
recordset open command when two or more functions are called resulting
in a "...Run-time error...(80040e14) is not a valid name. Make sure
that is does not include invalid characters or punctuation and that it
is not too long".

Thanks for any help.

'// ADO to retrieve table names
Sub ListTables()

'//Code to retrieve table names and post to a Excel worksheet...

Call seeupdate

End Sub

Sub seeupdate()
Dim dblocation As String
Dim queryname As String
Dim TableName As String
Dim i As Integer

Sheet2.Select
lastcell = Sheet2.Range("a65536").End(xlUp).Row

For i = 2 To lastcell
dblocation = "H:\Test\Stages.mdb"
TableName = Sheet2.Range("A" & i).Value

'//***** Code works fine when the query uses only one function
*****
'queryname = "SELECT MAX(A.TIME) AS SumOfVolume FROM " & TableName
& " A"
queryname = "SELECT Sum(A.Volume), Count(A.Volume), Max(A.Time)
FROM " & TableName & " A"

Call runaccessquery(dblocation, queryname, "Sheet2", i)
Next i
End Sub

Sub runaccessquery(dblocation As String, queryname As String,
outputsheet As String, rtndata As Variant)

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
Dim errorarray() As Variant

'// Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
dblocation & ";"

'//***** Here's where the code breaks when the SQL contains more
than one function ******
'// Open the recordset
rst.Open "[" & queryname & "]", _
cnn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

'// Display the records
x = rtndata
Do Until rst.EOF
y = 2
For Each fld In rst.Fields
Sheets(outputsheet).Cells(x, y).Value = fld.Value
y = y + 1
Next
Debug.Print
rst.MoveNext
x = x + 1
Loop

'// Close the recordset and connection
rst.Close
cnn.Close

End Sub

ADO Open command fails on multiple SQL functions
 

Hi

I've never har problems with that. And the only thing you seem to do different from what I usually do is
- square brackets around SQL statement,
- "adCmdStoredProc", and missing
Set rst = Nothing
Set cnn = nothing
at the very end.

Consider also a global connection that stays open during the repeating loop, instead of a new cnn each time. It's a little faster
and should also be more stable. It's impossible for me to test your code as is, so this is just guessing. And I may well be
overlooking something important.

Want a incredibly faster way to paste the results btw ?

Sheets(outputsheet).Cells(2, 1).CopyFromRecordset rst
rst.Close

No looping, simply that.

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"Zachary Bass" skrev i melding m...
Hello All:

About a dozen users are independently updating separate Access tables
in the same db. The following Excel front end code is designed to
return the list of tables that are then passed to a SQL command that
runs three functions: Count, Sum and Max to determine the row counts,
when the table was updated, etc.

The code works fine with only one function, but it crashes at the
recordset open command when two or more functions are called resulting
in a "...Run-time error...(80040e14) is not a valid name. Make sure
that is does not include invalid characters or punctuation and that it
is not too long".

Thanks for any help.

'// ADO to retrieve table names
Sub ListTables()

'//Code to retrieve table names and post to a Excel worksheet...

Call seeupdate

End Sub

Sub seeupdate()
Dim dblocation As String
Dim queryname As String
Dim TableName As String
Dim i As Integer

Sheet2.Select
lastcell = Sheet2.Range("a65536").End(xlUp).Row

For i = 2 To lastcell
dblocation = "H:\Test\Stages.mdb"
TableName = Sheet2.Range("A" & i).Value

'//***** Code works fine when the query uses only one function
*****
'queryname = "SELECT MAX(A.TIME) AS SumOfVolume FROM " & TableName
& " A"
queryname = "SELECT Sum(A.Volume), Count(A.Volume), Max(A.Time)
FROM " & TableName & " A"

Call runaccessquery(dblocation, queryname, "Sheet2", i)
Next i
End Sub

Sub runaccessquery(dblocation As String, queryname As String,
outputsheet As String, rtndata As Variant)

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
Dim errorarray() As Variant

'// Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
dblocation & ";"

'//***** Here's where the code breaks when the SQL contains more
than one function ******
'// Open the recordset
rst.Open "[" & queryname & "]", _
cnn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

'// Display the records
x = rtndata
Do Until rst.EOF
y = 2
For Each fld In rst.Fields
Sheets(outputsheet).Cells(x, y).Value = fld.Value
y = y + 1
Next
Debug.Print
rst.MoveNext
x = x + 1
Loop

'// Close the recordset and connection
rst.Close
cnn.Close

End Sub