DBConnect Action

Establishes a connection to a data source.

Use

The DBConnect action establishes a physical connection to an ADO or DDM database. After this action successfully completes, the connection is live and you can issue commands against it and process the results.

When you have concluded your operations over an open connection, use the DBClose action to terminate the connection and free any associated system resources.

Parameters

Connection  -  (Required)

A name that you assign to the connection object that represents the connection to the data source. The connection will be referred to by this name in all subsequent DB actions. For example, your Connection name might be "CustDBConnection".

Server  -  (Required)

The type of data source to which you are connecting. Use one of the pre-defined Server constants.

Connection String - (Required)

This parameter consists of semi-colon separated name/value pairs that specify the database or server and all relevant connection properties. The contents of the connection string can vary, depending upon the data source or server that is used for the connection.

Each name/pair value is formatted as 'name=value'. For example:

'Provider=Microsoft.Jet.OLEDB.4.0;DataSource=@test\crm.mdb'.

Clicking the prompt button will open the Data Link Properties window. This tool will automatically create the Connection String string for you based on the values entered. See below for a more detailed description of Data Link Property window.

ClosedData Link Properties settings for ADO data sources
Provider Tab

Select the data you want to connect to from the list provided. For more information on the different OLE DB providers click the Help button on the Data Link Properties dialog window.

Connection Tab

Use the Connection tab to specify how to connect to ODBC data.

The Connection tab is provider-specific and displays only the connection properties that are required by the Microsoft OLE DB Provider for ODBC. For more information of the different options on this tab click the Help button in the bottom right corner of the dialog window.

Advanced Tab

Use the Advanced tab to view and set additional initialization properties for your data.

The Advanced tab of the Data Link Properties dialog box is provider-specific and displays only the initialization properties required by the selected OLE DB provider. For more information about advanced initialization properties, click the Help button in the bottom right corner of the dialog window.

All Tab

Use the All tab to view and edit all OLE DB initialization properties available for your OLE DB provider. Properties can vary depending on the OLE DB provider you are using.

ClosedData Link Properties settings for DDM servers
Connection Tab

Specify the details necessary to connect to the DDM server. This tab provides you with two options for connecting:

  1. A pre-established DDM connection, OR
  2. A full connection definition specifying the server and all the relevant connection properties.

Use defined connection - select this option to specify a pre-established host connection definition from the drop down list. Refer to Connections for help on how to create a host connection using the Connection editor.

Specify connection options - select this option if you would like to define the connection manually.

  • Internet Address - The Internet address of the server you are connecting to.
  • DRDA Port - The port number of the DRDA server on the host.
  • Secure - Encrypt the connection to the server using SSL. Set to True if the server you are connecting to is a secure server.
  • Certificate (only enabled if a secure connection is specified) - Specifies how certificates are validated for secure connections. Select Ignore if you do not want the certificate sent from the host to be checked for validity. Select Validate if you only want to connect to the server if the server certificate can be validated. Select Validate and Ask to check for validity and display the certificate details to the user before continuing.
  • Username - The username to use when signing on to the DRDA erver.
  • Password - Enter the required password to sign on to the DRDA server.
  • CCSID - Enter a host code character set identifier from the drop down list, if required. This value is used to transcode between the host character set and the Windows character set.
Advanced Tab

Use the Advanced tab to view and set the following initialization properties for your data:

Access Permissions - Specifies the access permissions that will be applied to the data connection. Access permissions apply to host file locks and do not apply to record locks. Use a combination of one or more of the following values:

  • Read - Open recordsets on the connection with read-only permissions
  • Write - Open recordsets on the connection with write-only permissions
  • ReadWrite - Open recordsets on the connection with read/write permissions
  • ShareDenyNone - Allows others to open shared recordsets with any permissions. Neither read nor write access will be denied.
  • ShareDenyRead - Prevents others from opening a shared recordset with read permissions.
  • ShareDenyWrite - Prevents others from opening a shared recordset with write permissions.
  • ShareExclusive - Prevents others from opening a shared recordset.

This argument is only used for database connections.

Isolation level - The transaction isolation level, or commit lock level, for the connection. Use one of the following values:

  • None - There is no commitment control for the connection.
  • ReadUncommitted - Every record read for update is locked. If a record is updated, added, or deleted, that record remains locked until the transaction is committed or rolled back. Records that are accessed for update but are released without being updated are unlocked. Corresponds to IBM commit lock level *CHG.
  • ReadCommitted - Every record accessed is locked. Records that are not updated or deleted are locked only until a different record is accessed. Records that are updated, added, or deleted are locked until the transaction is committed or rolled back. Corresponds to IBM commit lock level *CS.
  • RepeatableRead - Every record accessed is locked until the transaction is committed or rolled back. Corresponds to IBM commit lock level *ALL.

This argument is only used for database connections.

Binary as Character - Process binary fields (CCSID of 65535) as character data type fields. Can be set to True or False. This argument is only used for database connections.

ClosedExamples

The following example connects to an Access database and uses the DBFind action to search the open recordset for a value saved in the variable varSearch.

Connect

DBConnect (testConnection, DBServerADO, Provider=Microsoft.Jet.OLEDB.4.0; Data Source=@test\crm.mdb; Mode=Share Deny None; Persist Security Info=False;)

DBOpen (recordset1, testConnection, Table1, DBUseClient, DBOpenDynamic, DBLockReadOnly, DBCmdTable)

Find

DBMove (recordset1, DBMoveFirst)

DBFind (recordset1, ="EntityRef = " & varSearch, DBFindForward)

recordset1.EOF = False MsgBox(Found, =varSearch & " was found.", MsgInformation, MsgOKOnly)

recordset1.EOF = True MsgBox(Not Found, =varSearch & " was not found.", MsgCritical, MsgOKOnly)

 

Close

DBClose (recordset1)

DBDisconnect (testConnection)

StopMacro

See Also

DBDisconnect Action | DBOpen Action | DBClose Action | DBFind Action | DBMove Action | DBAddNew Action | DBBeginTrans Action | DBCancelUpdate Action | DBCommitTrans Action | DBDelete Action | DBRollbackTrans Action | DBSeek Action | DBUpdate Action | ConnectionString property | Data Access | ConnectionString Property

© 2004-2021 looksoftware. All rights reserved.