473,714 Members | 2,500 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.tx t 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:\Databas e\"
strFile = Dir$(strPath & "*.txt")
Do While Len(strFile) > 0
DoCmd.TransferT ext 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.TransferT ext 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:\MyFolde r\"
strFile = Dir$(strPath & "*.txt")
Do While Len(strFile) > 0
DoCmd.TransferT ext 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*******@hotm ail.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("A ccess.Applicati on.8")

' For Access 2000, use Application.9
'Set objAccess = CreateObject("A ccess.Applicati on.9")

' Open the desired database
objAccess.OpenC urrentDatabase( strPathToMDB)

' Use the TransferText command to import the file
' (with Column Heads)
objAccess.DoCmd .TransferText acImportDelim,, sTable,sFilePat h,True

' Clean up
Set objAccess = Nothing

Nov 12 '05 #1
1 6699
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
1. You are using the incorrect syntax for the TransferText command.
The correct syntax is:

TransferText(Tr ansferType, SpecificationNa me, TableName, FileName,
HasFieldNames, HTMLTableName, CodePage)

See the Access help article "TransferTe xt Method."

2. To run a Function from a macro you do NOT open the module wherein
the function resides (in your case the function "ImportFile s"). 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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBP/FjUIechKqOuFEgE QJMBwCaAxq4TKiC cV0M+QQGmAmGbKM I4uwAn0B8
GxMOQQ+S7cS3Eoi sdjvJ/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.tx t 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:\Databas e\"
strFile = Dir$(strPath & "*.txt")
Do While Len(strFile) > 0
DoCmd.TransferT ext acImportDelim, "MyDb", "MyTable", strPath &
strFile
strFile = Dir$
Loop
End Function


< SNIP old posts >

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2245
by: porterboy | last post by:
Hi Folks, I have auto-completion set up in my python interpreter so that if I hit the tab key it will complete a variable or a python command*. eg. if I type >>> imp and if I then hit the tab key, the interpreter will complete it to... >>> import Now, I also use Matlab at the command line a lot and it has a nice
0
3402
by: Girish | last post by:
Hi Here is the STEPS/METHOD my service provider sends the calling customer numbers captured : Export Method - HTTPS 1. The partner provides Who's Calling with a URL address to direct the data to the partner. 2. Call Sync exports the call record data (XML) HTTP(S) in the Auto-lead
0
1573
by: G R | last post by:
Hi Here is the STEPS/METHOD my service provider sends the calling customer numbers captured : Export Method - HTTP 1. The partner provides Who’s Calling with a URL address to direct the data t the partner 2. Call Sync exports the call record data (XML) HTTP(S) in the Auto-lead Dat Format (ADF) 1.0 3. The partner system receives the data at the desired URL
1
1584
by: Dan | last post by:
I tried the following code to import a series of data files into an Access table, but i keep getting Syntax errors. I'm running it from a Macro using the RunCode action with the ImportFiles() function. Can someone give me a pointer on how to correct this. Thanks in advance. Code: Function ImportFiles() Dim strPath As String Dim strFile As String
4
3023
by: Steve Jorgensen | last post by:
I'm restarting this thread with a different focus. The project I'm working on now id coming along and will be made to work, and it's too late to start over with a new strategy. Still, I'm not coming to a firm conclusion over whether it was the better approach, and wonder if I should do it differently the next time I'm faced with a similar issue. I needed an app to automatically import from spreadsheets with a semi-dynamic structure,...
0
2668
by: DataFreakFromUtah | last post by:
Hello! No question here, just a procedure for the archive. Search critera: count records imported count data imported count number of rows imported count number of records imported record import count automatically import and count records prompt for number of records imported import count auto-import records autoimport records count records before and after
2
2468
by: Greg Strong | last post by:
Hello All, I've written code in a test database with test data. Everything seems to be working except compact database in VB code per http://www.mvps.org/access/general/gen0041.htm. The reason I say this is the auto number fields are NOT being reset to zero. I delete the data from tables with action delete queries, then call the compact DB code which is followed by importing data to tables and subsequent append queries to other tables....
3
1725
by: sparks | last post by:
They have a new data collection station. The people here are using access 97.... This third party site will only post data back to us when we do this. http://www.datastuff.com/cgi-bin/ocs?Lo=yourname&Rpt=703&Month=7-5&Select=CC this is posted back to us as a file called data. There is no extension to is so the first thing that IE does is ask to save and where.
3
5196
by: Mark Urish | last post by:
Is there a way to import data from an unmanaged dll? For example, unmanaged.dll: __declspec(dllexport) int unmanagedErrno; managed.cs: class Managed { //this doesn't work:
0
8801
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8707
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9314
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9174
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9074
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
5947
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4725
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3158
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2520
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.