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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access To Oracle Xmlexporter    
 
   
catalystvibe
post Dec 4 2013, 12:57 PM
Post#1



Posts: 24
Joined: 5-November 13



Hi,
I am attempting to migrate my Access 2007 db to an Oracle db and have got as far as trying to use the xmlexport tool which, if you're familiar with this operation, is the first step in migrating. When I go to use the exporter to generate the xml and SQL schema I get error #3270 Method 'Run' of object '_Application' failed ....(my file destination trying to export to) Database Schema Export did not complete successfully. Things you should know are that I am not an administrator on my machine and therefore unable to write to the folder where MS Access is installed, in reading through the how to process for migration this is a requirement in order for the exporter to work. I am wondering if there is a work around?
Thanks
Go to the top of the page
 
genoma111
post Dec 4 2013, 01:07 PM
Post#2



Posts: 2,018
Joined: 2-June 09
From: Bogotá - Colombia


This example from the msdn can help you to accomplish that task.
You can export to whatever location you want.
http://msdn.microsoft.com/en-us/library/ff193212.aspx
Regards,
Diego
Go to the top of the page
 
catalystvibe
post Dec 4 2013, 01:22 PM
Post#3



Posts: 24
Joined: 5-November 13



Diego,
Is this done within Access?
Thanks
Go to the top of the page
 
genoma111
post Dec 4 2013, 01:43 PM
Post#4



Posts: 2,018
Joined: 2-June 09
From: Bogotá - Colombia


Yeap.
It's pretty simple and works great.
Give it a try and you'll see.
If you have any questions post them here.
Regards,
Diego
Go to the top of the page
 
genoma111
post Dec 4 2013, 01:52 PM
Post#5



Posts: 2,018
Joined: 2-June 09
From: Bogotá - Colombia


For example:
CODE
Option Compare Database
Option Explicit
Private Sub Command0_Click()
Dim objOrderInfo           As AdditionalData
Dim objOrderDetailsInfo    As AdditionalData

Set objOrderInfo = Application.CreateAdditionalData
Set objOrderDetailsInfo = objOrderInfo.Add("IngresoDiario")
objOrderDetailsInfo.Add "ConsProc"
Application.ExportXML ObjectType:=acExportTable, DataSource:="Pacientes", _
DataTarget:="C:\Users\Diego\Documents\PacientesJVF.xml", _
SchemaTarget:="C:\Users\Diego\Documents\PacientesJVF.xsd", _
AdditionalData:=objOrderInfo

' Application.ExportXML acExportTable, "Pacientes", "C:\Users\Diego\Documents\PacientesJVF.xml"
End Sub

Oleave the same object names as in the msdn example for your reference.
Go to the top of the page
 
catalystvibe
post Dec 4 2013, 03:21 PM
Post#6



Posts: 24
Joined: 5-November 13



I don't have much experience with this, how is this different from the xml exporter tool that is within Access 2007 already? Also, can this be applied to the db as a whole or just individual objects within the db?
Thanks
Go to the top of the page
 
genoma111
post Dec 4 2013, 03:40 PM
Post#7



Posts: 2,018
Joined: 2-June 09
From: Bogotá - Colombia


Sometimes is easier if you do the tests.
THere is a way for exporting all tables:
CODE
Private Sub Command0_Click()
    Dim oAdd   As AdditionalData
    Dim dbs    As DAO.Database
    Dim tdf    As DAO.TableDef
    
    Set dbs = CurrentDb
    Set oAdd = Application.CreateAdditionalData
        
    For Each tdf In dbs.TableDefs
        If tdf.Name Like "MSys*" Or tdf.Name Like "~*" Then
        Else
            oAdd.Add tdf.Name
        End If
    Next tdf
    
    Application.ExportXML ObjectType:=acExportTable, DataSource:="Any Table", _
    DataTarget:="C:\YourPath\YourXmlFile.xml", _
    SchemaTarget:="C:\YourPath\YourXmlFile.xsd", _
    AdditionalData:=oAdd
    
    Set oAdd = Nothing
    Set dbs = Nothing
And Sub

Edit: The table you choose to be the DataSource should also be excluded in the For each tdf loop, otherwise you are going to include it again in your xml.
Go to the top of the page
 
catalystvibe
post Dec 5 2013, 01:00 PM
Post#8



Posts: 24
Joined: 5-November 13



should this be run as a module within my db?
Go to the top of the page
 
genoma111
post Dec 5 2013, 01:39 PM
Post#9



Posts: 2,018
Joined: 2-June 09
From: Bogotá - Colombia


Oh well, I thought you were more familiar with Access.
et's simplify a bit this task for you...
First add a command button to a form.
-. Create a form
-. Go to Design View
-. Go to Design Tab
-. In "controls" locate "Command Button" -> A rectangle with three dots inside.
-. Put your command button anywhere in your form.
-. Cancel the Wizard
-. Go to the "Property Sheet" (on the right side of your window) -> if it is not showed by default click on the "Property Sheet" button in your "Design tab".
-. In the "Property Sheet" go to the "Event" tab.
-. Locate the event "On Click"
-. Click on the three dots.
-. Click on "Code Builder"
-. Add this code:
CODE
Dim strPath
strPath = CurrentProject.Path ' Change this to your custom path i.e. "C:\Users\catalystvibe\Documents"
Call ExportTablesToXML("Pacientes", strPath)

