VBA - DoCmd.TransferText - Date issue

[Scaniafan]

Good morning all,

Let me start with informing you that I'm completely new to VBA programming. I've got some Access experience, and want to move on in this.


I'm trying to import data in to Access from a CSV file with a semicolon as a seperator. I've been able to do this with the following basic code:

DoCmd.TransferText acImportDelim, "", "TBL_Shipment_Data", CurrentProject.Path & "\Import\Shipment_Data\Shipment_Data.csv", True

My challenge at the moment is that I'm working with an CSV file where several columns are containing a date in a format that Access will not accept, being dd.mm.yyyy. The dot causes Acces not to import the date and thus no other columns.

I've been able to import via the wizard and selecting "date/time" as data type. However, as the database needs to run on different pc's, the saved import will not work as this refers to the specific file path rather than currentproject.path

How can I ensure the data is being imported correctly? I see two options with my limited knowledge:

- Be able to have the transfertext command convert the wrong date format to a correct format
- Ba able to have the currentproject.path in the runsavedimportexport

Thanks for any advise!

 

[James Deckert]

Have you gone into the Import Specification to configure date info?

 

[Scaniafan]

Apologies for the late reply, but I hadn't at the time you posted your answer, which was exactly what I didn't had due to lack of knowledge :banghead:

Thanks for the reply!