Welcome to the SRP Forum! Please refer to the SRP Forum FAQ post if you have any questions regarding how the forum works.
SRP_COM leaves MS-Access lock file
Hi
In the process of moving from ODBC (XO_Instance etc) to SRP_Com for a connection to an MS-Access database (.mdb). All working fine, excepting after the final RELEASE we still have an MS-Access lock file (.ldb) which prevents other users from accessing that mdb.
The final RELEASE is successful (returns a 1).
Using the Microsoft Jet 4.0 OLE DB Provider.
Access permissions = Share Deny None
Any ideas?
Thanks
Nick
In the process of moving from ODBC (XO_Instance etc) to SRP_Com for a connection to an MS-Access database (.mdb). All working fine, excepting after the final RELEASE we still have an MS-Access lock file (.ldb) which prevents other users from accessing that mdb.
The final RELEASE is successful (returns a 1).
Using the Microsoft Jet 4.0 OLE DB Provider.
Access permissions = Share Deny None
Any ideas?
Thanks
Nick
Comments
Can you double-check this on your end? Note, if you call SRP_COM as a subroutine but the method returns an object, you won't have the ability to release this object because you did not assign the object to a local variable that you can pass into the RELEASE service.
SRP_Com is called as a subroutine, designed using your "Much ADO about nothing" sample code.
The variable objConnection returns a numeric string, like 7148112. This is used in the final RELEASE call.
We only use the connection to write new rows to the .mdb table.
Thus objRecordSet = SRP_Com(objConnection, 'CALL', 'Execute', QueryStatement) returns nothing in the variable objRecordSet, so nothing to be released.
Thanks for your help.
Comments in the first version indicate results of calls.
Not sure what SRP version - but it would have been a few years ago. Is there a way to tell?
Testing in OI9.4.4
************************************************************************************************
declare function srp_com, dcount
debug
connectionString = "Provider=Microsoft Jet 4.0 OLE DB Provider;"
connectionString := "Data Source=G:\IT\Website\fpdb_pme\fpdb1_update_dev.mdb;"
connectionString := "MS Access;PWD=foo!"
If SRP_Com(objConnection, 'CREATE', 'ADODB.Connection') then
**************************************************
* we now have a connection object in objConnection
**************************************************
retVal = SRP_Com(objConnection, 'CALL', 'Open', ConnectionString)
**************************************************
* db has now been opened, a lock file is created
**************************************************
if SRP_Com('', 'ERROR') then
errText = SRP_Com('', 'ERROR')
end else
* delete everything in table
script = "delete * from company_update"
objRecordSet = SRP_Com(objConnection, 'CALL', 'Execute', script)
****************************************
* NB: no object returned in objRecordSet
* BUT delete is successful
****************************************
If SRP_Com('', 'ERROR') then
errText = SRP_Com('', 'ERROR'):' * ':script
end
if len(objRecordSet) then
retVal = SRP_Com(objRecordSet, 'RELEASE')
end
* now write a row
script = "insert into company_update (company_id) values (1)"
objRecordSet = SRP_Com(objConnection, 'CALL', 'Execute', script)
****************************************
* NB: no object returned in objRecordSet
* BUT insert is successful
****************************************
If SRP_Com('', 'ERROR') then
errText = SRP_Com('', 'ERROR'):' * ':script
end
if len(objRecordSet) then
retVal = SRP_Com(objRecordSet, 'RELEASE')
end
* now read all rows
script = "select company_id from company_update"
objRecordSet = SRP_Com(objConnection, 'CALL', 'Execute', script)
****************************************
* NB: no object returned in objRecordSet
****************************************
If SRP_Com('', 'ERROR') then
errText = SRP_Com('', 'ERROR'):' * ':script
end
DataRows = SRP_Com(objRecordSet, 'CALL', 'GetString', 2, -1, @FM, @RM)
If DataRows[-1, 1] EQ @RM then DataRows[-1, 1] = ''
****************************************
* NB: returns nothing
****************************************
if len(objRecordSet) then
retVal = SRP_Com(objRecordSet, 'RELEASE')
end
retVal = SRP_Com(objConnection, 'RELEASE')
****************************************
* release the connection object
****************************************
end
end
RETURN 0
************************************************************************************************
************************************************************************************************
$insert Microsoft_Ado_Equates
declare function OleCreateInstance
debug
connectionString = "Provider=Microsoft Jet 4.0 OLE DB Provider;"
connectionString := "Data Source=G:\IT\Website\fpdb_pme\fpdb1_update_dev.mdb;"
connectionString := "MS Access;PWD=foo!"
conn=OleCreateInstance("ADODB.Connection")
OlePutProperty(conn, 'MODE', adModeShareDenyNone)
x=OleCallMethod(Conn, 'Open', ConnectionString)
If OleStatus() Then
Conn=''
retval=OleStatus()
return
End
sql= "delete * from company_update"
rsCreate=OleCallMethod(Conn, 'Execute', sql)
x=OleStatus()
If x Then
debug
rsCreate=''
End
x=oleCallMethod(rsCreate, 'Close')
sql = "insert into company_update (company_id) values (3)"
rsCreate=OleCallMethod(Conn, 'Execute', sql)
x=OleStatus()
If x Then
debug
rsCreate=''
End
x=oleCallMethod(rsCreate, 'Close')
x=oleCallMethod(conn, 'Close')
conn=''
return 0
************************************************************************************************
If we can have an advance update that would be terrific!
*oSaveAs = OleCallMethod(oDocHandle2, "SAVEAS2", pdfName, wdFormatPDF$) ;* original
oSaveAs = SRP_Com(oDocHandle2, "CALL", "SAVEAS2", pdfName, wdFormatPDF$) ;* new
*oPrintOut = OleCallMethod(oWordApp, "PRINTOUT",wdBackground$) ;* original
oPrintOut = SRP_Com(oWordApp, "CALL", "PRINTOUT", wdBackground$) ;* new
@NickStevenson - Please use these links:
SRP_Utilities_RDK_2.2.10.1.zip
SRP_Utilities_RDK_2.2.10.1_64.zip
Those looks correct to me. If you want to send me (or post here) the full code I'll be happy to test.
The only problem I am left with (in both OI9 and OI10) is the call to SAVEAS2:
wdFormatPDF$ = 17
***oSaveAs = OleCallMethod(oDocHandle2, "SAVEAS2", pdfName, wdFormatPDF$) ;* original
oSaveAs = SRP_Com(oDocHandle2, "CALL", "SAVEAS2", pdfName, wdFormatPDF$) ;* new
The original line works fine (with OleCallMethod), the SRP_COM call now fails with 80020005 which I believe is Type Mismatch. If I leave that last parameter out (wdFormatPDF$) is does the SAVEAS2 correctly, but obviously as a word doc file.
compile subroutine OLE_Word_Automation_test(nul)
Declare Function OleCreateInstance, OleGetProperty, OleStatus, Msg, get_property, set_property, utility
declare function lcase, send_event, srp_com
Declare Subroutine OlePutProperty, Delay, Yield, Set_Status
$insert msg_equates
$insert file_status_equates
equ crlf$ to char(13):char(10)
equ tab$ to char(9)
* init variables
MergeDocument = "g:\ips\documents\manual_payment.dot" ;* the Word template to be used in the merge operation
DataSource = "t:\manual_payment.txt" ;* the data to be merged, comma or tab delimtted
FileToSaveAs = "t:\mytestfile.doc" ;* file to save as
useSaveAsPDF = 1 ;* set to 1 for SAVEAS PDF / 0 for print to printer
useOIOLE = 0 ;* set to 0 to use SRP_COM
PrintAndClose = 1
PrintToPDF = 1
debug
* make sure the FileToSaveAs file does not exist
if len(FileToSaveAs) and FileToSaveAs # MergeDocument then
osDelete FileToSaveAs
FileToSaveAsPDF = FileToSaveAs
swap '.docx' with '.pdf' in FileToSaveAsPDF
swap '.doc' with '.pdf' in FileToSaveAsPDF
osDelete FileToSaveAsPDF
end
* make sure the DataSource file exists
fileinfo = Dir(DataSource)
fileSize = fileInfo<1>
if fileSize > 0 else
oswrite 'dummy':char(9):char(9):char(13):char(10):'.':char(9):char(9) on DataSource
end
* do merge
gosub ole_word_merge
* end
return
*--------------------------------------------------------------------------------
ole_word_merge:
* create Word instance
if useOIOLE then
oWordApp = OleCreateInstance("Word.Application")
end else
retVal = SRP_Com(oWordApp, 'CREATE', "Word.Application")
end
gosub CheckError
if OleStatus else
if useOIOLE then
oNextHandle = OleGetProperty(oWordApp, "DOCUMENTS")
end else
oNextHandle = SRP_Com(oWordApp, "GET", "DOCUMENTS")
end
gosub CheckError
end
* make Word visible or not
if OLEStatus else
*wdVisible$ = -1
wdVisible$ = 1 ;* 0 for TESTING NS **********
if useOIOLE then
OlePutProperty(oWordApp, "VISIBLE", wdVisible$)
end else
Result = SRP_Com(oWordApp, "SET", "VISIBLE", wdVisible$)
end
gosub CheckError
end
* open the Word merge document
if OleStatus else
* use "ADD" for .dot, or "OPEN" for .doc
if useOIOLE then
oDocHandle = OleCallMethod(oNextHandle, "ADD", MergeDocument)
end else
oDocHandle = SRP_Com(oNextHandle, "CALL", "ADD", MergeDocument)
end
gosub CheckError
end
* execute mailmerge method
if OLEStatus else
if useOIOLE then
oMMerge = OleGetProperty(oDocHandle,"MAILMERGE")
end else
oMMerge = SRP_Com(oDocHandle, "GET", "MAILMERGE")
end
gosub CheckError
end
* open the datasource file - not sure why we do this
if OLEStatus else
wdOpenFormatText$ = 4 ;* not used
if useOIOLE then
oDataSource = OleCallMethod(oMMerge, "OPENDATASOURCE", DataSource)
end else
oDataSource = SRP_Com(oMMerge, 'CALL', "OPENDATASOURCE", DataSource)
end
gosub CheckError
end
* set mailmerge destination file
if OLEStatus else
wdSendToNewDocument$ = 0
if useOIOLE then
OLEPutProperty(oMMerge, "DESTINATION", wdSendToNewDocument$)
end else
Result = SRP_Com(oMMerge, "SET", "DESTINATION", wdSendToNewDocument$)
end
gosub CheckError
end
* execute the mail merge
if OLEStatus else
if useOIOLE then
oMergeExecute = OleCallMethod(oMMerge, "EXECUTE")
end else
oMergeExecute = SRP_Com(oMMerge, 'CALL', "EXECUTE")
end
gosub CheckError
end
if OLEStatus else
wdSuppressBlankLines$ = -1
if useOIOLE then
OlePutProperty(oMMerge, 'SUPPRESSBLANKLINES', wdSuppressBlankLines$)
end else
Result = SRP_Com(oMMerge, "SET", 'SUPPRESSBLANKLINES', wdSuppressBlankLines$)
end
gosub CheckError
end
* close the original Word document
if OLEStatus else
wdSaveChanges$ = 0
if useOIOLE then
oCloseOrig = OleCallMethod(oDocHandle, "CLOSE", wdSaveChanges$)
end else
oCloseOrig = SRP_Com(oDocHandle, 'CALL', "CLOSE", wdSaveChanges$)
end
gosub CheckError
end
* set the new merged document as the active document
if OLEStatus else
if useOIOLE then
oDocHandle2 = OleGetProperty(oWordApp, "ACTIVEDOCUMENT")
end else
oDocHandle2 = SRP_Com(oWordApp, "GET", "ACTIVEDOCUMENT")
end
gosub CheckError
if OLEStatus else
* get statistics of current document to ensure document is now activated and ready
* using line count to count the number of lines
* tries for a maximum of 5 seconds
statisticOK = 0
loopCount = 0
loop
wdStatistic$ = 1 ;* 2=Pages, 1=Lines
if useOIOLE then
oStatistics = OleCallMethod(oDocHandle2, "COMPUTESTATISTICS",wdStatistic$)
end else
oStatistics = SRP_Com(oDocHandle2, 'CALL', "COMPUTESTATISTICS",wdStatistic$)
end
if num(oStatistics) and oStatistics > 0 then
statisticOK = 1
end else
yield()
delay(1)
loopCount += 1
if loopCount > 5 then
statisticOK = 1
end
end
until statisticOK or loopCount > 10 repeat
end
end
* save the merged document to the specified folder and make sure it is saved properly
if OLEStatus else
if Len(FileToSaveAs) and (printToPdf # 1 or useSaveAsPDF # 1) Then
if useOIOLE then
oSaveAs = OleCallMethod(oDocHandle2, "SAVEAS", FileToSaveAs)
end else
oSaveAs = SRP_Com(oDocHandle2, 'CALL', "SAVEAS", FileToSaveAs)
end
gosub CheckError
end
end
* print the document
if OLEStatus else
if PrintToPDF and len(FileToSaveAs) and useSaveAsPDF then
gosub save_as_pdf
end Else
gosub print_the_document
end
gosub CheckError
end
* close the mailmerge Word document
if OLEStatus else
if PrintAndClose then
wdSaveChanges$ = 0
if useOIOLE then
oCloseNew = OleCallMethod(oDocHandle2, "CLOSE", wdSaveChanges$)
end else
oCloseNew = SRP_Com(oDocHandle2, 'CALL', "CLOSE", wdSaveChanges$)
end
gosub CheckError
end
end
* always try to quit even with previous error so that Word isn't holding the document locked
if PrintAndClose or OLEstatus then
wdSaveChanges$ = 0 ;* -2
if useOIOLE else
* SRP ONLY: clear idispatch variables in the REVERSE order they were created
retVal = SRP_Com(oCloseNew, 'RELEASE')
retVal = SRP_Com(oPrintOut, 'RELEASE')
retVal = SRP_Com(oSaveAs, 'RELEASE')
retVal = SRP_Com(oStatistics, 'RELEASE')
retVal = SRP_Com(oDocHandle2, 'RELEASE')
retVal = SRP_Com(oCloseOrig, 'RELEASE')
retVal = SRP_Com(oMergeExecute, 'RELEASE')
retVal = SRP_Com(oDataSource, 'RELEASE')
retVal = SRP_Com(oMMerge, 'RELEASE')
retVal = SRP_Com(oDocHandle, 'RELEASE')
retVal = SRP_Com(oNextHandle, 'RELEASE')
end
* clear connections (for iDespatch)
oCloseNew = ""
oPrintOut = ""
oSaveAs = ""
oStatistics = ""
oDocHandle2 = ""
oCloseOrig = ""
oMergeExecute = ""
oDataSource = ""
oMMerge = ""
oDocHandle = ""
oNextHandle = ""
* quit word application
if useOIOLE then
Quit = OleCallMethod(oWordApp, "QUIT", wdSaveChanges$)
end else
Quit = SRP_Com(oWordApp, 'CALL', "QUIT", wdSaveChanges$)
end
* clear final idispatch variable
if useOIOLE else
retVal = SRP_Com(Quit, 'RELEASE')
retVal = SRP_Com(oWordApp, 'RELEASE')
end
Quit = ""
oWordApp = ""
gosub CheckError
end
Return
*-------------------------------------------------------------------------------
CheckError:
* get the error
OleStatus = ''
if useOIOLE then
OleStatus = OleStatus()
end else
if SRP_Com('', 'ERROR') then
OleStatus = SRP_Com('', 'ERROR')
end
end
* see if the error is in the errors file
if OleStatus then
wdVisible$ = -1
if useOIOLE then
OlePutProperty(oWordApp, "VISIBLE", wdVisible$)
end else
Result = SRP_Com(oWordApp, "SET", "VISIBLE", wdVisible$)
end
msgTxt = 'OLE ERROR: ':OleStatus
gosub disp_msg
end
Return
*-------------------------------------------------------------------------------
disp_msg:
msg_def = ""
msg_def<3> = 'S'
msg_def = "BO"
msg_def = 'H'
msg_def = -2
msg_def = MsgTxt
msgRetVal = MSG(@WINDOW,msg_def)
Return
*-------------------------------------------------------------------------------
print_the_document:
* prints the document to the selected device
wdBackground$ = 0
if useOIOLE then
oPrintOut = OleCallMethod(oWordApp, "PRINTOUT",wdBackground$)
end else
*https://learn.microsoft.com/en-us/office/vba/api/word.application.printout
* seems to be a problem with wdBackground$ parm, so dropped it
*oPrintOut = SRP_Com(oWordApp, "CALL", "PRINTOUT", wdBackground$)
oPrintOut = SRP_Com(oDocHandle2, "CALL", "PRINTOUT")
end
gosub CheckError
return
*-------------------------------------------------------------------------------
save_as_pdf:
* uses Word to SAVEAS PDF
pdfName = lcase(FileToSaveAs)
swap '.rtf' with '.pdf' in pdfName
swap '.docx' with '.pdf' in pdfName
swap '.doc' with '.pdf' in pdfName
wdFormatPDF$ = 17
*https://learn.microsoft.com/en-us/office/vba/api/word.saveas2
if useOIOLE then
oSaveAs = OleCallMethod(oDocHandle2, "SAVEAS2", pdfName, wdFormatPDF$)
end else
oSaveAs = SRP_Com(oDocHandle2, "CALL", "SAVEAS2", pdfName, wdFormatPDF$)
end
gosub CheckError
return
*-------------------------------------------------------------------------------