To open a second DB - Access 2013.

J

JLCantara

Registered User.
Local time
Today, 12:18
Joined
Jul 22, 2012
Messages
332
Hello!!!

With Access 2007, the following code was working fine:
Code: 
    Dim app As Access.Application
    Dim xDB As Database
     Dim strPath as string
    ...
    strPath = "....\TestDB.accdb"
    Set app = Access.Application
    With app
        .UserControl = False
        .OpenCurrentDatabase strPath ' , , "ms1595888"
        Set xDB = .CurrentDb
    End With
Now, using Access 2013 it refuses .usercontrol = false (non valid reference). When commented, the next line crashes (DB already opened).
It seems that Access does not create a new application in app but refers to the ACTUAL db. Any solution???
 
A quick restructuring of code:
Code: 
    Dim app As Access.Application
    Dim xDB As Database
     Dim strPath as string
    ...
    strPath = "....\TestDB.accdb"
    Set app = [COLOR="Blue"]New[/COLOR] Access.Application
    Set xDB = app.OpenCurrentDatabase(strPath ' , , "ms1595888")
The New keyword creates a new instance of Access, OpenCurrentDatabase returns a database object (or at least I think it should) in the new instance of Access, and as a result you don't need to set UserControl. Untested but should do it.

If you want control of the the current instance then UserControl comes in.
 
I told you it was untested, but I remember now, OpenCurrentDatabase strangely enough doesn't return a database object so:
Code: 
    Dim app As Access.Application
    Dim xDB As Database
     Dim strPath as string
    ...
    strPath = "....\TestDB.accdb"
    Set app = [COLOR="Blue"]New[/COLOR] Access.Application
    
    With app 
        .OpenCurrentDatabase(strPath ' , , "ms1595888")
        Set xDB = .CurrentDb
    End With
 
What are you trying to do? Do you want to work with a DAO.Database object? And if so, you don't need an Access.Application at all . . .
Code: 
dim dbs as dao.database
set dbs = dbengine.opendatabase("....\TestDB.accdb")
Also, UserControl is false by default if you open Access programmatically, so there is no need to set it to False in that case.
 
Ah yes, forgot about DbEngine... it's been a while.
 
Hi vbaNet!

Doesn't work: the app ... new gives:

Automation error #-2147467229

Went back to copy/paste the error number: surprise Access doesn't respond to anything and the test app cannot be started.. So I have to restart my PC.
Done but it has been pretty complicated to open the Test app.

JLC.
 
Last edited:
That's peculiar JLC!

I've just run a test using the code I posted and it opens it in a new Access instance as can be seen in my Task Manager.

I'm sure you know this but as a reminder have you checked the references and did you kill all instances of Access before running the code? And one more thing, did you compile?
 
JLCantara said:
Done but it has been pretty complicated to open the Test app.
Click to expand...
Does that mean that it's working ok now?

One thing to remember is that when you're opening an instance through code and it gets stuck/errors at some point, most times that instance remains in memory so you have to physically kill it through Task Manager or write error handling code to dispose of that instance. In Task Manager, you look in the Details tab, not the Processes tab and you will find the instance.

That open instance can cause issues, so it's worth remembering.
 
Hi VBANet!

No. I should have sent a new post. You will notice that I added the error number...
The error sort of lock TestFE using an unknown user. Restarting the PC didn't change that but since all roads leads to Roma, I found a way to open it. Sometimes Access acts like a schizophrenic...

Thanks, JLC
 
Hi JLC,

If you have an error handler for disposing your app and db objects, you hopefully won't encounter this problem. When it errors, the app object lingers in memory.
 
You are right! A stray Access was created: I killed it and NeoTestFE could be opened this time...

Here the full function I run:
Code: 
 Public Function OpenDB()
     Dim app As Access.Application
    Dim xDB As Database
    
    Dim strPath As String
    
    strPath = ".\TestDB.accdb"
    Set app = New Access.Application
    
    With app
        .OpenCurrentDatabase (strPath)
        Set xDB = .CurrentDb
    End With
 End Function
Could it be that my Access his screwed?

Note that I get the same error message and number but in a different message box...

Thanks, JLC.
 
No your Access is fine JLC. Basically, it created the instance as requested (using New), but the garbage collector doesn't dispose of that instance even when the object goes out of scope.

This is one of the reasons that highlights the need for error handling and disposing of your objects (closing and setting it to Nothing).
 
It did not create the instance! That's what the error message says:
"Automation error".
That was my first impression but I checked the taskbar details and found that it actually creates the access application but a possible communication error seems to occur!

I have attached a capture of my references. Is one missing? I started NeoTestFE from scratch to avoid any interference.

Thanks, JLC.
 

Attachments

  • References.png
    References.png
    17.8 KB · Views: 137
Last edited:
Your references are fine. So you're still getting an error on the 'New Access.Application' line?

Test it on another machine.
 
Hi vbaInet!

Since I am stuck with Access 2013, I am afraid it wont be that easy. I am retired (and happy to be) from IT and I do not have Access to well equipped PC. Anyway, I will probably check that Monday...

Thanks, JLC.
 
JLC, we've got a few pensioners on here, BrianWarnock is one and I bet pbaldy is on his way there too ;)

One more thing to try:
* Set the reference to Excel or Word
* Try and see if you can create a new object of one of those.

I'm beginning to suspect your installation.
 
Hi vbaInet!

It is absolutely clear that MY version of Access 2013 is screwed! Since I subscribed to Office 365, all kinds of strange things happened: I had 2007 and it lost his help functionality. Here and then, opening an app would first display the standard Access open form then, seconds after, the opened app. On compact, it complains on lock conflicts: app is locked by my user name!!! I included my references because I doubt they were wrong; believe or not I cannot create a word.application - not listed!!!
I have tried to report the problem to MS: of course they routed me to this stupid Office 'machin truc' that has nothing to help!!!

I once reached someone at MS who proved to be very helpful. Do you have a suggestion with MS help? I have in mind a full download of Office 365 (note it came with my laptop).

Thanks, JLC.
 
JLC, I was researching your problem and came across this

https://social.technet.microsoft.co...previous#60965d20-072e-4680-964b-014a0b17dcd4

It points to a registry key that wasn't set properly when Office was installed. See if it helps.

As for an MS contact, one of the MVP's should have one. People like pbaldy (current MVP), GinaWhipp (current MVP) and The_Doc_Man (former MVP) might be able to help. PM one of them.
 
Believe it or not, I tried your link and got an automation error!!!
 
Ha, the ods are not in your favour JLC.

Open it with Chrome or Firefox.
 
You must log in or register to reply here.

Similar threads

A
Solved External Database Update Connection String
Replies
6
Views
1,085
Alibaba
A
J
VBA to print Word file and close is
Replies
8
Views
701
DickyP
DickyP
C
Access instance remains in memory once closed
2 3
Replies
57
Views
4,910
CJ_London
C
B
Access form in Office 365 is minimized when opening from Excel using Docmd.Openform
Replies
15
Views
745
Gasman
Gasman
N
VBA code crashes after installing KB5064081 update
2 3
Replies
55
Views
5,739
RonPaii
RonPaii

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom