Exporting a Collision Supplement using the CIECA EMS Standard

Posted on 2003-02-28
Medium Priority
Last Modified: 2013-11-24
I'm hoping that there's an expert here that has experience with an application implementing the EMS Standard which is used by applicaitons throughout the Body Shop / Collision Repair industry (data passed between apps in EMS standardized dBase IV files).

I'm charged with modifying an existing application to export an EMS formatted dbase IV file from my app that can be imported into one of the popular Collision Industry apps, sold by Mitchell, etc.  I'm looking for detail on how the package is wrapped up, how the different EMS file formats are used and created, and possibly any sample code in VB 6.0.

I'm aware of the CIECA, have signed my company up with the organization, and have been browsing their materials on file structures, etc.  Just need a lot more detail on implementation.

Thanks for your time - MT
Question by:supertedusa
  • 5
  • 4

Expert Comment

ID: 8043948
You can use DAO to access it, but if the dBase files are making heavy use of indexes the you will have trouble.  We use dBase for our order entry system here, and I have spent the last year working on VB code for working with dBase.  It seems that I can anything I want with the database.  The only problem is that the software we use freaks out afterwards.  Some other software that can read dBase seems to think my dBase files created with VB are just fine.
Personally I would go with a dBase OCX/ActiveX control/dll for dBase access.  It really isnt worth all of the headaches involved trying to do it yourself in VB.

I suppose that if you have all of the file header info for the EMS version of dBase files then you shouldnt have any problems.  You can just use ADO to do any standard database work and write a routine to create a compliant file to put the data in.

Author Comment

ID: 8044383
The software I'm working on will only export the files in dBase format, so I'm not concerned with reading them.

So are you able to create dBase files within VB code, specifying field headers and then adding in records?  I may have to create the files in Access first, then save them as dBase IV format, which is OK (I've got the field specs for the files).

The program uses Access databases (mdb extension, I think) to operate.  I've got to convert those db's to dBase IV as an export to be imported into collision systems.  That's the main goal I'm trying to find more info on.  Collision systems can expect up to 15 files for a particular import, so I'm also after more detail from someone who has actually worked on this type of stuff.

Thanks - MT

Expert Comment

ID: 8044491
ok I was confused I thought that the CIECA EMS Standard  extended the standard dBase IV format.  I browsed through the CIECA EMS Standard 2.5 since 3 was not available on the site.

If you know how to use ADO then it will be a breeze,  you can use this connection string to open the database.
oConn.Open "Driver={Microsoft dBASE Driver (*.dbf)}; DriverID=277; Dbq=c:\somepath"
The basic idea of dBase files is this.
A directory represents a database, and all of the dbf files in that directory represent tables in the database, this is why the connection string above only references the path and not a file name.

You would then specify the file names when executing SQL Queries like this:

the period in the file name should be replaced with the # sign.

If you want more info just let me know.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 8046147
So if I'm following you correctly, what they refer to as a "package" is actually a directory?

Is the Envelope (*.ENV) file the first thing dBase looks for so that it knows what the directory contains?

Expert Comment

ID: 8046891
no, as far as I understand it, any directory is a database as far as dBase is concerned.  If no tables exist then it is simply a empty database.

I have not heard of a ENV file, is this something that is specific to the system you use?

Author Comment

ID: 8049779
.ENV file is part of the EMS standard.

Are you able to create dBase files with VB code, or do you have to create the dBase tables first, then modify them with VB code?

I'm familiar with ADO, so any code samples for working with dBase (other than select queries)?


Accepted Solution

Da_Weasel earned 1500 total points
ID: 8050153
Creating database files from scratch needs to be done with ADOX.  It should be listed as "Microsoft ADO Ext 2.7 for DDL and Security"  It uses Connection Strings to create them so I assume that you can create a database of anytype that there is a connection string for.  I've never used it for anything other than Access databases, but you can test with various connection strings.

Here is a little example code that I found.
Dim catAccess As New ADOX.Catalog
  Dim tblValExp As New ADOX.Table
  Dim cnnAccess As New ADODB.Connection
  Dim rstValExp As New ADODB.Recordset
  catAccess.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb;Jet OLEDB:Engine Type=4;"
  catAccess.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb"
  With tblValExp
      .Name = "tblValExp1"
      .Columns.Append "Stock Name", adVarWChar, 40
      .Columns.Append "Stock Symbol", adVarWChar, 6     '1
      .Columns.Append "Stock Price", adSingle       '2
      .Columns.Append "Est Date", adVarWChar, 10           '3
      .Columns.Append "Est Year", adVarWChar, 6         '4
      .Columns.Append "Price High(0)", adSingle     '5
      .Columns.Append "Price High(1)", adSingle     '6
      .Columns.Append "Price High(2)", adSingle     '7
      .Columns.Append "Price High(3)", adSingle     '8
      .Columns.Append "Price High(4)", adSingle     '9
      .Columns.Append "Price High(5)", adSingle     '10
  End With
  catAccess.Tables.Append tblValExp
  Set tblValExp = Nothing
  Set catAccess = Nothing
  cnnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb;Persist Security
  rstValExp.Open "SELECT * FROM tblValExp1", cnnAccess, adOpenKeyset, adLockOptimistic
  rstValExp.Fields(0).Value = "Name"
  rstValExp.Fields(1).Value = "Symbol"
  rstValExp.Fields(2).Value = 1.5
  rstValExp.Fields(3).Value = Format(Now(), "dd/mm/yyyy")
  rstValExp.Fields(4).Value = "2000"
  rstValExp.Fields(5).Value = 1.5
  rstValExp.Fields(6).Value = 1.5
  rstValExp.Fields(7).Value = 1.5
  rstValExp.Fields(8).Value = 1.5
  rstValExp.Fields(9).Value = 1.5
  rstValExp.Fields(10).Value = 1.5
  Set rstValExp = Nothing
  Set cnnAccess = Nothing

Author Comment

ID: 8057916
OK.  Great.  I'll try this out and see if I can't make this work.

I'm not sure if it's going to fly or not though, because I have to save the files using the differnt file extensions specified by the EMS standard (.ENV, etc.).

Thanks for the help.  Although you didn't give me specific information on the EMS Standards or collision app related code, I appreciate all your efforts here and the fact that you actually checked out the 2.5 Standard on the CIECA website before commenting.  Much appreciated.


Expert Comment

ID: 8058222
not a prob, let me know if things worked out it...

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

580 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