Directory — Software — Blog Details for "LEARN MS-ACCESS TIPS AND TRICKS"

LEARN MS-ACCESS TIPS AND TRICKS

LEARN MS-ACCESS TIPS AND TRICKS
Learn Mail Merge in MS-Access. Learn how to enhance your Microsoft Access Forms with 3D Headings and Animated Controls. Step by step tutorial to prepare Graph Charts in Access. Use of Office Assistant in Access. Easy to implement Examples and Codes.

RSS Feed Homepage

Articles: 1, 2, 3, 4, 5, 6, 7

Articles

MS-Access Live Data in Excel
2008-08-21 20:31:00
We have already learned tricks to open and work with external data sources like Access Table, dBase Files and Excel Table without linking them permanently. We have tried VBA and Query based methods too.We have yet to try the ODBC connection string in Source Connect Str Property in a Query to open external data files with this method. Even though we can link external data into MS-Access manually we will be trying the VBA based method for linking external files later. But, before going into all that we will try a different trick, for a change in the one way traffic, so to speak, of data flow from external data sources into Access. But it is not exactly one way data flow into Access we can update external data from within Access, with or without direct links. The Data Sources that we have opened after setting Source Database and Source Connect String Properties in Queries are updatable. The changes which you make on these data sets from within MS-Access will be updated back in their...
More About: Links , Live

Database Connection String Properties
2008-08-15 08:42:00
Opening external data sources like dBase, Excel, Table from another MS-Access Database directly in VBA Code is only one of the options available to us. Those who are not comfortable with VBA have a better and much easier method available, without linking them permanently to MS-Access Database. In either case one thing is certain that we must know how to reference different external data sources correctly with Source Database Path and Connection String Values. But, these are very easy to learn.We may face little difficulty in finding ODBC (Open Database Connectivity) Connection string Syntax, but this also we can find out by going through some shortcut tricks. Let us not mix the ODBC part now with our current example and we will deal with it later.We have seen that we can display Values from Excel Cells or Range directly on MS-Access Form controls.After going through the earlier examples for opening Access Table, dBase Table and Excel tables directly in VBA, I hope at least few reade...
More About: Links , Properties

Opening Excel Database directly
2008-08-08 10:09:00
In the World of Personal Computers many Applications came and disappeared. One of the very popular Applications was WordStar (Word Processor) used under Operating Systems CP/M (Control Program for Microcomputers) and DOS (Disk Operating System) till 1992. There were dedicated Word Processing Machines in those days using WordStar and competing Software WordPerfect. Microsoft Excel is also one of the very early days Applications, since 1985, and we all know how popular it is.The first Worksheet Program Visicalc came out in 1979 with a bang and branched out into several names Supercalc, Multiplan, Lotus 1-2-3, Microsoft Excel and others putting more power into these clones than the original Worksheet Program Visicalc. (Source: Wikipedia)Like Microsoft Access, Microsoft Excel also has very powerful Automation features that can make working in Excel more interesting. I have published two Articles in articlebase.com featuring two simple macros for Automation. The links are given at th...
More About: Links , Opening , Database

Display Excel Value Directly on Form
2008-08-01 07:41:00
We have already seen how to open dBase Files directly and work with the data in VBA in the earlier Post Opening dBase File Directly. But, there are other alternatives (besides linking the files) to work with dBase Files in Access. We will explore this aspect in detail later. This Post was originally intended for a demo of opening Excel based Data Table directly and working with it in VBA Code. We will do that in the next Post. Before that we will look into some interesting and very easy trick with Excel.Why I said very easy, because you don't have to struggle with VBA Code to do this. I know you will be happier if you don't have to work with my spaghetti VBA Code. So, I thought we will take a break and do something different and interesting before continuing with databases.You got the clue from the title itself. That's right, you can display Values from Excel Worksheet directly on Microsoft Access Form s. We will go straight into a Demo.Open Microsoft Excel.Enter your ...
More About: Links , Display

Opening dBase Files Directly
2008-07-24 17:32:00
In the earlier Article Opening External Data Sources we have learned how to open another Microsoft Access Database and work with its Tables using Code. I have made a revision of the Code given there to display the Database Names loaded in WorkSpace(0) also on top of the list of Employee Names in the MsgBox. The Revised Code is given below. You may copy and replace the earlier Code and try them out.Public Sub OpenSecondDatabase()Dim wsp As Workspace, db As DatabaseDim rst As Recordset, msg As String, x As IntegerDim dbcount As IntegerSet wsp = DBEngine.Workspaces(0)Set db = wsp.OpenDatabase("c:Program Files Microsoft OfficeOffice11samplesNorthwind.mdb") Set rst = db.OpenRecordset("Employees", dbOpenDynaset)dbcount = wsp.Databases.Count - 1msg = ""For x = 0 To dbcount msg = msg & "Database(" & x + 1 & ") " & Dir(wsp.Databases(x).Name) & vbCrNextmsg = msg & vbCrWith rstx = 1Do While x < 6 And Not .EOF m...
More About: Links

