473,903 Members | 3,114 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Importing text file to and from Access table

2 New Member
Hi All

I am currently trying to import data from a text file to a table in Access and dont seem to have much idea of how I can do it.

I have a little background of VB6 but I am not very familiar with VBA for Access.

Well, where do I start in writting the code for importin/exporting data to/from Access tables and what are the important elements that I should look at when writing the code.

Can someone give me a idea of how data can be imported or exported to and from Access table using VBA.

Any suggestion is of greate help.
Sep 21 '07 #1
1 3521
ADezii
8,834 Recognized Expert Expert
Hi All

I am currently trying to import data from a text file to a table in Access and dont seem to have much idea of how I can do it.

I have a little background of VB6 but I am not very familiar with VBA for Access.

Well, where do I start in writting the code for importin/exporting data to/from Access tables and what are the important elements that I should look at when writing the code.

Can someone give me a idea of how data can be imported or exported to and from Access table using VBA.

Any suggestion is of greate help.
To Import/Export Text Files to/from Access Tables, use the TransferText() Method as documented below:
Expand|Select|Wrap|Line Numbers
  1. expression.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)
  2.  
  3. expression    Required. An expression that returns one of the objects in the Applies To list.
  4.  
  5. TransferType   Optional AcTextTransferType.
  6.  
  7. AcTextTransferType can be one of these AcTextTransferType constants. 
  8. acExportDelim 
  9. acExportFixed 
  10. acExportHTML 
  11. acExportMerge 
  12. acImportDelim default 
  13. acImportFixed 
  14. acImportHTML 
  15. acLinkDelim 
  16. acLinkFixed 
  17. acLinkHTML 
  18. If you leave this argument blank, the default constant (acImportDelim) is assumed.
  19.  
  20. Notes
  21.  
  22. Only acImportDelim, acImportFixed, acExportDelim, acExportFixed, or acExportMerge transfer types are supported in a Microsoft Access project (.adp).
  23.  
  24.  
  25. SpecificationName   Optional Variant. A string expression that's the name of an import or export specification you've created and saved in the current database. For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file. To create a schema file, you can use the text import/export wizard to create the file. For delimited text files and Microsoft Word mail merge data files, you can leave this argument blank to select the default import/export specifications.
  26.  
  27. TableName   Optional Variant. A string expression that's the name of the Microsoft Access table you want to import text data to, export text data from, or link text data to, or the Microsoft Access query whose results you want to export to a text file.
  28.  
  29. FileName   Optional Variant. A string expression that's the full name, including the path, of the text file you want to import from, export to, or link to.
  30.  
  31. HasFieldNames   Optional Variant. Use True (–1) to use the first row of the text file as field names when importing, exporting, or linking. Use False (0) to treat the first row of the text file as normal data. If you leave this argument blank, the default (False) is assumed. This argument is ignored for Microsoft Word mail merge data files, which must always contain the field names in the first row.
  32.  
  33. HTMLTableName   Optional Variant. A string expression that's the name of the table or list in the HTML file that you want to import or link. This argument is ignored unless the transfertype    argument is set to acImportHTML or acLinkHTML. If you leave this argument blank, the first table or list in the HTML file is imported or linked. The name of the table or list in the HTML file is determined by the text specified by the <CAPTION> tag, if there's a <CAPTION> tag. If there's no <CAPTION> tag, the name is determined by the text specified by the <TITLE> tag. If more than one table or list has the same name, Microsoft Access distinguishes them by adding a number to the end of each table or list name; for example, Employees1 and Employees2.
  34.  
  35. CodePage   Optional Variant. A Long value indicating the character set of the code page.
  36.  
  37. Remarks
  38. For more information on how the action and its arguments work, see the action topic.
  39.  
  40. You can leave an optional argument blank in the middle of the syntax, but you must include the argument's comma. If you leave a trailing argument blank, don't use a comma following the last argument you specify.
  41.  
  42.  
  43. Note  You can also use ActiveX Data Objects (ADO) to create a link by using ActiveConnection property for the Recordset object.
  44.  
  45.  
  46. Example
  47. The following example exports the data from the Microsoft Access table External Report to the delimited text file April.doc by using the specification Standard Output:
  48.  
  49. DoCmd.TransferText acExportDelim, "Standard Output", _
  50.     "External Report", "C:\Txtfiles\April.doc"
  51.  
