Category Archives: Access

Automate Your Import Process in Access

Import_Files_IHow about 1,2 million rows of data?
Well there you are, Excel specialist and forced to import a text file containing well over 1,2 million rows of data. You do not have the Power BI tools installed nor do you know how to work with those tools. YouTube? right have a look how it is done and repeat the steps. You will repeat the steps but you have no clue as to what you are doing. Why not import the data into Microsoft Access? You can apply the same functionality as you do in Excel when working with large amounts of data. Two million records? Just dunk it into Access and you are good to go. In this blog post I will describe the process of importing well over 1 million rows of data into Microsoft Access. You can see this as a monthly task so to be more specific we are going to save the steps so we can repeat them next month and the month after that.

Importing a flat file with over 1,2 million rows of data in Microsoft Access
So the goal is to import the flat file into an Access table and automate this process so we can repeat the steps every month. The first step would be to create a new blank database (no web stuff just a regular desktop database). When done you will be presented with a new table ready to be used. Close this table and don’t save it. Now on the ribbon click the External Data tab and from the Import and Link group choose Text File:

Import_Files_II

This will present you with an import wizard dialog.

Import_Files_III

We are going to follow the steps from the wizard but at some point we are going to take a side step. Let’s first determine what we are confronted with:

  • We need to provide the location of the .txt file. That’s the easy part. Just browse to that specific directory using the Browse button
  • Next you have three options to choose from. We are on the hunt for importing data into our database so we choose option 1. examine the other options so you know what is available out there.

When you have confirmed the .txt file you want to import click the OK button. You will be presented with the next step from the wizard.

Import_Files_IV

This is where we need to take some time to do some checking. Access tells you that it seems that your data is in a ‘Delimited’ format. It assumed right this time so we click Next. Here is the next step using the wizard.

Import_Files_V

Another dialog we have to check…
First we determine the delimiter used in our text file. In this case the ‘Tab’ delimiter is correct. Most of the users prefer a semicolon (;) which is also available. My data has a first row that contains header information so we check the check box “First Row Contains Field Names”. Now instead of clicking the Next button I am going to click the Advanced… button. Why? because I want to save the import steps I am making for later use. Here is the dialog which will pop up when you have clicked “Advanced…”

Import_Files_VI

In this dialog we determine the data types for the various fields from the text files. Let’s take a look at the different choices we have to make:

  • Language: defaulted to your installation (mine is dutch)
  • Decimal symbol: important for Europeans (US has a dot here)
  • File format: as determined in the previous step
  • Field information: because we are creating a File Import Specification which we would like to save we have to rename the field names to the appropriate names.
    TIP: do not press Enter after you have renamed Field1 to something else. Entering will close the dialog!!
  • I don’t want to import the field LineTotal so I will mark that field as ‘skip’. As you can see I have renamed all field names accordingly

Import_Files_VII

Saving the Import Specification
When you have renamed all fields you click the “Save As…” button. With this button you save all the parameters you have set during the wizard. So next time Access will know the field names and data types you have defined.

Import_Files_VIII

