WHAT IS THE BEST WAY TO CALL A ACCESS MODULE FROM VB?

Question

I need to revisit this question.  A VB2008 app uses an Access 2007 db on the back end.  Some of the tables are large and after hours of testing, it was determined that the BEST way to perform a task that needs to process tocuh every record is to call a module in the Access db to do the work.  The VB app CAN do the job via ADO but this is about 10x slower.

Keep in mind one VERY important fact - MS Access 2007 is not installed on the target systems HOWEVER Access 2007 Runtime SP3 is.

Method 1 - (currently employed HOWEVER, on some WinXP systems this will fail with a Cannot Create ActiveX error)

            Dim objAccess1 As Object

            If SystemType = 32 Then
                Shell("c:\Program Files\Microsoft Office\Office12\msaccess.exe " & Chr(34) & "C:\SCDBv4\Data\SCDBData.mdb" & Chr(34))
            Else
                Shell("c:\Program Files (x86)\Microsoft Office\Office12\msaccess.exe " & Chr(34) & "C:\SCDBv4\Data\SCDBData.mdb" & Chr(34))
            End If
            objAccess1 = GetObject("C:\SCDBv4\Data\SCDBData.mdb")

            objAccess1.Run("RebuildIndex")
            objAccess1.Quit(acExit)
            objAccess1 = Nothing

Method 2 - This uses Microsoft.Office.Interop. I am NOT sure where this object came from (resource wise). My guess is the MS Access 14.0 object library. The development system has Access 2010 installed.

Dim adb As New Access.Application
adb.OpenCurrentDatabase("full path/name of db")
adb.Run("access macro name")
adb.CloseCurrentDatabase()
adb.Quit()

Method 2 works on the development system HOWEVER, can this work on a system with only the Access Runtime Installed? If so, do I just distribute and install the Microsoft.Office.Interop.Access.dll?

If anyone has employed these methods (or another) successfully in an environment with ONLY Access 2007 Runtime is installed, please post your solution and or thoughts.

Thanks,

Roger

Answer 1

All,

WHAT IS THE BEST WAY TO CALL A ACCESS MODULE FROM VB?

I need to revisit this question.  A VB2008 app uses an Access 2007 db on the back end.  Some of the tables are large and after hours of testing, it was determined that the BEST way to perform a task that needs to process tocuh every record is to call a module in the Access db to do the work.  The VB app CAN do the job via ADO but this is about 10x slower.

Keep in mind one VERY important fact - MS Access 2007 is not installed on the target systems HOWEVER Access 2007 Runtime SP3 is.

Method 1 - (currently employed HOWEVER, on some WinXP systems this will fail with a Cannot Create ActiveX error)

            Dim objAccess1 As Object

            If SystemType = 32 Then
                Shell("c:\Program Files\Microsoft Office\Office12\msaccess.exe " & Chr(34) & "C:\SCDBv4\Data\SCDBData.mdb" & Chr(34))
            Else
                Shell("c:\Program Files (x86)\Microsoft Office\Office12\msaccess.exe " & Chr(34) & "C:\SCDBv4\Data\SCDBData.mdb" & Chr(34))
            End If
            objAccess1 = GetObject("C:\SCDBv4\Data\SCDBData.mdb")

            objAccess1.Run("RebuildIndex")
            objAccess1.Quit(acExit)
            objAccess1 = Nothing

Method 2 - This uses Microsoft.Office.Interop. I am NOT sure where this object came from (resource wise). My guess is the MS Access 14.0 object library. The development system has Access 2010 installed.

Dim adb As New Access.Application
adb.OpenCurrentDatabase("full path/name of db")
adb.Run("access macro name")
adb.CloseCurrentDatabase()
adb.Quit()

Method 2 works on the development system HOWEVER, can this work on a system with only the Access Runtime Installed? If so, do I just distribute and install the Microsoft.Office.Interop.Access.dll?

If anyone has employed these methods (or another) successfully in an environment with ONLY Access 2007 Runtime is installed, please post your solution and or thoughts.

