Monday, March 30, 2009

Preparing Your Database For SQL Server

Following on from my blog earlier on the month on learning about SQL Server, I have written an article discusses things you can tackle before upsizing your tables to SQLServer. I do this because once you are in an environment where you have Access as a front-end and a SQL Server back-end, things are going to get more complicated. Another good thing about this article is that it will probably improve your database model and reduce the size of your database at the same time.

Sunday, March 29, 2009

Using the Currency field data type internationally

Today I was reminded of the age old problem of the Currency field datatype in Access not reflecting the current users regional formatting settings as set in the Operating System.

The problem is that by default, Access stores the format as specified on the developers machine and does not change the formatting if an end user has a different currency format set in their regional settings.

Some would see this as expected behaviour. Some would see this as a flaw.

If you consider this is a flaw, fortunately there is a simple fix. Please note: The solution discussed in this article only works for tables, queries and _bound_ form/report controls

Wayne Phillips

Tuesday, March 17, 2009

Get ready for the Economic Upturn

If you are in the unfortunate position of not having much paid work on in this global economic downturn, now is the time to upgrade your skills because if you are a skilled developer who can help people solve business problems, positive times will not be far away. So as it is likely that you are an Access practitioner, I suggest that the best way to improve your skills is to push into areas where Access provides a stepping stone. To me SQL Server is one of those areas and here is what you have to do.

  1. Identify the database that you most proud off and convert it to run with a SQL Server backend.

  2. Do not stop when things get hard, solve all the problems.

  3. It would be best if you then started using that database in your day to day work

  4. When that works, start converting the Access queries to Views, Pass Thru Queries and Stored Procedures

One thing you shouldn't do though is become 100% immersed in your unpaid projects because its always better to write software that people need. For this you will need to make sure that you attend conferences, participate in online forums and contact all the people that can help you get back into the thick of things. Good luck to you in your endeavours.

Garry Robinson from Sydney, Australia

Brooks Writes: What are the choices for moving forms from Access to SS2005? Is there a form designer built in? If so, what is it called? Or do you need to use VB.NET in Visual Studio to do forms?

Garry: For forms you will need a .NET application and for reports SQL Reporting services are one choice. Initially though concern yourself only with getting your application to work with Linked SQL Server tables. Then move your queries to the backend then you will be in a position to worry about .Net forms etc. We sell a .NET convertor here

Sunday, March 15, 2009

Jet MDB security - under the hood

Due to recent discussions in the newsgroup, it has become apparent that some people do not properly understand how Jet implements the various levels of security features that it offers.

Here I intend to explain everything in a clearer format. I won't be discussing Access-specific security features here (such as MDE file protection) - nor will I be providing the actual significant implementation details of the security methods offered – I will simply explain in brief form how each method works, ‘under the hood’.


Wayne Phillips

PS. Garry has a book on Access protection and security