Solved

ADO.NET compact DB, export/import table

Posted on 2004-04-28
6
1,225 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 62 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

622 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