Archive for April, 2012

April 27th, 2012

Business Network International (BNI) Review

Is Business Network International (BNI) a worthwhile organization?
Will it benefit you?
What can they do for your business?
What are they?

BNI, in the simplest terms is an organization to help business owner network and promote business. They are very similar to numerous other groups, amongst others, your local Chamber of Commerce. The differences from other business networking groups are:

  • BNI supposedly only allows one business per category to join as a member
  • Require a minimum level of referrals per week/month from their members

 

Mandatory Production

As a member, at the weekly meetings, you are expected to give qualified referral to your fellow members.  Expected may be a little weak a term, because if you do not meet a minimal level of production, you will be kicked out of the group (and you are not reimbursed your membership fees).

This mandatory production is solely based on quantity and not quality.  Thus, the individual generating a $15.00 referral every week for the florist in the group will be in good standing within the group.  However, the individual who only gives 2 referral totaling $12,000.00 would be kicked out.  So it is all a numbers game at the end of the day.  So what you end up seeing is either bogus referrals gives just to generate numbers for the individual or partnership created between 2 or 3 member who pass referrals back and forth week after week.

 

Single Business per Category

On its face, this is a great concept.  By having only one business per business category, you ensure that you have no competition and thus all the member should priviledge you for all referrals within your category.

This is great in theory.  In my experience, this turns out not to be the reality.  This sacred BNI rule, is overlooked as long as business is being made!

 

What to think of BNI

Having risien to the highest post within a BNI chapter, as the vice-president (the president hold no real power as is merely a figure head) I will say, in my humble opinion, BNI is a business networking group like any other.  Do not go into this group thinking people there are your friends.  They may be very friendly, may say nice things to your face, but if they can make a quick buck by going behind your back, lying to you, they will.  These are business people, and typically, all they care about is $$$.  Just don’t think any otherwise.  As long as you go into this group without any premade expectation, it may be beneficial to you.

They always promote the line “Giver Gain”, but the reality that I experienced seems to disagree with the supposed fundamental philosophy.  Members care only about you to the extent that you give them referrals.  Most chapters have clicks within them, and you irk the wrong member, you may be out before you were ever in.  But then again, I guess this is sadly true of almost any group.

I was personally very disappointed with regional management here in Montreal, in the sense that they didn’t uphold their own rules at the end of the day.  I know several people that have prospered within BNI.   I also know countless people that didn’t get a single referral, or didn’t get enough to recoup their membership fees little along actually prosper from their membership.  Some of it has to do with the individual, how outgoing and social they are, and some of it has to do with product.  Most members are small business people, with little to no technical knowledge.  They are incapable (typically – there can be a few exceptions however) of providing referrals to large business, or niche markets.  They can however help promote businesses whose services and products target the average person!  This is why people such as, insurance brokers, accountants, massage therapist, … can do very well in these groups because they can easily be referred to almost anyone.  However, a software developer is a very hard referral and would probably do better focusing their marketing energies elsewhere.  In my opinion and experience, a company primarily focused on B2B is better looking elsewhere.

Note that BNI requires a substantial investment of time.  You are only allowed to miss 2 meetings a year.  Attendance in mandatory, as is production.  Meetings last a good 2 hours and then you are required to have weekly meetings with your fellow members, special events, and if you accept to take a role within the chapter you can easily take up 1-2 days a week.  Some people say that BNI will have a return of 55000$/yr.  I do not know where they get such numbers!  I can state that in 2.5 years, I netted under 3000$.  Deduct the annual fees, meal costs, cost of the commute, … and I was making less than a laborer in some third world nation.

I will also state that a colleague of mine, instead of joining a specific BNI chapter, decided to jump from chapter to chapter week after week.  This approach landed them a lot more business then I ever did as a member for 2.5+ years.  They got a lot more exposure because instead of being insulated to just those businesses within your local group, every week they met new business people.  So think hard before joining a Chapter!

Share and Enjoy

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print

CategoryUncategorized | Comment1 Comment »

Tags:
April 23rd, 2012

MS Access – VBA – Export Records to MS Word

Similarily to my post regarding exporting records to MS Excel, below is some sample code that illustrates how one can export data into a new Word document (in a table structure). The code determines the necessary rows and columns based on the table or query passed to it and then does the rest. You can easily from this simple example get into formatting fonts, etc…

