Create Specs

[a7n9]

Hello all,

This is a complicated problem. I'll try to explain what I'm trying to
achieve.
1. I have an Excel file which has specifications for text file we would
like to import in Access, it has field names, start and width.
2. I run a code to get these specs in the format of Access (when we
click on Advanced)
3. I create a Blank Access database (SpecsTbl) from VBA, create tables
MSysIMEXSpecs and MSysIMEXColumns.
4. Insert values in these tables according to the specs I've in the
excel file.
5. So I've created specs in Access but when I try to import text files
using these specs I get an error "an error occurred while trying to
load import/export specifications.
6. My final objective is to have the Access specs in a database using
VBA. The reason is the specs keep changing, so even though we save the
specs in Access we have to change those too. I would like to give this
Excel file to user and they should be able to do this without knowing
much details of whats going on.

Thanks for your help.

 

[John Nurick]

Instead of trying to manipulate import/export specifications from VBA, I
would write code to create a schema.ini file embodying the
specifications from the Excel file.

See these links for more:
Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512
How to Use Schema.ini for Accessing Text Data
http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090
Schema.ini File (Text File Driver)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

 

[a7n9]

Thanks for your reply, John. I read those articles before posting here
and I thought about creating schema but the users are comfortable using
specs and using the schema would be a learning curve for them and me
too.

 

[John Nurick]

Instead of creating a blank database from VBA, have you tried creating a
"template" database manually? Use the Import Text wizard to create an
import spec. If I understand things right, this creates the IMEX system
tables with whatever magic properties they needed. Then insert the
values for your specs. Then distribute the template database with the
application. If necessary you could store it in a blob field in the main
mdb file.