Opening database with VBA from another database caught in loop (1 Viewer)

bruceblack

Registered User.
Local time
Today, 21:43
Joined
Jun 30, 2017
Messages
119
Hi folks! Little stuck here. Some help would be very much appreciated :)

I'm trying to open another database from another.
There's many threads on this and tried them all with the same result :banghead:
When i open the database, it tries to open the other one, but keeps looping and opens instances over and over until it crashes my system

What is going on?
It erros on acc.DBEngine.OpenDatabase.
But when i use currentdatabase it does the same.
I tried it with a bat file and Shell, same result.


Code:
 Option Compare Database
 
Public Function OpenDb(sDb As String)
    'On Error GoTo Error_Handler
    'Early binding
    'Use the following line if being used in Access or using Access reference
    '   provides intellisense!
    Dim oAccess               As Access.Application
    'Late binding
    'Use the following line if being used outside of Access without an Access reference
'    Dim oAccess               As Object
 
    Set oAccess = CreateObject("Access.Application")    'Create a new Access instance
    With oAccess
        acc.DBEngine.OpenDatabase    'Open the specified db
        .Visible = True             'Ensure it is visible to the end-user
        .UserControl = True
'        .DoCmd.OpenForm "YourFormName"    'Open a form?
'        .DoCmd.RunMacro "YourMacroName"    'Run a Macro?
    End With
     If Not oAccess Is Nothing Then Set oAccess = Nothing
'Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: OpenDb" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
   ' Resume Error_Handler_Exit
End Function

Code:
Private Sub Form_Load()
 
If Application.Version = "14.0" Then
Call OpenDb("C:\tool\tool 2010.accdb")
End If
End Sub
 

Minty

AWF VIP
Local time
Today, 21:43
Joined
Jul 26, 2013
Messages
10,355
Two things ;
You aren't using the sDb string passed in to the function to specify which database to open. So It will probably defaults to opening another instance of itself - hence your loop.

Are you running this from "tool 2014.accdb" ? If so I suspect the same will apply.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:43
Joined
May 21, 2018
Messages
8,463
It works for me wit some changes. I think you have a cut and paste error because this will not compile
Code:
Public Function OpenDB(sDb As String)
    'On Error GoTo Error_Handler
    'Early binding
    'Use the following line if being used in Access or using Access reference
    '   provides intellisense!
    Dim oAccess               As Access.Application
    'Late binding
    'Use the following line if being used outside of Access without an Access reference
'    Dim oAccess               As Object
 
    Set oAccess = CreateObject("Access.Application")    'Create a new Access instance
    With oAccess
        .OpenCurrentDatabase sDb    'Open the specified db
        .Visible = True             'Ensure it is visible to the end-user
        .UserControl = True
'        .DoCmd.OpenForm "YourFormName"    'Open a form?
'        .DoCmd.RunMacro "YourMacroName"    'Run a Macro?
    End With
     If Not oAccess Is Nothing Then Set oAccess = Nothing
'Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: OpenDb" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
   ' Resume Error_Handler_Exit
End Function
I changed this line
.OpenCurrentDatabase sDb 'Open the specified db
 

MarkK

bit cruncher
Local time
Today, 14:43
Joined
Mar 17, 2004
Messages
8,178
It erros on acc.DBEngine.OpenDatabase.
What is acc? It is not declared anywhere.

Also, what are you trying to do, apart from opening Access from Access. Why do you need to do that? Note that DBEngine is a object provided by DAO, and you can use that to open a DAO.Database object, but that is not the same thing as an Access.Application object. It matters, as far as what object you need to create, what the job is that you are trying to accomplish.

For instance, if you need to work with data, don't open an Access.Application, open a DAO.Database.

hth
Mark
 

isladogs

MVP / VIP
Local time
Today, 21:43
Joined
Jan 14, 2017
Messages
18,186
I wrote the attached function in an answer to a similar question a few months ago. Perhaps you found it in your research

In that post, the OP wanted code to
a) open an external database, run a macro and close the 2nd database
b) close the current database
This definitely works

Code:
Public Function RunExternalDatabase() As Boolean
    
    Dim app As Access.Application, strPath As String
    
    'Start a new MSAccess application
    Set app = New Access.Application
    
    'Open the remote database and run a macro, then close the remote database
    With app
        'Syntax: .OpenCurrentDatabase(filepath, Exclusive, strPassword) - the last 2 items are optional
        strPath = "C:\Programs\MendipDataSystems\JATFA\JATFA.accdb" 'replace with your file path
        .OpenCurrentDatabase strPath, True, "password"
    
    '    .DoCmd.RunMacro "mcrRefreshPersTable" 'run your macro
        .CloseCurrentDatabase 'closes external database as that is current
    End With
    
    'Quit the spawned app
    app.Quit acQuitSaveNone
    Set app = Nothing
    
    'Quit the current app - optional
    Application.Quit acQuitSaveNone
    
End Function

NOTE: I never understood at the time why the OP didn't just link the external database tables & run the macro or code directly but they didn't want to do that
 

