Access 2007: How to

How to compact and repair database?

File menu (Alt+F) -> Manage -> Compact and Repair Database. Why? To reduce file/database size. Helpful – when you want to upload to a remote server.

Compacting and Repairing databases many times can solve many database issues. Use the feature once in a while – while developing

How to backup an Access Database?

File Menu (Alt+F) -> Manage -> Back Up Database. Follow the wizards/options.
You can backup your databases once in a while. Good practice to save your data and databases.

How to restore an Access Database?
Really you need to do nothing. Just open the backed-up database in Microsoft Access (2007)

How to set database properties in Access?
File Menu (Alt+F) -> Manage -> Database Properties

How to create different objects (database objects and front end objects) in Microsoft Access?
Under ‘create’ toolbar, check toolbar options to create different database and front end objects

Table: To store data

Table Templates: You can use existing tables (table templates) to create new tables

Table Design: You can design tables, assign primary keys, insert and delete rows from here. Also, you can use the ‘Lookup Column’ option for the table (Foreign key concept in Access). Under ‘table design’ option, you can also create indexes. Indexes make your queries run faster (you need to use the index column in your where clauses, group by clauses , order by clauses as appropriate)

Form: You can create Form objects using the ‘form’ option. It uses a table as the back-end and creates the form with table fields on the form. Open/select the table first – that need to be used in the form.

Split Form: A form with two sections. The upper section with Table Field/value pairs. Lower part – grid views of the table

Multiple Items: Will create a form with multiple rows on the form. Similar to Grid View

Blank Form: A plain blank form, you can design it on your own as you want. You can also control which table/query to connect to

More Forms -> Form Wizard : Will guide you step by step to create a form based on tables/queries

More Forms -> Modal Dialog : Will create a dialog box (Modal)

Form Design: Design your own forms as you want. Link to tables/queries as you want.

Report: Use the report menu to create a report based on a table or query. Open/select the table/query based on what – the report will be created. Click on the report toolbar option.
A report will automatically get created. Then you can manipulate what data should be displayed – what not

Report Design: You can design your reports from scratch here. You can use Record Source property to point to the data source (table, query). When the report designer will open, you will see a new toolbar at the top. You will see controls that are useful for reports

Query Wizard: Will guide you step by step to write queries.

Query Design: Will help you to build queries based on a graphical interface. You will also be able to write sql statements and see it in graphical interface

Macro: To write macros, classes, modules

What are the tools access provides to access data in other systems?
Use the External Data toolbar to find out

In the import section, you can see options to import data from other sources such as Access (other access database), Excel, Text File, XML File, ODBC (can be used to collect data from any data source that supports ODBC access such as SQL Server, Oracle).

In the export section, you will see options to export your access database (hence the data) to other data sources such as Excel, Word file, Sharepoint, text file, xml file

How to give custom functionality to access forms?
Use the Visual Basic option under the Database Tools toolbar. When you click – Visual Basic – a code window will appear – where you can write vb code to provide custom functionality.

In the code window, select the form where you want to write code – then write code on different events. At the top of code writing screen – you will see a object selection box and an event selection box.

Select objects and events to write codes for the corresponding events and objects

Alternate way: Open a form -> click an object -> right click -> properties (from toolbar you can also go to properties) -> click event tab -> in the event – select event procedure -> click eclipses (…) -> you will be taken to the place where you need to write code (for the object/event).

How to create relationships in Access?
When you design ER diagram, you usually define relationships among tables. To implement relationships – you usually need to place one table’s fields (foreign key concept) into another table (or create new table based on the related fields) – anyway – check the DBMS theory section of this web-site.

In access, under database tools options, you will see ‘Relationships’ option

Click Relationships option -> you will see all existing relationships. Click any relation as shown by lines and arrows – you will be able to modify the relation (also you will see an option to create a new relationship)

Here you can add tables by right clicking and selecting show table. Then from the toolbar select ‘Edit Relationships’ -> then click ‘Create new’ to create new relationships. Don’t forget to use the ‘Join Type’ option to define relationship type.

Whether you use this access relationship feature or not -> using relationship in database design is highly recommended. Also, relationship simplication is highly recommended. Table design must reflect the relationships. If your databases/applications grow very big – the importance will be realized.

How to password protect access database?
Under Database tools option, use the Encrypt with password option

How to Migrate Access data to Microsoft SQL Server?
Use the SQL Server option under the ‘Database Tools’ toolbar.

You may also use the Export feature under External Data toolbar. USe ODBC

How to deploy your Access Database/Application so that your clients can not access the code (also can not modify the design)?
Use the Make ACCDE option under the ‘Database Tools’ toolbar

