Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.vb.database.ado,microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.vb.database.ado,microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 fails to open documents | New Users to Excel | |||
Workbook fails to open - Excel 2003 | Excel Discussion (Misc queries) | |||
Excel Template fails to open??? | Excel Discussion (Misc queries) | |||
Shortcut file fails to open | Excel Discussion (Misc queries) | |||
Autorefresh Fails on Open | Excel Discussion (Misc queries) |