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