This function card inserts a row right into a SQL database and requires to come back the identity number produced:
Function WriteDatabase(backupTypeID, numImages, folderSize, success, errorMessage, strLogFileName) On Error Resume Next err.clear Set objConnection = CreateObject("ADODB.Connection") Set objRecordSet = CreateObject("ADODB.Recordset") objConnection.Open "Provider=SQLOLEDB;Data Source=x.x.x.x;Initial Catalog=DB;User ID=sa;Password=xxxxxx" sqlquery = "INSERT INTO tblImageCopies (BackupCopyDate, BackupCopyTypeID, NumImages, ImagesFolderSize, Success, ErrorMessage) VALUES (GETDATE(), " & backupTypeID & ", " & numImages & ", " & folderSize & ", " & success & ", " & errorMessage & "); SELECT scope_identity() AS ImageCopyID;" objRecordSet.Open sqlquery,objConnection objRecordSet.MoveFirst WriteDatabase = objRecordSet("ImageCopyID") objRecordSet.Close objConnection.Close If err.number <> 0 Then WriteLog "Error writing to the EHN database - " & err.number & " " & err.description, strLogFileName End If End Function
It effectively card inserts the row, however i obtain the error message '3704 Operation isn't permitted once the object is closed.' if this attempts to return the identity number within the record set. After I execute this sql query on the server, it really works. Anybody in a position to help?
I actually do exactly the same factor (much the same anyway). In my opinion that you will find two teams of results returning, one for that Place after which another for that Choose. Try calling objRecordSet.NextRecordset().
Use "SET NOCOUNT ON " at the outset of your query