Import xml into ms access database
-
I use a webservice to import the xml into a ms access database, and interop code is not working. This is my code so far, but it is not working properly. im strConnection As String Dim objDataSet As New DataSet() Dim objConnection As OleDbConnection Dim objAdapter As OleDbDataAdapter Dim intI As Integer Dim ds As New DataSet Dim streamRead As New System.IO.FileStream("C:\NTB100514_07_22_2009_225551.xml", System.IO.FileMode.Open) ds.ReadXml(streamRead) streamRead.Close() If ds.Tables.Count > 0 Then strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "c:\PC Auditer.accdb" objConnection = New OleDbConnection(strConnection) objConnection.Open() objAdapter = New OleDbDataAdapter() Dim cb As New OleDbCommandBuilder(objAdapter) For intI = 0 To ds.Tables.Count - 1 If ds.Tables(intI).Rows.Count > 0 Then DataGridView1.DataSource = ds.Tables(intI) objAdapter.SelectCommand = New OleDbCommand("SELECT * FROM [" + ds.Tables(intI).TableName + "] ", objConnection) objAdapter.Fill(ds, ds.Tables(intI).TableName.ToString) objAdapter.UpdateCommand = cb.GetUpdateCommand() objAdapter.Update(ds, ds.Tables(intI).TableName.ToString) End If Next objConnection.Close() End If In my access database all the primairy keys and relationships are defined. The first time (intI=0) the update command works well, but the second time (intI=1) iI get an error. Kind regards, Jurgen ps: I am a beginner, so excuse me if my code is lokking so nice.
-
I use a webservice to import the xml into a ms access database, and interop code is not working. This is my code so far, but it is not working properly. im strConnection As String Dim objDataSet As New DataSet() Dim objConnection As OleDbConnection Dim objAdapter As OleDbDataAdapter Dim intI As Integer Dim ds As New DataSet Dim streamRead As New System.IO.FileStream("C:\NTB100514_07_22_2009_225551.xml", System.IO.FileMode.Open) ds.ReadXml(streamRead) streamRead.Close() If ds.Tables.Count > 0 Then strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "c:\PC Auditer.accdb" objConnection = New OleDbConnection(strConnection) objConnection.Open() objAdapter = New OleDbDataAdapter() Dim cb As New OleDbCommandBuilder(objAdapter) For intI = 0 To ds.Tables.Count - 1 If ds.Tables(intI).Rows.Count > 0 Then DataGridView1.DataSource = ds.Tables(intI) objAdapter.SelectCommand = New OleDbCommand("SELECT * FROM [" + ds.Tables(intI).TableName + "] ", objConnection) objAdapter.Fill(ds, ds.Tables(intI).TableName.ToString) objAdapter.UpdateCommand = cb.GetUpdateCommand() objAdapter.Update(ds, ds.Tables(intI).TableName.ToString) End If Next objConnection.Close() End If In my access database all the primairy keys and relationships are defined. The first time (intI=0) the update command works well, but the second time (intI=1) iI get an error. Kind regards, Jurgen ps: I am a beginner, so excuse me if my code is lokking so nice.
What is the error ? Have you stepped through in the debugger to see what is happening ? What line has the error ?
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
-
What is the error ? Have you stepped through in the debugger to see what is happening ? What line has the error ?
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
Hi Christian, The error is in line: objAdapter.Update(ds, ds.Tables(intI).TableName.ToString) "Missing the DataColumn 'ID' in the DataTable 'Shares' for the SourceColumn 'ID'" I don't have a datacolumn 'ID' in my table Shares. I think it has to do with the primairy key and referential integrety. The previous table 'SOS' has a column ID (primairy key) which has a relation with the column 'ID-Share' from table 'Shares'. Kind regards, Jurgen
-
Hi Christian, The error is in line: objAdapter.Update(ds, ds.Tables(intI).TableName.ToString) "Missing the DataColumn 'ID' in the DataTable 'Shares' for the SourceColumn 'ID'" I don't have a datacolumn 'ID' in my table Shares. I think it has to do with the primairy key and referential integrety. The previous table 'SOS' has a column ID (primairy key) which has a relation with the column 'ID-Share' from table 'Shares'. Kind regards, Jurgen
There are four steps to it, which are defined below: Step 1: Click on External Data ribbon, go to the "Import" section and click on XML file option. Step 2: Use the browse option to go to the location where the XML file is located from which data needs to be imported. Click Open and then OK. Step 3: Access will scan through the file and display the structure of the table to be created. The Import Options includes the following options, namely Structure Only / Structure & Data / Append data to existing table. In our process we will choose the option of both structure and data. Step 4: If you plan to import the same file agian in the future then you can check the box "Save Import Steps". Now you can double click on the table that is created and would be able to see the structure and the data in it i.e. imported from the XML file. Eliza http://www.mindfiresolutions.com/[^]
Cheers, Eliza