coding, programming, working-924920.jpg

How to import/export data from sql with vba access

Export from mssql table to xml file (in my case 2016)

First, in access you need to enable xml library

Secondly, create a function within vba module similar to the one I made.

Option Compare Database

Option Explicit

Dim sqlCommand As ADODB.Command

Dim conn As ADODB.Connection

Dim rst As New ADODB.Recordset

Public Function ExportaXmlImportexBCM()

Set conn = cn ‘ cn is database function to establish connection to sql server,

Dim xDoc As New MSXML2.DOMDocument60

Dim schemaNode As MSXML2.IXMLDOMNode

Const adPersistXML = 1

rst.Open “SELECT * FROM importex_bonuri_consum”, conn

rst.Save xDoc, adPersistXML

With xDoc

        ‘Remove schema node and hence unused namespace declarations

        ‘First select the Schema node and then call removeChild

        .SetProperty “SelectionNamespaces”, _

            “xmlns:s=’uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882′”

        Set schemaNode = .SelectSingleNode(“//s:Schema”)

        With .DocumentElement

            .RemoveChild schemaNode

            .removeAttribute “xmlns:z”

            .removeAttribute “xmlns:rs”

            .removeAttribute “xmlns:s”

            .removeAttribute “xmlns:dt”

        End With

        ‘Use MSXML DOM to save the document, change your variables to fit to your environment

        .Save “\\192.168.1.100\xmls\imports\example.xml”

    End With

rst.Close

End Function

Importing into mssql table from xml file with vba access (in my case 2016)

First option is using sql Bulk metod from mssql server.

I used it import for a front-end made by myself to an acconunting software called Nexus used in Romania. The table is called

Public Function ImportaBCMImportex()

Set conn = cn ‘ cn is database function to establish connection to sql server,

Dim sqlCommand As New ADODB.Command

With sqlCommand

.ActiveConnection = conn

.CommandType = adCmdText

.CommandText = “declare @xmldata XML = (SELECT CONVERT(XML, BulkColumn) AS BulkColumn FROM OPENROWSET(BULK ‘\\192.168.1.100\xmls\imports\example.xml‘, SINGLE_CLOB)as X)” _

& “;WITH XMLNAMESPACES(‘urn:schemas-microsoft-com:rowset’ AS rs, ‘#RowsetSchema’ ASz)” _

& “insert into importex_bonuri_consum (id_importex,id_document,id_carnet,serie_document,numar_document,data_document,id_gestiune,den_gestiune,id_primitor,den_primitor, ” _

& “id_centru_profit,den_centru_profit,id_beneficiar,cif_beneficiar,den_beneficiar,cont_debit,observatii,validare,valoare,nume_transport,id_transport)” _

& “SELECT Nodes.Attr.value (‘@id_importex’, ‘varchar(25)’) AS ‘id_importex’, ” _

& “Nodes.Attr.value (‘@id_document’, ‘varchar(25)’) AS ‘id_document’,” _

& “Nodes.Attr.value (‘@id_carnet’, ‘bigint’) AS ‘id_carnet’, ” _

& “Nodes.Attr.value (‘@serie_document’, ‘varchar(10)’) AS ‘serie_document’, ” _

& “Nodes.Attr.value (‘@numar_document’, ‘varchar(20)’) AS ‘numar_document’, ” _

& “Nodes.Attr.value (‘@data_document’, ‘date’) AS ‘data_document’,” _

& “Nodes.Attr.value (‘@id_gestiune’, ‘varchar(25)’) AS ‘id_gestiune’,” _

& “Nodes.Attr.value (‘@den_gestiune’, ‘varchar(30)’) AS ‘den_gestiune’,” _

& “Nodes.Attr.value (‘@id_primitor’, ‘varchar(25)’) AS ‘id_primitor’,” _

& “Nodes.Attr.value (‘@den_primitor’, ‘varchar(30)’) AS ‘den_primitor’,” _

& “Nodes.Attr.value (‘@id_centru_profit’, ‘varchar(25)’) AS ‘id_centru_profit’,” _

& “Nodes.Attr.value (‘@den_centru_profit’, ‘varchar(30)’) AS ‘den_centru_profit’,” _

& “Nodes.Attr.value (‘@id_beneficiar’, ‘varchar(25)’) AS ‘id_beneficiar’,” _

& “Nodes.Attr.value (‘@cif_beneficiar’, ‘varchar(20)’) AS ‘cif_beneficiar’,” _

& “Nodes.Attr.value (‘@den_beneficiar’, ‘varchar(100)’) AS ‘den_beneficiar’,” _

& “Nodes.Attr.value (‘@cont_debit’, ‘varchar(17)’) AS ‘cont_debit’,” _

& “Nodes.Attr.value (‘@observatii’, ‘varchar(250)’) AS ‘observatii’,” _

& “Nodes.Attr.value (‘@validare’, ‘bit’) AS ‘validare’,” _

& “Nodes.Attr.value (‘@valoare’, ‘decimal(13,2)’) AS ‘valoare’,” _

& “Nodes.Attr.value (‘@nume_transport’, ‘varchar(20)’) AS ‘nume_transport’, ” _

& “Nodes.Attr.value (‘@id_transport’, ‘varchar(25)’) AS ‘id_transport’ FROM @xmldata.nodes(‘/xml/rs:data/z:row’) AS Nodes(Attr) “

.Execute

End With

End Function

I will update the second option for restricted mssql servers which don’t allow bulk method for import.

Leave a Reply

Your email address will not be published.