UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Importexportspecifications.add Method (access), Access 2010    
 
   
Pavl
post Mar 27 2020, 01:12 PM
Post#1



Posts: 126
Joined: 18-October 12
From: RosBif en France


Hello to the forum.
Having another go at importexportspecs, looking at this MS article and trying to follow that by typing directly into 'immediate window', following intellisense and using an xml file saved in notepad++ (this is a reduced version of an import spec created and saved from a test accdb).
Getting error 'expected linenumber, labe or statement at end of statement'
Have only changed private details on path in this quote.
QUOTE
CurrentProject.ImportExportSpecifications.Add(impoexpotest4,<?xml version="1.0" encoding="utf-8" ?>
<ImportExportSpecification Path = "C:\Users\xxxx\Documents\yyyy\accdb_current\WorkingVersion.accdb" xmlns="urn:www.microsoft.com/office/access/imexspec">
<ImportAccess ImportExportSpecs="false" MenusAndToolbars="false" Relationships="true" NavPane="false" StructureAndData="true" QueriesAsTables="false" Resources="false">
<AccessObject Source="tbl_b24notes" ObjectType="Table" />
<AccessObject Source="tbl_b24fates_multi_dates" ObjectType="Table" />
<AccessObject Source="B24_testnotes3" ObjectType="Query" />
<AccessObject Source="B24s_flightcheck_qry" ObjectType="Query" />
<AccessObject Source="B24s_2019_SecondForm" ObjectType="Form" />
<AccessObject Source="B24s_flightlistform" ObjectType="Form" />
</ImportAccess>
</ImportExportSpecification>)asimportexportspecification

These objects exist and the earlier test import worked well, I could delete all objects and "execute" again with success.

Reason for banging my head against this is that I want to be able to regularly import about 190 objects into a new/clean accdb from a master "workingversion" of about 600 objects.
Why ? The accdb is for display of historical information and I have a large amount of work still to do but have number of core objects that function together as the display part with a lot of extra objects for data entry, editing and general development.
I want (and do so regularily) backup both the full fat version and the thin core version as separate accdbs.
Why ? To keep the core version unconstipated (does that word exist?).

So I am trying to create/sve a spec for a regular import and then "add" this to a fresh accdb and then "execute".
But my syntax in the vba is clearly wrong.
Any help would be much appreciated.
Go to the top of the page
 
ADezii
post Mar 28 2020, 11:35 AM
Post#2



Posts: 3,090
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
So I am trying to create/save a spec for a regular import and then "add" this to a fresh accdb and then "execute".

To the best of my knowledge, not that easy a Task, but I'll give it a try.
  1. Create a New Database, then: External Data ==> New Data Source ==> From Database ==> Access.
  2. Browse to the location of the Database with the Import Specification, then Open, OK.
  3. In the Import Options Dialog Box, click Options, and under Import, check the Import/Export Specs box, then OK (see Graphic).
  4. This Specification will now exist in the New Database and can be used in normal fashion.

Attached File(s)
Attached File  Import_Specs.JPG ( 53.19K )Number of downloads: 0
 
Go to the top of the page
 
Pavl
post Mar 28 2020, 12:00 PM
Post#3



Posts: 126
Joined: 18-October 12
From: RosBif en France


Thank you "ADezii".
I was aware of the standard method as that is how I created the test version.
But as my post says ... I need to do this for approx 180/190 objects ... which would take a while each time ...
As I want to have this process "execute" from a newly created accdb then the import spec will not be saved within that new accdb ... it would have to be created.
Once I have the first version saved out as xml then I hope to be able to write a procedure (perhaps from a very simple one button form) that will create and execute this import from saved file/s.
I have tried to understand the following to gain insight but this stuff is above my altitude ... ...from this forum... and ...also from this forum... and ...another forum...

It surely must be that I dont understand the "add" method and its arguments ... MS state this can be done (and this very old blog post here ... ) rather casually mentions at the end that this is easy !

