• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1554
  • Last Modified:

How do you specify the version of access to use when using the New Access.Application object

Hello All,
I have a system that has both Access 2003 and Access 2007 installed on it.  I'm writing some code to loop through a bunch of Access 2007 databases, open each one up, run a function that resides in each database and then closes.  

My problem is that when I use the New Access.Application object the version of Access it chooses is Access 2003.

Any idea how to force it to use Access 2007?

Thanks
0
shannonds
Asked:
shannonds
  • 4
  • 4
  • 3
  • +3
1 Solution
 
Dale FyeCommented:
do all of the Access 2007 databases contain the accdb file extension?

What does your code look like that is performing this operation?

Are you getting an error message?  If so, what line is highlighted and what is the error message?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As far as I know, you cannot - and you really SHOULD not. If you're running Access 2007/2010/2013 with ANY prior version you'll get all sorts of issues and errors. When 2007 first came out, we had a rash of questions that came about due to people trying to run both versions concurrently, from odd behavior to data loss. The end result was "don't do that".

Access uses the "last registered version" on the machine, so close all 2003 instances on that machine, then open Access 2007 and run your code.

Also, the CreateObject syntax appears to give you the ability to specify the version, but it's unreliable (and normally doesn't work). CreateObject will use the "last registered version" paradigm.
0
 
Dale FyeCommented:
I have not tried this, and don't have time to try it this morning.

Could you use the followhyperlink method to open the appropriate version based on the file extension, to synch with Scott's comment about the "last registered version paradigm?

Two loops, one for the mdb's, calling the followhyperlink for the first one, then closing that application, and using CreateObject.  Then when done with the 2003 files, use followhyperlink to open the first accdb file, and then use CreateObject for the rest.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure there's a reason to use a loop ... the author wants to open 2007 databases ONLY, and the issue is that the machine is trying to open those using an instance of 2003 (which, of course, cannot open .accdb files). The "fix" is to make sure that 2007 is the registered version on the machine. Other fixes, like trying to force CreateObject to use a specific version, just don't work.

For example, I can do this:

Dim obj As Object
Set obj = CreateObject("Access.Application.11")

And this is supposed to open a specific version of Access, but it may or may not.

The easy fix is just close all 2003 instances, and then open 2007 and run the code from there. This will make 2007 the "last registered version", and the CreateObject code should work. Or, Wayne Phillips seems to have a fix for this: http://www.everythingaccess.com/tutorials.asp?ID=How-to-specify-which-version-of-Access-to-use-for-OLE-automation
0
 
shannondsAuthor Commented:
Good morning and thanks for your replies.

Here's an example of the code:

Sub RunForeignProcedureTEST()
Dim appAccess As New Access.Application

appAccess.OpenCurrentDatabase "E:\DBRemoteFuncTest.accdb"
appAccess.Run "RemoteFuncTest"
appAccess.Quit
Set appAccess = Nothing
MsgBox "Done!"

End sub

The object is to open a remote database and run a function that resides in it and then close the database.

The problem is that it tries to open the accdb in Access 2003 instead of 2007.  Both versions are on this machine temporarily because we're in the middle of doing a conversion.  

I have already tried closing all the 2003 instances on the machine and then opening up Access 2007 and running the code.  I still have the same issue.  No matter what I've tried, it keeps trying to open in 2003.
0
 
shannondsAuthor Commented:
I think I've got it...

The link to Wayne Phillips fix might just work...  Initial testing looks good.  I'll let you know for sure once I'm done testing it.


Sub RemoteTest()
Dim objAccess As Object

