I have the following script:
dim accessApp
set accessApp = createObject("Access.Application")
accessApp.OpenCurrentDataBase("H:\My Documents\Access\Medical\Medical
Backend\Medical Appointmentsbe.mdb")
accessApp.Run "UpdateMeds"
accessApp.Quit
set accessApp = nothing
This script seems to run completely but I get an error:
Line 4
Char 1
Error Unknown runtime error
Code: 800A9C68
Source: Microsoft VBScript runtime error
The code this script runs is an Access App as follows:
Function UpdateMeds()
' Dim tells the database about a variable such as dbs, wrk and
' strSQL and the type of data the variable it will store.
Dim dbs As DAO.Database
Dim wrk As DAO.Workspace
Dim strSQL As String, strQuery As String, strMessage As String
' strSQL represents the data that follows the = sign each time strSQL
' is included in this code.
strSQL = "UPDATE tblTimerDate SET LastTimerDate = " & _
Format(Now, "\#mm/dd/yyyy\ hh:nn:ss\#")
On Error GoTo Err_Handler
Set dbs = CurrentDb
Set wrk = DAO.DBEngine.Workspaces(0)
' begin transaction
wrk.BeginTrans
'Rename table BUSUpdateTbl to BUSUpdateTblBak
dbs.TableDefs("BUSUpdateTbl").Name = "BUSUpdateTbl_bak"
'Rename table BUSUpdateTbl to BUSUpdateTblBak
dbs.TableDefs("PillLine").Name = "PillLine_bak"
' Appends Rx table to RX1 table "Adds new RX's to RX1 table"
strQuery = "1Append Rx to RX1 Query"
dbs.Execute strQuery ', dbFailOnError
' Append Patient table to Patients table "Adds new records"
strQuery = "2Append Patient to PatientsQuery"
dbs.Execute strQuery ', dbFailOnError
' Deletes records from Patients that are "GONE" over 1 year
strQuery = "3Delete >1 years From Patients qry"
dbs.Execute strQuery ', dbFailOnError
' Deletes records from RX1 that are "GONE" over 1 year
strQuery = "4RX1 Delete >1 years Query"
dbs.Execute strQuery ', dbFailOnError
'Makes Table BusUpdateTbl for updating housing in Patients table
strQuery = "5MakeBUSUpdateTblqry"
dbs.Execute strQuery ', dbFailOnError
'Updates/Changes Housing to GONE for IM GONE Patients table
strQuery = "6UpdateGoneImqry"
dbs.Execute strQuery ', dbFailOnError
'Updates housing and DOB in Patients table
strQuery = "7UpdateBusHousingQry"
dbs.Execute strQuery ', dbFailOnError
' Makes PillLine Table
strQuery = "8MakePillLineTable"
dbs.Execute strQuery ', dbFailOnError
' Updates PillLineTable Housing to GONE if no match in BUSQry
strQuery = "9BUSUpdateTblWithoutMatchingPillLine"
dbs.Execute strQuery ', dbFailOnError
' Update tblTimerDate table
strQuery = "embedded SQL to update tblTimerDate"
dbs.Execute strSQL ', dbFailOnError
' no error so commit transaction
wrk.CommitTrans
' Select BUSUpdateTbl
DoCmd.SelectObject acTable, "BUSUpdateTbl_Bak", True
' Delete BUSUpdateTbl
DoCmd.DeleteObject , ""
' Select PillLine table
DoCmd.SelectObject acTable, "PillLine_Bak", True
' Delete BUSUpdateTbl
DoCmd.DeleteObject , ""
DoCmd.Quit
Exit_Here:
Set dbs = Nothing
Exit Function
Err_Handler:
' strMessage = Error & vbNewLine & vbNewLine & _
' "(Error in " & strQuery & ")" & _
' vbNewLine & vbNewLine & "Transaction rolled back and no tables
updated."
' MsgBox strMessage, vbExclamation, "Error"
' roll back transaction
wrk.Rollback
'Rename table BUSUpdateTbl to BUSUpdateTblBak
dbs.TableDefs("BUSUpdateTbl_bak").Name = "BUSUpdateTbl"
'Rename table BUSUpdateTbl to BUSUpdateTblBak
dbs.TableDefs("PillLine_bak").Name = "PillLine"
Resume Exit_Here
End Function
Any idea why I get this error?

Signature
Thanks,
Dennis
Van T. Dinh - 07 Oct 2006 03:49 GMT
1. Have you tested that the function works correctly if you open the
database in Access?
2. Is the function global / public?
3. From what I read, sample codes don't seem to use parens in
OpenCurrentDatabase in this case.
Try:
accessApp.OpenCurrentDataBase _
"H:\My Documents\Access\Medical\Medical Backend\Medical Appointmentsbe.mdb"
accessApp.Run "UpdateMeds"
accessApp.DoCmd.Quit

Signature
HTH
Van T. Dinh
MVP (Access)
>I have the following script:
>
[quoted text clipped - 115 lines]
>
> Any idea why I get this error?
Don - 08 Oct 2006 17:13 GMT
Van,
This script gives the error:
Line: 1, Char: 1, Error: Object required;'accessApp', Code:800A01A8, Source:
Microsoft VBScript runtime error.
>1. Have you tested that the function works correctly if you open the
database in Access?<
Yes I run this code via timer form daily.
>2. Is the function global / public?<
I am not sure. I think public. Code is General Declarations.
Any other ideas?

Signature
Thanks,
Dennis
> 1. Have you tested that the function works correctly if you open the
> database in Access?
[quoted text clipped - 130 lines]
> >
> > Any idea why I get this error?
Douglas J. Steele - 08 Oct 2006 17:36 GMT
The error implies that you removed the two lines of code
dim accessApp
set accessApp = createObject("Access.Application")
from your script. All Van was trying to show was that you didn't want the
parentheses around the path to the database, and that you needed to use
DoCmd.Quit, not just Quit.

Signature
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
> Van,
>
[quoted text clipped - 150 lines]
>> >
>> > Any idea why I get this error?