And intellisense states "Add(Name As String, SpecificationDefinition As String) As ImportSpecification"
But clearly I got that bit wrong ....
I note that there is no specific requirement for a "description" ....

Simply dont understand the MS articles which offer no help whatsoever to beginners like me.
They are all available from the first MS article refering to methods (including 'Add') and properties .... here ....

I was also hoping to be able to refer directly to a saved xml file (in current project directory) instead of pasting in the xml ....

If intellisense accepts the first line up to the chosen new name and following comma then surely the rest of it, "SpecificationDefinition", must be capable of being entered ?

Does anyone know the correct structure of the whole "add" method ?
Paul
Go to the top of the page
 
ADezii
post Mar 28 2020, 01:35 PM
Post#4



Posts: 3,090
Joined: 4-February 07
From: USA, Florida, Delray Beach


I will see if I can come up with another approach.
Go to the top of the page
 
isladogs
post Mar 28 2020, 03:10 PM
Post#5


UtterAccess VIP
Posts: 2,404
Joined: 4-June 18
From: Somerset, UK


Whilst the wizard driven XML import/export data tasks introduced in A2007 may arguably be more powerful than the older approach using system tables, the process is much less transparent than was previously the case

See if my article about the 'new' approach helps: View and Edit IMEX Data Task Specifications

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
ADezii
post Mar 28 2020, 03:20 PM
Post#6



Posts: 3,090
Joined: 4-February 07
From: USA, Florida, Delray Beach


I finally got the whole process to work, but it is not exactly intuitive and is a little convoluted.
  1. I created an Import Specification named 'Order Details Import Specs' that defines the required steps to Import the 'Order Details.txt' File in the 'C:\Test\ Folder'. I then saved the XML to a unique location in the System Registry that is common to all Office Applications (see Graphics), namely:
    CODE
    Computer\HKEY_CURRENT_USER\Software\VB and VBA Program Settings\Import_Spec\XML
  2. Code to Save Import Specification XML to the Registry:
    CODE
    On Error Resume Next
    Dim strXML As String
    Dim MySpec As ImportExportSpecification

    Set MySpec = CurrentProject.ImportExportSpecifications("Order Details Import Specs")

    strXML = MySpec.XML

    DeleteSetting "Import_Spec", "Order Details"

    SaveSetting appname:="Import_Spec", Section:="XML", _
                          Key:="Order Details", setting:=strXML
  3. I then created a New Database with Code that will:
    1. Retrieve the XML from the Specification defined in the Master Database ('Order Details Import Specs').
    2. Added a New Specification named 'Demo Import Spec' based on the XML from the Specification in the MASTER.
    3. Executed the Import Specification.
    4. Refreshed the Database Window so it can display the newly Imported Table.
  4. Code Definition in New Database:
    CODE
    On Error Resume Next
    Dim strXML As String

    strXML = GetSetting(appname:="Import_Spec", Section:="XML", Key:="Order Details")

    CurrentProject.ImportExportSpecifications.Add "Demo Import Spec", strXML

    CurrentProject.ImportExportSpecifications("Demo Import Spec").Execute

    RefreshDatabaseWindow
  5. Again, I do realize that this approach is a little convoluted, but it is the only one that I can come up with for the present time. I also works quite well. laugh.gif

This post has been edited by ADezii: Mar 28 2020, 03:40 PM
Attached File(s)
Attached File  Registry1.JPG ( 13.85K )Number of downloads: 0
Attached File  Registry2.JPG ( 20.65K )Number of downloads: 3
 
Go to the top of the page
 
Pavl
post Mar 28 2020, 03:36 PM
Post#7



Posts: 126
Joined: 18-October 12
From: RosBif en France


Thank you to "isladogs" and "ADezii".
I have had one attempt at the form/module mentioned by "isladogs" but was advised by you that this would not accomplish my aims .. but will read again.
"ADezil" ... will read, digest and attempt.
Please bear with me as i am slower than you lot ...
Paul
Go to the top of the page
 
isladogs
post Mar 28 2020, 04:50 PM
Post#8


