Converting a text file to .mdb format

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

schworakAuthor Commented:
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.
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
schworakAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.