Thanks,

Roger

Answer 2

Have you tried using DAO to manipulate the data?

http://msdn.microsoft.com/en-us/library/office/ff965871.aspx#DataProgrammingWithAccess2010_DirectDAOExample

Answer 3

Hi Rodger,

It looks to me like you just need to reference the local Access installation or the "Microsoft Access 14.0 object library" -the file is: msacc.olb 

---

Brian, ProcessIT- Hawke's Bay, New Zealand

Answer 4

I always use following code that works great everywhere:

Dim objAccess As Object
Dim varRC as Variant
Set objAccess = CreateObject("Access.Application")
objAccess..OpenCurrentDatabase "YourPath\YourDatabase.mdb"
varRC = objAccess.Run("YourFunction")
objAccess.Quit acSaveNone
Set objAccess = Nothing

HTH

Henry

Answer 5

Henry - This works GREAT. 

I have tested this out and it works 100% in the development system.  I put together the install program to install the app on a client and it throws a "Cannot create ActiveX" error.

I have installed the Microsoft.Office.Interop.Access.dll file ..... no luck.

I have installed the 2007 Microsoft Office System Redistributable Primary Interop Assemblies on the client.... no luck.

Any thoughts as to what I am missing from the install?

Roger

Answer 6

Hi Roger

On the client computer you need to have Access installed and you have to be sure the macro security is set in a way that VBA code can run (trusted location/file or set macro security to low)

You don't need the Redistributable Primary Interop Assemblies. These are for managed code (.Net), not for VBA only stuff.

Henry

Answer 7

Henry,

Thanks.  The clients will NOT have Access installed BUT they will have Access 2007 Runtime SP3 installed.

Trusted locations aleady set via this reg entry which has been working when using the Shell method (#1 from above).

HKEY_CURRENT_USER

Software\Microsoft\Office\12.0\Access\Security\Trusted Locations\Location3 [and the path portion of the entry points to the mdb's location].

As I mentioned in the original post, I need a solution that 1) avoids the Shell method and 2) can be used with only the Access 2007 Runtime in place.

The solution you proposed is perfect for the task as long as it meets those two items!

Thanks again for any help!

Roger

Answer 8

I would be rather surprised if we could create an Access object from an installation having ONLY the Access run time version, neither that the Run Time ever exposes a public Access object.

Answer 9

As I understand the runtime it is the full version of access but with the UI / Design features disabled. 

I have used Excel 2003 with Access 2010 runtime to run DAO updates regularly. (although not called a function)

---

Brian, ProcessIT- Hawke's Bay, New Zealand

Answer 10

Hi Roger

That's strange. But I never use the runtime for several other reasons, so I never run into this problem before.

I tried it now, too, and get the same error, even when I try to create a new instance of the already running Access.Application. Seems that OLE automation with the Access Runtime is blocked or I've overseen a point.

So you will have to find another solution for the function you want to call if not somebody else have a solution to make this run.

The name of the function (RebuildIndex) you use indicats this function is doing something that may not require an Access.Application. Maybe a few DAO calls are enough to do the same stuff.
Can you give an some more details what your function "RebuildIndex" is doing?
Maybe we can find a way to do what is done in this remote function without using OLE automation at all.

An alternative could be to set a reference to the MDB (or better MDE) where your function is located in the VBA references and then call it directly in the application itself. I know this may reduce the stability, specially in a RT environment (if the location changes, for example) - but it could be worth a try if you have control over the locations of the MDB/MDEs.

Henry

Answer 11

found the reason. The Access runtime requires to pass a file in the command line (that will become the currentdb). It's described in this kb article here:

http://support.microsoft.com/kb/295179/en-us

Henry

Answer 12

Hi Roger

The (known) solution using OLE automation with the Access runtime is to open the remote application using a shell command. It's described here:

http://support.microsoft.com/kb/210132/en-us

Henry

Answer 13

Henry,

