Access vba open database

Access vba open database DEFAULT

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, &#;).

'' 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 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.

'' 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 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.

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

MS AccessMS Access VBA ProgrammingSours: https://www.devhut.net//01/21/ms-access-vba-open-another-database/

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

 

Sours: https://www.access-programmers.co.uk/forums/threads/how-to-open-another-database-using-vba/
  1. Pitbull halloween costume ideas
  2. Mexican table decorations
  3. Miss u friend images
  4. Frankincense tablets

Opening an External Database

MS-Access/ Getting Started

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.

ArgumentDescription
dbnameA string value that represents the full path and filename of the database you want to open.
optionsAn optional Boolean true (-1) or false (0) that indicates whether to open the database in exclusive (True) or shared mode (False).
Read-onlyAn optional Boolean true (-1) or false (0) that indicates whether to open the database as read-only.
ConnectSpecifies 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
Public Sub OpenSeveralDatabases(strUsrName As String, strPwd As String) Dim wsAccess As Workspace Dim dbAccess As DAO.Database Dim dbdBase As DAO.Database Dim dbODBC As DAO.Database 'Create the Access workspace Set wsAccess = DBEngine(0) 'Print the details for the default database Debug.Print "Access Database "; wsAccess.Databases.Count & _ "-"& CurrentDb.Name 'Open a Microsoft Access database -shared -read-only Set dbAccess = wsAccess.OpenDatabase("C:\Temp\db1.accdb", False, True) Debug.Print "Access Database "; wsAccess.Databases.Count & _ "-"& dbAccess.Name 'Open a dBase IV database -exclusive -read-write Set dbdBase = wsAccess.OpenDatabase( _ "dBase IV;DATABASE=C:\Temp\db2.dbf", True, False) Debug.Print "Database "; wsAccess.Databases.Count & _ "-"& dbdBase.Name 'Open an ODBC database using a DSN -exclusive -read-only Set dbODBC = wsAccess.OpenDatabase( _ "", dbDriverComplete, True, "ODBC;DATABASE=myDB;DSN=myDSN") Debug.Print "Access Database "; wsAccess.Databases.Count & _ "-"& dbODBC.Name 'Clean up wsAccess.Close Set dbAccess = Nothing Set dbdBase = Nothing Set dbODBC = Nothing Set cn = Nothing Set wsAccess = Nothing End Sub

[Previous][Contents][Next]

Sours: https://sourcedaddy.com/ms-access/opening-external-database.html
40. VBA - DoCmd (Programming In Microsoft Access 2013) 🎓

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

NameRequired/OptionalData typeDescription
filepathRequiredStringThe name of an existing database file, including the path name and the file name extension.
ExclusiveOptionalBooleanSpecifies 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.
bstrPasswordOptionalStringThe 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.

Sours: https://docs.microsoft.com/en-us/office/vba/api/access.application.opencurrentdatabase
Access VBA 13 - Connect Database module

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):
Microsoft Access Object Library

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:
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:
NewTable
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

Sours: https://software-solutions-online.com/vba-open-access-database-from-excel/

Similar news:

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.

Sours: https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/workspace-opendatabase-method-dao


579 580 581 582 583