Module For Importing Spreadsheet to Access DB table

So I am able to successfully import my spreadsheet by adding the coding below as a module in my Access Database.  However I expect the coding is far from bullet proof, and I need to be sure it will work going forward.  As the coding came about by modifying code found on the internet I have little understanding as to if it is best suited for importing an Excel file to Access.

For instance, why am I using "Function" vs "Sub"?, Do I need to worry about error handling?  Why is the Function "Public"?  Etc.  

Where I am asking for help is to "shore up" the coding to make sure it will work going forward.  Thanks.  - Tom

Option Compare Database

Public Function ImportEmployList()

DoCmd.TransferSpreadsheet acImport, 9, "Employee Table", "C:\Users\tom\Desktop\EDPR\Database Files\Employee List.xlsx", True

End Function
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
Here's a quick answer.

Function vs. Sub?  Most likely the sample you found was set up so that it could be called from another object.  Functions typically return a value but, as in your case, they don't have to.  From another procedure you can call a function simply by using its name, e.g.  myVar = ImportEmployeList()

Public?  It's in a stand-alone module as opposed to a Form module.  The public scope make's it available to other procedure in other modules.

Error Handling?  In my opinion it's always good/best practice to have error handling.  Without it you'll get a runtime error that shuts down your app.  Better to have a handler so you can decide what to do when/if an error occurs.
OM Gang
omgangIT ManagerCommented:
I guess I should also point out that there is no reason you couldn't include the command to import the Excel workbook directly within an event procedure on a Form, e.g. Button_Click event.  Your code doesn't have to be in a public module as a function.

One reason to use a function, though, is you can call it from multiple events so making it reusable.  Here's an example.  It can be called from other parts of your application and can receive and optional filename as a parameter.  The function returns True unless an error occurs so that the calling procedure can respond accordingly.

Public Function ImportEmployList(Optional strFileName As String) As Boolean
On Error GoTo Err_ImportEmployList

    Dim strOutputFile As String
    If strFileName = "" Then 
        strOutputFile = "Employee List"
        strOutputFile = strFileName
    End If

 DoCmd.TransferSpreadsheet acImport, 9, "Employee Table", "C:\Users\tom\Desktop\EDPR\Database Files\" & strOutputFile & ".xlsx", True

ImportEmployList = True

    Exit Function

    MsgBox Err.Number & ", " & Err.Description, , "Error in Function"
    Resume Exit_ImportEmployList

 End Function 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I would not hard code the import type since it makes it harder to know what is going on.  I suggest

acSpreadsheetTypeExcel12Xml which is 10 rather than 9.  I know it makes no sense but acSpreadsheetTypeExcel12Xml (10) is the default spreadsheet format for Excel12 not, acSpreadsheetTypeExcel12 (9)

I'm surprised that you are not getting errors since 9 should produce a binary spreadsheet which has a different extension.

The sample may have been shown as a function rather than a procedure so it could be executed using the DoCmd.RunCode option from a Switchboard.  That option can only run functions.  It can't run procedures.

I also don't hard-code paths since not everyone has the same directory structure on their local PC or the same mapping on network folders.  I define default folders for different actions and have a setup option to change them.  When they are used, I present the default path for that action but allow the user to over ride it.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

tomfarrarAuthor Commented:
Hi Pat - So:

"acSpreadsheetTypeExcel12Xml which is 10 rather than 9.  I know it makes no sense but acSpreadsheetTypeExcel12Xml (10) is the default spreadsheet format for Excel12 not, acSpreadsheetTypeExcel12 (9)"

is saying replace 9 with 10, is that correct?  

Or are you suggesting I use:
acSpreadsheetTypeExcel12Xml  rather than 9?

- Tom
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As with any imported data, I'd further suggest that you import to a "staging" table, and not directly to your live data table. Importing to a staging table gives you the chance to validate the data before moving it to the live table. I can't tell you how many times I've heard "But they said the format was always gonna be the same", just after the client imported 10,000 invalid rows of data to their live production tables (and brought their business to a screeching halt).
Always use the constant when passing arguments.  The constants are much more understandable.  Who remembers what 7 is?  No one, they would have to go to the object browser and search for the object to find out what "7" means.  So ALWAYS use the constants.  And in this case I believe that the correct constant is the one with the XML suffix rather than the unsuffixed constant.

I also agree with Scott.  I generally link my external files so I can do a little cleanup first if necessary before appending the data to the permanent table.
tomfarrarAuthor Commented:
Thanks all for the comments.  I will incorporate your good thoughts..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.