-. So your code should look like:
CODE
Private Sub Command0_Click()
Dim strPath
strPath = CurrentProject.Path ' Change this to your custom path i.e. "C:\Users\catalystvibe\Documents"
Call ExportTablesToXML("Pacientes", strPath)
End Sub

-. Now paste this code just below the "End Sub" of the previous code:
CODE
Sub ExportTablesToXML(strATable As String, _
                               strPath As String, _
                      Optional strFile As String = "MyDbInXml")
                      
    Dim oAdd    As AdditionalData
    Dim dbs     As DAO.Database
    Dim tdf     As DAO.TableDef
    Dim qdf     As DAO.QueryDef
    Dim strPF   As String
    
    strPF = strPath & "\" & strFile
    
    Set dbs = CurrentDb
    Set oAdd = Application.CreateAdditionalData
        
    For Each tdf In dbs.TableDefs
        If tdf.Name Like "MSys*" Or tdf.Name Like "~*" Or tdf.Name = strATable Then
        Else
            oAdd.Add tdf.Name
        End If
    Next tdf
    For Each qdf In dbs.QueryDefs
        If qdf.Name Like "~*" Then
        Else
            oAdd.Add qdf.Name
        End If
    Next qdf
    
    Application.ExportXML ObjectType:=acExportTable, DataSource:=strATable, _
    DataTarget:=strPF & ".xml", _
    SchemaTarget:=strPF & ".xsd", _
    AdditionalData:=oAdd
    
    Set oAdd = Nothing
    Set dbs = Nothing
    
    MsgBox "All tables have been successfully exported"
End Sub

This code will export all your tables and queries to your xml file.
Queries will be treated as tables...
Queries that use parameters taken from other objects (i.e. Forms) will need that those parameters are filled in the appropriate object before running this code, otherwise the procedure will fail.
HTH,
Diego
Go to the top of the page
 
catalystvibe
post Dec 13 2013, 03:42 PM
Post#10



Posts: 24
Joined: 5-November 13



Finally worked up the cojones to give this and try and got this:
Private Sub Command0_Click()
strPath = CurrentProject.Libraries \ Documents
Call ExportTablesToXML("Pacientes", strPath)
End Sub
Sub ExportTablesToXML(strATable As String, _
strPath As String, _
Optional strFile As String = "MyDbInXml")

Dim oAdd As AdditionalData
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Dim strPF As String

strPF = strPath & "\" & strFile

Set dbs = CurrentDb
Set oAdd = Application.CreateAdditionalData

For Each tdf In dbs.TableDefs
If tdf.Name Like "MSys*" Or tdf.Name Like "~*" Or tdf.Name = strATable Then
Else
oAdd.Add tdf.Name
End If
Next tdf
For Each qdf In dbs.QueryDefs
If qdf.Name Like "~*" Then
Else
oAdd.Add qdf.Name
End If
Next qdf

Application.ExportXML ObjectType:=acExportTable, DataSource:=strATable, _
DataTarget:=strPF & ".xml", _
SchemaTarget:=strPF & ".xsd", _
AdditionalData:=oAdd

Set oAdd = Nothing
Set dbs = Nothing

MsgBox "All tables have been successfully exported"

End Sub
VB didn't like the strPath in this part Call ExportTablesToXML("Pacientes", strPath)
Go to the top of the page
 
catalystvibe
post Dec 13 2013, 03:51 PM
Post#11



Posts: 24
Joined: 5-November 13



tried it again with the full path and srill didn't work. I certainly have objects (forms) that call on data from other objects, could this be why it isn't working?
Private Sub Command0_Click()
Dim strPath
strPath = "C:\Users\ewalling\Documents"
Call ExportTablesToXML("Pacientes", strPath)
End Sub
Sub ExportTablesToXML(strATable As String, _
strPath As String, _
Optional strFile As String = "MyDbInXml")

Dim oAdd As AdditionalData
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Dim strPF As String

strPF = strPath & "\" & strFile

Set dbs = CurrentDb
Set oAdd = Application.CreateAdditionalData

For Each tdf In dbs.TableDefs
If tdf.Name Like "MSys*" Or tdf.Name Like "~*" Or tdf.Name = strATable Then
Else
oAdd.Add tdf.Name
End If
Next tdf
For Each qdf In dbs.QueryDefs
If qdf.Name Like "~*" Then
Else
oAdd.Add qdf.Name
End If
Next qdf

Application.ExportXML ObjectType:=acExportTable, DataSource:=strATable, _
DataTarget:=strPF & ".xml", _
SchemaTarget:=strPF & ".xsd", _
AdditionalData:=oAdd

Set oAdd = Nothing
Set dbs = Nothing

MsgBox "All tables have been successfully exported"
End Sub
Go to the top of the page
 
genoma111
post Dec 13 2013, 07:36 PM
Post#12



Posts: 2,018
Joined: 2-June 09
From: Bogotá - Colombia


The previous path seems strange, but the full path should work.
What error are you receiving?
Edit: Do you have a table called "Pacientes" ?
If not put the name of a table of your own database there.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 11:47 AM