472,364 Members | 2,056 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,364 software developers and data experts.

Importing text file to and from Access table

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 3374
ADezii
8,832 Expert 8TB
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
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,...
1
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...
9
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...
1
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...
1
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...
2
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...
17
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...
5
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...
2
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...
4
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...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
2
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
1
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
1
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...

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.