How to create database documentation in Access?Use the Database Documenter option under the ‘Database Tools’ toolbar. Just need to select the tables/queries/reports…..and select some other options as appropriate – childish stuff

How to separate backend (i.e. tables) from front-end (i.e. Forms)?Use the ‘Access Database’ option under the ‘Database Tools’ toolbar. It will ask you confirmation on splitting database

How to link the front end of a split database to it’s back-end?Use the Linked Table Manager under the Database Tables toolbar.
First, bring the back-end tables using the ‘Access’ option under the ‘External Data’ toolbar.

From: http://sitestree.com/?p=5223
Categories:90
Tags:
Post Data:2008-10-06 00:15:08

Shop Online: https://www.ShopForSoul.com/
(Big Data, Cloud, Security, Machine Learning): Courses: http://Training.SitesTree.com
In Bengali: http://Bangla.SaLearningSchool.com
http://SitesTree.com
8112223 Canada Inc./JustEtc: http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning)
Shop Online: https://www.ShopForSoul.com/
Medium: https://medium.com/@SayedAhmedCanada

• Approaches:
  • Access exports its own tables: write access table data in a text file then use load data command from mysql to load those data in a table. Example: select access table->right click->export. From MySQL: run the queryLOAD DATA LOCAL INFILE ‘c:student.txt’ INTO TABLE student FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘rn’;
    Syntax:

    LOAD DATA INFILE SyntaxLOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'    [REPLACE | IGNORE]    INTO TABLE tbl_name    [CHARACTER SET charset_name]    [FIELDS        [TERMINATED BY 'string']        [[OPTIONALLY] ENCLOSED BY 'char']        [ESCAPED BY 'char']    ]    [LINES        [STARTING BY 'string']        [TERMINATED BY 'string']    ]    [IGNORE number LINES]    [(col_name_or_user_var,...)]    [SET col_name = expr,...]

  • You can also use mysqlimporti command. Such as: Check the syntax here
  • For converting you can also use: Converters That Generate Intermediate Files : Some converters can create sql files based on your access database. Then use MySQL tools to convert: mysql -h some_host -p -u some_user mydb < file.sql
  • Converters That Perform Direct Data Transfer:Use Access to MySQL Tool
• A Great Resource

From: http://sitestree.com/?p=5130
Categories:90
Tags:
Post Data:2011-01-29 22:20:26

Shop Online: https://www.ShopForSoul.com/
(Big Data, Cloud, Security, Machine Learning): Courses: http://Training.SitesTree.com
In Bengali: http://Bangla.SaLearningSchool.com
http://SitesTree.com
8112223 Canada Inc./JustEtc: http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning)
Shop Online: https://www.ShopForSoul.com/
Medium: https://medium.com/@SayedAhmedCanada

What VBA can do?

• VBA can be used to automate Access
• VBA provides a true automation tool, replacing and extending anything that you can do with Access macros
• Can manipulate/utilize Access event model
• Can populate and manipulate access controls
• Provides complete control over the data and layout of reports from code

What to learn in VBA?

• Familiarize yourself with the editor to write VBA codes
• Understand Variables, Constants, and Data Types in VBA
• Learn procedures in VBA
• Common VBA Functions: Financial and date calculations, text manipulation, and mathematical functions
• Flow-of-Control Statements in VBA
• Arrays
• Objects: Built in VBA Objects
• Scope and Lifetime of variables
• VBA to automate Access forms
• Access event handling with VBA
• Populate and manipulate controls such as List and Combo boxes with VBA
• VBA to control the data and layout of reports from code
• Get information about Access objects such as forms, reports, tables, and queries with VBA
• Control and manipulate data access with ADO
• ADOX to create an entirely new Access table without ever touching the Access user interface
• Working with Text files using VBA
• VBA code in Access to manipulate applications such as Microsoft Word or Microsoft Excel
• Working with XML Files using VBA
• Using Windows API from VBA
• VBA Alternatives: Access macros or Structured Query Language (SQL)

From: http://sitestree.com/?p=5123
Categories:90
Tags:
Post Data:2006-10-24 21:07:29

Shop Online: https://www.ShopForSoul.com/
(Big Data, Cloud, Security, Machine Learning): Courses: http://Training.SitesTree.com
In Bengali: http://Bangla.SaLearningSchool.com
http://SitesTree.com
8112223 Canada Inc./JustEtc: http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning)
Shop Online: https://www.ShopForSoul.com/
Medium: https://medium.com/@SayedAhmedCanada

A great idea is half or a quarter of the game. You always don’t need a unique idea to create a successful business. Business is more about doing and taking actions than knowing or having an idea. True that if you have a unique idea with a real use to people and a huge market, your probability of success is much more.

