Function AccessShowReport

The function below can be used to preview a report created in MS Access.

'Purpose : Preview an Access Report from VB
'Inputs : sAccessDBPath The path and filename of the access database containing the report to show
' sReportName The name of the report to show
'Outputs : Returns an empty string on success, else returns an error message.


Function AccessShowReport(sAccessDBPath As String, sReportName As String) As String
Dim oAccess As Object 'Access.Application

On Error GoTo ErrFailed
AccessShowReport = ""
'Create Access
Set oAccess = CreateObject("Access.Application")
'Open Database
oAccess.OpenCurrentDatabase sAccessDBPath
'Open report
oAccess.DoCmd.OpenReport sReportName, 0 'acViewNormal
'Show Access Report
oAccess.Visible = True
oAccess.CloseCurrentDatabase
Set oAccess = Nothing
Exit Function

ErrFailed:
Debug.Print Err.Description
Debug.Assert False
AccessShowReport = Err.Description
If oAccess Is Nothing = False Then
oAccess.CloseCurrentDatabase
Set oAccess = Nothing
End If
End Function

READ MORE - Function AccessShowReport

Connection Type

Microsoft Excel is a component of MS Office application, which is useful to create spreadsheet. This Office utility is fruitful to create and maintain huge lists easily and effortlessly. It is used for storing contacts, doing statistical analysis, performing financial calculations and much more. Excel is extensively used for storing hundreds and thousands of contacts.

MS Excel application is a boon for people in the marketing field, executives etc, as they prefer to use MS Excel for creating and preserving enormous contacts databases of various clients and prospective customers, along with their names, address, contact numbers, email identity etc, which can be stored in Excel XLS files. With the help of Excel spreadsheets, transactions can be easily conducted.

If you selected "Excel" for the Connection Type.

Optionally, click Create Database to create a new and empty spreadsheet file.

Otherwise:

Click to navigate to and select a .XLS file, then click Open.

If necessary, enter your login name in the User field.

If necessary, enter your password.

Enter any additional parameters required by your spreadsheet.

Click Test Connection.

If the connection was successful, click OK to conclude.

DAverage
In Excel, the DAverage function averages all numbers in a column in a list or database, based on a given criteria. The syntax for the DAverage function is: DAverage( range, field, criteria ) range is the range of cells that you want to apply the criteria against. field is the column to average the values. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DCount
In Excel, the DCount function returns the number of cells in a column or database that contains numbers and meets a given criteria. The syntax for the DCount function is: DCount( range, field, criteria ) range is the range of cells that you want to apply the criteria against. field is the column to count the values. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DCountA
In Excel, the DCountA function returns the number of cells in a column or database that contains nonblank values and meets a given criteria. The syntax for the DCountA function is: DCountA( range, field, criteria ) range is the range of cells that you want to apply the criteria against. field is the column to count the values. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DGet
In Excel, the DGet function retrieves from a database a single record that matches a given criteria. The syntax for the DGet function is: DGet( database, field, criteria ) database is the range of cells that you want to apply the criteria against. field is the column to retrieve. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria. Note: If no record matches the criteria, the DGet function returns #VALUE! error. If more than one record matches the criteria, the DGet function returns #NUM! error.