Opening External Data Sources
2008-07-18 09:02:00
Linking of external data sources like dBase, Excel or Tables from another Microsoft Access Data base, in a Back-End, Front-End scenario, is the most common and efficient method to work with data, besides Tables from within the Database. If such Data Sources are linked with Access database then it is easy to build queries on them to process and prepare customized Reports, or Design Forms to Add/Modify Data from within Access. The Source data can remain in the parent Application for updating information. We can work with the linked Tables with the same ease of using another Table of MS-Access except modifying the structure of the linked Table.To link a Table manually, highlight Get External Data in File Menu and select Link Tables? from the displayed Menu. The Common Dialog Box will be displayed and you can browse to the location of another MS-Access Database, dBase File or Excel File and select it. They will expose Objects that can be linked to the MS-Access Database.If the sourc...
More About: Links , Opening

PIE CHART OBJECT AND VBA
2008-07-10 20:42:00
This is the continuation of a discussion on working with Chart Object in VBA. We have created a small Table and a Report with a Chart Object on it, in the earlier Post Working with Chart Object in VBA and gone through few simple Property changes on the Chart Object to get prepared for the Demo of the VBA Programs. Please go through Steps 1 to 7 explained in the earliest Post, by following the link above, and continue.You may have a look at the Column Chart and VBA topic at your convenience as well.If you have already tried out the Code given in the earlier Posts then you are familiar with most of the Code Segments that you see here and will have no difficulty in understanding them. The difference is only in the formatting method of individual PIE Slices and few property changes also. In this example we will try three types of Pie Charts: 1. 3D Pie, 2. 3D Pie Exploded, 3. Pie of Pie. Copy and Paste the Code given below into a Global Module in your Database.Public Function PieC...

Column Chart and VBA
2008-07-04 08:24:00
In our earlier example, Working with Graph Chart in VBA, we have made preparations to try out Charts with VBA and did sample Runs for three Types of Charts viz. Column Clustered, Line Chart and Pie Chart. If you have landed on this page for the first time you may go to the earlier Post by clicking on the Link above. Go through the simple preparations explained there, by following Steps 2 to 7, to try out the Code given here.Earlier, we could change the Chart type through Code to one of three Types with common property settings that can be applied to all the three. But we have conditionally skipped some properties not relevant to PIE Chart, like Axis Titles. We will deal with PIE Chart separately later and will see how to format individual Pie Slices and change other settings as well.Here, we will try only one Category of Chart viz. Column Type and will run the Code for 4 different designs. This type of Chart with vertical Bars is the most commonly used Charts and loosely referr...

Working with Chart Object in VBA
2008-06-26 22:21:00
Working with Chart Object in VBA is a complicated matter, but interesting too. Once the code is developed it is very useful and can be used across Applications to prepare Graph Charts with the same look and feel in minutes. There are several Chart Types, Chart Areas and numerous Object Groups and Objects to work with. The Range of values that can go into them, are too many. There are Chart Type specific properties and value-ranges, like the Auto Scaling property that can be used only for 3D Charts. I did some searching, experimenting and created a VBA Routine that can modify the Graph Chart elements with beautiful formatting to my liking. I hope you will like it too. The sample run result image is given below.Resizing of Graph Chart and formatting of several elements, like Chart Title, Axis Titles, Chart Area, Plot Area and Chart Bars with Gradient color, Legends, Grid Lines, Data Labels and alignment of Data Labels are done with VBA Code. Placing a Chart Object on the Report wi...
More About: Working

