VB.Net Interop Error 0x800A0BDB -
i have vb.net program executes macros in .accdb access database. until last week, worked fine. macros execute fine when run directly in access 2 of 3 macros generating error when executed vb program:
error occurred: system.runtime.interopservices.comexception (0x800a0bdb): system resource exceeded. @ microsoft.office.interop.access.docmd.runmacro(object macroname, object repeatcount, object repeatexpression) @ niceprintmsaccessmacros.module1.executemacros()
here log output vb program:
7/31/2013 12:19:42 pm searching test 'run' file (\[server name removed]\apps\macros_to_run\macros_to_run_test.txt)... if found, application proceed in test mode. 7/31/2013 12:19:42 pm production 'run' file (\[server name removed]\apps\macros_to_run\macros_to_run_test.txt) found. proceeding in production mode. 7/31/2013 12:19:42 pm starting production file run. 7/31/2013 12:19:45 pm file: \[server name removed]\apps\my labels\labels\labels.accdb 7/31/2013 12:19:45 pm looking file... 7/31/2013 12:19:45 pm file found. opening database... 7/31/2013 12:19:47 pm database open. running macro(s)... 7/31/2013 12:19:47 pm running macro maccartitemlabels... 7/31/2013 12:20:13 pm error occurred: system.runtime.interopservices.comexception (0x800a0bdb): system resource exceeded. @ microsoft.office.interop.access.docmd.runmacro(object macroname, object repeatcount, object repeatexpression) @ niceprintmsaccessmacros.module1.executemacros() 7/31/2013 12:20:13 pm checking backup of today's database... 7/31/2013 12:20:13 pm backup database found (\[server name removed]\apps\macros_to_run\databasebackup\prod\7-31-2013_backup_labels.accdb) 7/31/2013 12:20:13 pm production mode exited. 7/31/2013 12:20:13 pm executing routine cleanup of logs 30 days or older... 7/31/2013 12:20:13 pm log cleanup completed.
here code module1.vb:
imports microsoft.office.interop module module1 dim macrosfile string dim macros2runfile string = "\\[server name removed]\apps\macros_to_run\macros_to_run_prod.txt" dim macros2runfile_test string = "\\[server name removed]\apps\macros_to_run\macros_to_run_test.txt" dim logfile string = "\\[server name removed]\apps\macros_to_run\logs\log_" + today.tostring.remove(today.tostring.indexof(" "), today.tostring.length - today.tostring.indexof(" ")).replace("/", "-") + ".log" dim dbbackupdirectory string = "\\[server name removed]\apps\macros_to_run\databasebackup\" dim backupdb string dim filedirectory string = "\\[server name removed]\apps\my labels\labels\" dim oaccess access.application dim file string sub main() try log("searching test 'run' file (" + macros2runfile_test + ")... if found, application proceed in test mode.") if my.computer.filesystem.fileexists(macros2runfile_test) log("test 'run' file (" + macros2runfile_test + ") found." + vbnewline + vbtab + vbtab + vbtab + "proceeding in test mode." + vbnewline + vbtab + vbtab + vbtab + "skipping search production 'run' file (" + macros2runfile + ").") file = "test_db_labels.accdb" log("starting test file run.") dbbackupdirectory = dbbackupdirectory + "test\" macrosfile = macros2runfile_test executemacros() createdbbackup() log("test mode exited.") cleanlogfiles() cleanbackupdatabases() elseif my.computer.filesystem.fileexists(macros2runfile) log("production 'run' file (" + macros2runfile_test + ") found." + vbnewline + vbtab + vbtab + vbtab + "proceeding in production mode.") file = "labels.accdb" log("starting production file run.") dbbackupdirectory = dbbackupdirectory + "prod\" macrosfile = macros2runfile executemacros() createdbbackup() log("production mode exited.") cleanlogfiles() cleanbackupdatabases() else log("the application exiting because cannot find either of following:" + vbnewline + "-" + macros2runfile_test + vbnewline + "-" + macros2runfile) end if catch ex exception log(ex.tostring + vbnewline) end end try end sub sub cleanlogfiles() try log("executing routine cleanup of logs 30 days or older...") dim file_date date dim log_string string each log_file in my.computer.filesystem.getfiles("\\[server name removed]\apps\macros_to_run\logs\") log_string = log_file.remove(log_file.length - 4, 4).remove(0, 40).replace("-", "/") file_date = convert.todatetime(log_string) if file_date.adddays(30) <= today my.computer.filesystem.deletefile(log_file) log(log_file + " has been deleted.") end if next log("log cleanup completed." + vbnewline + vbnewline) catch ex exception log(ex.tostring) end try end sub sub log(byval text string) try my.computer.filesystem.writealltext(logfile, now.tostring + vbtab + text + vbnewline, true) catch ex exception end try end sub sub executemacros() try log("file: " + filedirectory + file) oaccess = createobject("access.application") 'oaccess.visible = false log("looking file...") if my.computer.filesystem.fileexists(filedirectory + file) log("file found. opening database...") oaccess.opencurrentdatabase(filedirectory + file, false) log("database open. running macro(s)...") dim lines() string = system.io.file.readalllines(macrosfile) dim integer = 0 until > lines.count - 1 log("running macro " + lines(i).tostring + "...") oaccess.docmd.runmacro(lines(i).tostring) log("macro " + lines(i).tostring + " has completed.") += 1 loop log("all macros specified have been run." + vbnewline + vbtab + vbtab + vbtab + "saving file on exit...") oaccess.docmd().quit(microsoft.office.interop.access.acquitoption.acquitsaveall) log("exit complete.") else log("could not find file: " + filedirectory + file) try log("attempting exiting of microsoft access without saving...") oaccess.docmd().quit(microsoft.office.interop.access.acquitoption.acquitsavenone) log("exit complete.") catch ex exception log("error occurred: " + ex.tostring) end try end if log("releasing interopservices unmanaged com object...") system.runtime.interopservices.marshal.releasecomobject(oaccess) oaccess = nothing log("release complete.") 'my.computer.filesystem.copyfile(file, file.replace("labels.accdb", "labels_backup.accdb")) 'still work in progress catch ex exception log("error occurred: " + ex.tostring) end try end sub sub createdbbackup() try log("checking backup of today's database...") backupdb = today.tostring.remove(today.tostring.indexof(" "), today.tostring.length - today.tostring.indexof(" ")).replace("/", "-") + "_backup_" + file if my.computer.filesystem.fileexists(dbbackupdirectory + backupdb) log("backup database found (" + dbbackupdirectory + backupdb + ")") else log("an existing backup of today's database not found. creating backup...") my.computer.filesystem.copyfile(filedirectory + file, dbbackupdirectory + backupdb) log("backup has been created (" + dbbackupdirectory + backupdb + ")") end if catch ex exception log("error occurred: " + ex.tostring) end try end sub sub cleanbackupdatabases() try dim file_date date dim db_string string each db_file in my.computer.filesystem.getfiles(dbbackupdirectory) db_string = db_file.remove(0, 51).replace("-", "/") db_string = db_string.replace("_backup_" + file, "") file_date = convert.todatetime(db_string) if file_date.adddays(15) <= today my.computer.filesystem.deletefile(db_file) log(db_file + " has been deleted.") end if next catch ex exception log("error occurred: " + ex.tostring) end try end sub end module
any appreciated! also, have not had time research if can translated simple .vbs script changing code not require vs. ideas on how perform same task of executing access database macro in vbs script?
thanks
Comments
Post a Comment