Thanks again.  If you notice, this is what I am doing in mentod #1.  I do have a few installations where the user runs into trouble with a 'Cannot create ActiveX" error.  The only major difference I see between my method #1 and the one in the KB link above is that the shell command returns a LONG.  In mine, no return is accepted.

I will make the change to have the shell return a LONG to a variable.

My goal was in this exercise was twofold.... Find a way to resolve the error some XP users experience and two - find a way NOT to use the Shell method.

It seems clear that the Shell needs to stay.  Testing will determine if the return of a LONG to a variable from the Shell will solve the error SOME users experince.

Your question about the "RebuildIndex" function and could the same be done with DAO - sure it could.  The db that I use has between 10,000 and 60,000 records.  I have run several performance tests using DAO and I found it quite slow compared to the same code running as a Access module.  There are some routines that need to update a single field in every record in a table.  The performance difference in the Access module was far faster than using DAO to perform the same task via VB. 

My original design was to use DAO via VB to do all the work like that but in the testing process, I found that it was simply too slow compared to the Access VBA module. Hence, I needed some way to call these modules from the VB app.

Roger

Answer 14

My original design was to use DAO via VB to do all the work like that but in the testing process, I found that it was simply too slow compared to the Access VBA module. Hence, I needed some way to call these modules from the VB app.

Hi Roger,

My simplest way to call a VBA-routine in an external database is:

    Run  glo_dbpath & "\" & dbname & "." & routinename [, ...]

where glo_dbpath is the specification of the path to the external database, dbname the name of the database file without extension, and routinename the name of the Sub you want to call.

However, this does not work if the extension is .mdb (I am still working in A2003), but if you just rename the file to a .mde extension, it works.

Imb.

Answer 15

Hi Roger

For your shell command: if the shell command fails and Access isn't started by it the ActiveX error you see will occur. Before you call the shell command ensure the target file exists and the user has sufficient permission on the drive to open it. Also ensure the trust settings are correct else Access RT may also not be able to open the database.

To the performance issue that you report when you are doint it with DAO instead of VBA:
Hard to understand what you do if you don't give an example. Also doing it with VBA you have to use an access method, either DAO or ADO to access the file. So when it's running fast in the remote access application there is no reason why it should be running slow when you run it locally.

How did you try to let it run? Did you link the tables in the remote MDB to the local MDB and run it against the linked tables? There are other ways - that may be much faster - than doing it with linked tables. And there was (and maybe still is) a known performance issue with linked tables becoming slow when the 8.3 name convention of the target mdb wasn't fullfilled, means if the name was longer than 8 characters (followed by .mdb).

So now let's look how it could run in the same speed when you run it locally.
instead of linking the tables and then running against the CurrentDB() object try following:

Dim dbr As Database 'remote database
Dim strRDBName As String
Dim rs As Recordset
strRDBName = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name))) & "ole.mdb"
Set dbr = DBEngine.OpenDatabase(strRDBName)
'you even may try to open the dbr exclusive with the corresponding parameter
Set rs = dbr.OpenRecordset("SELECT <Something> From <YourTable>", _
           dbOpenDynaset, _
           dbSeeChanges Or dbFailOnError)
While Not rs.EOF
  'do something
  rs.MoveNext
Wend
rs.Close
Set rs = Nothing
dbr.Close
Set dbr = Nothing

There is no reason why this should run slower than when you run the same code thru OLE automation and the Run method.

If this doesn't fit to you please give us an indication what you are doing in your function RebuildIndex. The fastest would be a bulk update anyway and if you can achieve this you are on the fast lane. Also here you can use the above method to run the SQL statement on the remote MDB by calling

dbr.Execute "UPDATE ....", dbFailOnError OR dbSeeChanges

HTH

Henry

Answer 16

Henry,

Sorry for any delay in posting to this thread.  I have modified the code and tested on WinXP (in a Win7 Virtual PC window), 32-bit Vista and 64-bit Win 7.  All seems good using the .Run where a long is returned.

A new release of the software went out so if there are any changes or other findings, I'll update this thread!

Thanks again,

Roger