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
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
  • 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.
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf


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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Viewers will learn the different options available in the Backstage view in Excel 2013.
Suggested Courses

764 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