How To Create An Access Database From Excel

How To Create An Access Database From Excel

To create an Access database from an Excel spreadsheet is simple enough to setup and apply. However, there are some preparations involved as data is being migrated from a ‘flat-file’ environment (which is what data in a spreadsheet provides) to a relational database (RDBMS – which is one of Microsoft Access’s strengths).

Take a look at this video tutorial to see some of the final steps in migrating and populating data to create an Access database from an Excel spreadsheet…

httpvh://www.youtube.com/watch?v=QVNLiAJUV-8

How To Create An Access Database From Excel

In this video which by the way is very well explained you need to have an existing database in place which means having relationships created and other objects. In fact, you do not have to have any objects in place as part of the import routines will generate new tables.

Have a plan, sketch out the data sets that will become separate tables in your Access database. Know you primary key fields (fields that will be responsible when joining tables together). Also know the data types as in the video it mentioned the contact number being stored as a number with an input mask. In some cases, it could be a text data type instead.

Creating an Access database will take some time and good planning and getting to know other important objects like queries too. In the video tutorial, it did mention the APPEND query!

create an access databaseTo learn more about Microsoft Access, check out my eBooks on how to create an Access database and in the meantime why not sign up for your
FREE copy of
how to import data into Microsoft Access.

Microsoft Access Training: Everyone Needs It! Start Training MS Access Now!

Microsoft Access Training: Everyone Needs It! Start Training MS Access Now!

microsoft access trainingIf you have dabbled with Microsoft Access but still struggle, then Microsoft Access training is essential to avoid the pitfalls and master the design, methodologies and functionality of such a powerful application.

Training MS Access has been my privilege and vocation for nearly twenty years and whichever method you use to learn Microsoft Access, there are pros and cons in the way you master the application and the time it can take.

I found quick article below to highlight some of the methods around to consider…

Is Microsoft Access Training Necessary?

There was a time when spreadsheets were sufficient for managing the massive waves of data that information Continue reading “Microsoft Access Training: Everyone Needs It! Start Training MS Access Now!”

Access Navigation Pane: How To Use And Customise Microsoft Access Navigation Pane View

Access Navigation Pane: How Customise Microsoft Access Navigation Pane View

access navigation paneWith the introduction of Access 2007 (and 2010), users will have noted not only the Fluent Ribbon Bar but the new Access Navigation Pane replacing the older database window.

This utility has some nice and simple features to further enhance how you manage your Access objects and in particular, filter and search objects when sharing an Access database.

From the standard object type including tables, queries, forms and reports, users can add as many groups and collect related objects in one view which makes it a very handy tool for collaborating workgroups and roles.

Objects are not copied (duplicated) when being assigned to a group but instead have a shortcut link pointing to the natural storage of the object type.

 

Access Navigation Pane: How To Use And Customise Microsoft Access Navigation Pane View

The following Continue reading “Access Navigation Pane: How To Use And Customise Microsoft Access Navigation Pane View”

Access Where Clause: The Difference Between Group By and Where in Microsoft Access Queries

Access Where Clause: The Difference Between Group By and the Access Where clause

Simple Access queries show detail, line by line records but summarising your data requires the Groups and Totals query. However, how you apply criteria may have an effect if you do not follow the rules. Using the Access ‘Where’ clause can be a better way to apply criteria to your query.

This video tutorial (less than 5 minutes) will explain the use for an Access Where clause query instead of using the ‘Group By’ option…

httpv://www.youtube.com/watch?v=7ucCnNjiJtI&feature=related

Access Where Clause: The Difference Between Group By and the Access Where clause

To clarify and confirm, the Access Where clause cannot be seen (or is visible) to the output of an Access query unlike the ‘Group By’ option which affects data distribution.

This is a useful attribute especially when handling complex criteria that sits in the background and is processed first before the output is called which is what the ‘Where’ clause means.access where clauseTo learn more about this type of query and other advanced Access queries, take a look at my eBook offer on More Access Database Queries.

Microsoft Access Color: How To Pick And Set The Exact Color In An Access Database

Microsoft Access Color: How To Pick And Set The Exact Color In An Access Database

First of all, I want to apologise (or apologize!). I’ve used the term Microsoft Access color in my title and not Microsoft Access colour (the correct spelling). This is because our American cousins built or at least assembled Microsoft Access together setting the captions to the local language – so please excuse the misspelt words.

Setting colour in your forms and reports is easy enough to find and apply even with looking at the ‘Custom’ tab when picking a colour by graphically picking from a colour pallete or setting the Red, Green, Blue (RGB) values. However, what if you wanted to pick an exact colour match but do not know the RGB code, then here’s a useful quick way to find the RGB value from a visual screens users wish to capture.

Microsoft Access Color: How To Pick And Set The Exact Color In An Access Database