bruceblack

Registered User.
Local time
Today, 21:43
Joined
Jun 30, 2017
Messages
119
Hey guys! Thanks for all the replys! Awesome

However, im still no step further.

What im trying to do is:

open expeditool2010 if the version is 14.0
open expeditool2013 if the version is 15.0
open expeditool 2016 if the version is 16.0

This has to do with that people use different versions of Access on our network. This gives conflicts in the references. But if you open the same file with the same version, its avoided. So i want access to check which file to open depending on the version they are using.

It still keeps looping. :(
 

isladogs

MVP / VIP
Local time
Today, 21:43
Joined
Jan 14, 2017
Messages
18,186
The easiest way to avoid version specific reference library issues is to change the relevant code to late binding and then you don't need the reference at all.

Alternatively, use the standard rule and do all development work in the lowest version of Access that your users have. In this case, version 14.0 (A2010).The reason is Access should upgrade references to later versions when needed... but it can't downgrade them
 
Last edited:

bruceblack

Registered User.
Local time
Today, 21:43
Joined
Jun 30, 2017
Messages
119
Thank you for your answer. Due to restriction and the design of my database, i need/want to do it like this. Theres many other reasons.

Its STILL not working. How difficult is it to open another database...??
It says

DATABASE IS CURRENTLY OPEN and at the same time it keeps opening the same instance over and over.

Please guys, my code should be able to get it work.:(
 

isladogs

MVP / VIP
Local time
Today, 21:43
Joined
Jan 14, 2017
Messages
18,186
Thank you for your answer. Due to restriction and the design of my database, i need/want to do it like this. Theres many other reasons.

Its STILL not working. How difficult is it to open another database...??
It says

DATABASE IS CURRENTLY OPEN and at the same time it keeps opening the same instance over and over.

Please guys, my code should be able to get it work.:(

You may want to do it like this but the alternatives I suggested would be FAR easier.
Doing either of those methods I suggested would mean only one app ever needs to be developed
Doing it your way, requires three versions to be maintained & updated whenever one version is changed. Later this year, A2019 will be available & you'll need to allow for that as well. That's both high maintenance and it seems totally unnecessary

The clients I work with use a mixture of Access 2010/2013/2016 and both 32-bit & 64-bit versions are used. I develop in Access 2010 32-bit & ensure it works in 64-bit. There are NEVER any reference issues

Having got my rant out of the way, have you tried my code from post #5 which I know works
 

bruceblack

Registered User.
Local time
Today, 21:43
Joined
Jun 30, 2017
Messages
119
Thanks for that man. You're right. I'll do it that way then. :)

Just for your information: I have a Split database
It's so annoying they have all these different versions here.
I need to stop all these bugs fast!

The easiest way to avoid version specific reference library issues is to change the relevant code to late binding and then you don't need the reference at all.

Although, i have no idea what you mean. :confused:

"change the relevant code" you mean the code thats giving me issues when running a different version? In this case, i get date/time and object reference errors.

"change to late binding"...im not sure how to go about that.
 

isladogs

MVP / VIP
Local time
Today, 21:43
Joined
Jan 14, 2017
Messages
18,186
Ok I'm glad you are going to try another way but which way exactly?

1. Develop in the lowest version A2010? That should be all you need to do.
2. Use late binding? More effort but will definitely work

If the latter there are numerous articles explaining the differences between early and late binding as well as the +/- points of each.
Do a google or forum search.
This one is from a Word site but the principles are similar
https://wordmvp.com/FAQs/InterDev/EarlyvsLateBinding.htm

How many version specific references do you actually have?
In my experience, Excel reference causes most version related issues so would be a good candidate to start with.
 

bruceblack

Registered User.
Local time
Today, 21:43
Joined
Jun 30, 2017
Messages
119
Thanks!

Well, option 1 is what i already did unconciously. I build it in 2010.
But they get errors in 2016

The references i use in 2010 are:

Visual basic for applications
Microsoft access 14.0 object library (OLB)
OLE automation
Microsoft office 14.0 access database engine object

Microsoft access 14.0 object library (AGAIN!!!!?)
Microsoft HTML object library


Also i get a Runtime error from time to time.
No idea what is causing it.

WILL READ YOUR ARTICLE! THANKS! :)
 

isladogs

MVP / VIP
Local time
Today, 21:43
Joined
Jan 14, 2017
Messages
18,186
Thanks!

Well, option 1 is what i already did unconciously. I build it in 2010.
But they get errors in 2016

The references i use in 2010 are:

Visual basic for applications
Microsoft access 14.0 object library (OLB)
OLE automation
Microsoft office 14.0 access database engine object

Microsoft access 14.0 object library (AGAIN!!!!?)
Microsoft HTML object library


Also i get a Runtime error from time to time.
No idea what is causing it.

WILL READ YOUR ARTICLE! THANKS! :)

How can you have 2 Access 14.0 object libraries? It shouldn't be possible. What's the OLB mean?
First compile your database - does it compile without error? If not, THAT may be one of your problems
Fix any compile errors if necessary then make a backup