UtterAccess VIP
Posts: 2,404
Joined: 4-June 18
From: Somerset, UK


Hi Pavl
Can you explain your comment where you state that I advised you that my approach would not achieve your aims. I'm not clear what you are talking about.


--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
ADezii
post Mar 29 2020, 06:52 AM
Post#9



Posts: 3,090
Joined: 4-February 07
From: USA, Florida, Delray Beach


I have found a more efficient and cleaner approach that avoids using the System Registry. The XML for the Specification is written to a File, presumably on a Public Server, where all Client PCs can then access and execute. I'll post the Code when I get a chance.
Go to the top of the page
 
ADezii
post Mar 29 2020, 08:42 AM
Post#10



Posts: 3,090
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
I was also hoping to be able to refer directly to a saved xml file (in current project directory) instead of pasting in the xml ....

  1. Assuming you have an Import Specification named 'Order Details Import Specs', Copy the XML Definition of this Specification to a File, presumably to one on a Public Server where all the FEs can access it. For purposes of my Demo, I simply used 'C:\Test\MySpec.xml' as the Path for the XML.
    CODE
    Dim strXML As String
    Dim MySpec As ImportExportSpecification

    Set MySpec = CurrentProject.ImportExportSpecifications("Order Details Import Specs")

    strXML = MySpec.XML

    Open "C:\Test\MySpec.xml" For Output As #1
      Print #1, strXML
    Close #1
  2. Modify the XML File if needed.
  3. Have all the FEs execute the following Code that will:
    1. Read the entire XML File into Memory and assign it to a Variable.
    2. Add the Import Specification to the Front End Database.
    3. Execute the Specification, Importing the Table from the Text File, both of which are defined in the Specification:

    CODE
    On Error Resume Next
    Dim strFile As String

    strFile = "C:\Test\MySpec.xml"

    Open strFile For Binary Access Read As #1
    strFile = Space(LOF(1))

    Get #1, , strFile   'Read entire File into Memory and place in strFile

    Close #1

    With CurrentProject
      .ImportExportSpecifications.Add "Order Details Import", strFile
      .ImportExportSpecifications("Order Details Import").Execute
    End With

    RefreshDatabaseWindow
  4. Should you wish to DELETE the Import Specification after Execution, try:
    CODE
    CurrentProject.ImportExportSpecifications("Order Details Import").Delete
  5. The Code has been fully tested and is completely operational.
Go to the top of the page
 
Pavl
post Mar 29 2020, 10:53 AM
Post#11



Posts: 126
Joined: 18-October 12
From: RosBif en France