Anyway, coming to the core point of this short-note: Having a hosting service in a cloud computing environment with different platform support can make a good business and profit to my sense. 100s and thousands of traditional hosting companies are there and many of them are making good businesses. Now, recent advancement in cloud computing can provide reliable and on-demand hosting service. Creating such hosting service can be profitable. Very recently, some companies such as Mosso and GoGrid have come up with similar hosting services. However, the market is still immature with huge potential. Traditional hosting services may die slowly and cloud based hosting may evolve (may be more appropriate for businesses). For small web-sites and web-applications with not many visitors, traditional hosting services may always be a good solution (unless cloud hosting can beat the price and quality)

Not all cloud hosting services offer hosting in multiple platforms. To my knowledge, Mosso provides hosting in .Net, ASP, and PHP platforms. A cloud hosting service with multiple/major platforms support has the potential to have good markets. A cloud hosting with .Net/ASP, J2EE, and PHP platform supporting major backend databases may be useful and in demand to many. For example, consulting companies usually work in different platforms, and have clients who need diversified hosting platforms. Such companies may be interested to buy such hosting services.

Personally, I was looking for a hosting service having all the platforms (.Net/ASP, J2EE, and PHP) support. Why? at different points of time, I worked with different platforms, I don’t want to forget them rather want to keep current with all these platforms. Such hosting services will allow to play with applications in different platforms and be keep in touch with all these platforms. Though this is feasible only when the service charge is reasonable ( in cloud computing the service charge can be made dependent on your use of the infrastructure).

IT Training institutes and Universities may be interested in such services. To my sense, it’s a great idea to provide IT and Computer Students access to such hosting platform (as institutes can get special discount when buying many licenses together, may be pricey for individual students). Students will be able to test their skills and share their skills with the whole world. Hence, there can be a huge market. I think, training institutes/ community colleges / universities provide access to software, providing access to hosting is no way less important in current times as most business/enterprise applications are now web based, service oriented, and/or distributed (through the web).

It’s not a well researched idea. At this moment, just thought – it should be something useful.

From: http://sitestree.com/?p=5203
Categories:91
Tags:
Post Data:2007-12-08 22:36:31

Shop Online: https://www.ShopForSoul.com/
(Big Data, Cloud, Security, Machine Learning): Courses: http://Training.SitesTree.com
In Bengali: http://Bangla.SaLearningSchool.com
http://SitesTree.com
8112223 Canada Inc./JustEtc: http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning)
Shop Online: https://www.ShopForSoul.com/
Medium: https://medium.com/@SayedAhmedCanada

2 Cents on AWS Lambda

[youtube https://www.youtube.com/watch?v=vmgxBXaZsTw&w=640&h=360] From: http://sitestree.com/?p=10416
Categories:Amazon Web Services
Tags:
Post Data:2017-01-21 17:52:00

Shop Online: https://www.ShopForSoul.com/
(Big Data, Cloud, Security, Machine Learning): Courses: http://Training.SitesTree.com
In Bengali: http://Bangla.SaLearningSchool.com
http://SitesTree.com
8112223 Canada Inc./JustEtc: http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning)
Shop Online: https://www.ShopForSoul.com/
Medium: https://medium.com/@SayedAhmedCanada

2 Cents on AWS Cloudformation

[youtube https://www.youtube.com/watch?v=cpbc7UVgRLQ&w=640&h=360] From: http://sitestree.com/?p=10414
Categories:Amazon Web Services
Tags:
Post Data:2017-01-21 17:49:03

Shop Online: https://www.ShopForSoul.com/
(Big Data, Cloud, Security, Machine Learning): Courses: http://Training.SitesTree.com
In Bengali: http://Bangla.SaLearningSchool.com
http://SitesTree.com
8112223 Canada Inc./JustEtc: http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning)
Shop Online: https://www.ShopForSoul.com/
Medium: https://medium.com/@SayedAhmedCanada

[youtube https://www.youtube.com/watch?v=-gJ-QS6yVJU&w=640&h=360] From: http://sitestree.com/?p=10411
Categories:Amazon Web Services
Tags:
Post Data:2017-01-07 20:58:09

Shop Online: https://www.ShopForSoul.com/
(Big Data, Cloud, Security, Machine Learning): Courses: http://Training.SitesTree.com
In Bengali: http://Bangla.SaLearningSchool.com
http://SitesTree.com
8112223 Canada Inc./JustEtc: http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning)
Shop Online: https://www.ShopForSoul.com/
Medium: https://medium.com/@SayedAhmedCanada