Next try removing one of the access refs & compile again - it SHOULD still work

Apart from that issue, I don't see how Access 14.0 reference could be an issue as its one of the built in libraries

BUT so is Microsoft office 14.0 access database engine object library
So I doubt that will cause version issues either

Whereas if you had Microsoft Excel 16.0 Object Library there would be issues

So please check some more basic things.
Apologies if you know/do any of these already

1. In the VBE Options, make sure 'Require Variable Declaration' is ticked.
If not, tick it now.

That will add the line Option Explicit below Option Compare Database in all NEW code modules and will alert you if any variables haven't been defined. However, it won't add it to EXISTING code modules

2. Next go through EVERY code module and ADD that line if its missing

3. Compile again & fix any errors. BACKUP again

4. Next DECOMPILE to remove any compile code that may be corrupted
This is very quick but can fix many issues.
For info on decompiling., see http://www.fmsinc.com/microsoftaccess/performance/decompile.asp

5. Now compile again & finally do a compact & repair.

If you follow all of that through without omitting anything, you may find all your troubles are over.

In other words, almost certainly no need to worry about late binding as you aren't referencing external programs such as Excel or Outlook

UNLESS ... do you have any users on 64-bit Access? If so, there's some other things to look at!
 

bruceblack

Registered User.
Local time
Today, 21:43
Joined
Jun 30, 2017
Messages
119
Best help on the forum! Thanks for this little guide you wrote out :)

So, i followed all the step. This worked all wel....BUT...

I forgot one reference that got missing when using 2016 so i didnt get to write it down in the list of references. Hints my problems prehaps???

Microsoft Outlook 14.0 object library

This is the one giving me problems im sure.
Whats the best way going about this reference??
I need it so send attachments from outlook inside access :confused:

Thank you so much for your help so far!!!
 

isladogs

MVP / VIP
Local time
Today, 21:43
Joined
Jan 14, 2017
Messages
18,186
Best help on the forum! Thanks for this little guide you wrote out :)

So, i followed all the step. This worked all wel....BUT...

I forgot one reference that got missing when using 2016 so i didnt get to write it down in the list of references. Hints my problems prehaps???

Microsoft Outlook 14.0 object library

This is the one giving me problems im sure.
Whats the best way going about this reference??
I need it so send attachments from outlook inside access :confused:

Thank you so much for your help so far!!!

The Outlook reference should in theory get upgraded to 16.0 when using 2016 but if you then copy that database back to a PC running 2010 it will then fail.

So it may be worth replacing your Outlook references using late binding. Basically replace definitions like Dim ... As Outlook.MailItem with Dim ... As Object. But that's too simplistic. Read up on the topic properly before proceeding. Make a backup first!
Most of my databases include email capability including attachments. None contain the Outlook reference.

Many developers start coding databases using early binding to get the benefits of intellisense. Then, when the code is working, the code is changed to late binding before distribution to end users.

So what did OLB mean? If you somehow had two identical Access 14.0 references, its possible only one gets upgraded to 16.0 causing conflict. Just a guess which you would need to confirm for yourself.

Is 32/64 bit an issue?
 

bruceblack

Registered User.
Local time
Today, 21:43
Joined
Jun 30, 2017
Messages
119
Interresting point about the second reference! Im still trying to find out what that msacc.olb does. I know its rooted in C:\ somewhere, as where the other one is in program files (like all other references).

This was the reason why i would have 1 file everybody open, and access will check their version of Access, and open the same file.

If a new version of Access comes out, i only have to copy the file (since its a split database, i can just make many copies, like Expeditool2018.accdb and put a new version check in the main file VBA). Not to troublesome.

I think thanks to you i fixed alot of problems by doing some debugging and error checking with your method. Awesome!

What you think is best? Would be handy to know how to open a database from another one without problems.
 

isladogs

MVP / VIP
Local time
Today, 21:43
Joined
Jan 14, 2017
Messages
18,186
Interresting point about the second reference! Im still trying to find out what that msacc.olb does. I know its rooted in C:\ somewhere, as where the other one is in program files (like all other references).

The main Access reference is msacc.olb in Program Files
If you have a copy elsewhere on your computer, it shouldn't appear in the reference list.

This was the reason why i would have 1 file everybody open, and access will check their version of Access, and open the same file.

If a new version of Access comes out, i only have to copy the file (since its a split database, i can just make many copies, like Expeditool2018.accdb and put a new version check in the main file VBA). Not to troublesome.

Yes I understand the idea but still think its totally unnecessary

I think thanks to you i fixed alot of problems by doing some debugging and error checking with your method. Awesome!

What you think is best?

Excellent. My advice is still:
Remove the second Access reference if not already done.
Change Outlook code to late binding

Would be handy to know how to open a database from another one without problems.

See the code I supplied in post #5.
It works & is perfectly safe ... though IMO not the solution you need here

P.S. Please see the comment in my signature line :)
 

Users who are viewing this thread

Top Bottom