Access VBA Code used for Loading data from CSV file

Hi ,

My VBA is of average standard but not great.
I inherited some code which is beyond my understanding.

In simple terms the code uploads data (esp a date field) from a CSV file in tblBankAIB

The format of the date I need is dd/mm/yyyy - However, the upload seems to mix dd/mm/yyyy and mm/dd/yyyy.

I believe that the code in mdlStatementsUpload is the relevant bit.
How do I adjust this code to ensure that the date format dd/mm/yyyy is what loads into tblBankAIB

This 53 second video shows the load process and error.

Thanks for help!
Patrick O'DeaAsked:
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.

Patrick O'DeaAuthor Commented:
See the text in asterisks .... this may be the code that needs to be adjusted??

For i = 1 To lRows
    sText = Trim(wrksh.Cells(i, m_AIB_Col_Text).Value)
    If Trim(wrksh.Cells(i, m_AIB_Col_Type).Value) = "2" Then
      If wrksh.Application.WorksheetFunction.CountA(wrksh.Range(wrksh.Cells(i, 1), wrksh.Cells(i, m_AIB_Columns))) > 0 Then
   ******************    [b] vtmp = wrksh.Cells(i, m_AIB_Col_Date).Value    ****************
        sDebit = wrksh.Cells(i, m_AIB_Col_DebitValue).Value
        sCredit = wrksh.Cells(i, m_AIB_Col_CreditValue).Value

Open in new window

ste5anSenior DeveloperCommented:
Why are you using Excel to import a CSV?

Use the Access import functions (wizard). They are more reliable, than those of Excel. Especially as depending on the Excel settings, you may not read what is in the file, but what Excel thinks it should be.
I don't see any instance of an m/d/y ordered date in the CSV file that you posted with your question.
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!

Import the CSV file ONCE manually.  Press the advanced button which gives you options regarding how dates are formatted.  Specify the format in the .csv file which looks like it is dd/mm/yyyy.  Then in the final step, save the import spec with a nice name.  In the future, you can automate the import and reference this Import Spec.

Keep in mind that Access will display dates according to your Windows settings.  So, if your Windows dates are displayed as dd/mm/yyyy, then Access will respect that UNLESS you have specifically formatted a control.  The other IMPORTANT point is that SQL Server natively assumes US date format which is mm/dd/yyyy so you have to be careful when working with text strings to format them as the query engine expects.  And finally, dates are not stored as strings which is why databases (and even Excel) can deal with this at all.  Dates are stored as double precision numbers with positive values being dates after Dec 30, 1899 and negative dates being dates prior to Dec 30, 1899.  The origin date (0 date) is Dec 30, 1899.  Time is stored as a decimal.   NEVER under any conditions format a date except to display it for humans.  In all code and queries, leave the date data types alone.  DO NOT format them.  Formatting a date turns it into a string at which point it behaves like a string rather than a date.  It is hard enough in the US where we are not constantly fighting the battle of month vs day.  In countries where date order is not US standard, it is even more important that you understand the difference between a string that looks like it might be a date and a field that actually is a date.
Patrick O'DeaAuthor Commented:
Thanks all for contributions.

Pat, two simple questions:

1. You say "press the advanced button"..... Where is that exactly??

2. Also, can I clarify.  When you say import the file once manually ... Are you implying that the database will somehow "learn" from this import and then behave differently (on a subsequent import) as a result of this learning.
If you had opened the wizard, you would have seen it.
Advanced ButtonAdvanced DialogNotice on the "Advanced" dialog that you get the option to Save.  Once you name and save the spec, you can reference it in the TransferText method and reuse your definition of the file layout.

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
Patrick O'DeaAuthor Commented:
Thanks Pat, I'd never spotted that "advanced" button before.
It is very useful when working with import files that are slightly out of normal.
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.