How to import dynamically named file using Access 2003 VBA

Hello

I am using the DoCmd.TransferText via Access 2003 VBA to write a macro to import csv file that is named based on "filename mm-dd-yyyy" from a filepath. Everyday a different file is created in the filepath with changes to the date "mm-dd-yyyy"

Iwrote my macra via Access 2003 VBA as follows:

Sub TestTransferText()

DoCmd.TransferText acImportDelim, , _
"tblportfolio", "M:\AACP\AACP PORTFOLIO 09-19-2008" & Format$(Date, "mm-dd-yyyy") & ".csv", True

End Sub

When I run the macro - Access prompt the below error:
"The Microsoft Jet engine could not find the object 'AACP PORTFOLIO 09-19-200809-233-2008.csv'. make sure the object exists and that you spells its name and path name correctly."

Checked the filepath - it is correct. I have checked the 3 files which I have named AACP PORTFOLIO 09-19-2008, AACP PORTFOLIO 09-22-2008 and AACP PORTFOLIO 09-23-2008; they are named correctly

I am not sure where I went wrong. Please advise

2 things are wrong...
1) You will need to fill the second "value" in the command, the import spefication to make sure everything as desired.

2)
DoCmd.TransferText acImportDelim, , _
"tblportfolio", "M:\AACP\AACP PORTFOLIO 09-19-2008" & Format$(Date, "mm-dd-yyyy") & ".csv", True

You have the date twice in there, one time hard coded, one time in the format. Remove the hardcoded part and you should just import the file for today.

Since you are using VBA, then I personally would not mess with transfer text ... but that is just me ... I like to use a well formed SQL statement that utilizes an ODBC connection string right in the SQL statement ... Here is the generic syntax for a SQL statement that will connect to your CSV and push the records into your existing table ....
{Line breaks for clarity}

Also, everything, in between the <..> marks (includeing the <> marks) will need to be replaced with info that fits your situation.

In your situation, I beleive a code snippet could look something like this ...

{Note, my code assumes that all the fields are the same, but if they are not, you can easily add the field names in the appropriate spot!}

Intresting I have never tried anything like this,

Brent,

How does your code know what the delimiter is??? CSVs or text files can come in many different formatings...

Hello Mailman ....

>> How does your code know what the delimiter is??? <<

I have no idea! ... ... I *think* the delimiter has to be a "standard" delimiter (comma, semi-colon, tab ...) ... As far as I can remember, I have only used this technique on a regular basis with comma delimited files. However, I vaguely remember varying the delimiter for experimentation ... but I can not speak with certainty untill I "play" again ... I will try to look at this later today an see what delimiters work ...

Brent: Also, where is this documented? Is it a Jet thing or does it work with other DBMSs?

>> Brent: Also, where is this documented? <<

I have no idea where this is documented. Probably in the JET specifications. I read about it in my "early years" concerning SQL DDL and how a truly generic SQL Statement would look something like this (note between and including the < > is to be replaced with your particulars) ...

SELECT * FROM [<datasource>].<table>

But the syntax did not gain in significance until I was a further along in my knowledge base on databases. Then the syntax really hit home when a fellow MVP (Doug Steele) proposed a solution to and issue with the syntax I proposed for this thread.

So ... translating the generic syntax in the JET world you have something like this ...

SELECT * FROM [<ODBC Connect String>].<table>
... Or ...
SELECT * FROM [<Full path of MDB>].<table>
...Or...
SELECT * FROM [;Database=<Full path of MDB>].<table>


Which leads to the syntax I have shown above for connecting to a demilited text file through ODBC. As far as I know, you can use this syntax to connect to any datasource that can be connected to with an ODBC connection string. I have used it for Excel, Text, Access, and SQL Server. Do take note that this is not that different from the IN clause that Access generates when creating remote queries ...

SELECT * FROM <table> IN '<full path of MDB>'
...Or...
SELECT * FROM <table> IN [;Database=<full path of MDB>]
...Or...
SELECT * FROM <table> IN [<ODBC Connect String>]


>> Is it a Jet thing or does it work with other DBMSs? <<

There is a similar concept to this in SQL Server, and I am unsure about it in other db engines ... in SQL Server, to access remote data you would have the following syntax:

SELECT * FROM OPENDATASOURCE (<provider_name>, <init_string> ).<owner>.<table>

Example from BOL ...

This syntax is quite handy, especially when using JOINS between two DIFFERENT datasources.