Sep 21 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
5441
by: Elmo Watson | last post by:
I've been asked to develop a semi-automated type situation where we have a database table (sql server) and periodically, there will be a comma delimited file from which we need to import the data, replacing the old. I naurally know that we can use to kill the other data, but does anyone have any examples of importing a comma delimited file into SQL Server with ASP?
1
2807
by: D Mat | last post by:
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...
9
4039
by: Edward S | last post by:
I budget for a Project in an Excel sheet as illustrated below. The months below are usually a 2 year period i.e. 24 months, though it could be over 24 months depending upon a Project. I then need to input this in an Access database, where I do a comparison with the Actual cost. The table “TblBudget” in Access is made of 4 fields, namely: (1) CostElement (2) CostCenter (3) Month (4) Amount$. At the moment this method is very cumbersome....
1
3688
by: sparks | last post by:
I have never done this and wanted to ask people who have what is the best way. One person said import it to excel, then import it into access table. but since this will be done a lot, I am trying to avoid this extra step. can access read in this file directly into a table as append data? thank you very much for any pointers
1
2137
by: don | last post by:
I'm trying to import a comma delimited text file into MS Access 2002 version - the first column is set for an auto increment primary key and everything works right the first time I import a text file, however when I try to append to this new database table Access always messes up the import - as long as I ieave the auto increment column out it has no trouble appending to the database , and then I have to add the auto increment column...
2
3622
by: nutthatch | last post by:
I want to be able to import an Excel spreadsheet into Access 2K using the macro command Transferspreadsheet. However, the file I am importing (over which I have no control) contains some records that are "dirty" i.e. the field contents do not comply with the expected format (date/time) and they end up in a seperate table of import errors. (The records in "error" are actually empty fields.) This is a regular event and I do not want to...
17
2041
by: OdAwG | last post by:
Just some questions regarding tables. I am new Access Database and need a little help. I have the following data listed below 01. I have a table called tbl_Customer with the following information listed below 02. In that table I have three columns that have numeric data in it (City, State, Marital_Status) 03. I need to convert the numeric data in table tbl_customer (City, State, Marital_Status) to alpha numeric data 04. using the...
5
3191
by: hharriel | last post by:
Hi, I am hoping someone can help me with an issue I am having with excel and ms access. I have collected data (which are in individual excel files) from 49 different school districts. All districts have used the same excel template and populated the same 32 data fields (columns). I created one large excel file from all 49 files which gives me a master table of 60,000 or so records. I have tried to import this master table into access...
2
2738
by: Debbiedo | last post by:
I have a text file that I am importing into an Access table that was generatred from data exported from a Word file. Several (about 20-30) fields are from check boxes on the Word form. These fields have either 0 (Not checked) or 1 (Checked). Due to requirements from a third party software application, they HAVE to be either Y(Checked) or N (Not checked) and MUST be a text field in the Access table. I will be importing several thousand of...
4
6523
by: johnporter123 | last post by:
Does anyone have a method of importing a large "FLAT" CSV file into access. The file has well over 255 columns (Fields). Before anyone flames me over normalization, I do not have access to the source DB for the file and the CSV file has the data I need, but it is buried in this huge text file output from another Compan'y database. The purpose of getting it into Access is so that I can adjust it to 1st normal form, and recompute the tables in...
0
10003
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
10882
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
10988
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
10504
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9692
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8055
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7213
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
5897
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4732
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

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.