More question on Data Auto Import

Dan

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 ?
---------------

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 differentimport 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, andimporting 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" <da*******@hotmail.com> wrote in message
news:3f*********************@news.frii.net...

Hi,

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

Nov 12 '05 #1

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
1. You are using the incorrect syntax for the TransferText command.
The correct syntax is:

TransferText(TransferType, SpecificationName, TableName, FileName,
HasFieldNames, HTMLTableName, CodePage)

See the Access help article "TransferText Method."

2. To run a Function from a macro you do NOT open the module wherein
the function resides (in your case the function "ImportFiles"). To
run a function from a macro you use the RunCode action (see the Access
help article on "RunCode Action").

3. To run a public function from the IMMEDIATE (aka debug) window
(Ctrl-G) you'd type this:

? ImportFiles [Enter key]
HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP/FjUIechKqOuFEgEQJMBwCaAxq4TKiCcV0M+QQGmAmGbKMI4uwA n0B8
GxMOQQ+S7cS3EoisdjvJ/CSq
=mGJS
-----END PGP SIGNATURE-----
Dan wrote:

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

< SNIP old posts >

Nov 12 '05 #2