Shell """C:\Program Files (x86)\Microsoft Office\OFFICE12\MSACCESS.EXE"" E:\DBRemoteFuncTest.accdb"
Set objAccess = GetObject("E:\DBRemoteFuncTest.accdb").Application
objAccess.Run "RemoteFuncTest"
objAccess.Quit

Set objAccess = Nothing

MsgBox "Done!"

End Sub
0
 
Nick67Commented:
Now, I don't run concurrent versions on the same box.  I tried but it's a pain.  I have run the main version (A2003) on the box and the new version (A2007) in a virtual box to save myself the pain.

Let's go sideways.  My deployment script runs Access from VBScript.  When I specify
Dim obj As Object
 Set obj = CreateObject("Access.Application.11")

On a machine that does not have Access 2003 on it, the script bombs.
So, you want Access 2007, which is version 12

Paste the following code into notepad, and save it as test.vbs
Run it.
Does it open the file in Access 2007?

Main()

'----------------
Sub Main()
RunForeignProcedureTEST()
end sub

Sub RunForeignProcedureTEST()
Dim appAccess
Set AppAccess=CreateObject("Access.Application.12")
appAccess.OpenCurrentDatabase "E:\DBRemoteFuncTest.accdb"
'appAccess.Run "RemoteFuncTest"
'appAccess.Quit
'Set appAccess = Nothing
appAccess.usercontrol = true
MsgBox "Done!"

End sub

Open in new window

Almost everything is commented out, and usercontrol is added in.
First we need to see if VBScript is going to open the correct version.
Most of what you can do in VBA you can do in VBScript, which can be handy at times.
0
 
Jacques Bourgeois (James Burger)Commented:
You have to consider that the location of Office can be different from one computer to another. So the Shell solution is not a very good one although it might work most of the time if your users are all on 64-bit.

If you are on a 32-bit computer, then Office installs by default in Program Files instead of Program Files (x86). And although few do it, a user also has the option of installing Office in a directory of his choice. So MSACCESS.EXE can be anywhere. This is the main reason why Microsoft decided to go through the registry when they designed the CreateObject and GetObject, and then later the way ActiveX works to launch an application.

It is possible to run both versions of Office, it brings many hiccups such as that one. It might not apply in your case, but for Access, since most users work only with the Forms that were designed by the creator of the database, working in either 2003 or 2007 makes no difference.

In the few instances were I had customers that wanted to have the 2 versions installed because users were complaining that they did not like the ribbon in Word and Excel, or any other reason, we acted as Outlook does. We removed Access 2003 and kept only Access 2007. Since Access 2007 can work with .mdb files, this worked well with only a few little simple adjustments here and there.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Dim obj As Object
Set obj = CreateObject("Access.Application.11")
This won't work with multiple versions of Access on the machine. On my machine, where I have every version from 2003 forward, I ran this code:
Dim obj As object
Set obj = CreateObject("Access.Application.11")
MsgBox obj.Version
Set obj = Nothing

Open in new window

The Msgbox tells me the version is 15.0 every time. I tried .12, and .14 as well. Every one returns "15.0".

The only way to insure that you're using the correct version of Access is to register the correct version before running the code.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I have already tried closing all the 2003 instances on the machine and then opening up Access 2007 and running the code.  I still have the same issue.  No matter what I've tried, it keeps trying to open in 2003.
Are you running the code from an Access 2003 database, or from a 2007 database? If 2003, try switching to a 2007 database and see if that works.
0
 
Nick67Commented:
@Scott
Did you try it from VBScript?
Nick67
0
 
Luke ChungPresidentCommented:
The correct Access version needs to be invoked before starting the Access database. Relying on Windows to handle the association is not going to work.

We offer a commercial product, Total Access Startup, that handles the launching of the correct version of Access and distributes the front-end database updates whenever someone launches the database. It's designed for enterprise environments where one wants to manage all the organization's Access applications across a network centrally, so it may be overkill for your situation.
0
 
shannondsAuthor Commented:
Thanks Scott for directing me to the fix that Wayne Phillips came up with.  It ended up being the answer to what I was looking to do.

Here is my final code for anyone interested.  It's being run out of an Access 2007 database and simply loops through a list of databases, one at a time, open it, runs the code that exists in the opened database and then closes it.  It saved a ton of time when you think about how long it would take to do this manually on a few hundred databases.

Thanks again...

Private Sub cmdRunLinkRefresh_Click()
Dim db As DAO.Database, rst As DAO.Recordset, unit As Double
Dim strSQL As String, strRS As String, objAccess As Object, Param As String
On Error Resume Next

strRS = "SELECT [DBPath_Filename] AS DBPath, Distribute FROM DBLIST WHERE (((Distribute)=Yes));"

Set db = CurrentDb()
Set rst = db.OpenRecordset(strRS)
DoCmd.SetWarnings False

rst.MoveFirst

If rst.EOF Then
    MsgBox "There are currently no databases selected.  Please select the database or databases to be distributed to. "
Else
    unit = 100 / DCount("*", "SELECT DBList.[DBPath_Filename] AS DBPath, DBList.Distribute FROM DBList WHERE (((DBList.Distribute)=Yes));")
    rst.MoveLast
    rst.MoveFirst
    Do While Not rst.EOF
        Debug.Print rst!DBpath
        Param = Chr(34) & "C:\Program Files (x86)\Microsoft Office\OFFICE12\MSACCESS.EXE" & Chr(34) & " " & Chr(34) & rst!DBpath & Chr(34)
        Shell Param, vbMaximizedFocus
        Set objAccess = GetObject(rst!DBpath).Application
        objAccess.Run "RelinkTbls"
        objAccess.Quit
        Me.ProgressBar9 = Me.ProgressBar9 + unit
        strSQL = "UPDATE DBList SET DBList.Distribute = No WHERE (((DBList.[DBPath_Filename])='" & rst!DBpath & "'));"
        DoCmd.RunSQL strSQL
        DoCmd.Requery Me.frmDistribute_Sub
        rst.MoveNext
    Loop
End If
DoCmd.SetWarnings True

Me.ProgressBar9 = 0
Set rst = Nothing
Set db = Nothing
Set objAccess = Nothing

MsgBox "Task complete..."

End Sub
0
 
shannondsAuthor Commented:
Not sure how to grade this seeing that the solution was a link to a solution that Wayne Phillips came up with, but then again, I probably wouldn't have known about it had you not posted the link.  So for that you get an A and all the points.
0
 
Nick67Commented:
Now, having relinked the tables on hundreds of database you may need to:
a) have them set to compact & repair on close, or
b) run through that same kind of loop and set that option, close, re-open and reset that option
c) have them grow substantially from having that done, and do nothing
d) do a manual compact & repair on them all.

Some of that can be coded, too.
Glad you got it sorted.
Multiple versions of Access (2003 & 2007) on the same machine was a headache!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 4
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now