Converting a text file to .mdb format

Posted on 1997-06-12
Medium Priority
Last Modified: 2012-05-04
I am looking for a code sample and/or good explination about how to convert a delimited text file into a .mdb format by opening the text file using the dbengine and also opening the .mdb file with the dbengine, reading one record at a time from the text file and filling in the .mdb file.

I have tried several times to make the schema.ini file work but it is returning unexpected results every time even though I am vurtully copying the code from the help file.

The text file is being delimited with the pipe character "|" but another printable character can be used if that is causing the problem. Although I have tried several other characters too such as Tilde "~" and even the exclamation point "!" and get the exact same results.

I get 1 of 2 results...

1, The parse doesn't work and I get 1 field in each record that includes the delimiter character I am using instead of several fields in each record.

2, An error to the effect "filename" is an invalid file name. and this makes no sence to me.

Any help would be greatly appreciated.
Question by:schworak
  • 2

Expert Comment

ID: 1427162
It's much simpler and easier to just parse the file one line at a time (with Line Input #, or just Input #FileNo, field1, field2, ..., fieldn), and add records to the .mdb file with something like:

with rs
  .Fields!Field1 = field1
  .Fields!Field2 = Field2
end with

I dabbled with using the schema.ini file last year to access a text file as a database - it's poorly documented (even Microsoft admitted to that when I got them on the phone), and in my opinion it doesn't give you any advantage over a simple method like that described above.

Hope this helps.


Author Comment

ID: 1427163
Due to the ability for the file requirements to change, fields to be added/removed/resized it is required that the settings be in the schema.ini so a complete recompilation and update won't be required every time this happens.

I am making some headway on solving the problem myself and have found one key item, the schema file must be in the same directory as the text file that is being read for it to do anything.

Now that I know that (which was not documented by the way) I may be able to push forward.

If someone comes up with any additional info, I would appreciate it.

Accepted Solution

y96andha earned 300 total points
ID: 1427164
OK, this is a simple sample I just wrote:

------- F:\SCHEMA.INI --------
[Text ISAM]

Col1=Name char width 50
Col2=Job char width 9
Col3=Feel char width 9
Col4=Good char width 9

------- F:\TESTDB.TXT--------

------- Code in VB Form --------
Private Sub Command1_Click()
Dim db As Database, tdb As Database
Dim tb As Recordset, rs As Recordset
Dim fld As Field, fld2 As Field
Dim td As TableDef
DBEngine.IniPath = "f:\schema.ini"
Set db = OpenDatabase("F:\Program\Microsoft Visual Basic\dbgtst.mdb")
Set tdb = OpenDatabase("F:\", False, False, "Text;")
Set rs = tdb.OpenRecordset("TESTDB")
On Error Resume Next
db.TableDefs.Delete "test"
On Error GoTo 0
Set td = db.CreateTableDef("test")
For Each fld In rs.Fields
    Set fld2 = td.CreateField(fld.Name, fld.Type)
    td.Fields.Append fld2
Next fld
db.TableDefs.Append td
Set tb = db.OpenRecordset("test")
While Not rs.EOF
    For Each fld In rs.Fields
        tb(fld.Name) = fld
    Next fld
End Sub


I had previously created the database "F:\Program\Microsoft Visual Basic\dbgtst.mdb" with Access 95.

Just create a form, add a command button and paste the code into the code section.  You may need to change a couple of paths.


Author Comment

ID: 1427165
I did descover that the SCHEMA.INI file must be in the same directory as the text file that is being read in. If it is not, it won't work and I can't figure out what setting to change to change this. But no matter. I have the answer I needed. Thanks!

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question