Create Specs "a7n9" <a7n9wvu[ at ]gmail.com> 06.07.2006 15:12:40 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.

Re: Create Specs John Nurick <j.mapSoN.nurick[ at ]dial.pipex.com> 06.07.2006 20:40:39 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 On 6 Jul 2006 08:12:40 -0700, "a7n9" <a7n9wvu[ at ]gmail.com> wrote: [Quoted Text] >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 [Microsoft Access MVP] Please respond in the newgroup and not by email.

Re: Create Specs "a7n9" <a7n9wvu[ at ]gmail.com> 07.07.2006 16:15:49 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.

Re: Create Specs John Nurick <j.mapSoN.nurick[ at ]dial.pipex.com> 07.07.2006 19:09:34 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. On 7 Jul 2006 09:15:49 -0700, "a7n9" <a7n9wvu[ at ]gmail.com> wrote: [Quoted Text] >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 [Microsoft Access MVP] Please respond in the newgroup and not by email.