Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Multiuser / Networking / October 2006

Tip: Looking for answers? Try searching our database.

Scripting Access

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Don - 05 Oct 2006 03:08 GMT
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?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.