Linking with IBM AS400 Tables
2008-06-19 17:13:00
We can convert IBM iSeries (AS400) DB2 Table into dBase Format, download and Link or Import into MS-Access Databases. The downloading procedure must be created and run from AS400 Menus. If it requires several steps before the output file being transferred to local drive then it can be automated with Macro Scripts. The key strokes can be recorded and modified in such a way that the target file goes to a specific location on the Local Machine with a predefined name that remains linked with the Microsoft Access Database. If you have sufficient Access Privileges to iSeries (AS400) Main Frame Tables you can Link them directly into your MS-Access Database. We are going to look into this method with ODBC based procedure shortly.Normally, Reports are generated from AS400 and provided to Users as Hard Copies, or converted into Report Spool File in Text Form, if Soft Copy is requested for. We can download this File either in Text form or upload it into Microsoft Excel (may not Parse the dat...
More About: Links , Linking

REPAIRING COMPACTING DATABASE WITH VBA
2008-06-12 17:28:00
As far as Microsoft Access is concerned compacting the Database is an essential function to keep the Database size to the minimum. When you work with the Database MS-Access creates temporary work-objects within the Database resulting the file size to expand. We can display some of these temporary objects with VBA Code. Copy and paste the following Code into the Global Module of a Database that was not compacted recently. Press Ctrl+G to open the Debug Window (Immediate Window), click somewhere within the code and press F5 to execute it.Public Sub tmpObjects()Dim ctr As Container, doc As DocumentDim cdb As Database, itemSet cdb = CurrentDbSet ctr = db.Containers("tables")For Each item In ctr.Documents If Left(item.Name, 1) = "~ " Then Debug.Print item.Name End IfNextSet db = NothingSet ctr = NothingEnd SubYou will find a listing of temporary work-files, similar to the sample lines given below, in the Debug Window:~sq_cBRVISIT~sq_cBR~sq_cBRVISIT~sq _cBRV...

Database Open Close Event Alerts
2008-06-05 16:39:00
Your Application is installed on a Network and shared by different Users. You would like to monitor the activities of this Database , like which User Open s or Close s it and at what time? Or your database is not a Secured one (which means anybody can open it) and installed on Network for the benefit of few trusted Users. You already knew their Workstation IDs/Network IDs, but you would like to know some one else opens it to explore and when they do catch them red handed. Warn the unauthorized intruder immediately by sending a message to his Workstation informing that he is not in the permitted zone and request him to close it immediately. He will know that you are on watch over your Database and will not attempt a second time, when you are around!. If he responded positively by closing the Database then you will get an Alert message from the Database transmitted by the Closing Event .You are doing some maintenance work on the Database and you would like to know if some one opens it ...

List Box and Date Part Two
2008-05-29 21:27:00
Continued from LIST BOX AND DATE PART ONEWe have seen that when working with date values in List Boxes we have to convert the selected value into one form or the other before they can be used for data processing tasks. We will try two more examples here with different date expressions. In all these cases the output is same but data selection parameters are used differently. We can Filter Data using Data Range parameters i.e. by entering Start-Date in a Text Box and End-Date in another, on a Report Parameter Screen, or by entering these values directly into Parameter Queries to filter data from the Source Table or Other Queries. But here we are trying to do it differently and get some familiarity in working with Date related expressions.Open the Form LISTBOXDATE that we have created in the earlier example in Design View.Make a copy of the List Box and Paste it in the same area of the Form. Drag and place it to the right side of the Combo Box. See the Sample image given below:Pla...
More About: Part

List Box and Date Part One
2008-05-22 14:58:00
One of our regular readers from South Africa, Mr. Nick Els, has made a suggestion to bring out an Article on usages of List Boxes with Date related settings. This is the First Part of a two part Series on this particular Topic. You may go through the earlier Posts: Selected List Box Items and Dynamic Query and Create List from Another List Box.Descriptive names of Months or its Numeral form combined with Year Values from Combo Boxes or List Boxes can be used in a variety of ways for comparing with Date Field values in Queries for filtering data. I mentioned variety because all methods require creating expressions at Query level or in Text Boxes on Forms to reformat the values into a compatible form before they are being compared. We will split this Article into two parts instead of overcrowding with all of them here.List Boxes can be created on Data Entry Forms, Main Switchboards (Control Screen) for opening Forms or Reports or on Report Parameter Forms for using in Queries for Data...

Create List from another Listbox.
2008-05-15 18:12:00
stumble it!If you have not tried the earlier example Selected List Box Items and Dynamic Query please visit that page and try it out before continuing with this. The Table and Queries created for that example are used here also.I will give you briefly what you should do before continuing. Import the Orders Table from the Northwind.mdb sample database. Create two Select Queries by copying and pasting the following SQL String into the SQL editing window and save the Queries with the names as suggested below.Query : OrdersinQSELECT Orders.*FROM OrdersWHERE ((([Orders].[OrderDate]) Between #5/1/1998# And #5/31/1998#));Query : OrdersOUTQSELECT OrdersINQ.*FROM OrdersINQWHERE (((OrdersINQ.OrderID) In (11067)));In the earlier example we have used a method by highlighting items and using them as criterion for the output Query. Here, we are using different methods for the same result in a variety of ways.When you create a Form with Microsoft Access Form Wizard it will give you a list of Fie...

More articles from this author:
1, 2, 3, 4, 5, 6, 7