Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

sync mdb with mdb on web server

Discussion in 'Information Technology' started by nycdon, Oct 31, 2005.

Tags:
  1. nycdon

    nycdon
    Expand Collapse
    Guest

    I'm looking into collecting data via asp.net, and putting into an Access mdb
    on hosting web server. What possilbilties are there for passing these updates
    to an Acccess 2002 mdb, say on desktop or office server.
    Or can users work directly with mdb on web server?
    Thanks,
    Don
     
  2. Loading...

    Similar Threads Forum Date
    Synchronicity Interfaith Dialogues Nov 18, 2007
    Apple posts iSync security fix (MacCentral) Interfaith Dialogues Apr 20, 2005

  3. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    You can write an ASP.NET web service that runs on the server and returns the
    data to your Access app. Access 2002 and Access 2003 can call a web service
    if you install the appropriate version of the Office Web Services Toolkit.
    The Office 2003 version is at ...

    http://www.microsoft.com/downloads/...8a-e1cf-48a3-9b35-169d819ecf18&DisplayLang=en

    And the Office XP version is at ...

    http://www.microsoft.com/downloads/...0f-002a-4f5b-af74-978f2cd6c798&DisplayLang=en

    I'm not entirely sure what you mean by the second part of your question -
    I'm not sure what 'work directly' might mean, in this context.

    --
    Brendan Reynolds

    "nycdon" <nycdon@discussions.microsoft.com> wrote in message
    news:5C71A3E5-4970-436B-8DF4-B97BA08A2DC7@microsoft.com...
    > I'm looking into collecting data via asp.net, and putting into an Access
    > mdb
    > on hosting web server. What possilbilties are there for passing these
    > updates
    > to an Acccess 2002 mdb, say on desktop or office server.
    > Or can users work directly with mdb on web server?
    > Thanks,
    > Don
    >
     
  4. nycdon

    nycdon
    Expand Collapse
    Guest

    Thanks Brendan. Not sure what I meant either by acccessing directly either,
    in this context..;)

    So ASP.Net web service would be like an exposed function/class, that Access
    Web service could pick up? Like asp.net perhaps reveals a select query
    showing updated data, and Access service grabs that data and updates the
    office mdb?


    "Brendan Reynolds" wrote:

    > You can write an ASP.NET web service that runs on the server and returns the
    > data to your Access app. Access 2002 and Access 2003 can call a web service
    > if you install the appropriate version of the Office Web Services Toolkit.
    > The Office 2003 version is at ...
    >
    > http://www.microsoft.com/downloads/...8a-e1cf-48a3-9b35-169d819ecf18&DisplayLang=en
    >
    > And the Office XP version is at ...
    >
    > http://www.microsoft.com/downloads/...0f-002a-4f5b-af74-978f2cd6c798&DisplayLang=en
    >
    > I'm not entirely sure what you mean by the second part of your question -
    > I'm not sure what 'work directly' might mean, in this context.
    >
    > --
    > Brendan Reynolds
    >
    > "nycdon" <nycdon@discussions.microsoft.com> wrote in message
    > news:5C71A3E5-4970-436B-8DF4-B97BA08A2DC7@microsoft.com...
    > > I'm looking into collecting data via asp.net, and putting into an Access
    > > mdb
    > > on hosting web server. What possilbilties are there for passing these
    > > updates
    > > to an Acccess 2002 mdb, say on desktop or office server.
    > > Or can users work directly with mdb on web server?
    > > Thanks,
    > > Don
    > >

    >
    >
    >
     
  5. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    Once you've installed the Web Services Toolkit, you can add a reference to a
    web service, and the toolkit creates a VBA class for you that has methods
    that call the corresponding methods in the web service. Using the
    toolkit-created class in the Access app is very easy. The tricky bit is
    writing the web service in the first place. ADO.NET makes it easy to get XML
    from the DataSet object, unfortunately, the XML is not in a form that can be
    easily consumed by Access. I had to use XSLT in the web service to transform
    the XML into a form that could be used as the source for an ADO recordset in
    Access. I can post the code if you think it will help - it's in C#.

    --
    Brendan Reynolds

    "nycdon" <nycdon@discussions.microsoft.com> wrote in message
    news:E49E6AF2-6764-4E17-8283-D788D3DDBB7A@microsoft.com...
    > Thanks Brendan. Not sure what I meant either by acccessing directly
    > either,
    > in this context..;)
    >
    > So ASP.Net web service would be like an exposed function/class, that
    > Access
    > Web service could pick up? Like asp.net perhaps reveals a select query
    > showing updated data, and Access service grabs that data and updates the
    > office mdb?
    >
    >
    > "Brendan Reynolds" wrote:
    >
    >> You can write an ASP.NET web service that runs on the server and returns
    >> the
    >> data to your Access app. Access 2002 and Access 2003 can call a web
    >> service
    >> if you install the appropriate version of the Office Web Services
    >> Toolkit.
    >> The Office 2003 version is at ...
    >>
    >> http://www.microsoft.com/downloads/...8a-e1cf-48a3-9b35-169d819ecf18&DisplayLang=en
    >>
    >> And the Office XP version is at ...
    >>
    >> http://www.microsoft.com/downloads/...0f-002a-4f5b-af74-978f2cd6c798&DisplayLang=en
    >>
    >> I'm not entirely sure what you mean by the second part of your question -
    >> I'm not sure what 'work directly' might mean, in this context.
    >>
    >> --
    >> Brendan Reynolds
    >>
    >> "nycdon" <nycdon@discussions.microsoft.com> wrote in message
    >> news:5C71A3E5-4970-436B-8DF4-B97BA08A2DC7@microsoft.com...
    >> > I'm looking into collecting data via asp.net, and putting into an
    >> > Access
    >> > mdb
    >> > on hosting web server. What possilbilties are there for passing these
    >> > updates
    >> > to an Acccess 2002 mdb, say on desktop or office server.
    >> > Or can users work directly with mdb on web server?
    >> > Thanks,
    >> > Don
    >> >

    >>
    >>
    >>
     
  6. nycdon

    nycdon
    Expand Collapse
    Guest

    Thanks much Brendan..and sure, if you could post the c# sharp that would be
    great!

    Don

    "Brendan Reynolds" wrote:

    > Once you've installed the Web Services Toolkit, you can add a reference to a
    > web service, and the toolkit creates a VBA class for you that has methods
    > that call the corresponding methods in the web service. Using the
    > toolkit-created class in the Access app is very easy. The tricky bit is
    > writing the web service in the first place. ADO.NET makes it easy to get XML
    > from the DataSet object, unfortunately, the XML is not in a form that can be
    > easily consumed by Access. I had to use XSLT in the web service to transform
    > the XML into a form that could be used as the source for an ADO recordset in
    > Access. I can post the code if you think it will help - it's in C#.
    >
    > --
    > Brendan Reynolds
    >
    > "nycdon" <nycdon@discussions.microsoft.com> wrote in message
    > news:E49E6AF2-6764-4E17-8283-D788D3DDBB7A@microsoft.com...
    > > Thanks Brendan. Not sure what I meant either by acccessing directly
    > > either,
    > > in this context..;)
    > >
    > > So ASP.Net web service would be like an exposed function/class, that
    > > Access
    > > Web service could pick up? Like asp.net perhaps reveals a select query
    > > showing updated data, and Access service grabs that data and updates the
    > > office mdb?
    > >
    > >
    > > "Brendan Reynolds" wrote:
    > >
    > >> You can write an ASP.NET web service that runs on the server and returns
    > >> the
    > >> data to your Access app. Access 2002 and Access 2003 can call a web
    > >> service
    > >> if you install the appropriate version of the Office Web Services
    > >> Toolkit.
    > >> The Office 2003 version is at ...
    > >>
    > >> http://www.microsoft.com/downloads/...8a-e1cf-48a3-9b35-169d819ecf18&DisplayLang=en
    > >>
    > >> And the Office XP version is at ...
    > >>
    > >> http://www.microsoft.com/downloads/...0f-002a-4f5b-af74-978f2cd6c798&DisplayLang=en
    > >>
    > >> I'm not entirely sure what you mean by the second part of your question -
    > >> I'm not sure what 'work directly' might mean, in this context.
    > >>
    > >> --
    > >> Brendan Reynolds
    > >>
    > >> "nycdon" <nycdon@discussions.microsoft.com> wrote in message
    > >> news:5C71A3E5-4970-436B-8DF4-B97BA08A2DC7@microsoft.com...
    > >> > I'm looking into collecting data via asp.net, and putting into an
    > >> > Access
    > >> > mdb
    > >> > on hosting web server. What possilbilties are there for passing these
    > >> > updates
    > >> > to an Acccess 2002 mdb, say on desktop or office server.
    > >> > Or can users work directly with mdb on web server?
    > >> > Thanks,
    > >> > Don
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >
     
  7. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    OK, here's the C# code from the web service ...

    using System;
    using System.Collections;
    using System.ComponentModel;
    using System.Data;
    using System.Diagnostics;
    using System.Web;
    using System.Web.Services;

    namespace dsdata
    {
    [System.Web.Services.WebService(Namespace="http://dsdata.us/webservices/")]
    public class primary : System.Web.Services.WebService
    {
    public primary()
    {
    //CODEGEN: This call is required by the ASP.NET Web Services Designer
    InitializeComponent();
    }

    #region Component Designer generated code

    //Required by the Web Services Designer
    private IContainer components = null;

    /// <summary>
    /// Required method for Designer support - do not modify
    /// the contents of this method with the code editor.
    /// </summary>
    private void InitializeComponent()
    {
    }

    /// <summary>
    /// Clean up any resources being used.
    /// </summary>
    protected override void Dispose( bool disposing )
    {
    if(disposing && components != null)
    {
    components.Dispose();
    }
    base.Dispose(disposing);
    }

    #endregion


    [WebMethod]
    public System.String GetData(System.DateTime StartDateTime)
    {
    System.String strResult;
    System.String strConnection =
    System.Configuration.ConfigurationSettings.AppSettings["primary"];
    System.Data.OleDb.OleDbConnection objConnection = new
    System.Data.OleDb.OleDbConnection(strConnection);
    System.Data.OleDb.OleDbCommand objCommand = new
    System.Data.OleDb.OleDbCommand(
    "SELECT * FROM tblAbsence WHERE RecordEntered > ?",objConnection);
    System.Data.OleDb.OleDbParameter objParam = objCommand.CreateParameter();
    objParam.DbType = System.Data.DbType.DateTime;
    objParam.Direction = System.Data.ParameterDirection.Input;
    objParam.OleDbType = System.Data.OleDb.OleDbType.Date;
    objParam.ParameterName = "RecordEntered";
    objParam.Value = StartDateTime;
    objCommand.Parameters.Add(objParam);
    System.Data.OleDb.OleDbDataAdapter objAdapter = new
    System.Data.OleDb.OleDbDataAdapter(objCommand);
    System.Data.DataSet objDataset = new DataSet("Absences");
    objAdapter.Fill(objDataset, "Absences");
    System.Xml.XmlDataDocument objSource = new
    System.Xml.XmlDataDocument(objDataset);
    System.Xml.Xsl.XslTransform objTransform = new
    System.Xml.Xsl.XslTransform();
    System.String strTransform =
    System.Configuration.ConfigurationSettings.AppSettings["transform"];
    objTransform.Load(strTransform);

    System.IO.StringWriter sw = new System.IO.StringWriter();
    objTransform.Transform(objSource, null, sw,
    new System.Xml.XmlUrlResolver());
    sw.Close();

    strResult = sw.ToString();
    strResult = System.Text.RegularExpressions.Regex.Replace(strResult,
    ">\\W*</z:row>", "/>");
    objDataset.Dispose();
    objAdapter.Dispose();
    objCommand.Dispose();
    objConnection.Dispose();

    return strResult;

    }
    }
    }

    And here is the XSLT that transforms the XML. Unfortunately, this part is
    very specific to the particluar data being transformed, this is where the
    majority of the work will probably be required to adapt this to your needs
    ....

    <xsl:stylesheet version='1.0'
    xmlns:xsl='http://www.w3.org/1999/XSL/Transform'>
    <xsl:eek:utput method='xml' omit-xml-declaration = 'yes' indent='yes'/>
    <xsl:template match='/'>
    <xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
    xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
    xmlns:rs='urn::rowset' xmlns:z='#RowsetSchema'>
    <s:Schema id='RowsetSchema'>
    <s:ElementType name='row' content='eltOnly'>
    <s:AttributeType name='TeacherID' rs:number='1' rs:nullable='true'
    rs:maydefer='true' rs:writeunknown='true'>
    <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10'
    rs:fixedlength='true'/>
    </s:AttributeType>
    <s:AttributeType name='StudentID' rs:number='2' rs:nullable='true'
    rs:maydefer='true' rs:writeunknown='true'>
    <s:datatype dt:type='string' dt:maxLength='8'/>
    </s:AttributeType>
    <s:AttributeType name='AbsenceDate' rs:number='3' rs:nullable='true'
    rs:maydefer='true' rs:writeunknown='true'>
    <s:datatype dt:type='dateTime' rs:dbtype='variantdate' dt:maxLength='16'
    rs:fixedlength='true'/>
    </s:AttributeType>
    <s:AttributeType name='AbsenceTypeID' rs:number='4' rs:nullable='true'
    rs:maydefer='true' rs:writeunknown='true'>
    <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10'
    rs:fixedlength='true'/>
    </s:AttributeType>
    <s:AttributeType name='AbsenceReason' rs:number='5' rs:nullable='true'
    rs:maydefer='true' rs:writeunknown='true'>
    <s:datatype dt:type='string' dt:maxLength='255'/>
    </s:AttributeType>
    <s:AttributeType name='AbsenceNotes' rs:number='6' rs:nullable='true'
    rs:maydefer='true' rs:writeunknown='true'>
    <s:datatype dt:type='string' dt:maxLength='255'/>
    </s:AttributeType>
    <s:AttributeType name='RecordEntered' rs:number='7' rs:nullable='true'
    rs:maydefer='true' rs:writeunknown='true'>
    <s:datatype dt:type='dateTime' rs:dbtype='variantdate' dt:maxLength='16'
    rs:fixedlength='true'/>
    </s:AttributeType>
    <s:extends type='rs:rowbase'/>
    </s:ElementType>
    </s:Schema>
    <rs:data>
    <xsl:for-each select='Absences/Absences'>
    <xsl:element name='z:row' namespace='#RowsetSchema'>
    <xsl:attribute name='TeacherID'>
    <xsl:value-of select='TeacherID'/>
    </xsl:attribute>
    <xsl:attribute name='StudentID'>
    <xsl:value-of select='StudentID'/>
    </xsl:attribute>
    <xsl:attribute name='AbsenceDate'>
    <xsl:value-of select='AbsenceDate'/>
    </xsl:attribute>
    <xsl:attribute name='AbsenceTypeID'>
    <xsl:value-of select='AbsenceTypeID'/>
    </xsl:attribute>
    <xsl:attribute name='AbsenceReason'>
    <xsl:value-of select='AbsenceReason'/>
    </xsl:attribute>
    <xsl:attribute name='AbsenceNotes'>
    <xsl:value-of select='AbsenceNotes'/>
    </xsl:attribute>
    <xsl:attribute name='RecordEntered'>
    <xsl:value-of select='RecordEntered'/>
    </xsl:attribute>
    </xsl:element>
    </xsl:for-each>
    </rs:data>
    </xml>
    </xsl:template>
    </xsl:stylesheet>

    And finally, the VBA code that calls the toolkit-created class
    ("clsws_primary"), uses the returned XML string as the source for an ADO
    recordset, and uses that recordset to write the data to the local database
    ....

    Private Sub cmdUpdate_Click()

    Dim rstMaxDate As ADODB.Recordset
    Dim dtmMaxDate As Date
    Dim objPrimary As clsws_primary
    Dim strXML As String
    Dim objStream As ADODB.Stream
    Dim rstSource As ADODB.Recordset
    Dim rstDest As ADODB.Recordset
    Dim fldSource As ADODB.Field
    Dim fldDest As ADODB.Field

    DoCmd.Hourglass True

    Set rstMaxDate = New ADODB.Recordset
    With rstMaxDate
    .ActiveConnection = CurrentProject.Connection
    .Open "SELECT Max(RecordEntered) as MaxDate FROM tblAbsences"
    If Not IsNull(.Fields("MaxDate")) Then
    dtmMaxDate = .Fields("MaxDate")
    End If
    .Close
    End With

    Set objPrimary = New clsws_primary
    strXML = objPrimary.wsm_GetData(dtmMaxDate)
    'Debug.Print strXML
    Set objPrimary = Nothing

    Set objStream = New ADODB.Stream
    objStream.Open
    objStream.WriteText strXML
    objStream.Position = 0
    Set rstSource = New ADODB.Recordset
    rstSource.Open objStream
    objStream.Close
    Set objStream = Nothing

    Set rstDest = New ADODB.Recordset
    With rstDest
    .ActiveConnection = CurrentProject.Connection
    .Open "SELECT * FROM tblAbsences", , adOpenDynamic, adLockOptimistic
    End With

    Do Until rstSource.EOF
    rstDest.AddNew
    For Each fldSource In rstSource.Fields
    For Each fldDest In rstDest.Fields
    If fldDest.name = fldSource.name Then
    fldDest.Value = fldSource.Value
    Exit For
    End If
    Next fldDest
    Next fldSource
    On Error Resume Next
    rstDest.Update
    If Err <> 0 Then
    rstDest.CancelUpdate
    End If
    On Error GoTo 0
    rstSource.MoveNext
    Loop

    rstDest.Close
    rstSource.Close

    Me.sfrAbsenceAll.Form.Requery

    DoCmd.Hourglass False

    End Sub

    --
    Brendan Reynolds


    "nycdon" <nycdon@discussions.microsoft.com> wrote in message
    news:F1AB9018-2F17-4465-9348-A5A617CF9B14@microsoft.com...
    > Thanks much Brendan..and sure, if you could post the c# sharp that would
    > be
    > great!
    >
    > Don
    >
    > "Brendan Reynolds" wrote:
    >
    >> Once you've installed the Web Services Toolkit, you can add a reference
    >> to a
    >> web service, and the toolkit creates a VBA class for you that has methods
    >> that call the corresponding methods in the web service. Using the
    >> toolkit-created class in the Access app is very easy. The tricky bit is
    >> writing the web service in the first place. ADO.NET makes it easy to get
    >> XML
    >> from the DataSet object, unfortunately, the XML is not in a form that can
    >> be
    >> easily consumed by Access. I had to use XSLT in the web service to
    >> transform
    >> the XML into a form that could be used as the source for an ADO recordset
    >> in
    >> Access. I can post the code if you think it will help - it's in C#.
    >>
    >> --
    >> Brendan Reynolds
    >>
    >> "nycdon" <nycdon@discussions.microsoft.com> wrote in message
    >> news:E49E6AF2-6764-4E17-8283-D788D3DDBB7A@microsoft.com...
    >> > Thanks Brendan. Not sure what I meant either by acccessing directly
    >> > either,
    >> > in this context..;)
    >> >
    >> > So ASP.Net web service would be like an exposed function/class, that
    >> > Access
    >> > Web service could pick up? Like asp.net perhaps reveals a select query
    >> > showing updated data, and Access service grabs that data and updates
    >> > the
    >> > office mdb?
    >> >
    >> >
    >> > "Brendan Reynolds" wrote:
    >> >
    >> >> You can write an ASP.NET web service that runs on the server and
    >> >> returns
    >> >> the
    >> >> data to your Access app. Access 2002 and Access 2003 can call a web
    >> >> service
    >> >> if you install the appropriate version of the Office Web Services
    >> >> Toolkit.
    >> >> The Office 2003 version is at ...
    >> >>
    >> >> http://www.microsoft.com/downloads/...8a-e1cf-48a3-9b35-169d819ecf18&DisplayLang=en
    >> >>
    >> >> And the Office XP version is at ...
    >> >>
    >> >> http://www.microsoft.com/downloads/...0f-002a-4f5b-af74-978f2cd6c798&DisplayLang=en
    >> >>
    >> >> I'm not entirely sure what you mean by the second part of your
    >> >> question -
    >> >> I'm not sure what 'work directly' might mean, in this context.
    >> >>
    >> >> --
    >> >> Brendan Reynolds
    >> >>
    >> >> "nycdon" <nycdon@discussions.microsoft.com> wrote in message
    >> >> news:5C71A3E5-4970-436B-8DF4-B97BA08A2DC7@microsoft.com...
    >> >> > I'm looking into collecting data via asp.net, and putting into an
    >> >> > Access
    >> >> > mdb
    >> >> > on hosting web server. What possilbilties are there for passing
    >> >> > these
    >> >> > updates
    >> >> > to an Acccess 2002 mdb, say on desktop or office server.
    >> >> > Or can users work directly with mdb on web server?
    >> >> > Thanks,
    >> >> > Don
    >> >> >
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
    #6 Brendan Reynolds, Nov 1, 2005
    Last edited by a moderator: Feb 10, 2015
  8. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    This same subject came up in another forum, and in the course of the
    discussion I came across this KB article, which shows how to do something
    similar in VB.NET ...

    http://support.microsoft.com/default.aspx?scid=kb;en-us;316337

    --
    Brendan Reynolds

    "Brendan Reynolds" <brenreyn@discussions.microsoft.com> wrote in message
    news:e3Smdtl3FHA.1416@TK2MSFTNGP09.phx.gbl...
    > OK, here's the C# code from the web service ...
    >
    > using System;
    > using System.Collections;
    > using System.ComponentModel;
    > using System.Data;
    > using System.Diagnostics;
    > using System.Web;
    > using System.Web.Services;
    >
    > namespace dsdata
    > {
    > [System.Web.Services.WebService(Namespace="http://dsdata.us/webservices/")]
    > public class primary : System.Web.Services.WebService
    > {
    > public primary()
    > {
    > //CODEGEN: This call is required by the ASP.NET Web Services Designer
    > InitializeComponent();
    > }
    >
    > #region Component Designer generated code
    >
    > //Required by the Web Services Designer
    > private IContainer components = null;
    >
    > /// <summary>
    > /// Required method for Designer support - do not modify
    > /// the contents of this method with the code editor.
    > /// </summary>
    > private void InitializeComponent()
    > {
    > }
    >
    > /// <summary>
    > /// Clean up any resources being used.
    > /// </summary>
    > protected override void Dispose( bool disposing )
    > {
    > if(disposing && components != null)
    > {
    > components.Dispose();
    > }
    > base.Dispose(disposing);
    > }
    >
    > #endregion
    >
    >
    > [WebMethod]
    > public System.String GetData(System.DateTime StartDateTime)
    > {
    > System.String strResult;
    > System.String strConnection =
    > System.Configuration.ConfigurationSettings.AppSettings["primary"];
    > System.Data.OleDb.OleDbConnection objConnection = new
    > System.Data.OleDb.OleDbConnection(strConnection);
    > System.Data.OleDb.OleDbCommand objCommand = new
    > System.Data.OleDb.OleDbCommand(
    > "SELECT * FROM tblAbsence WHERE RecordEntered > ?",objConnection);
    > System.Data.OleDb.OleDbParameter objParam =
    > objCommand.CreateParameter();
    > objParam.DbType = System.Data.DbType.DateTime;
    > objParam.Direction = System.Data.ParameterDirection.Input;
    > objParam.OleDbType = System.Data.OleDb.OleDbType.Date;
    > objParam.ParameterName = "RecordEntered";
    > objParam.Value = StartDateTime;
    > objCommand.Parameters.Add(objParam);
    > System.Data.OleDb.OleDbDataAdapter objAdapter = new
    > System.Data.OleDb.OleDbDataAdapter(objCommand);
    > System.Data.DataSet objDataset = new DataSet("Absences");
    > objAdapter.Fill(objDataset, "Absences");
    > System.Xml.XmlDataDocument objSource = new
    > System.Xml.XmlDataDocument(objDataset);
    > System.Xml.Xsl.XslTransform objTransform = new
    > System.Xml.Xsl.XslTransform();
    > System.String strTransform =
    > System.Configuration.ConfigurationSettings.AppSettings["transform"];
    > objTransform.Load(strTransform);
    >
    > System.IO.StringWriter sw = new System.IO.StringWriter();
    > objTransform.Transform(objSource, null, sw,
    > new System.Xml.XmlUrlResolver());
    > sw.Close();
    >
    > strResult = sw.ToString();
    > strResult = System.Text.RegularExpressions.Regex.Replace(strResult,
    > ">\\W*</z:row>", "/>");
    > objDataset.Dispose();
    > objAdapter.Dispose();
    > objCommand.Dispose();
    > objConnection.Dispose();
    >
    > return strResult;
    >
    > }
    > }
    > }
    >
    > And here is the XSLT that transforms the XML. Unfortunately, this part is
    > very specific to the particluar data being transformed, this is where the
    > majority of the work will probably be required to adapt this to your needs
    > ...
    >
    > <xsl:stylesheet version='1.0'
    > xmlns:xsl='http://www.w3.org/1999/XSL/Transform'>
    > <xsl:eek:utput method='xml' omit-xml-declaration = 'yes' indent='yes'/>
    > <xsl:template match='/'>
    > <xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
    > xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
    > xmlns:rs='urn::rowset' xmlns:z='#RowsetSchema'>
    > <s:Schema id='RowsetSchema'>
    > <s:ElementType name='row' content='eltOnly'>
    > <s:AttributeType name='TeacherID' rs:number='1' rs:nullable='true'
    > rs:maydefer='true' rs:writeunknown='true'>
    > <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10'
    > rs:fixedlength='true'/>
    > </s:AttributeType>
    > <s:AttributeType name='StudentID' rs:number='2' rs:nullable='true'
    > rs:maydefer='true' rs:writeunknown='true'>
    > <s:datatype dt:type='string' dt:maxLength='8'/>
    > </s:AttributeType>
    > <s:AttributeType name='AbsenceDate' rs:number='3' rs:nullable='true'
    > rs:maydefer='true' rs:writeunknown='true'>
    > <s:datatype dt:type='dateTime' rs:dbtype='variantdate' dt:maxLength='16'
    > rs:fixedlength='true'/>
    > </s:AttributeType>
    > <s:AttributeType name='AbsenceTypeID' rs:number='4' rs:nullable='true'
    > rs:maydefer='true' rs:writeunknown='true'>
    > <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10'
    > rs:fixedlength='true'/>
    > </s:AttributeType>
    > <s:AttributeType name='AbsenceReason' rs:number='5' rs:nullable='true'
    > rs:maydefer='true' rs:writeunknown='true'>
    > <s:datatype dt:type='string' dt:maxLength='255'/>
    > </s:AttributeType>
    > <s:AttributeType name='AbsenceNotes' rs:number='6' rs:nullable='true'
    > rs:maydefer='true' rs:writeunknown='true'>
    > <s:datatype dt:type='string' dt:maxLength='255'/>
    > </s:AttributeType>
    > <s:AttributeType name='RecordEntered' rs:number='7' rs:nullable='true'
    > rs:maydefer='true' rs:writeunknown='true'>
    > <s:datatype dt:type='dateTime' rs:dbtype='variantdate' dt:maxLength='16'
    > rs:fixedlength='true'/>
    > </s:AttributeType>
    > <s:extends type='rs:rowbase'/>
    > </s:ElementType>
    > </s:Schema>
    > <rs:data>
    > <xsl:for-each select='Absences/Absences'>
    > <xsl:element name='z:row' namespace='#RowsetSchema'>
    > <xsl:attribute name='TeacherID'>
    > <xsl:value-of select='TeacherID'/>
    > </xsl:attribute>
    > <xsl:attribute name='StudentID'>
    > <xsl:value-of select='StudentID'/>
    > </xsl:attribute>
    > <xsl:attribute name='AbsenceDate'>
    > <xsl:value-of select='AbsenceDate'/>
    > </xsl:attribute>
    > <xsl:attribute name='AbsenceTypeID'>
    > <xsl:value-of select='AbsenceTypeID'/>
    > </xsl:attribute>
    > <xsl:attribute name='AbsenceReason'>
    > <xsl:value-of select='AbsenceReason'/>
    > </xsl:attribute>
    > <xsl:attribute name='AbsenceNotes'>
    > <xsl:value-of select='AbsenceNotes'/>
    > </xsl:attribute>
    > <xsl:attribute name='RecordEntered'>
    > <xsl:value-of select='RecordEntered'/>
    > </xsl:attribute>
    > </xsl:element>
    > </xsl:for-each>
    > </rs:data>
    > </xml>
    > </xsl:template>
    > </xsl:stylesheet>
    >
    > And finally, the VBA code that calls the toolkit-created class
    > ("clsws_primary"), uses the returned XML string as the source for an ADO
    > recordset, and uses that recordset to write the data to the local database
    > ...
    >
    > Private Sub cmdUpdate_Click()
    >
    > Dim rstMaxDate As ADODB.Recordset
    > Dim dtmMaxDate As Date
    > Dim objPrimary As clsws_primary
    > Dim strXML As String
    > Dim objStream As ADODB.Stream
    > Dim rstSource As ADODB.Recordset
    > Dim rstDest As ADODB.Recordset
    > Dim fldSource As ADODB.Field
    > Dim fldDest As ADODB.Field
    >
    > DoCmd.Hourglass True
    >
    > Set rstMaxDate = New ADODB.Recordset
    > With rstMaxDate
    > .ActiveConnection = CurrentProject.Connection
    > .Open "SELECT Max(RecordEntered) as MaxDate FROM tblAbsences"
    > If Not IsNull(.Fields("MaxDate")) Then
    > dtmMaxDate = .Fields("MaxDate")
    > End If
    > .Close
    > End With
    >
    > Set objPrimary = New clsws_primary
    > strXML = objPrimary.wsm_GetData(dtmMaxDate)
    > 'Debug.Print strXML
    > Set objPrimary = Nothing
    >
    > Set objStream = New ADODB.Stream
    > objStream.Open
    > objStream.WriteText strXML
    > objStream.Position = 0
    > Set rstSource = New ADODB.Recordset
    > rstSource.Open objStream
    > objStream.Close
    > Set objStream = Nothing
    >
    > Set rstDest = New ADODB.Recordset
    > With rstDest
    > .ActiveConnection = CurrentProject.Connection
    > .Open "SELECT * FROM tblAbsences", , adOpenDynamic,
    > adLockOptimistic
    > End With
    >
    > Do Until rstSource.EOF
    > rstDest.AddNew
    > For Each fldSource In rstSource.Fields
    > For Each fldDest In rstDest.Fields
    > If fldDest.name = fldSource.name Then
    > fldDest.Value = fldSource.Value
    > Exit For
    > End If
    > Next fldDest
    > Next fldSource
    > On Error Resume Next
    > rstDest.Update
    > If Err <> 0 Then
    > rstDest.CancelUpdate
    > End If
    > On Error GoTo 0
    > rstSource.MoveNext
    > Loop
    >
    > rstDest.Close
    > rstSource.Close
    >
    > Me.sfrAbsenceAll.Form.Requery
    >
    > DoCmd.Hourglass False
    >
    > End Sub
    >
    > --
    > Brendan Reynolds
    >
    >
    > "nycdon" <nycdon@discussions.microsoft.com> wrote in message
    > news:F1AB9018-2F17-4465-9348-A5A617CF9B14@microsoft.com...
    >> Thanks much Brendan..and sure, if you could post the c# sharp that would
    >> be
    >> great!
    >>
    >> Don
    >>
    >> "Brendan Reynolds" wrote:
    >>
    >>> Once you've installed the Web Services Toolkit, you can add a reference
    >>> to a
    >>> web service, and the toolkit creates a VBA class for you that has
    >>> methods
    >>> that call the corresponding methods in the web service. Using the
    >>> toolkit-created class in the Access app is very easy. The tricky bit is
    >>> writing the web service in the first place. ADO.NET makes it easy to get
    >>> XML
    >>> from the DataSet object, unfortunately, the XML is not in a form that
    >>> can be
    >>> easily consumed by Access. I had to use XSLT in the web service to
    >>> transform
    >>> the XML into a form that could be used as the source for an ADO
    >>> recordset in
    >>> Access. I can post the code if you think it will help - it's in C#.
    >>>
    >>> --
    >>> Brendan Reynolds
    >>>
    >>> "nycdon" <nycdon@discussions.microsoft.com> wrote in message
    >>> news:E49E6AF2-6764-4E17-8283-D788D3DDBB7A@microsoft.com...
    >>> > Thanks Brendan. Not sure what I meant either by acccessing directly
    >>> > either,
    >>> > in this context..;)
    >>> >
    >>> > So ASP.Net web service would be like an exposed function/class, that
    >>> > Access
    >>> > Web service could pick up? Like asp.net perhaps reveals a select query
    >>> > showing updated data, and Access service grabs that data and updates
    >>> > the
    >>> > office mdb?
    >>> >
    >>> >
    >>> > "Brendan Reynolds" wrote:
    >>> >
    >>> >> You can write an ASP.NET web service that runs on the server and
    >>> >> returns
    >>> >> the
    >>> >> data to your Access app. Access 2002 and Access 2003 can call a web
    >>> >> service
    >>> >> if you install the appropriate version of the Office Web Services
    >>> >> Toolkit.
    >>> >> The Office 2003 version is at ...
    >>> >>
    >>> >> http://www.microsoft.com/downloads/...8a-e1cf-48a3-9b35-169d819ecf18&DisplayLang=en
    >>> >>
    >>> >> And the Office XP version is at ...
    >>> >>
    >>> >> http://www.microsoft.com/downloads/...0f-002a-4f5b-af74-978f2cd6c798&DisplayLang=en
    >>> >>
    >>> >> I'm not entirely sure what you mean by the second part of your
    >>> >> question -
    >>> >> I'm not sure what 'work directly' might mean, in this context.
    >>> >>
    >>> >> --
    >>> >> Brendan Reynolds
    >>> >>
    >>> >> "nycdon" <nycdon@discussions.microsoft.com> wrote in message
    >>> >> news:5C71A3E5-4970-436B-8DF4-B97BA08A2DC7@microsoft.com...
    >>> >> > I'm looking into collecting data via asp.net, and putting into an
    >>> >> > Access
    >>> >> > mdb
    >>> >> > on hosting web server. What possilbilties are there for passing
    >>> >> > these
    >>> >> > updates
    >>> >> > to an Acccess 2002 mdb, say on desktop or office server.
    >>> >> > Or can users work directly with mdb on web server?
    >>> >> > Thanks,
    >>> >> > Don
    >>> >> >
    >>> >>
    >>> >>
    >>> >>
    >>>
    >>>
    >>>

    >
    >
     
    #7 Brendan Reynolds, Nov 2, 2005
    Last edited by a moderator: Feb 10, 2015

Share This Page