
Another common request every now and then is how to launch another database from within the current database. As always, there are a number of ways of handling this, below are a few:
- FollowHyperlink Method
- Shell
- Access Automation
FollowHyperlink Method
FollowHyperlink is native to Access and can be used to open any type of file in its default associated program. Note that since it is native command, you cant use it in another program (Word, Excel, ).
'' Procedure : OpenDb3' Author : Daniel Pineault, CARDA Consultants Inc.' Website : http://www.cardaconsultants.com' Purpose : Open another database' Copyright : The following is release as Attribution-ShareAlike International' (CC BY-SA ) - https://creativecommons.org/licenses/by-sa//' Req'd Refs: None required'' Input Variables:' ~~~~~~~~~~~~~~~~' sDb : Fully qualified path and file name with extension of the database to' open'' Usage:' ~~~~~~'Call OpenDb3("C:\Users\Daniel\Documents\Databaseaccdb")'' Revision History:' Rev Date(yyyy/mm/dd) Description' **************************************************************************************' 1 Initial Release - Website Demo'PublicFunction OpenDb3(sDb AsString) OnErrorGoTo Error_Handler Application.FollowHyperlink sDb Error_Handler_Exit: OnErrorResumeNextExitFunction Error_Handler: MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ "Error Source: OpenDb3" & vbCrLf & _ "Error Description: " & Err.Description & _ Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _ , vbOKOnly + vbCritical, "An Error has Occurred!"Resume Error_Handler_Exit EndFunction |
' ' Procedure : OpenDb3 ' Author : Daniel Pineault, CARDA Consultants Inc. ' Website : http://www.cardaconsultants.com ' Purpose : Open another database ' Copyright : The following is release as Attribution-ShareAlike International ' (CC BY-SA ) - https://creativecommons.org/licenses/by-sa// ' Req'd Refs: None required ' ' Input Variables: ' ~~~~~~~~~~~~~~~~ ' sDb : Fully qualified path and file name with extension of the database to ' open ' ' Usage: ' ~~~~~~ 'Call OpenDb3("C:\Users\Daniel\Documents\Databaseaccdb") ' ' Revision History: ' Rev Date(yyyy/mm/dd) Description ' ************************************************************************************** ' 1 Initial Release - Website Demo ' Public Function OpenDb3(sDb As String) On Error GoTo Error_Handler Application.FollowHyperlink sDb Error_Handler_Exit: On Error Resume Next Exit Function Error_Handler: MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ "Error Source: OpenDb3" & vbCrLf & _ "Error Description: " & Err.Description & _ Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _ , vbOKOnly + vbCritical, "An Error has Occurred!" Resume Error_Handler_Exit End Function
Shell
Shell is one of those universal commands that allows one to do so much. Once again, it can be used to launch programs and open files. Typically, you need to supply the exe file and then whatever command line switches each individual exe recognizes. However, since office is registered and part of the path variable, we dont even need to supply the path to the msaccess.exe file and need only provide the database path/file to open.
The only time you want to include the path/file of the executable for Access (and other Office apps) would be in the case that you have multiple versions installed and you want to use a specific one.
'' Procedure : OpenDb2' Author : Daniel Pineault, CARDA Consultants Inc.' Website : http://www.cardaconsultants.com' Purpose : Open another database' Copyright : The following is release as Attribution-ShareAlike International' (CC BY-SA ) - https://creativecommons.org/licenses/by-sa//' Req'd Refs: None required'' Input Variables:' ~~~~~~~~~~~~~~~~' sDb : Fully qualified path and file name with extension of the database to' open'' Usage:' ~~~~~~'Call OpenDb2("C:\Users\Daniel\Documents\Databaseaccdb")'' Revision History:' Rev Date(yyyy/mm/dd) Description' **************************************************************************************' 1 Initial Release - Website Demo'PublicFunction OpenDb2(sDb AsString) OnErrorGoTo Error_Handler Shell "cmd /c " & Chr(34) & sDb & Chr(34), vbHide Error_Handler_Exit: OnErrorResumeNextExitFunction Error_Handler: MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ "Error Source: OpenDb2" & vbCrLf & _ "Error Description: " & Err.Description & _ Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _ , vbOKOnly + vbCritical, "An Error has Occurred!"Resume Error_Handler_Exit EndFunction |
' ' Procedure : OpenDb2 ' Author : Daniel Pineault, CARDA Consultants Inc. ' Website : http://www.cardaconsultants.com ' Purpose : Open another database ' Copyright : The following is release as Attribution-ShareAlike International ' (CC BY-SA ) - https://creativecommons.org/licenses/by-sa// ' Req'd Refs: None required ' ' Input Variables: ' ~~~~~~~~~~~~~~~~ ' sDb : Fully qualified path and file name with extension of the database to ' open ' ' Usage: ' ~~~~~~ 'Call OpenDb2("C:\Users\Daniel\Documents\Databaseaccdb") ' ' Revision History: ' Rev Date(yyyy/mm/dd) Description ' ************************************************************************************** ' 1 Initial Release - Website Demo ' Public Function OpenDb2(sDb As String) On Error GoTo Error_Handler Shell "cmd /c " & Chr(34) & sDb & Chr(34), vbHide Error_Handler_Exit: On Error Resume Next Exit Function Error_Handler: MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ "Error Source: OpenDb2" & vbCrLf & _ "Error Description: " & Err.Description & _ Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _ , vbOKOnly + vbCritical, "An Error has Occurred!" Resume Error_Handler_Exit End Function
Access Automation
Automation! Over the years Ive come to truly appreciate the power of automation. With automation, you can basically do anything you want, so obviously, you can open a database.
'' Procedure : OpenDb' Author : Daniel Pineault, CARDA Consultants Inc.' Website : http://www.cardaconsultants.com' Purpose : Open another database' Copyright : The following is release as Attribution-ShareAlike International' (CC BY-SA ) - https://creativecommons.org/licenses/by-sa//' Req'd Refs: Uses Late Binding, so none required'' Input Variables:' ~~~~~~~~~~~~~~~~' sDb : Fully qualified path and file name with extension of the database to' open'' Usage:' ~~~~~~'Call OpenDb("C:\Users\Daniel\Documents\Databaseaccdb")'' Revision History:' Rev Date(yyyy/mm/dd) Description' **************************************************************************************' 1 Initial Release' 2 Error handling updated for posting on website'PublicFunction OpenDb(sDb AsString) OnErrorGoTo 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 instanceWith 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?EndWith Error_Handler_Exit: OnErrorResumeNextIfNot oAccess IsNothingThenSet oAccess = NothingExitFunction Error_Handler: MsgBox "The following error has occurred" & 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 Occurred!"Resume Error_Handler_Exit EndFunction |
' ' Procedure : OpenDb ' Author : Daniel Pineault, CARDA Consultants Inc. ' Website : http://www.cardaconsultants.com ' Purpose : Open another database ' Copyright : The following is release as Attribution-ShareAlike International ' (CC BY-SA ) - https://creativecommons.org/licenses/by-sa// ' Req'd Refs: Uses Late Binding, so none required ' ' Input Variables: ' ~~~~~~~~~~~~~~~~ ' sDb : Fully qualified path and file name with extension of the database to ' open ' ' Usage: ' ~~~~~~ 'Call OpenDb("C:\Users\Daniel\Documents\Databaseaccdb") ' ' Revision History: ' Rev Date(yyyy/mm/dd) Description ' ************************************************************************************** ' 1 Initial Release ' 2 Error handling updated for posting on website ' 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 Error_Handler_Exit: On Error Resume Next If Not oAccess Is Nothing Then Set oAccess = Nothing Exit Function Error_Handler: MsgBox "The following error has occurred" & 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 Occurred!" Resume Error_Handler_Exit End Function
Which One To Use?
Simple is typically best so Shell and FollowHyperlink are about a simple as can be BUT, the automation approach offer you the ability to interact with the database after it is launched. So it enables you to open forms, reports, run macros, VBA code and the likes with great ease. So which one should be used depends on what you need to accomplish (like always)!
how to open another database using VBA
Thanks for your prompt reply.
Yes, I use this but not working. The reason to run link is because the database is secured with .mdw If I run the mdb then the system give message "you don't have necessary permission to use " because the database is secured.
Also used below mention code but not succeeded
Private Sub cmdWrkOpen_Click() ' Open a workgroup-secured database. ' Open a database. Const ACCESSEXE = "msaccess.exe" Const FILENAME = "C:\data\Northwind_be.mdb" Const SECUREWRK = "C:\developer.mdw" Const SECUREUSER = "Developer" Const SECUREPWD = "Developer" Dim strFilePath As String, varAppID As Variant, strShell As String On Error GoTo err_cmdWrkOpen strFilePath = SysCmd(acSysCmdAccessDir) If Len(Dir(FILENAME)) > 0 And Len(Dir(SECUREWRK)) > 0 Then ' Open the database while using Chr(34) to add inverted commas. strShell = strFilePath & ACCESSEXE & " " & FILENAME & _ "/WRKGRP " & Chr(34) & SECUREWRK & Chr(34) & _ "/USER " & Chr(34) & SECUREUSER & Chr(34) & _ "/PWD " & Chr(34) & SECUREPWD & Chr(34) varAppID = Shell(strShell, vbNormalFocus) Else MsgBox "Problem Opening Your Application. Contact Your DBA", vbCritical, _ "Database Is Out of Action" End If ' Reinstate the following line once you have finished testing. ' DoCmd.Quit acQuitSaveAll exit_cmdWrkOpen: Exit Sub err_cmdWrkOpen: Select Case Err.Number Case Else MsgBox "Error No. " & Err.Number & " -> " & Err.Description, vbCritical End Select Resume exit_cmdWrkOpen End Sub
I don't understand why, the below mention code is working in my system but not on other systems
Application.FollowHyperlink "C:\Users\Qazi\Desktop\hrmst.lnk
Opening an External Database
Sometimes you need to work with data in another Access database, a dBase IV database, or Excel spreadsheet, but you don't want a permanent link. You can do so by opening a temporary connection to it with OpenDatabase method on the DBEngine object. Although the connection to the external database is temporary, the new Database object is still added to the Databases collection.
The OpenDatabase method is fairly straightforward.
The following table describes the OpenDatabase method arguments.
Argument | Description |
---|---|
dbname | A string value that represents the full path and filename of the database you want to open. |
options | An optional Boolean true (-1) or false (0) that indicates whether to open the database in exclusive (True) or shared mode (False). |
Read-only | An optional Boolean true (-1) or false (0) that indicates whether to open the database as read-only. |
Connect | Specifies connection information such as passwords |
The following code demonstrates how to open several different databases using various techniques. After opening each database, you'll notice that the code prints the name of the database, and a count of the respective Databases collection.
Specifically, it opens the following databases from the following sources:
- Microsoft Access database
- dBase IV database using Jet
- SQL Server database using ODBC through Jet
[Previous][Contents][Next]
Access VBA: Open a database or file using VBA
=========
QUESTION
I have a database that I use on a regular basis, and when I am finished working with that database, I invariably always move on to the same second database file.
Is there a way for me to open another file using a button or sub within VBA?
=========
ANSWER
See the code in EXAMPLES
=========
EXAMPLES
*Note: this second bit of code opens the new database file in seemingly the same instance, if you will, of MSAccess, causing this file to close at the same time as you close the database that houses this code to open it.
Because of this, we prefer the first example using Shell
=========
APPLIES TO / KEY WORDS
Microsoft Access
Open File
VBA
=========
REF
http://www.erlandsendata.no/english/index.php?d=envbafoldersfileopen
http://social.technet.microsoft.com/Forums/en-US/officeitpro/thread/9fcbeaea87ebef4d88
http://www.anysitesupport.com/access-vba-open-a-database-or-file-using-vba/
http://anySiteHosting.com
Related Posts
Sours: https://www.anysitesolutions.com/access-vba-open-a-database-or-file-using-vba/Database open access vba
Application.OpenCurrentDatabase method (Access)
You can use the OpenCurrentDatabase method to open an existing Microsoft Access database as the current database.
Syntax
expression.OpenCurrentDatabase (filepath, Exclusive, bstrPassword)
expression A variable that represents an Application object.
Parameters
Name | Required/Optional | Data type | Description |
---|---|---|---|
filepath | Required | String | The name of an existing database file, including the path name and the file name extension. |
Exclusive | Optional | Boolean | Specifies whether you want to open the database in exclusive mode. The default value is False, which specifies that the database should be opened in shared mode. |
bstrPassword | Optional | String | The password to open the specified database. |
Return value
Nothing
You can use this method to open a database from another application that is controlling Microsoft Access through Automation, formerly called OLE Automation. For example, you can use the OpenCurrentDatabase method from Microsoft Excel to open the Northwind.mdb sample database in the Access window. After you have created an instance of Access from another application, you must also create a new database or specify a particular database to open. This database opens in the Access window.
If you have already opened a database and wish to open another database in the Access window, you can use the CloseCurrentDatabase method to close the first database before opening another.
Note
Use the OpenAccessProject method to open an existing Access project (.adp) as the current database.
Note
Don't confuse the OpenCurrentDatabase method with the ActiveX Data Objects (ADO) Open method or the Data Access Object (DAO) OpenDatabase method. The OpenCurrentDatabase method opens a database in the Access window. The DAO OpenDatabase method returns a Database object variable, which represents a particular database but doesn't actually open that database in the Access window.
Example
The following example opens an Access database from another application through Automation and then opens a form in that database.
You can enter this code in a Visual Basic module in any application that can act as a COM component. For example, you might run the following code from Excel, Visual Basic, or Access.
When the variable pointing to the Application object goes out of scope, the instance of Access that it represents closes as well. Therefore, you should declare this variable at the module level.
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
In this article I will explain how you can open an existing Access database from Excel using VBA.
If you wish to only import the data into excel you could use the topic covered in the article below:
In order to create a blank access database from excel using VBA, please see the article below:
Similar to the what was mentioned in the article VBA, Create New Access Database From Excel there are 2 method for automating an Access database:
- Early binding
- Late binding
In the first method we add reference to the Access Object Library, before execution. It will run faster and we will have access to the VBA editor intellisense. On the other hand there is always the risk of compatibility issues arising when the program is run on a computer with a different version of Access installed. For more information about early vs late binding please see the link below:
Note: Although this code was written in VBA for Excel, it is applicable to all other office applications.
–
Open Existing Access Database:
The codes below will open the access database located in the path “D:StuffBusinessTemp” under the name “NewDB.accdb”:
Late Binding:
Early Binding:
In early binding you will need to add reference to the Access Object Library before running the code. This can be done in the VBA editor (Tools>>Reference):
Note: I have the Microsoft Access Object Library installed on my computer. There might be a different version installed the computer you are using. This will not affect the result of the code.
Result:
–
Running SQL Commands:
SQL statements can be run using the code below:
Where strSQL is a string with the SQL statement.
The code below will open the access database located in the path “D:StuffBusinessTemp” under the name “NewDB.accdb” and create a new table called “NewTable”:
Result:
You can download the file and code related to this article from the link below:
If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com
Tagged with:Access, Automation, Excel, Open, VBA
Similar news:
- Enders speed razor
- Hannity ratings vs maddow
- Embroidery stencils letters
- Gangster cartoon art
- Blackbird designs free
- Free texting clipart
- Free full episode
- High pressure pipe fittings
- Right stuf anime
- Vestidos de confirmacion 2020
- Sonic super silver
- 21 ft solar cover
Workspace.OpenDatabase method (DAO)
- 2 minutes to read
Applies to: Access , Office
Opens a specified database in a Workspace object and returns a reference to the Database object that represents it.
Syntax
expression .OpenDatabase(Name, Options, ReadOnly, Connect)
expression A variable that represents a Workspace object.
Parameters
Name | Required | String | the name of an existing Microsoft Access database engine database file, or the data source name (DSN) of an ODBC data source. See the Name property for more information about setting this value. |
Options | Optional | Variant | Sets various options for the database, as specified in Remarks. |
ReadOnly | Optional | Variant | True if you want to open the database with read-only access, or False (default) if you want to open the database with read/write access. |
Connect | Optional | Variant | Specifies various connection information, including passwords. |
Return value
Database
You can use the following values for the options argument.
True | Opens the database in exclusive mode. |
False | (Default) Opens the database in shared mode. |
When you open a database, it is automatically added to the Databases collection.
Some considerations apply when you use dbname:
If it refers to a database that is already open for access by another user, an error occurs.
If it doesn't refer to an existing database or valid ODBC data source name, an error occurs.
If it's a zero-length string ("") and connect is "ODBC;" , a dialog box listing all registered ODBC data source names is displayed so the user can select a database.
To close a database, and thus remove the Database object from the Databases collection, use the Close method on the object.
Note
When you access a Microsoft access database engine-connected ODBC data source, you can improve your application's performance by opening a Database object connected to the ODBC data source, rather than by linking individual TableDef objects to specific tables in the ODBC data source.
Example
This example uses the OpenDatabase method to open one Microsoft Access database and two Microsoft Access database engine-connected ODBC databases.