Converting a text file to .mdb format

Posted on 1997-06-12
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 100 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to open from address saved in DB? 26 177
Use Multiple Forms 4 49
Help me. 3 46
VB6 ListBox Question 4 32
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now