Could someone please help me with auto importing a series of data
files into an Access table. I tried to follow code given below in a
previous messagebut i'm getting error messages.
Here's my database stats:
Path: C:\Database (contains the database and all the text files to
be imported)
Text files to import: (SampleData4.txt and SampleData3.txt as testing
examples)
Macro name: MyMacro (with action OpenModule and opens the MyModule)
Table name: MyTable
Database name: MyDb
I used the following code in a module called MyModule and then ran it
from the
Module window:
Function ImportFiles()
Dim strPath As String
Dim strFile As String
strPath = "C:\Database\"
strFile = Dir$(strPath & "*.txt")
Do While Len(strFile) > 0
DoCmd.TransferText acImportDelim, "MyDb", "MyTable", strPath &
strFile
strFile = Dir$
Loop
End Function
But when i run itfrom the Module window, it gives me the "Compile
Error: Syntax Error" message -- the "Function ImportFiles()" line gets
highlited in yellow.
Also, when i run MyModule, it asks for a Macro. And the macro i had
created
doesn't appear in the drop down box. I created MyModule with an
OpenModule
action.
What am i doing wrong ?
---------------[color=blue]
>
>Re: Question on Importing text data to Access
>From: Allen Browne
>Date Posted: 12/28/2003 7:43:00 PM
>
>
>
>If the files all have the same structure, you would not need a[/color]
different[color=blue]
>import spec for each one: just use the same spec. (If they are all
>different, you don't have to use an import spec.)
>
>Use Dir() in a loop to read the names of all the files in a loop.
>Use DoCmd.TransferText to import each file.
>
>You don't need CreateObject if you are running the code in Access,[/color]
and[color=blue]
>importing into the existing database. The basic idea will be:
>
>Function ImportFiles()
> Dim strPath As String
> Dim strFile As String
>
> strPath = "C:\MyFolder\"
> strFile = Dir$(strPath & "*.txt")
> Do While Len(strFile) > 0
> DoCmd.TransferText acImportDelim, "MySpec", "MyTable", strPath &
>strFile
> strFile = Dir$
> Loop
>End Function
>
>--
>Allen Browne - Microsoft MVP. Perth, Western Australia.
>Tips for Access users -
http://allenbrowne.com/tips.html
>Reply to group, rather than allenbrowne at mvps dot org.>
>
>"D Mat" <danmat347@hotmail.com> wrote in message
>news:3fef8389$0$204$75868355@news.frii.net...[color=green]
>> Hi,[/color]
> I'm trying to get MS Access 2000 to automatically import a series of
> (~200) flat text, tab delimited, data files into a single Access table,
> with consistent fields and rows.
> The files have different, but somewhat logical naming structures
> (ex.Jun01.txt, Jun02.txt, Jul01.txt, etc...).
>
> Is there a relatively simple way to accomplish this, considering i'm new
> to Access. I was especially hoping to do this without having to
> manually create new import specs or type in file names for each new data
> file i need to import. With 200-300 files, that can get tedious.
>
> I tried the following code i found on the internet (listed below) which
> i created as a module and then run it under a Macro with the OpenModule
> action. But i keep getting error message which says that i'm invoking an
> invalid outside procedure.
>
> Thanks for any help !
>
> Code:
>
> Dim objAccess
> Dim strPathToMDB
> Dim sTable
> Dim sFilePath
>
> Const acImportDelim = 0
>
> ' // NOTE: User must edit variables in this section //
> '
> ' The following 3 lines of code are the only variables
> ' that need be edited
> ' Provide paths to the Access MDB, Text file for import
> ' and the new table name.
> '
> strPathToMDB = "C:\DbTest.mdb"
> sFilePath = "C:\Attachments.txt"
> sTable = "Attach"
> '
> ' /////////////////////////////////////////////////////
>
> ' Create Access 97 Application Object
> Set objAccess = CreateObject("Access.Application.8")
>
> ' For Access 2000, use Application.9
> 'Set objAccess = CreateObject("Access.Application.9")
>
> ' Open the desired database
> objAccess.OpenCurrentDatabase(strPathToMDB)
>
> ' Use the TransferText command to import the file
> ' (with Column Heads)
> objAccess.DoCmd.TransferText acImportDelim,,sTable,sFilePath,True
>
> ' Clean up
> Set objAccess = Nothing[/color]
