Solved

Open and run a macro in access runtime from excel

Posted on 2004-08-27
12
260 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:LisaPrice
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11915168
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
 

Author Comment

by:LisaPrice
ID: 11915552
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11915604
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:LisaPrice
ID: 11915810
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11915929
Hmm, I'm not sure if the spaces have any effect. Can you rename Import Pat to say Import_Pat?
0
 

Author Comment

by:LisaPrice
ID: 11916512
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11916615
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
 

Author Comment

by:LisaPrice
ID: 11916855
Huh?? I don't understand/
I'm not a programmer, just try because they tell me to and read a lot of help.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11917043
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
 

Author Comment

by:LisaPrice
ID: 11917512
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
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 400 total points
ID: 11917621
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
 

Author Comment

by:LisaPrice
ID: 11958419
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

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question