'---------------------------------------------------------------------------------------
' Procedure : Export2DOC
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Export a recordset to a MS Word table in a new document
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
'
'
' Usage:
' ~~~~~~
'
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Apr-23             Initial Release
'---------------------------------------------------------------------------------------
Function Export2DOC(sQuery As String)
    Dim oWord           As Object
    Dim oWordDoc        As Object
    Dim oWordTbl        As Object
    Dim bWordOpened     As Boolean
    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
    Dim iCols           As Integer
    Dim iRecCount       As Integer
    Dim iFldCount       As Integer
    Dim i               As Integer
    Dim j               As Integer
    Const wdPrintView = 3
    Const wdWord9TableBehavior = 1
    Const wdAutoFitFixed = 0
 
    'Start Word
    On Error Resume Next
    Set oWord = GetObject("Word.Application")    'Bind to existing instance of Word

    If Err.Number <> 0 Then    'Could not get instance of Word, so create a new one
        Err.Clear
        On Error GoTo Error_Handler
        Set oWord = CreateObject("Word.application")
        bWordOpened = False
    Else    'Word was already running
        bWordOpened = True
    End If
    On Error GoTo Error_Handler
    oWord.Visible = False   'Keep Word hidden until we are done with our manipulation
    Set oWordDoc = oWord.Documents.Add   'Start a new document

    'Open our SQL Statement, Table, Query
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sQuery, dbOpenSnapshot)
    With rs
        If .RecordCount <> 0 Then
            .MoveLast   'Ensure proper count
            iRecCount = .RecordCount    'Number of records returned by the table/query
            .MoveFirst
            iFldCount = .Fields.Count   'Number of fields/columns returned by the table/query

            oWord.ActiveWindow.View.Type = wdPrintView 'Switch to print preview mode (not req&#39;d just a personal preference)
            oWord.ActiveDocument.Tables.Add Range:=oWord.Selection.Range, NumRows:=iRecCount, NumColumns:= _
                                            iFldCount, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
                                            wdAutoFitFixed
 
            Set oWordTbl = oWordDoc.Tables(1)
            'Build our Header Row
            For i = 0 To iFldCount - 1
                oWordTbl.Cell(1, i + 1) = rs.Fields(i).Name
            Next i
            'Build our data rows
            For i = 1 To iRecCount
                For j = 0 To iFldCount - 1
                    oWordTbl.Cell(i + 1, j + 1) = Nz(rs.Fields(j).Value, "")
                Next j
                .MoveNext
            Next i
        Else
            MsgBox "There are no records returned by the specified queries/SQL statement.", vbCritical + vbOKOnly, "No data to generate an Word spreadsheet with"
            GoTo Error_Handler_Exit
        End If
    End With
 
    '    oWordDoc.Close True, sFileName 'Save and close

    'Close Word if is wasn't originally running
    '    If bWordOpened = False Then
    '        oWord.Quit
    '    End If

Error_Handler_Exit:
    On Error Resume Next
    oWord.Visible = True   'Make Word visible to the user
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set oWordTbl = Nothing
    Set oWordDoc = Nothing
    Set oWord = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Export2DOC" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

You may also wish to review my MS Access Sample- Export Data to Excel and/or Word

Share and Enjoy

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print

CategoryMS Access - Word Automation, MS Access Queries, MS Access Tables, MS Access VBA Programming | CommentNo Comments »

Tags:
April 19th, 2012

MS Access – VBA – Export Records to Excel

Although there are a couple buit-in technique for exporting table & query records to Excel, mainly , if one wants to control the process a little more (edit fonts, print areas, etc) you need to create your own custom procedure to do so.  Below is a simple example to get you started in the right direction. This example uses late binding techniques and thus does not require any libraries to work!

'---------------------------------------------------------------------------------------
' Procedure : Export2XLS
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Export recordset to Excel
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
'
'
' Usage:
' ~~~~~~
' Export2XLS "qryCustomers"
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Apr-18                 Initial Release
'---------------------------------------------------------------------------------------
Function Export2XLS(sQuery As String)
    Dim oExcel          As Object
    Dim oExcelWrkBk     As Object
    Dim oExcelWrSht     As Object
    Dim bExcelOpened    As Boolean
    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
    Dim iCols           As Integer
    Const xlCenter = -4108
 
    'Start Excel
    On Error Resume Next
    Set oExcel = GetObject(, "Excel.Application")    'Bind to existing instance of Excel

    If Err.Number <> 0 Then    'Could not get instance of Excel, so create a new one
        Err.Clear
        On Error GoTo Error_Handler
        Set oExcel = CreateObject("excel.application")
        bExcelOpened = False
    Else    'Excel was already running
        bExcelOpened = True
    End If
    On Error GoTo Error_Handler
    oExcel.ScreenUpdating = False
    oExcel.Visible = False   'Keep Excel hidden until we are done with our manipulation
    Set oExcelWrkBk = oExcel.Workbooks.Add()    'Start a new workbook
    Set oExcelWrSht = oExcelWrkBk.Sheets(1)
 
    'Open our SQL Statement, Table, Query
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sQuery, dbOpenSnapshot)
    With rs
        If .RecordCount <> 0 Then
            'Build our Header
            For iCols = 0 To rs.Fields.Count - 1
                oExcelWrSht.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
            Next
            With oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), _
                                   oExcelWrSht.Cells(1, rs.Fields.Count))
                .Font.Bold = True
                .Font.ColorIndex = 2
                .Interior.ColorIndex = 1
                .HorizontalAlignment = xlCenter
            End With
            oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), _
                              oExcelWrSht.Cells(1, rs.Fields.Count)).Columns.AutoFit    'Resize our Columns based on the headings
            'Copy the data from our query into Excel
            oExcelWrSht.Range("A2").CopyFromRecordset rs
            oExcelWrSht.Range("A1").Select  'Return to the top of the page
        Else
            MsgBox "There are no records returned by the specified queries/SQL statement.", vbCritical + vbOKOnly, "No data to generate an Excel spreadsheet with"
            GoTo Error_Handler_Exit
        End If
    End With
 
    '    oExcelWrkBk.Close True, sFileName 'Save and close the generated workbook

    '    'Close excel if is wasn't originally running
    '    If bExcelOpened = False Then
    '        oExcel.Quit
    '    End If

