Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Open and run a macro in access runtime from excel

Posted on 2004-08-27
12
Medium Priority
?
263 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 1200 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

670 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