Hello "isladogs".
I dont know how to correctly quote a thread on this forum ... so it is on ...Access Automation>Msysimexspecs, Access 2010 ... your post 15 (which might appear ... here....

Am trying the method from "ADezii" now that I have a bit of time but am struggling ... will reply separately below.
Paul
Go to the top of the page
 
Pavl
post Mar 29 2020, 11:02 AM
Post#12



Posts: 126
Joined: 18-October 12
From: RosBif en France


Thank you "ADezii"
No time till now ....
Thank you for revised version ... was struggling with even getting the first bit to run ... I had noted the "SaveSetting" and found out that this would create the HKey upon first use but never got that far ..
So will try new version and report back.
Paul
Go to the top of the page
 
ADezii
post Mar 29 2020, 11:24 AM
Post#13



Posts: 3,090
Joined: 4-February 07
From: USA, Florida, Delray Beach


Forget writing to the Registry and try the newer Revision.

P.S. - If you are still struggling, I can actually Upload the Text and XML Files, then all you would need to do is to Copy them into a certain Folder, and Copy-N-Paste then execute the correct Code. Just let me know.
This post has been edited by ADezii: Mar 29 2020, 11:39 AM
Go to the top of the page
 
isladogs
post Mar 29 2020, 03:03 PM
Post#14


UtterAccess VIP
Posts: 2,404
Joined: 4-June 18
From: Somerset, UK


@pavl
I'd forgotten that thread which you managed to quote correctly.
IIRC your aims at that time did not require the use of my code.
It seems that you are now asking something slightly different and my code should now be helpful.
However it is likely that my code and adezii's are similar as its the same process being managed.
My code doesn't make any changes to the registry either. Its not necessary to do so.

@adezii
Just out of interest, did you look at my example app / code?

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
ADezii
post Mar 29 2020, 04:11 PM
Post#15



Posts: 3,090
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
Just out of interest, did you look at my example app / code?

I did, and I must say that it is a very impressive piece of Programming. like.png
  1. The problem may reside with me in that I think I may be getting a little lost regarding this Thread.
  2. I was, and still is, under the assumption that the OP's intent is to:
    • Create Import Specifications for multiple Objects that will need to be Imported into a 'MASTER' Database.
    • Create New Databases and add the Import Specs previously created in the 'MASTER'.
    • Execute the Specs in the New Database.
    • DELETE the Specifications in the New DB after the Import Process for each has been completed.
    • I assumed, possibly incorrectly, that a FE...BE structure will exist which is why I suggested copying the Text and XML Files to a Public Server.
  3. If you interpret things differently, please let me know. The bottom line is that only the OP knows what he wants for sure.
Go to the top of the page
 
Pavl
post Mar 30 2020, 02:48 AM
Post#16



Posts: 126
Joined: 18-October 12
From: RosBif en France


Thank you both again ...

Clearly I am not being clear .....

Firstly I have no coding experience other than that gained over time on my own and with help from the various forums such as this one and the Google family.

The accdb is only for display of historical information, often incomplete, sometimes conflicting, and in 'development' for quite a while ....

One main/full fat accdb with all objects, both core functioning along with detritus objects from previous edits/changes and currently useful editing objects.
This is a long term project and the original information is nothing like 'clean' or organised ... so there are many changes/alterations/additions over time.
Hence the current bloated accdb.
The core objects are fully functional BUT they will be changed slightly over time (new data, better method of organisation and so on).

So I want to 'fork' the accdb into two parallel versions.

Version one is the full fat ... with normal regular backup copies. This is the version that I will continue to work with for edits/development.

Version two is just the core/thin objects ... again with regular backup copies .... but ....made by the import process from the 'fat' version ... not by copying out from this 'thin' version.
This is the version that is only 'used' ... no edits/changes.

This import process is intended to go only from current working 'fat' version into a newly created 'thin' version.

I am happy with having to occasionally modify a stored xml file if the core object list changes over time.

So I can do/have done the fork .... but that involves nearly 200 objects (from over 600 total ... hence the desire for a reusable import specification.).

This number is intended for reduction ... which will also be part of the edit/development as it progresses.

So I need to copy out (import into clean new accdb) the core objects on a regular basis.

It would be no good just making backups of the thin version because they would no longer be current.

In the meantime I am having a go at the methods you have both kindly offered ... might be a little while ...

Thanks again
Paul
Go to the top of the page
 
isladogs
post Mar 30 2020, 03:11 AM
Post#17


UtterAccess VIP
Posts: 2,404
Joined: 4-June 18
From: Somerset, UK


Ah. I remember now why I said that there was a better way.
You are once again making this unnecessarily complicated.

You can import just the objects you need to a new blank database (your 'thin' one)
When you do so, tick the option to include import/export specs.
This will copy all saved imports/exports to the new database. Job done!

If you've already made your thin database, you can still transfer the import/export specs from the original database using that method



--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
Pavl
post Mar 30 2020, 03:47 AM
Post#18



Posts: 126
Joined: 18-October 12
From: RosBif en France


Thank you isladogs ...
Yes .. I see your reasoning and this makes perfect sense ...
So now the process involves copying/backing up just the 'thin' one form itself each time ... by importing ... and would need to delete all objects and all indexes first ?

The background for my proposed method was to create 'clean' new copies instead of backups ...
I have had past problems with compact repair and all the vba vanishing ... reading articles seems to suggest these as either "non documented features"! or residual effects from a bloated database with lots of redundant artefacts ... hence me looking for a way to get fresh versions instead of deleting then copying over the top each time.

And just as an aside ...
I have tried the second method from Adezii and failed straight way from a command button to run the first bit of procedure ..
Ignored that fail and just ran the second bit from another cmd button on same form... and it worked.

This was from a newly created empty accdb with a small test xml file.
It just plonked the objects straight in.

Dont know how to test if the first bit actually did enough or was not needed ..
Got error line 3 .... Set impospec = CurrentProject.ImportExportSpecifications("test3") ....
Error was "Index does not exist..."
I see "ITEM(0)" and "NAME" from the MS article but not "INDEX" .... so beginners like me get puzzled ..

So as far as I can see the first procedure was stopped before it did anything ?

In "Manage Data Tasks" I now have a first test specification and a new one .....
This has no name and no description ....... but has the icon and supposed ability to edit.
Immediate window ...
? CurrentProject.ImportExportSpecifications.Count ... gives result of 2.

Anyway .. thank you both for your help and will continue with both methods ....
Paul
Go to the top of the page
 
isladogs
post Mar 30 2020, 07:06 AM
Post#19


UtterAccess VIP
Posts: 2,404
Joined: 4-June 18
From: Somerset, UK


Hi Pavl
Let me repeat what I said before. None of this code is needed to fulfil your aims. Neither mine nor adezii's.

Importing required objects plus import/export specifications into a new blank database is creating a new 'clean' copy.
That's ALL you need to do.

On the highly unlikely event it goes wrong, you've still got the original database so you can repeat if necessary,

Once the objects have been transferred there should be no need to ever do it again. Just use the new database
However if data is still being added / edited in the original database , you can easily synchronise this data at a later date.

So stop trying to make the code work. Both sets of code do work fine BUT you don't need them.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
ADezii
post Mar 30 2020, 08:58 AM
Post#20



Posts: 3,090
Joined: 4-February 07
From: USA, Florida, Delray Beach


After clarification by both you and isladogs, I agree with him wholeheartedly that this process can be greatly simplified. With a very small amount of Code, as many Objects as you desire can be Imported from an External (in your case, MASTER) Database. Here is another approach that I can up with. To be honest, I am running out of them. laugh.gif
  1. I created an Internal List (tblObjectsToImport) of Objects to Import, namely: Tables, Queries, Forms, Reports, Modules. These Objects exist in the C:\Test\MASTER.accdb Database.
    ImportIDObject_NameObject_TypeObject_Value
    1InvoicesTable0
    2OrdersTable0
    3ProductsTable0
    4Order DetailsForm2
    5Employee Address BookReport3
    6Yearly Sales ReportReport3
    7RecordsetWrapperModule5
    8Customer ListForm2
    9UtilitiesModule5
    10Product DetailsForm2
    11Purchase Price TotalsQuery1
    12Inventory SoldQuery1
  2. With an absolute minimal amount of Code, I Imported all of the Objects listed in tblObjectsToImport to my Current DB.
    CODE
    On Error Resume Next
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Const conPATH_TO_MASTER = "C:\Test\MASTER.accdb"

    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset("tblObjectsToImport", dbOpenForwardOnly)
    DoCmd.Hourglass True

    With rst
      Do While Not .EOF
        DoCmd.DeleteObject ![Object_Value], ![Object_Name]
        DoCmd.TransferDatabase acImport, "Microsoft Access", conPATH_TO_MASTER, _
                               ![Object_Value], ![Object_Name], ![Object_Name]
          .MoveNext
      Loop
    End With

    DoCmd.Hourglass False

    RefreshDatabaseWindow

    rst.Close
    Set rst = Nothing
  3. I have uploaded a Graphic of my DB Window after Code Execution.
  4. I did not address Import Specs at this time since I am not sure if they are even needed.

This post has been edited by ADezii: Mar 30 2020, 09:00 AM
Attached File(s)
Attached File  DB_Window.JPG ( 27.28K )Number of downloads: 1
 
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    14th July 2020 - 05:02 AM