Import/Export Specifications (1 Viewer)

[ryetee]

I have no real idea what an import/export specification is and why they are necessary.

I have a query that I run against 1 database and in the vba I export a csv which in turn is used to update a table in a second database. The 2 database are identical as far as the design is concerned and only the data may be different.

When I tried to import the data via vba I then came across the import/export specification through an error I received. I noted if I used the wizard I could import without a problem but in the VBA it was crashing because I didn't have one of the aforementioned beasts. Somewhere on this site I found out how to create one using the import wizard and going into the advanced option and saving it. Now I can set this up in the first instance but if the tables change I presume the import/export specification file must change too. So! Can I create this specification file within vba when I create my export file. I presume I would have to delete the old one a create the new but how do I do that?

Any help appreciated because I don't understand what I've researched thus far!

 

[CJ_London]

Now I can set this up in the first instance but if the tables change I presume the import/export specification file must change too.

correct

So! Can I create this specification file within vba when I create my export file.

you can, but it is a lot easier to create a schema.ini file - see these links

https://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx

http://stackoverflow.com/questions/...-i-need-to-export-my-csv-file-to-datagridview

http://aspdotnetcodes.com/Importing_CSV_Database_Schema.ini.aspx

http://windowssecrets.com/forums/showthread.php/123797-create-schema-ini-dinamic

 

[smig]

You can create the spec in the db or as schema.ini as CJ_London suggested.

Why dont you directly import the records you want either linking to the other db or using DAO ?

 

[ryetee]

You can create the spec in the db or as schema.ini as CJ_London suggested.

Why dont you directly import the records you want either linking to the other db or using DAO ?

Not sure what a DAO is but what I'm trying to do is explained in this thread http://www.access-programmers.co.uk/forums/showthread.php?t=273814.

The approach I've taken is for the user to click a couple of buttons; one is to export some records effectively using "DoCmd.TransferText acExportDelim" and the other then effectively uses "DoCmd.TransferText acImportDelim".

I found out when testing the import that I needed these specifications. I've created them all now but using the wizard but I want to be able to do it within the code if possible.

 

[ryetee]

correct

you can, but it is a lot easier to create a schema.ini file - see these links

https://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx

http://stackoverflow.com/questions/...-i-need-to-export-my-csv-file-to-datagridview

http://aspdotnetcodes.com/Importing_CSV_Database_Schema.ini.aspx

http://windowssecrets.com/forums/showthread.php/123797-create-schema-ini-dinamic

THanks I'll take a look and report back!!

 

[ryetee]

correct

you can, but it is a lot easier to create a schema.ini file - see these links

https://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx

http://stackoverflow.com/questions/...-i-need-to-export-my-csv-file-to-datagridview

http://aspdotnetcodes.com/Importing_CSV_Database_Schema.ini.aspx

http://windowssecrets.com/forums/showthread.php/123797-create-schema-ini-dinamic

Had a quick look and it seems painful!! If I can only have one schema.ini file then I'll have to create dynamically. Thought it would be easier than this!!

 

[mh123]

If you go to navigation options then show system objects in access, you can look at MSysSpecs and MSysColumns which are system tables. In the 1st table you have the names of your import specifications, and the second the column names the specifications are looking for (and their type), you can see the spec ID each MSysColumn row corresponds with.

If it's unlikely to change very often, set up an import specification and then use that to import your files but if you have a change you can just add a row in the MSysColumn table as and when required for extra fields (not perfect, but worth it if the table isn't going to change very often imo)?

 

[ryetee]

Had a quick look and it seems painful!! If I can only have one schema.ini file then I'll have to create dynamically. Thought it would be easier than this!!

Had a further look now I'm properly awake on a Sunday and it doesn't seem as painful as I first thought! One of the original links was also in something like Italian which threw me!
I have a question.
You have to supply the path to where you want to place the schema.ini file. I want to put this in the my documents folder.
Depending on the machine this can be different (c:\user\fred\my documents, C:\users\anotheruser\my documents etc). How do I overcome that?

 

[ryetee]

Mmmm after all that it appears there is a bug in the transfertext method (http://support2.microsoft.com/kb/241477) when you try and use a schema so going back to let the wizard create the specification file!

 

[CJ_London]

The Schema.ini file needs to be in the same directory as the file being imported/exported.

With regards different paths, look at using the environ function - here is a link

http://codevba.com/office/environ.htm#USERPROFILE

With regards MSysSpecs and MSysColumns tables, be aware that although you can create these tables yourself, Access won't recognise them, although you can edit them once created. To create them, you need to create a specification by importing a table as already discussed

 

[CJ_London]

Mmmm after all that it appears there is a bug in the transfertext method

Relates to .mdb - is that what you are using? and also shows a very simple workaround, but up to you

 

[ryetee]

Relates to .mdb - is that what you are using? and also shows a very simple workaround, but up to you

No using accdb but getting the error message suggested in the bug.
Decided to not use schema for the time being and manage any table changes that crop up, which should be fairly rare.

I know this http://support2.microsoft.com/kb/241477 states .mdb but I can'tget rid of the 3625 error stated.

 

[essaytee]

Somehow this thread escaped me but is the same as this thread, as pointed out by the OP.

You want to transfer records between two near identical Access databases; Append and Update queries will do the job quite nicely and effortlessly.

 

[ryetee]

Somehow this thread escaped me but is the same as this thread, as pointed out by the OP.

You want to transfer records between two near identical Access databases; Append and Update queries will do the job quite nicely and effortlessly.

It's not quite the same as the above thread. The first thread was asking about the approach. After posting I decided to go ahead with using the TransferText method. I then hit some problems so decided it merited another thread!