undy2
asked on
ADO.NET compact DB, export/import table
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 :-)
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 :-)
ASKER
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 :-)
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 :-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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( mDataAdapt er)
mDataAdapter.Fill(mDataSet )
mDataAdapter.Update(mDataS et)
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(mData Set)"
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 :-)
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
mCommandBuilder = New OleDb.OleDbCommandBuilder(
mDataAdapter.Fill(mDataSet
mDataAdapter.Update(mDataS
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(mData
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 :-)
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( mDataAdapt er)
mDataAdapter.FillSchema(mD ataSet, SchemaType.Mapped)
mDataSet = ReadXMLFile("c:\Test.xml")
mDataAdapter.Update(mDataS et.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.
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
mCommandBuilder = New OleDb.OleDbCommandBuilder(
mDataAdapter.FillSchema(mD
mDataSet = ReadXMLFile("c:\Test.xml")
mDataAdapter.Update(mDataS
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.
ASKER
Thanks for the help! it's working like a charm :-)
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