Output rows updated to file.

[JazzSP8]

Hi All

I'd like to output the number of rows affected by some queries in a VBA script, so it would run the query and then number that pops up in the message box would be output to a file.

I've tried searching but most people want the results outputting

Ideally I'd like this in an Excel file as there is already a template set up to record the numbers and I could just tell it where to put the data, however I could work with a CSV if the Excel thing is a pipe dream?

Can anyone suggest a way this can be done?

Thanks in advance

 

[JazzSP8]

[Gentle Bump]

 

[Trevor G]

I don't understand your requirements. Do you want a query to transfer to a dedicated Excel workbook and to a specific worksheet and range? Including a message box, why the message box? What is the sort of criteria you are using?

Sorry but not enough clear information to help you find a solution.

 

[JazzSP8]

Hi Trevor

Thanks for the reply, apologies for not being clear.

I've got a script that runs several SQL queries one after each other, after each query has been run Access naturally gives you a message box asking if you want to append / paste data and tells you how many rows are going to be affected.

I want to be able to record that number in a spreadsheet, and yes, ideally in a specific place, so, the first query would go to Sheet 1 Cell G2 for example.

(If this can't be done then a text / csv file will work well, I can build something in Excel no problem.)

Hope that makes things clearer?

 

[Trevor G]

The message you get is a warning message so it doesn't translate into something you can use to record the amount of records. You would need to use some code like this, you have to set the references to use Both Excel and ADO. So in the VBA screen select the Tools Menu and then References then search down the list for Microsoft Excel and click the box and the same for Microsoft ADO. Place this in a module sheet, I tested this to do the count for a table, but if you add the command to open or run the query and adjust he field names and query name in the code it will work.

Function CountRecords()
'You need to set the reference to use Excel.
'Select the Tools Menu and then References
'Then search down the list for Microsoft Excel
Dim ws As Excel.Application
Set ws = CreateObject("Excel.Application")

On Error GoTo Catch
Dim strSql As String
Dim objRst As ADODB.Recordset
'Count all records in a query
strSql = "select FieldName from QueryName"
Set objRst = New ADODB.Recordset
'' Client-side cursor
objRst.CursorLocation = adUseClient
objRst.Open strSql, CurrentProject.Connection
'' Returns the actual count of records
'This will list the number also in the Debug Window
Debug.Print "Client-side objRst record count: " & objRst.RecordCount
'' Clean up
'This now activates Excel
With ws
.Visible = True 'Make it Visible
.Workbooks.Add 'Add a workbook this can be changed to Open
.Sheets("Sheet2").Select 'Which sheet
.Range("g3") = objRst.RecordCount 'Which cell
End With
objRst.Close
Set objRst = Nothing
Exit Function
Catch:
MsgBox "cmdRecordCount_Click()" & vbCrLf & vbCrLf _
& "Error#: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

 

[JazzSP8]

Thanks Trevor!

I'll give that a go this afternoon and let you know if I come across any problems!

Thanks again for your time, much appreciated