There are several free downloadable colour utilities and one I use is via this link; Quick HTML Color Picker tool.access colorTo find the exact Access colour required and apply it to your form or report you need to:

  1. Open the downloaded utility which is a pop-up small window.

  2. Navigate to the colour you wish to capture it’s RGB code which can be any Windows based view whether it be a web page, corporate document with branded colours or any application you wish to view.

  3. Click the ‘Pick Color’ button from the pop-up window which changes you cursor to a picker icon and a preview window which follows you mouse movements.

  4. Click on the desired colour and voila! The RGB code is generated back at the pop-up tool.

  5. Now take this colour code and apply it to you designs.

Access colour is really simple and effective. There are no excuses left to developers saying colour shades applied are a near match.

If you need to know how to format forms and reports or any other control and attribute options to these objects, then my eBook offers are at your disposal (for a small fee but guaranteed).

Access Macros: There Are Now 3 Types Of Microsoft Access Macros (2010)

Access Macros: There Are Now 3 Types Of Microsoft Access Macros (2010)

With the latest version of Microsoft Access (2010), there are now 3 types of Access macros developers can utilise and release the power of this application further.

The 3 types are as follows:

  1. Macro Objects
  2. Embedded Macros
  3. Data Macros

Macro objects are avialable to all versions of Microsoft Access but version 2010 has a new interface tool plus more key commands and functionality. It is where you store the general procedures to your database.

Embedded macros were introduced from version 2007 and now is the default procedure when creating event driven procedures in forms and some reports.

Data macros which is exclusive to Access 2010 is the new and powerful to trigger procedures at the data level (via a Table) which is similar to Triggers in SQL Server.

Access Macros: There Are Now 3 Types Of Microsoft Access Macros (2010)

access macros ebook coverTo learn more about these 3 types of Access macros, why not take a sneak preview of my eBook by clicking on the link below or image.
Understanding and Using Microsoft Access Macros – preview
.

Access SQL View: An Alternative View For Your Microsoft Access Database Query

Access SQL View: An Alternative View For Your Microsoft Access Database Query

We all know that Access queries is the heart of how to drive your database and in some cases using the Access SQL view to create such queries gives you better control and a clearer understanding of the Simple Query Language (S.Q.L.).

Take a look at how to gain access to the SQL view and the visual relationship between the gird and SQL view.

httpvh://www.youtube.com/watch?v=_pE7S-lzpa4

Access SQL View: An Alternative View For Your Microsoft Access Database Query

Where do you need to use the Access SQL view? Well, there are a handful of more specialised queries that the QBE grid will not be available for and that means having to use this view.

access sql viewFor example, the UINION query is such an instance and this query can start life by using the grid interface as shown in the video tutorial and then switching to the SQL view to continue and complete the query definition. I’m not going to explain what this query is about here but you may want to consider my More Advanced Queries eBook to find out.

In the meantime, If you want to start to master the Access SQL view and write in SQL (which is pronounced ‘sequel’), You can download my FREE SQL Reference Guide which will give some pointers into writing SQL in Access.

Microsoft Access Properties: What's The 'HasModule' Property In An Access Database?

Microsoft Access Properties: What’s The ‘Has Module’ Property In An Access Database?

There are many Microsoft Access properties to set and control your Access database with and when combined in certain permutations, it changes the whole dynamics of your database and how they will perform and behave.

One property I want to mention here affects performance and the overall file size of your database and applies to the main form or report properties. The property is called ‘Has Module’.microsoft access properties

Microsoft Access Properties: What’s The ‘Has Module’ Property In An Access Database?

The ‘Has Module’ property is a Boolean value (which means either a True or False option) and by default it is set as No (False). When Continue reading “Microsoft Access Properties: What's The 'HasModule' Property In An Access Database?”

Compile Access? What About Decompiling Microsoft Access Databases Too?

Compile Access? What About Decompiling Microsoft Access Databases Too?

The Compile Access VBA and the need to decompile your code can significantly reduce the file size of your Access database and also resolves corruptions (if found).

Microsoft Access stores VBA code as both source and compiled code and sometimes some of the compiled VBA code can become corrupt meaning it needs to be flushed or rinsed clean. compile access

Therefore, running Microsoft Access in a “decompile” mode causes all the compiled VBA code to be flagged as invalid which then is removed.

Once this happens and the compiled code has been removed, the database size is also significantly reduced when the database is onece again compacted.

Compile Access? What About Decompiling Microsoft Access Databases Too – How?

You will need to open your Access database using the /decompile” switch (which is not widely documented) which will discard all the old VBA Access compile code, and leave just your Access VBA source code. You will then need to re-compile the VBA code again.

To decompile your Access database, follow these steps:

  1. From the Windows, Start, Run command line (or Windows key + R), type: msaccess.exe /decompile where msaccess.exe includes the complete path of your installed Microsoft Access database application i.e.
    “C:Program FilesMicrosoft OfficeOffice14MSACCESS.EXE” /decompile

     

  2. When Microsoft Access opens, open the database you want to decompile making sure any trusted locations and securities are enabled (where applicable).
  3. Navigate to any module (use ALT + F11) and run the Access Compile command which is found via the Debug menu and then save your changes.
  4. Go back to the database application and carry out a new Compact and Repair Database action (which varies between versions).

Now the database file size should be reduced and any strange errors related to the Access VBA code has been resolved.