Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ADO.NET compact DB, export/import table

Posted on 2004-04-28
6
Medium Priority
?
1,226 Views
Last Modified: 2008-03-10
G'day everyone

These tasks were simple in DAO, so hopefully can be done in ADO.NET using VB.NET, i'm working with an access database
i would like to be able to...

* compact and repair the db
* export a table (fast, i'm going to be exporting about 500,000 records)
* import the data i exported back into a table.

Any help will be very helpful!

Cheers :-)
0
Comment
Question by:undy2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 5

Expert Comment

by:tgannetts
ID: 10938711
See the following link for Microsoft's recommendations on compacting/repairing an Access database:

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

It states that compacting and repairing cannot be achieved through ADO.NET and instead you need to access the Microsoft Jet OLE DB Provider and Replication Objects (JRO).

As far as your other queries are concerned, there are a number of methods for exporting/importing tables. The best one depends on what you intend to do with the data - export to another access database, export to excel, export to a csv file?

What kind of exporting/importing will you be doing?

Tom
0
 

Author Comment

by:undy2
ID: 10938786
Hey tom..

well the clients of my application will not have MS access installed on their PC's... i pretty much want a fast and effective way to export data out of the database to a file, and then back into the database. I'll do it with any file format, i have learnt with ADO.NET that some things work faster than others, so whatever is fastest!

Cheers :-)
0
 
LVL 5

Accepted Solution

by:
tgannetts earned 248 total points
ID: 10939471
You could import/export the database data to and from an XML file:

Public sub CreateXMLFile(byval oDs as DataSet)

    'Create a file name to write to.
    Dim filename As String = "c:\Test.xml"
   
    'Create the FileStream to write with.
    Dim myFileStream As New System.IO.FileStream(filename, System.IO.FileMode.Create)
   
    'Create an XmlTextWriter with the fileStream.
    Dim myXmlWriter As New System.Xml.XmlTextWriter (myFileStream, System.Text.Encoding.Unicode)
   
    'Write to the file with the WriteXml method.
    oDs.WriteXml(myXmlWriter)
    myXmlWriter.Close()

End Sub

Public sub ReadXMLFile(byval location as string) as DataSet

    'Create a new DataSet.
    Dim oDs As New DataSet("New DataSet")
     
    ' Create new FileStream to read schema with.
    Dim fsReadXml As New System.IO.FileStream(location, System.IO.FileMode.Open)
   
    'Create an XmlTextReader to read the file.
    Dim myXmlReader As New System.Xml.XmlTextReader(fsReadXml)
   
    'Read the XML document into the DataSet.
    oDs.ReadXml(myXmlReader)
    ' Close the XmlTextReader
    myXmlReader.Close()

    Return oDs

End sub

Tom
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:undy2
ID: 10940292
hey tom...

the CreateXMLFile() is working like a charm

although i am having trouble with the ReadXMLFile().. here is what i got so far..

    Public Sub loadTable()
        Dim mDataAdapter As New OleDb.OleDbDataAdapter
        Dim mDataSet As New System.Data.DataSet
        Dim mConn As OleDb.OleDbConnection
        Dim mCommandBuilder As OleDb.OleDbCommandBuilder
        Try
            mDataSet = ReadXMLFile("c:\Test.xml")
            mDataAdapter.SelectCommand = New OleDb.OleDbCommand("SELECT * FROM testTable;", mConn)
            mCommandBuilder = New OleDb.OleDbCommandBuilder(mDataAdapter)
            mDataAdapter.Fill(mDataSet)
            mDataAdapter.Update(mDataSet)
            mDataSet.AcceptChanges()
        Catch
            MsgBox(Err.Description)
        End Try
    End Sub

it comes up with the error "syntax error in INSERT INTO statement" on the line "mDataAdapter.Update(mDataSet)"

maybe this isn't the best/easiest way of saving to a table, i only converted to ADO.NET from DAO about 3 days ago..

Cheers :-)
0
 
LVL 5

Expert Comment

by:tgannetts
ID: 10947565
Use the following code instead:

Public Sub loadTable()

     Dim mDataAdapter As New OleDb.OleDbDataAdapter
     Dim mDataSet As New System.Data.DataSet
     Dim mConn As OleDb.OleDbConnection
     Dim mCommandBuilder As OleDb.OleDbCommandBuilder
     Try
         mDataAdapter.SelectCommand = New OleDb.OleDbCommand("SELECT * FROM testTable;", mConn)
         mCommandBuilder = New OleDb.OleDbCommandBuilder(mDataAdapter)
         mDataAdapter.FillSchema(mDataSet, SchemaType.Mapped)

         mDataSet = ReadXMLFile("c:\Test.xml")
         
         mDataAdapter.Update(mDataSet.Tables(0))
         mDataSet.AcceptChanges()

     Catch ex as exception
         MsgBox(ex.message)
     End Try
End Sub

To be able to store the changes in the data set, you will need to modify the ReadXML and WriteXML functions:

Update the ReadXML line to:

oDs.ReadXml(myXmlReader, XmlReadMode.DiffGram)

Update the WriteXML line to:

oDs.WriteXml(myXmlReader, XmlWriteMode.DiffGram)

This will store the row state in the XML file, otherwise each row state following the fill operation will be set to Added, and all the rows will be added, rather than the appropriate command e.g. delete or update.

Hope this helps (and works...)

Tom.
0
 

Author Comment

by:undy2
ID: 10956251
Thanks for the help! it's working like a charm :-)
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question