Open and run a macro in access runtime from excel

Hi,
This is Microsoft XP and I am trying to use Excel to open a runtime access database and run a macro.  I found this code but I'm not sure where I put my access commands.

Sub OpenRunTimePat()
Dim objAccess As Object
  Dim accpath As String, dbpath As String
  On Error Resume Next
  dbpath = "s:\rcsxp\rcsxp.mdb"
  Set objAccess = GetObject(dbpath)
  If Err <> 0 Then
    If Dir(dbpath) = "" Then 'dbpath is not valid
      MsgBox "Couldn't find database."
      Exit Sub
    Else  'The full version of Microsoft Access is not installed.
'      accpath = "C:\Program Files\Common Files\Microsoft Shared" & _
'           "\Microsoft Access Runtime\MSAccess.exe"
      accpath = "Y:\Program Files\Microsoft Office" & _
           "\Office10\MSAccess.exe"
      If Dir(accpath) = "" Then
         MsgBox "Couldn't find Microsoft Access."
         Exit Sub
      Else
         Shell pathname:=accpath & " " & Chr(34) & dbpath & Chr(34), _
           windowstyle:=1
         Do 'Wait for shelled process to finish
           Err = 0
           Set objAccess = GetObject(dbpath)
         Loop While Err <> 0
      End If
    End If
  End If
Set objAccess = Nothing
End Sub

I am using "Y"   because we have Citrix.  The users have Excel and Word but no Access.  I need to Open S:\RCS\Rcs.mdb and run the macro "Import Pat"
Does anyone know if this is possible and how to do it??
Thanks
Lisa
LisaPriceAsked:
Who is Participating?
 
shanesuebsahakarnCommented:
It shouldn't do - in your code above, you have those lines commented out. Is that correct?

Do your Citrix users all have access to the full version of Access installed on the Y: drive?
0
 
shanesuebsahakarnCommented:
Hmm, don't recall if this works with the runtime, but use the /x command line parameter to execute a macro when opening Access.

Before the shell line, add:
pathname=pathname & " /x """Import Pat""""

I *think* that will work, but I don't use macros at all.
0
 
LisaPriceAuthor Commented:
This is how I wrote it and it errors out.
Shell pathname:=accpath  & Chr(34) & dbpath & Chr(34)& " /x """Import Pat"""", _
           windowstyle:=1
and the reason I am using a macro is because it runs a queries and transfers text to a table.  IF I was to use a function how could i write it?

Lisa
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
shanesuebsahakarnCommented:
There's are a couple of extra "s in there, my fault - try:
Shell pathname:=accpath  & Chr(34) & dbpath & Chr(34) & " /x ""Import Pat""", _
           windowstyle:=1

You can run queries using DoCmd.OpenQuery, and DoCmd.TransferText to import/export text - these are the exact equivalents of their macro actions. In fact, if you click on a macro and go Tools->Macro->Convert Macros to Visual Basic, Access can do it for you, but try the above first.
0
 
LisaPriceAuthor Commented:
I didn't get any errors but it didn't do what it's suppose to do.  I opened the database and no records were imported.  Its like it doesn't run the Import Pat macro at all.
Lisa
0
 
shanesuebsahakarnCommented:
Hmm, I'm not sure if the spaces have any effect. Can you rename Import Pat to say Import_Pat?
0
 
LisaPriceAuthor Commented:
I watched my task bar and it isn't even opening access.  Windowstyle:= 1 should show access.  The path I am using is
      accpath = "E:\Program Files\Microsoft Office" & _
           "\Office10\MSAccess.exe"

Thats where our network person said office is installed for citrix (I have a regular desktop and citrix) and i looked and its there.  The original path from the code I found was C:\ProgramFiles\CommonFiles\MicrosoftShared\
Microsoft Access Runtime\msaccess.ext
we don't have any folder Microsoft Access Runtime except in my Microsoft Access Developer which the users don't have.  I'm not sure if its the path or the shell line?
Lisa
0
 
shanesuebsahakarnCommented:
Hmm, IIRC Shell (at least the Access version) needs DOS pathnames, not Win long filenames. I could be wrong about that, but try changing your paths into DOS8.3 format.
0
 
LisaPriceAuthor Commented:
Huh?? I don't understand/
I'm not a programmer, just try because they tell me to and read a lot of help.
0
 
shanesuebsahakarnCommented:
Hmm, ok, well in DOS, filenames can be a maximum of 8 chars long. To convert a Windows long folder/filename to a DOS format filename, you take the first 6 (if it is longer than 8) letters and add a ~<number>. The number depends on how many others are have the same first six letters, but it's usually 1. So:

E:\Program Files\Microsoft Office\Office10\MSACCESS.EXE
becomes:
E:\PROGRA~1\MICROS~1\OFFICE10\MSACCESS.EXE

for example. You'll also need toconvert your dbpath string as well.
0
 
LisaPriceAuthor Commented:
That didn't work either.  It must be in the path.  I don't have any runtime folder in the common files folder anywhere , would that have anything to do with it.  
Lisa
0
 
LisaPriceAuthor Commented:
Finally, after much persuading, and since I can't get any of the above to work (I'm running out of time), I have managed to get the two users a full version of access so I can just use the acc.OpenCurrentdatabase command along with acc.docmd to run the queries and file transfer.  I do thank you for all your help and maybe if I had more time we could have gotten it to work.  Thanks
Lisa
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.