Access vba open database

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 can&#;t use it in another program (Word, Excel, &#;).

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 don&#;t 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.

Access Automation

Automation! Over the years I&#;ve come to truly appreciate the power of automation. With automation, you can basically do anything you want, so obviously, you can open a database.

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)!

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

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.

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]

=========
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

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

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.

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:

1. Early binding
2. 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

• 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

Return value

Database

You can use the following values for the options argument.

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.

Example

This example uses the OpenDatabase method to open one Microsoft Access database and two Microsoft Access database engine-connected ODBC databases.