Archive for category Access

FW: Using Database Library Files in Your Access Application

Andrew Wrigley based in Argentina and the UK wrote an really good article to describe how to use Database Library Files in Access application. I found it is very useful and solved a lot of my problem.

Below is the link to the nice articel:


Migration Access database to MySQL

A couple of years ago, MySQL provided MySQL GUI Tools Bundle. Somehow, they decided to stop working on the bundle and started developing MySQL WorkBench. Now the Workbench works pretty good and the functionality is getting rich. I use it to manage the MySQL database all the time. However, it was a great pain when you want to migrate your Microsoft Access database to your MySQL server. The Workbench has the migration functionality. I tried several times and none of them worked. So this new migration function in the Workbench still need time to get mature.

In the meanwhile, there are two solutions for migrating Access database to a MySQL server.

1) Use the MySQL GUI Tools Bundle. This one includes a specific tool for migrate database. I used it before and was very happy about the results. If you have newer version of MS Access database, you have to save the database to Access 2003 format with a file extension .mdb. The GUI Tool – migration can successfully achieve the goal with no problem. You can find the MySQL GUI Tools Bundles through the following link:

2) Use a freeware to achieve the same goal. The tool called Access to MySQL. Below is the introduction from the developer’s website.

  • Wizard interface.
  • Transfer data directly from one server to another.
  • Create a dump file.
  • Select tables to transfer.
  • Select fields to transfer.
  • Transfer password protected databases.
  • Supports both shared security and user-level security.
  • Optional transfer of indexes.
  • Optional transfer of records.
  • Optional transfer of default values in field definitions.
  • Identifies and transfers auto number field types.
  • Command line interface.
  • Easy install, uninstall and upgrade.

I did use it to migrate the newer version of MS Access database. It works like a charm. You can download the application from the following site:


Tags: , ,

Solve an issue encountered when SpotFire read Microsoft Access database


SpotFire read data from an Access database for generating graphs. An error message always pops up and says:

Error message: The required OLE DB provider (Microsoft.ACE.OLEDB.12.0) for Microsoft Access is not installed 


Download Microsoft Access Database Engine 2010 Redistributable package at the following link:

If your OS is 64 bit version of Windows 7, please download and install the x64  version (accesdatabaseengine_x64.exe).

There is a trick for installing the 64 bit version on the machine with MS Office 2010 32bit version installed. Install the 64-bit runtime files using the “/passive” switch like this:

“AccessDatabaseEngine_X64.exe /passive”

Of course, in the Windows command line window. It works like a charm.




Tags: , , , ,

A skeleton of a MS Access VBA function operating data tables

Use Microsoft Access VBA can do all sorts of database operations. The following is the framework of a VBA subroutine. It includes all VBA database operation mechanisms. You can just borrow it and adapt it to your situation.

Sub FillAField(tablename)
    Dim db As Database
    Dim db_record As DAO.Recordset
    Dim sql As String
    Dim id As String
    Dim field2 As Long

    'Open connection to current Access database
    Set db = CurrentDb()

    ' Create SQL statement to retrieve value from GST table
    ' replace to your sql statement
    sql = "select field1, field2 from " & tablename

    Set db_record = db.OpenRecordset(sql)
    ' move the record pointer
    'Retrieve value if data is found
    Do While Not db_record.EOF
        id = db_record("id")
        field2 = db_record("field2")
        ' process the field2 and generate some data for another field
        Fields = Split(field2, "_")
        theyear = Fields(UBound(Fields))
        sql = "update " & tablename & " set year = " & theyear & " where id=" & id & ";"
        ' update data in the same table
        db.Execute sql
        ' move the record pointer

    Set map_record = Nothing
End Sub

Tags: , , ,

SQL in MS Access 2007

Microsoft Access 2007 is a powerful tool to handle data, large or small, simple or complex. Recently I was forced to use it to do something because the data source was stored in an Access database. It comes out that it just inspired me to learn a little bit more about the new functions of the Access and applied SQL skills to operate the database and manipulate the data. Its SQL is little bit different from what I am familiar with, the MySQL SQL language. Even though, I was able to manipulate the data quickly by the help from googling the internet. The following lists some examples that I used and little bit different from what I know. They took me a little bit time but worth to do it.

Put select data into a new table

	SELECT field1, field2, ...
	INTO newtable
	FROM oldtable;

Insert select data into a existing table

	INSERT INTO table1 (field1, field2, ... )
	SELECT field1, field2, ...
	FROM table2;

Update with string function

	UPDATE table1 SET field3 =  field1 & field2
	WHERE left(field2,2) = field1;

Update data from another table

	UPDATE table1 a INNER JOIN table2 b ON
	SET field1 = a.field1/b.field2;

Select from 2+ tables (inner join)

	SELECT a.field1, a.field2, ..., b.field1, b.field2, ...
	FROM table1 a INNER JOIN table2 b on a.id2 = b.id2
	GROUP BY a.field1, a.field2;

Tags: , , , , , , ,