DMax
In Excel, the DMax function returns the largest number in a column in a list or database, based on a given criteria. The syntax for the DMax function is: DMax( database, field, criteria ) database is the range of cells that you want to apply the criteria against. field is the column to find the largest number in. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DProduct
In Excel, the DProduct function returns the product of the numbers in a column in a list or database, based on a given criteria. The syntax for the DProduct function is: DProduct( database, field, criteria ) database is the range of cells that you want to apply the criteria against. field is the column to multiply the numbers in. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DVarP
In Excel, the DVarP function returns the variance of a population based on the entire population of numbers in a column in a list or database, based on a given criteria. The syntax for the DVarP function is: DVarP( database, field, criteria ) database is the range of cells that you want to apply the criteria against. field is the column to be used by the DVarP function. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DMin
In Excel, the DMin function returns the smallest number in a column in a list or database, based on a given criteria. The syntax for the DMin function is: DMin( database, field, criteria ) database is the range of cells that you want to apply the criteria against. field is the column to find the smallest number in. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DStDevP
In Excel, the DStDevP function returns the standard deviation of a population based on the entire population of numbers in a column in a list or database, based on a given criteria. The syntax for the DStDevP function is: DStDevP( database, field, criteria ) database is the range of cells that you want to apply the criteria against. field is the column to be used by the DStDevP function. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DStDev
In Excel, the DStDev function returns the standard deviation of a population based on a sample of numbers in a column in a list or database, based on a given criteria. The syntax for the DStDev function is: DStDev( database, field, criteria ) database is the range of cells that you want to apply the criteria against. field is the column to be used by the DStDev function. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DSum
In Excel, the DSum function sums the numbers in a column or database that meets a given criteria. The syntax for the DSum function is: DSum( range, field, criteria ) range is the range of cells that you want to apply the criteria against. field is the column to sum the values. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

DVar
In Excel, the DVar function returns the variance of a population based on a sample of numbers in a column in a list or database, based on a given criteria. The syntax for the DVar function is: DVar( database, field, criteria ) database is the range of cells that you want to apply the criteria against. field is the column to be used by the DVar function. You can either specify the numerical position of the column in the list or the column label in double quotation marks. criteria is the range of cells that contains your criteria.

READ MORE - Connection Type

SQL ODBC connection

SQL ODBC connection strings

Standard Security:< br> "Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Uid=Your_Username;Pwd=Your_Password;"

Trusted connection:< br> "Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=yes;"

SQL OLE DB connection strings

Standard Security:

"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;"

Trusted connection:

"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"

SQL OleDbConnection .NET strings

Standard Security:

"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;"

Trusted connection:

"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"

READ MORE - SQL ODBC connection

SQL Functions

SQL Functions

Keyword Description

Create Function Ext_Orders

(parmWhere CHAR(5)) CREATE FUNCTION names the UDTF and registers it with SQL. This function is called Ext_Orders (external orders). This name will be referenced by SQL statements using the function.

You may define an optional comma-delimited parameter list. For table functions, parameter lists are usually passed to a) limit the number of rows returned or b) assist in the calculation of column values.

Returns Table This keyword defines the column list (column names and attributes) that the table function will return.

External Name 'ExtDB.orders' This option is applicable only to functions written in a language other than SQL. The external name is a program name that will be called when the function is invoked. For Java programs, the external name is specified as Class.method. For non-Java programs, the name is specified as library/program.

Language Java This keyword specifies that the function is written in Java. Other options include SQL, COBOL, CL, RPGLE, C, etc.

Parameter Style DB2General The parameter style keyword determines how the database manager will pass parameters to an external program. Research these options thoroughly before writing programs.

For Java, the DB2General style is a DB2-specific option that specifies that the table function will extend the com.ibm.db2.app.UDF class.

Another parameter style called "Java" is used when writing routines conforming to the SQLJ Part 1: SQL Routines standard. However, this standard only supports scalar (not table) functions, so DB2General must be used.

Fenced The fenced option specifies that multiple invocations of the function (i.e., the function is used more than once in a single statement) will run in separate threads so as to avoid potential conflict with each other.

No SQL This parameter, which is mainly for optimization, specifies that there are no DB2 SQL statements to be run by the function.

Disallow Parallel This option is required for UDTFs that cannot be multi-threaded.

Scratchpad This option specifies that a static storage area will be allocated to the function. This storage area will be preserved between calls to the function. If a number isn't specified, 100 bytes will be allocated.

This keyword is special for Java UDTFs because if it is not specified, Java will instantiate a new instance of the class each time the function is invoked.

Final Call This option specifies that the FIRST and FINAL call types will be invoked.

Returns Null on Null Input If one or more input parameters passed to the table function are NULL, the function will return zero rows.

READ MORE - SQL Functions

C, C++ and Java Assignment Operators

