Solved

Open and run a macro in access runtime from excel

Posted on 2004-08-27
12
258 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

813 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now