Re: Import Text with existing schema.ini file utility

From: Daniel M (DanielM_at_discussions.microsoft.com)
Date: 01/23/05 

Date: Sun, 23 Jan 2005 06:15:03 -0800

Thanks, John,
I have tried and tried using the Docmd.TransferText feature within Access
but the schema.ini file is ignored. I have tried to leave out the
"schema.ini" portion where the import specifications normally is and I have
also included the "schema.ini" file. I even tried adding the path
"CC:\Documents and Settings\Daniel Maxwell\My Documents\FCC\schema.ini" file.
 This is the same folder that the 27 text files are located in and also where
the access database is stored.

I have searched for how to include the schema.ini file in an SQL statement
as well.

I really don't want to have to manually enter the specifications for every
table because there are 27 tables and each table (external text file) has
between 4 fields and 70 fields. I have already created a schema.ini file for
all of the tables and wish to use it but cann't get Microsoft Access to
successfully use the "schema.ini" file. It is frustrating. I have checked
that the current version of the Microsoft Jet engine is 4.0 service pack 8
(security release, or something that is related to security after service
pack 8).

Another reason to use the "schema.ini" file is that I can specify how long
the text width should be otherwise the database will be bloated with 255
characters for every text field. The advanced feature in the Import Text
wizard in Access 2003 does not allow us to specify how long the text field
should be in a delimited text file. I use a pipe delimiter (|).

Any help in troubleshooting the problems getting the schema.ini file to work
would be greatly appreciated.

The line you wrote below could be useful. Let me modify it and check it out.
However, I noticed this is DAO. What is the difference between this and ADO?
It seems like the Access documentation on Microsoft web site suggests
converting to ADO.

(I would like to simply use "mycurrentdatabase" in an Access module instead
of opening a database in DAO, is this possible?)

> strSQL = "SELECT * INTO tbl_Facility FROM " _
> & "[Text;HDR=NO;Database=C:\Documents and Settings\Daniel\My " _ & "Documents\FCC\;].facility#txt"
> oDB.Execute strSQL

Thank you,
Daniel

"John Nurick" wrote:

> Hi Daniel,
>
> If you're working from within Access, just use the TransferText macro
> action or the DoCmd.TransferText VBA statement.
>
> If you want a utility that will work from the command line without
> having to fire up Access, there's a sample VBScript below. Either way,
> schema.ini needs to be in the same folder as the textfiles.
>
> 'CODE START
> 'Sample VBScript to import data from a textfile into
> 'a table in an MDB database without opening Access
> 'Modify strSQL and filenames as required
>
> Option Explicit
>
> Dim oJet 'DAO.DBEngine
> Dim oDB 'DAO.Database
> Dim strSQL 'String
>
> Set oJet = CreateObject("DAO.DBEngine.36")
> Set oDB = oJet.OpenDatabase("D:\Folder\Database.mdb")
>
> strSQL = "SELECT * INTO MyTable FROM " _
> & "[Text;HDR=Yes;Database=D:\Folder\;].FileName#txt"
> oDB.Execute strSQL
>
> 'Repeat the above pair of lines for as many files
> 'as you need to import.
>
> oDB.Close
>
> 'CODE END
>
>
> On Sat, 22 Jan 2005 14:47:02 -0800, "Daniel M"
> <DanielM@discussions.microsoft.com> wrote:
>
> >I have created a schema.ini file which accurately describes several text
> >files and I am looking for a utility someone has already written to simply
> >import those text files into Microsoft Access tables. Please point to a
> >utility or write one for me and I will pay you for it. The files reside in a
> >folder and the Access database will be in the same folder. Thank you.
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>

Relevant Pages

  • Re: Import Text with existing schema.ini file utility
    ... schema.ini needs to be in the same folder as the textfiles. ... 'Modify strSQL and filenames as required ... Dim oJet 'DAO.DBEngine ... John Nurick [Microsoft Access MVP] ...
    (microsoft.public.access.modulesdaovba)
  • Access 2000 security issue
    ... I hope someone can help me out set up the security properly. ... I have Microsoft Access 97 and Access 2000 in my computer and I developed my ... database with Access 2000 with the updated patch SP-3. ... as long as both mdb and mdw files were in the same folder. ...
    (comp.databases.ms-access)
  • Re: creating folders
    ... Is this a question about Microsoft Access (the database application)? ... When I convert a client ... This folder will be used for storing word documents. ...
    (microsoft.public.access.gettingstarted)
  • Re: Message Alert
    ... You or another user can press the F11 key to show the database window ... So the two strSQL lines needs to be changed also: ... assembled strSQL by pressing Ctrl + G, which opens the Immediate ...
    (microsoft.public.access.modulesdaovba)
  • Re: Is This Possible ... ? Yes - Upload images to an Access database
    ... It looks like your databases folder is outside the root of your web ... I created a database exactly as you said, the only change I made was to ... and underneath an 'upload' button and a 'view images' button. ... Dim con As New Data.OleDb.OleDbConnection ...
    (microsoft.public.dotnet.framework.aspnet)