Error_Handler_Exit:
    On Error Resume Next
    oExcel.Visible = True   'Make excel visible to the user
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set oExcelWrSht = Nothing
    Set oExcelWrkBk = Nothing
    oExcel.ScreenUpdating = True
    Set oExcel = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Export2XLS" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

You may also wish to review my MS Access Sample- Export Data to Excel and/or Word

Share and Enjoy

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print

CategoryMS Access - Excel Automation, MS Access Queries, MS Access Tables, MS Access VBA Programming | Comment7 Comments »

Tags:
April 16th, 2012

MS Access – Decompile a Database

Decompiling an Access Database

As a developer, it becomes necessary to decompile your database from time to time.  The frequency depends on a multitude of factors, but at the very least, one should decompile one’s application prior to release.  Decompilation is yet one more step a develpper has to take to ensure their database/code is clean and optimal for their end-users.  Also, keep in mind that the best approach is to decompile the database on the development machine and then recompile on the end-user machine.  This ensures that the database is compiled using the end-user’s libraries minimizing any surprises when put into production.

Before going any further on this subject, let me emphasize once more the importance of making a backup of your database prior to performing a decompile of your database!

 

One-time Decompile

The MSACCESS.EXE command line accepts several command line switches, one of which is to allow decompiling your database.  There is no other means to decompile a database.  So one merely needs to create a shortcut including the appropriate command line switch in order to decompile any given database.  The basic syntax would be:

"FullPath\MSACCESS.EXE" "FullPathAndDbNameWithExtension" /decompile

Examples:

"C:\Program Files\Microsoft Office\Office\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile

Or

"C:\Program Files (x86)\Microsoft Office\Office12\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile

 

Reusable decompilation method

On the other hand, it become tedious to create a shortcut for each database you create/manage and as such a more automated method may be a good idea to implement.

  1. Using Windows Explorer (etc.) navigate your way to your MSACCESS.EXE and the copy the file.
  2. Navigate to the %APPDATA%\ Microsoft\Windows\SendTo\ Folder
  3.  Right-Click within the folder and select Paste shortcut from the context menu
  4. Rename the shortcut as you wish, for instance MSACCESS Decompile
  5. Right-Click on the newly created shortcut and select Properties from the context menu
  6. On the Shortcut tab, edit the Target control by adding /decompile to the existing value.
    You should end up with something along the lines of:
"C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE" /decompile

Or

"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" /decompile
  1. Click Ok
  2. Close windows explorer.

Now whenever you wish to decompile a database you need only right-click on the Access database in Windows Explorer and select “Send To –> MSACCESS Decompile.

 

Special Note

Since after decompiling we always perform a compact of the database, you can perform both in one step if you’d like by appending a /compact to your /decompile command.  This is not obligatory by any means.  Personally, I prefer the granularity of performing each step myself, but should you wish to automate things a little more you’d do something along the lines of:

"C:\Program Files\Microsoft Office\Office\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile /compact

Or

"C:\Program Files (x86)\Microsoft Office\Office12\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile /compact
"C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE" /decompile /compact

Or

"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" /decompile /compact

Do note that if you do implement this variation for the reusable decompilation method, you’d probably be best to also change the name attributed to the command in step 4. to MS Access Decompile and Compact

 

Proper Steps to Follow when Decompiling an Access Database

I found the following instructions from David Fenton, and thought they fit right into this subject.

  1. Backup your database.
  2. Compact your database.
  3. Decompile your database (per either method listed above).
  4. Close that instance of Access.
  5. Open a new instance of Access and open the database you just decompiled, but BE SURE YOU BYPASS ALL STARTUP CODE (i.e., hold down the shift key). If you don’t do that, then you might as well go back to step 3 and try again, since if the startup code runs, your code will recompile before you are ready to do so.
  6. Compact the decompiled database (and be sure you hold down the shift key so that it bypasses the startup code; see #5).
  7. Open the VBE and on the Debug menu, choose COMPILE [name of project].
  8. On the file menu, save the project.
  9. Compact again.

Why are all these steps necessary?

Because you want to not just decompile the VBA, you want to make sure that all the data pages where the compiled p-code was stored are completely discarded before you recompile.

I also recommend:

  1. In the VBE options, turn off COMPILE ON DEMAND
  2. In the VBE, add the COMPILE button to your Toolbar.
  3. Compile often with that button on the toolbar, after every two or three lines of code.

Last of all, read Michael Kaplan’s article on the subject to understand it better.

Share and Enjoy

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print

CategoryMS Access, MS Access General Information, MS Access VBA Programming | CommentNo Comments »

Tags: