Again Access... hey Access was a great database.
So if you are using access and you are thinking in how to use it from PHP or from a C/C++ app then you should check
mdbtools.
Does it work on iOS, well there is a discussion about at (http://old.nabble.com/Port-libmdb-(mdb-tools)-for-iOS-td34282658.html) but I think there might be some licensing issues.
If that is the case I would recommend writting your own version of mdbtools, Take a look at https://github.com/brianb/mdbtools/blob/master/HACKING for detail info about the file format.
Again Access... hey Access was a great database.
So if you are using access and you are thinking in how to use it from PHP or from a C/C++ app then you should check
mdbtools.
Does it work on iOS, well there is a discussion about at (http://old.nabble.com/Port-libmdb-(mdb-tools)-for-iOS-td34282658.html) but I think there might be some licensing issues.
If that is the case I would recommend writting your own version of mdbtools, Take a look at https://github.com/brianb/mdbtools/blob/master/HACKING for detail info about the file format.
This is not an scenario that I commonly find, but now with Android and with the spread of Linux base environments an devices you are just don't want to be limited to just one technology.
So I found this library which I found extremely useful and I can recommend: Jackcess
The following are some code samples from the Jackcess library
- Displaying the contents of a table:
System.out.println(Database.open(new File("my.mdb")).getTable("MyTable").display());
- Iterating through the rows of a table:
Table table = Database.open(new File("my.mdb")).getTable("MyTable");
for(Map<String, Object> row : table) {
System.out.println("Column 'a' has value: " + row.get("a"));
}
- Searching for a row with a specific column value:
Map<String, Object> row = Cursor.findRow(table, Collections.singletonMap("a", "foo"));
if(row != null) {
System.out.println("Found row where 'a' == 'foo': " + row);
} else {
System.out.println("Could not find row where 'a' == 'foo'");
}
- Creating a new table and writing data into it:
Database db = Database.create(new File("new.mdb"));
Table newTable = new TableBuilder("NewTable")
.addColumn(new ColumnBuilder("a")
.setSQLType(Types.INTEGER)
.toColumn())
.addColumn(new ColumnBuilder("b")
.setSQLType(Types.VARCHAR)
.toColumn())
.toTable(db);
newTable.addRow(1, "foo");
- Copying the contents of a JDBC ResultSet (e.g. from an external database) into a new table:
Database.open(new File("my.mdb")).copyTable("Imported", resultSet);
- Copying the contents of a CSV file into a new table:
Database.open(new File("my.mdb")).importFile("Imported2", new File("my.csv"), ",");
Remember that if you want to use it android you use some tweaks:
"The following steps will make Jackcess compatible with the Android platform.
- Set the system property "com.healthmarketscience.jackcess.brokenNio=true"
- Set the system property "com.healthmarketscience.jackcess.resourcePath=/res/raw/"
- Copy the *.txt, *.mdb, and *.accdb files from the "com/healthmarketscience/jackcess/" directory in the Jackcess jar to the "/res/raw" Android application directory.
- Before executing any Jackcess code, set the current Thread's context classloader, e.g. "Thread.currentThread().setContextClassLoader(Database.class.getClassLoader())"."
Visual Basic and Access were a great combination. They allowed the creation of small powerful apps, specially apps for data collection, or just applications that needed a small database that could run standalone.
A good option when you are upgrading your Access database and you do not need a centralized SQL Server (or that is just too much for your environment) is to migrate to other options like SQLite or SQL Server.
SQL Server has now a lot of versions (SQL Server Compact 3.5, SQL Server Compact 4.0, SQL Server Express and now even SQL Server LocalDB) a good table that compares this databases feature by feature can be located here: http://erikej.blogspot.com/2011/01/comparison-of-sql-server-compact-4-and.html)
If what you need is to support a simple, small (less that 4G) standalone database then using SQL Server Compact edition might be for you.
The main version for SQL Compact as at the moment 3.5 and 4.0.
Moving from Access to SQL Compact is not hard. There are many third party tools some free some not: http://erikej.blogspot.com/2009/04/sql-compact-3rd-party-tools.html
Primeworks tools are very easy to use, but you can also use the Microsoft SQL Server Assistant for Access (good links for this tools are in MSDN http://msdn.microsoft.com/en-us/library/hh313039.aspx and also the SSMA Blog http://blogs.msdn.com/b/ssma/)
SQL Server Compact uses the ADO.NET and OLE DB providers, and in many scenarios is just what you need. So take it as an option if you just need a replacement for MS Access
When I use SQLServer 2008 Express for my development tests, I always forget which things I have to do in order to make my SQL Server 2008 Express instance available to other machines over the networks.
So to not forget that again this are the things that you have to check to be able to acces sql server express from other machines:
1. Go to the Start Menu\All Programs\Microsoft SQL Server 2008 R2\ and run SQL Server Configuration Manager
2. In the SQL Server Configuration Manager Window, expand the tree on the left and expand the SQL Server Network configuration element on the tree. Make sure that at least TCP/IP protocol is enabled.
3. Now Click on the SQL Server Services element on the tree and make sure that the SQL Server Browser service is running. It is needed in order to make other computer able to see your server.
I was in an AccessMigration to SQL Server and i needed to open an Access97 database with security (using a system.mdw file).
It took me a while to do, but it is possible to open an Access97 Database in Access2010.
I really thing that it is a little obscure. But these are the steps:
1. First open Access2010
2. After Opening Access2010, press Ctrl + G. That will open the Microsoft Visual Basic for Applications window.
3. On the Immediate Window run this command (to run it just time the command and press Enter): DoCmd.RunCommand acCmdWorkgroupAdministrator
4. Once you run this command you will see a dialog with a title of Workgroup Administrator, press the Join Button
5. When you press the join Button a Dialog with Title Workgroup Information File will prompt Click Browse and find your System.mdw file and press OK. And press Ok on the Workgroup Administrator Dialog
6. Now close Access 2010
7. Open Windows explorer. Find your .mdb file and double click it. Access2010 will prompt for user/password and then will upgrade your database.
If during a VB6 migration you decide o take a step forward and move your databases to MS SQL
don't worry. The SQL Server team has a tool just for that.
I just don't know why it is so hard to find it in google. But the appropiate link is:
http://blogs.msdn.com/b/ssma/p/access.aspx
Also a recent article in Teched goes into all the gory details: http://technet.microsoft.com/en-us/magazine/hh334645.aspx
Good Luck and don't hesitate to contact us for any doubts.
Microsoft Access was (and is still) very used for simple data entry applications.
But Microsoft Access despite all its good is kind of an old technology and there was
no clear alternative for a simple mortal development environment… until Microsoft Lightswitch.
This series of posts illustrates the experiences of migrating Microsoft Access applications to LigthSwitch.
This post shows the result of migrating all Northwind Access example tables:
The following is Microsoft LightSwitch Solution explorer showing all Tables
And these are some of the Northwind tables in LIghtswitch
The migration process is straightforward. Text and Memo fields can be migrated to string, Numbers can be migrated to Int32 or Int64, Currency is map to Money. Yes\No can be mapped to boolean with a Value List.
There is support for specifying a caption (Display ID in Lightswitch), Description, and validation rules.
I will keep posting about Query migration, form migration and also report migration. Stay tuned.