C, C++ and Java Assignment Operators
= -- simple assignment
+= -- add rhs, then assign to lhs
-= -- subtract rhs, then assign to lhs
*= -- multiply by rhs, then assign to lhs
/= -- divide lhs by rhs, then assign to lhs
&= -- bitwise and, then assign to lhs
|= -- bitwise or, then assign to lhs
^= -- bitwise exclusive or, then assign to lhs
%= -- bitwise remainder, then assign to lhs
<<= -- left shift lhs by rhs, then assign to lhs >>= -- signed right shift, then assign to lhs
>>>= -- unsigned right shift, then assign to lhs

Infix Operators
|| -- logical or
&& -- logical and
| -- integer bitwise or
^ -- integer bitwise exclusive or
& -- integer bitwise and
== -- numerical equality
!= -- numerical inequality
< -- less than > -- greater than
<= -- less than or equal to >= -- greater than or equal to
<< -- left shift >> -- signed right shift
>>> -- unsignd right shift
+ -- addition or string concatenation
- -- subtraction
* -- multiplication
/ -- division
% -- remainder

READ MORE - C, C++ and Java Assignment Operators

import java.sql

import java.sql.*;

public class DataTransfer {

private static String[] tableNames = new String[]{"SOME_TABLE_NAME"};
private static final int FETCH_SIZE = 100;
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection sourceConnection = ...;
Connection targetConnection = ...);

// records transfered counter
int c = 0;

for (String tableName : tableNames) {
final PreparedStatement ps = sourceConnection.prepareStatement("SELECT * FROM " + tableName);
ps.setFetchSize(FETCH_SIZE);
final ResultSet rs = ps.executeQuery();
final ResultSetMetaData metaData = rs.getMetaData();
final int columnCount = metaData.getColumnCount();
String insertSQL = "INSERT INTO " + tableName + " ";

while (rs.next()) {
StringBuffer columns = new StringBuffer("(");
StringBuffer params = new StringBuffer("(");

for (int i = 0; i < columnCount; i++) { columns.append(metaData.getColumnName(i+1)); params.append("?"); if ((i+1) != columnCount) { columns.append(","); params.append(","); } } columns.append(")"); params.append(")"); final String sql = insertSQL + columns.toString() + " VALUES " + params.toString(); c++; if (c > 0 && c % FETCH_SIZE == 0) {
System.out.println("Transfered " + c + " records.");
}

final PreparedStatement insertStatement = targetConnection.prepareStatement(sql);
for (int i = 0; i < columnCount; i++) {
insertStatement.setObject(i+1, rs.getObject(i+1));
}
insertStatement.execute();
targetConnection.commit();

insertStatement.close();
}
rs.close();
ps.close();
}
sourceConnection.close();
targetConnection.close();
System.out.println("Total transfered " + c + " records.");
}
}

READ MORE - import java.sql

Simple Database

A Simple Database Backup:

You can use mysqldump to create a simple backup of your database using the following syntax.

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]
[username] - this is your database username
[password] - this is the password for your database
[databasename] - the name of your database
[backupfile.sql] - the file to which the backup should be written.


The resultant dump file will contain all the SQL statements needed to create the table and populate the table in a new database server. To backup your database 'Customers' with the username 'sadmin' and password 'pass21' to a file custback.sql, you would issue the command:

mysqldump -u sadmin -p pass21 Customers > custback.sql

You can also ask mysqldump to add a drop table command before every create command by using the option --add-drop-table. This option is useful if you would like to create a backup file which can rewrite an existing database without having to delete the older database manually first.

mysqldump --add-drop-table -u sadmin -p pass21 Customers > custback.sql

Backing up only specified tables

If you'd like restrict the backup to only certain tables of your database, you can also specify the tables you want to backup. Let's say that you want to backup only customer_master & customer_details from the Customers database, you do that by issuing

mysqldump --add-drop-table -u sadmin -p pass21 Customers customer_master customer_details> custback.sql

So the syntax for the command to issue is:

mysqldump -u [username] -p [password] [databasename] [table1 table2 ....]
[tables] - This is a list of tables to backup. Each table is separated by a space.

READ MORE - Simple Database