Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sending data to Access (2003)

    What would be the best way, in terms of performance, to update a large number of rows using .NET code? Would it be to use a query and send all the parameters (by importing XML for example), or would it be better to open a connection and updating each row, one after the other? Another way to ask the same question: should the processing be in the Access database or in the front end application?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sending data to Access (2003)

    If you're using a Dot.Net application against an Access file as the datastore, it would be better to handle it in the front end, but it's a bit hard to tell exactly what your question entails. Access 2003 doesn't support managed code and the Jet engine is not the most efficient in the world, so it depends on what exactly you need to do. You can simply create and execute the SQL to update the data from the front end using the Jet OLEDB provider and a command object. I don't understand the remark about "sending parameters" using XML, but if you are doing something exotic, then you need to provide more details.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sending data to Access (2003)

    Hi Charlotte,
    We have a schedule screen in the DOT.NET front end which is made up of many small objects. My colleague, who would prefer to be using SQL Server, says that with SQL it would be better to process all the changes in the front end and create a recordset or XML file, then open the database connection, and plop all the changes into the database at once (the chunky approach). The chatty approach would be to loop through all our changes and update the db directly using ADO. I have little experience with SQL Server and can only conceive of using ADO.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sending data to Access (2003)

    The chunky approach is prefered in any case, but I don't understand the "plopping" your colleague is talking about. When using SQL Server, one chunky approach is to create a stored procedure and simply call it, passing in the parameters, so that it can update itself. Another is to use a disconnected dataset to capture all the updates in the front end and then simply reconnect and update the SQL Server in one fell swoop, if that's what he/she means. I still don't see where the xml file comes into play in the update unless he's talking about the xml file that holds the disconnected dataset. Could that be the meaning?
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sending data to Access (2003)

    Charlotte:
    What we mean by using XML in SQL server is this: We have an object which has a big ArrayList as one of its params. With SQL Server, we would use a stored procedure and pass it all the object properties as sql params. The ArrayList is converted to XML and also being sent as a string param to the stored procedure. Inside the stored procedure we would convert the XML to a temporary table and update the needed data. The questions are: 1. Can something like that be done with Access? 2. Is it the recommended way in term of preformance?

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sending data to Access (2003)

    OK, that clarifies it somewhat. Access doesn't have stored procedures, just fairly dumb queries, so processing couldn't be done the same way. Especially since Access doesn't have temporary tables in the sense that SQL Server has. I don't yet have Access 2003, so I can't offer suggestions on the specifics of updating it from an XML file, but you may be able to have Access link to the XML file as a table and then run an update query using that linked file something like you would use a temp table in SQL Server. A better front-end alternative might be to use a DataAdapter and let it handle the operation of updating the Access back end.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •