Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses

610 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