Can you help me write a simple code? (1 Viewer)

[rgwood86]

Hi all,

Here is what I want to do, I just need a code to help me do it!

Close an open and active access database (DB1)

Open another database (DB2)

Close DB2

Open DB1

It sounds simple enough but I just cant work out how to do it!

Many thanks in advance

 

[jdraw]

Sounds like a school assignment.

 

[rgwood86]

Sadly its really not!

I am trying to use this solution to get around a problem I have with a linked table which I cant refresh the connections in because the file is always "open" due to it being linked. This way, I would close the database with the linked table, open the new database with the code to refresh the data, once this has happened, close the 2nd database and reopen the first database with the linked table which is now updated.

It seems like a longwinded way around the problem but I cant work out a better solution at present!

 

[isladogs]

Close an open and active access database (DB1)

Application.Quit

Open another database (DB2)

Public Function OpenDatabase()

On Error GoTo ErrHandler
    Dim strPath As String, strName As String
    strpath = Application.CurrentProject.path 'change path as necessary
    strName = "YourDBName.accdb"

    'open external database
    Call Shell("msaccess.exe " & strpath & "\" & strName, vbMaximizedFocus)
    DoEvents
    
    Application.Quit 'this closes the first db in the same code
    
ExitHandler:
    On Error Resume Next
    Exit Function
    
ErrHandler:
    MsgBox "Error " & err.Number & " in OpenDatabase routine : " & err.description, vbOKOnly + vbCritical
    Resume ExitHandler

End Function

Usage:

Call OpenDatabase

Close DB2
As above

Open DB1
As above

The obvious point is you need to open the second database BEFORE you close the first!

 

[rgwood86]

I need to have DB1 so to speak, the active database, close before DB2 opens. Is there a way of doing this? All the codes I have mean that as soon as DB1 closes it stops the process of being able to open a second database.

When DB2 opens, I have set a module to run on a form opening which does what I require. But, if DB1 is still open when this module is run it doesn't work as required.

:banghead:

 

[plog]

Why do you need to refresh a linked table that often?

 

[rgwood86]

The table holds data that can change at any time, multiple times, throughout the day. It is normally when a user knows that something in the data has changed that they will then need to refer to this to complete a task. The users could just go in an refresh the table but not all users have access to this, and I am trying to create a one click does all button on the database they will use to allow them to complete this task.

 

[rgwood86]

Would putting some sort of timer on the open code allow the process to start, but with enough of a delay to allow the other database to be closed before the 2nd database actually opened?

 

[jdraw]

Perhaps you should step back and tell us in simple, plain English what you are trying to do logically. Once readers understand what, they may have more focused responses for How it may be done.

Tell us also about the 2 databases.

 

[isladogs]

Regardless of whether there is a better way, have you noticed I gave you the code you required in post #4?

 

[rgwood86]

Ok so my problem is.

I have a linked table in an Access database to an excel file. The Excel file gets data from an external source and I need to refresh the connections to allow me to use the new data which can change multiple times throughout the day.

So I have the code that does this perfectly, aside from the fact that because the file is linked, it is always in a read only status and therefore it prompts me to try and save the changes each time which I cant do as I cannot overwrite the file, and nor would I want to do this anyway.

My theory was then to do the refreshing of data in another access project so that when the code is run it works as planned, which it does. So now, I want to write something that I can attach to a button that will allow me to close the active database, open and run the second database with the code, then close this and reopen the 1st database. Ideally without the user noticing what's going on, but that's not essential right now!

 

[rgwood86]

Regardless of whether there is a better way, have you noticed I gave you the code you required in post #4?

Yes, and that's great I really do appreciate this - but as far as I understand it, this wont work as the 1st database will already be open and this stops the code working as planned which is why I was trying to work out a way of closing one database and opening another in that order

 

[isladogs]

Immediately the second database is opened, the first is closed.
Repeat same code in second database to reverse the process
So as far as I can see it will work - presumably you haven't tried it!

 

[rgwood86]

Immediately the second database is opened, the first is closed.
Repeat same code in second database to reverse the process
So as far as I can see it will work - presumably you haven't tried it!

Sorry Colin, I didn't think it would work as I read it that the first DB would still be open. I have tried it now, (every file is saved in the same folder so I assume the only thing I need to replace in the code you kindly provided is the file name - Test.accdb).

So I put the below in a module:

Public Function OpenDatabase()
On Error GoTo ErrHandler
Dim strPath As String, strName As String
strPath = Application.CurrentProject.Path 'change path as necessary
strName = "Test.accdb"
'open external database
Call Shell("msaccess.exe " & strPath & "" & strName, vbMaximizedFocus)
DoEvents

Application.Quit 'this closes the first db in the same code

ExitHandler:
On Error Resume Next
Exit Function

ErrHandler:
MsgBox "Error " & Err.Number & " in OpenDatabase routine : " & Err.Description, vbOKOnly + vbCritical
Resume ExitHandler
End Function



but I get the error:

The command line you used to start Microsoft Access contains an operation that Microsoft Access doesn't recognise.

Exit and restart Microsoft Access using valid command-line options.

 

[jdraw]

Did you check your code vs Colin's example carefully?

Seems you may have missed a slash "\"  in this line

Call Shell("msaccess.exe " & strPath & "" & strName, vbMaximizedFocus)

 

[rgwood86]

I have no idea how the back slash appeared to be missing when I copied and pasted the above...it was definitely there in the code (which is as below), but I am still getting the error message

Public Function OpenDatabase()
On Error GoTo ErrHandler
Dim strPath As String, strName As String
strPath = Application.CurrentProject.Path 'change path as necessary
strName = "Test.accdb"
'open external database
Call Shell("msaccess.exe " & strPath & "\" & strName, vbMaximizedFocus)
DoEvents

Application.Quit 'this closes the first db in the same code

ExitHandler:
On Error Resume Next
Exit Function

ErrHandler:
MsgBox "Error " & Err.Number & " in OpenDatabase routine : " & Err.Description, vbOKOnly + vbCritical
Resume ExitHandler
End Function

 

[jdraw]

I edited you code in #16. Added the slash, but then put the code within code tags.

When you post code, you should use code tags. To do so, highlight the code, then click the # sign in the window header area.

It seems the slash is/was removed by the forum software. Within code tags, the slash is processed differently (apparently).

 

[rgwood86]

I edited you code in #16. Added the slash, but then put the code within code tags.

When you post code, you should use code tags. To do so, highlight the code, then click the # sign in the window header area.

Thank you jdraw.

I have copied and pasted your edited code over mine, but still get the same error message:

The command line you used to start Microsoft Access contains an operation that Microsoft Access doesn't recognise.

Exit and restart Microsoft Access using valid command-line options.

 

[jdraw]

I'll await Colin to respond. It is his sample with which he has detailed knowledge.
I noticed the missing slash, and offered that suggestion. However, I did note the slash was being removed, so did the edit etc.
Colin has been on the forum this AM, so I expect his return shortly.

 

[rgwood86]

Ah I think I may have found the problem.

I specified the exact path, but because the file is saved deep in many folders, some of which contain two words as the name of the folder, it is the space in these e.g. G:\2018\Data\Client Data\.... that is causing the problem. It looks like up to Client the path is being read correctly, but then it stops at the space and cant work because obviously that path doesn't exist.

Is there a way of getting around this?