Save you import specification by giving it a name. Now follow the regular steps to finish the wizard (we don’t need to take any further action, we don’t need a primary key so you can set those to no primary key. Choose a table name at the and, and your table will be created containing the data from the .txt file

Import_Files_XIII

Save the import steps?
At the end you will be asked if you want to save these import steps. Confirm and click OK after you have given it an appropriate name. Why do we need to save the steps twice? I am going to explain right now..

Import_Files_IX

Two steps to automate
Developers will not use the file import steps you have saved as the image above shows. They will use the File Import Specification file we have created earlier. Strange thing is that this file can not be found in the combo box when you creat an automation macro. Let me show you what I mean:

Automate this process using the autoexec macro
Every Access developer knows that you can create an automation process by naming your macro “autoexec”. So we are going to create a macro which will fire the import steps as described above using the saved import steps. From the ribbon choose create macro. You will be presented with the following screen:

Import_Files_XIV

Activate additional options
By default you will not see all available options. You have to click the “Show All Actions” button. This will reveal more available options with an exclamation mark in front of it. Can you figure out what that means? Anyway we choose the option ImportExportData and on the center screen we are presented with a combo box to choose from. Remember what I said about the Import Specification File we save during the wizard? You won’t find that file in the combo box here. What you see here is the filename we presented right at the end of the wizard. Now save the macro and name it “autoexec”. Delete the table you created earlier and close and reopen the database. Your table will be created automatically for you.

Automate from code (VBA)
This step takes a little more work but gives just that little more flexibility. First create a module (Ribbon – Create module). Now type the following bit of code as you see in the image:

Import_Files_XV

 

Import_Files_XVI

Explanation
Although it doesn’t return anything the macro editor expects a function here so we create a function. The first line of code tells Access to delete the table. The second line of code tells Access it should import the text file from that specific location using the Import Specification we have saved during the wizard steps. By not deleting the table this piece of code will do the clean up first for you. Save the module (give it a name you like)

Now create the macro
Instead of using ImportExport commands choose the Macro commands option and look for RunCode. In the text box in the center of the screen type in your function name and you are good to go. Name your macro (autoexec would be great, but be aware that you already have a autoexec macro from the previous steps. Rename that for now to something different).

Import_Files_XVII

Close and reopen the database and you data will be imported accordingly.

Concluding: If Excel hasn’t got the room to fit your needs Microsoft Access might be able to offer you just that little more. If you have imported the data into the table you will see that Access also offers all filter capabilities you have in Excel. Why not use both?

 

 

 

Advertisements

Access: Tuning Your Client Settings

Access_MaintenanceMicrosoft Access is a great application for creating databases. It is also used as a tool for just storing data. Where Excel will limit you to just over a million rows this application has no issues whatsoever to store a couple of million rows. In that state Access just serves as a backend. I enjoy seeing people create databases in Microsoft Access. From small community databases to extensive company databases. It is usually the application to start with before taking the next step to for example SQL Server.

Split Database
When done creating your database it is good practice to split your database. You will get a front end and a back-end. The front end contains the forms, queries, reports, macros and modules. The back-end contains the tables. The front end contains links to the tables of the backend. Now when multiple users need to use your database you provide them with a copy of your front end. You must also remember that users need to have a copy of Microsoft Access installed on their computer. In all other cases you either have to create an App or create a runtime version. What you need to know about the client application is that 9 out of 10 times this is installed via the IT Department. Users don’t know or don’t care about the environment they are going to use your database in. They should but that’s where your advice comes into play.

Client Settings
When end users develop databases they often use the environment that is presented to them. As I told you Access is a great application but also has a lot of settings that can be turned of when your database is ready to be used. So let’s have a look at the settings when you start creating a database. For this example I am using Access 2013 but it also applies to previous versions. Remember we are talking about client settings for a single user here.

Current database Options
Access_CurrentDBWow that are a lot of options to choose from. Which ones are good and more important what does it mean when I disable an option? Let’s have a closer look.

  1. Use Access Special Keys
    This option gives the option to enter the VBA screen via the ALT-F11 keys combination. It also provides the option to show the navigation pane when pressing F11. So if you think users should not be able to do so, uncheck this option.
  2. Compact on Close
    This must ne the favorite option for a lot of people. My advice would be to compact and repair your database manually and not by setting this option to true. When working with multiple users Access will try to compact the database when someone closes his front end. When other users are still using the shared backend Access stops the compacting process because that would conflict with the current usage of the database. This option causes quite some corruption to the various databases. So uncheck! There is a button for it and use that when you know no one is using the database.
  3. Use windows themed controls on forms? That’s a personal choice. If checked Access will always check first to see if the used theme is present (takes time!). My advice… uncheck!
  4. Enable layout view. Great if you are developing databases. When done uncheck the end-user only needs the final view and not the layout view! So uncheck.
  5. Enable design changes for tables in Datasheet View. It surprises me that this option is actually there when end users are working with a finished database. Of course you have to uncheck this one. Nobody makes changes to the database but the developer.

Well that’s the first step. Now for the next step. In this paragraph i will point out the various options you might ‘uncheck’. See if you can find them in the Access – Options dialog.

  • Auto index on Import/Create
    This is a notorious option in Access. I wrote a special post on this which you can find here. It’s best to clear all the options you find there. Indexing should be done manually but that’s my opinion.
  • Enable Error Checking. This is a great option while you are developing the database. But when the database is done uncheck this option on the client setting.
  • Proofing
    I never use proofing in a database environment. But this could be a good option for the end-user. So your choice here.
  • Client settings
    – Display options. Check this one very carefully. Show animations is not one essential part of the database. So uncheck. Show Action Tags… (). This option offers additional choices when user works in a form. So if desired leave it on otherwise uncheck. (Add to contacts, show map for address etc).
  • Name Auto Correct options
    This is also a famous option in Access. Wow this changes all my field changes in my queries and forms if I made changes to my table design. Yes it does and the big issue here is that it does not change your VBA added functionality. Furthermore a background process has to check this every time just to make sure no table design changes have been made. So for the developer yes keep it checked. Once finished please turn this off. You will see a significant change in performance when Access does not have to check this.

Options, options and more options
Microsoft Office applications are great. One downside can be the gazillion settings that are available to the end users. For databases all these options can cause performance issues. So when deploying you database make sure the client settings for your end users are optimized for working with your database. Help them… this is a one time investment which will save a lot of issues later on.

 

Access Webdatabases New Kid On The Block

Access Webdatabase

The Access Webdatabase functionality came available with the release of Office 2010. Wow finally we can create databases for the web was my initial thought. It did not take long to realize that this was a wrong assumption. One of the prerequisites was SharePoint 2010 – Access Services. Hmm i did not have SharePoint on my pc so i was not able to build my first web database. Sure there were third parties offering hosted environments to create Access Webdatabases but i registered to late to take advantage of their free offer. After a certain period you had to pay for their services. I asked my company if it was possible to use some space in our SharePoint environment to play around with Access Services. They agreed and i was able to build my first webdatabase.

A new learning curve
I soon discovered that creating webdatabases is a whole new experience. The embedded macro, the macro editor, the switchboard everything i almost never used in my Access development became important. What? Why would you want to do it like that when you can do it like this… everytime this question popped up in my mind when creating my database. During that process i forgot one major part and that was Access Services (on SharePoint 2010). It was not that Access could not do it the way i wanted it but it was SharePoint restricting me constantly. I decided to start all over again and tried to find out what could be done instead of what i would like. First steps: create a table and record validation. In this blogpost i will write about creating a table for a webdatabase. In future post i will write about the process of creating a simple webdatabase for SharePoint. So if you have the possibility join me in this adventure of creating a table for a webdatabase to publish later.

Continue reading

Microsoft Access 2010 VBA Programming Inside Out

Inside Out Series
I am a great fan of the Inside Out series. They give a great in depth view of the specific application. Take a peek at the book section and you will see that there are more book from the Inside series there. This one is the next step when you are done with the Access 2010 Inside Out by Jeff Conrad and John Viescas. This is a comprehensive book and together with this addition you are ready to tackle any database challenge with Microsoft Access.

Sample Chapter
I really like the possibility to check out a sample chapter on the MS Press blog. This way you get a inside view of the style of writing by the author and the subject he’s writing about. For this book the sampe chapter is about using SQL Azure. How cool is that. The next step in your programming career and you get to read a free chapter about it. You can read it here

Yep another one for the bookshelf or eReader

Happy reading 🙂

Access: Add a Webslice to a Webcontrol

Websclice in a webcontrol

In this post i’ll show you how to add a webslice to a webcontrol in Access 2010. I got the idea watching the video from Albert Kallal. He created a webdatabase called “Room Bookers”. In this database Albert used various techniques to try out the new web database technology added in the Access 2010 version.

Now one of the features I liked was the fact that he imlpemented a webcontrol showing the weather in a certain place. That’s what I wanted to try. The webcontrol is a control you can add to your form in Access. You can use it (among other things) to show internetpages or webslicers. Webslicers are designated pieces which are a part of a website. In the weather part I just wanted a piece of the website to use in my Access form. So here we go.

Add the webcontrol to the form

First create an empty db with one form. On this form add a webcontrol which you can find in the ribbon under controls. After you click the webcontrol on the form you’ll be prompted to enter the hyperlink or webaddress. In this case I added the webaddress from Bing. The webcontrol got filled with the complete website and that’s not what I wanted. I just wanted the little piece showing me the weather. So how did Albert do this? He used a webslice instead of a whole website. Ok so it’s of to create a webslice.
Continue reading

Nasty Autonumber Bug!

Pesky autonumber bug (2007 & 2010)
It has been around for a while; the autonumber bug. So far it hasn’t caused any troubles and Microsoft has a simple workaround for it. Before entering data in the table save the table first. So what is the autonumber bug exactly. When you create a table in datasheetview your first field will be a autonumber field. After you create your first fieldname there is no problem yet. When you create your second field there is still no problem. The problem arises when you start entering data in the records when the table has not been saved yet. When you start entering data in the first custom field you will see that the autonumber is incremented by one. If you enter data in the second field the autonumber is incremented by one again.

Tables in webdatabases
When creating tables for webdatabases this issue might arise more then when creating rich client tables because most of the times you will be designing those in designview. So moral to this post is to save your table first before starting data entry! Because this bug will only show up in datasheetview you will find this bug in Access 2007 and 2010.

Use Autonumbers but do not use them for anything else!
One of those questions in the communities has been “How can I reset my autonumber?”. Most of the times you will see replies which start with “Why would you want to do that?”. The autonumber field should only be used for making the record unique within the table. Because autonumber can be unpredictable it is not wise to use it for any other purpose than that. But to answer the question; is it possible? Yes it is and if you want to reset it you know what the answer will be 😉

Maus

Be Aware of the unnecessary indexes

Access IconMicrosoft Access
Access is one of those applications used by millions of people to create databases. A great application with maximum flexibility. I have met a lot of people who develop databases in Access. Looking at their work I discovered that a lot of people do not realize that there are a lot of unnecessary indexes on their tables. If I ask them about that they often tell me that they did not put that specific index on that specific field. So I tell them about the Access feature that does that for them without them knowing it. Let me explain.

Table design
If you create a table in Access (design view is best to see it happen) you add a fieldname and set a datatype. If you look at the field properties at the bottom of the screen you can see if that field will be indexed or not. Now here’s the catch. If your fieldname ends on any of the following letters Access will autmatically add an index to that field. [ID], [Code], [Num], [Key] This can vary depending on the language you are using.

Let’s take a look at the following scenario:

We design a table with just one field something stupid. So we go to table design and add the first field [ID] set to autonumber. We add another field called [Stupid] and set this to a Yes/No data type. If you do that you will see that Access actually adds an index to the [Stupid] field because the name of this field ends on [ID]. If you don’t know that Access will do this then you will end up with a lot of unnecessary indexes in your table.

You can try this yourself (my office is set to English). Add the following fields to the table in design: Liquid – Yes/No datatype, Fluid – Yes/No datatype, PrivatePostalCode – Text datatype. You will see that you have actually got three fields with indexes without you setting them.

How to avoid this?
Ok you made your point so how can we avoid this? The first remedy would of course be think about your fieldnames carefully. If you do need to add fields which end with the designated letters then go to the Access options screen and disable or remove the options there.

  • Open Access
  • File – Options – Object Designers (view settings in the picture) and change or remove accordingly

In 2007 you will go to Office button – Access Options – Object Designers. In Earlier versions go to “Tools – Options – Table design”.

So concluding: if you don’t want any indexes you did not set check your tables after you are done designing them. Remove any unwanted indexes or change your settings in the Options settings.

Happy programming 🙂