Copy XML data to Access Database -- vb.net data

vb.net data : Copy XML data to Access Database

james
10/10/2004 5:43:25 PM

I finally found a way to read an XML data file into a dataset and display
that in a datagrid.
And found a way using ADOX, to create an empty Access database. (no tables)
What I am having a problem doing now (after tons of research) is once the
xml data is loaded into the dataset, copying the contents of the dataset
into a new table and putting that(along with all the table structure and
data) into the newly created Access database.
I am sure that it is something I can do from ADOX, but, I cannot seem to
grasp where the problem is. Any suggestions would be greatly appreciated.
james

james
10/10/2004 8:50:16 PM

Thanks for the info. And also, by the way, it IS ADOX as in I have added a
Reference to ADOX
to my project to build an empty Access database ( .mdb file) without any
table(s) in it.
Go to Project : Add Reference, Select the Com tab, then scroll down to :
Microsoft ADO Ext.2.7 for Dll and Security. Select that and it will add the
following file:
MSADOX.dll

Then in my project, I do : Imports ADOX
and I can build an empty Access database using ADOX's Catalog function(s).
Again, I appreciate your help. I guess I need to figure out how to step thru
the recordset and get the Column Header names, and associated data and write
that out to the new database.
james

[quoted text, click to view]

Scott M.
10/10/2004 9:23:56 PM

You'll need a OLEDB.OLEDBCommand object with its CommandText property
configured (this can be accomplished with a DataAdapter as well). By the
way (FYI), it's not ADOX, it's ADO.NET.

[quoted text, click to view]

james
10/11/2004 10:44:38 AM

Scott, I guess the problem is, I have searched for ways to make an empty
Access database in which to copy an XML file ( an old Datflex 3.1d data file
converted to XML) to an new table and copy that into the empty Access
database file I just created. As far as I know ADOX is the only way to
create an empty Access Database without having to used Access itself to
create the database.
What is going on is this, I am converting an old DOS based database program,
written with Dataflex 3.1d to an new program written in VB.NET and importing
the old data into an Access database. The database is small, around 3,000
records. And Access seems to be the best way to store it as the program is
not used by more than one client at a time.
A person that works for the originators of Dataflex was kind enough to send
me a small program that will export all the data files to XML and keep all
the headers, column info etc. intact.
And I have managed to read the XML files into a dataset and display them in
a Datagrid. And they display correctly. But, I want (and the customer
wants) the files to be converted into Access tables and files. And that is
where I am stuck. I know I am going to need to step thru the info in the
dataset (or even the datagrid) and pull the header info, column attributes
and the data out and put it into a new table and write that table to the
empty Access database file.
I am open to any suggestions.
Thanks for your help.
james

[quoted text, click to view]

Scott M.
10/11/2004 11:22:16 AM

Sorry about that James. I thought you were referring to ADO.NET as ADOX.

What I can't figure out is why you are using ADOX (COM) at all in your
solution. What you are describing can be done without COM. I think you are
making this job tougher than it actually is.

[quoted text, click to view]

james
10/11/2004 11:57:40 AM

If it were for this one client, then yes I could. But, this program I am
writting, has a potential client base of at least 300 more clients. And it
depends on how well I can create a system to convert the other, potential
client's old databases to Access. The catch is, I have to get the first
person's system up and running and if he is happy with it, he can help me
get access to the other users.
The original database was written using Dataflex 3.1d (DOS based database
system) and the person who wrote and distributed the original program is
long gone. He sold his code to another company that has no interest in
maintaining the software or even doing updates. Thus, the reason I was
approached about doing a conversion to something that might be more
maintainable(updateable) in the future.
The old system used small database files for all kinds of stuff. It stored
each table used, in seperate database files. 63 total and 10 of which are
for program info, the rest the actual customer data. Originally, I was able
to export all 63 files to XML and use Access 2000 to import the data into
Access databases, using a time-limited demo of Visual Dataflex10's
development enviroment. That worked flawlessly. But, since I cannot afford
VDF10 ($800.00), and there is a potential market for more users of the
program, I needed a way to do a reliable conversion without VDF10. Lucky
for me one of the developers of Dataflex and Visual Dataflex10, in the
Netherlands, was kind enough to send me a small program that uses the
original Dataflex runtime (like Quickbasic's old runtime) to convert the
data files to XML. So, I have the ability now, to convert future user's
data to XML for later conversioin to Access .mdb files. My hopes were to be
able to add the conversion function to the actual application without me
having to visit each customer and do a manual conversion for them.
(although, that is probably a better idea and would be worth more money to
me.)
Sorry about the long post, but, I felt an explaination was in order.
james

[quoted text, click to view]

Scott M.
10/11/2004 12:05:24 PM

Would it be possible for you to manually create the Access DB and create &
configure the table yourself, rather than have it done programmatically? I
ask because if you do that, you won't need COM (ADOX) at all and with about
5 lines of code, you can take the XML and fill up your Access table with
data.

[quoted text, click to view]

james
10/11/2004 1:31:50 PM

Thanks Scott. I have already been able to use the Dataset's .ReadXML()
method to read the dataset into the dataset and display it in a datagrid.
My question is, how to move the table that is in the dataset(which, displays
correctly in the datagrid) to my empty Access database that I have created
using ADOX ?
Can I just add the table to the empty database? If so, how? If I understand
you correctly you are saying that I can use methods in the
OLDDB.OLEDBDataAdaptor to fill a new table and save it via
the DataAdaptor's Update method. Is that correct?
Will using the update method also pass along the table's column headers, and
individual column's settings and the data? I know that works with a table
that has already been built in the database and it will update the data with
new (updated) records. I just did not know how or if, it would copy the new
dataset & table to the empty database.
james

(I appreciate your help and hanging on thru all the silly questions)

[quoted text, click to view]

Scott M.
10/11/2004 1:49:37 PM

Well, if it were me and manually creating the DB and the tables were not an
option, then I guess I would use ADOX just for creating the empty database,
but from there I would use SQL statements to create the table(s) and
configure them.

Once the table(s) were in place, it's actually very easy to pump XML data
into a DataSet, through a DataAdapter and to the Access table.

1. Make OLEDB.OLEDBConnection to Access DB.
2. Declare and configure OLEDB.OLEDBDataAdapter.
3. Use a DataSet's .ReadXML() method to read the XML into the DataSet
4. Use the DataAdapter's .Update() method to take the data from the DataSet
and put it into Access.

[quoted text, click to view]

Scott M.
10/11/2004 3:39:09 PM

[quoted text, click to view]

Not quite. You already have the data saved in your DataSet. The
DataAdapter's update method takes an argument that specifies what the data
is that should be pushed up to the base. You simply thell the DataAdapter
to use your DataSet data to update the actual DB (you've got to make sure
that the db table and the tables in the DataSet are set identically).

Psudeo Code:

dim da as new DataAdapter(SQL Select Command, connection)
Dim cb As New OleDb.OleDbCommandBuilder(da) or configure the Update & Insert
commands yourself

da.Update(Your DataSet, The DB Table)

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about vb.net data

home · blog · groups

Questions? Comments? Contact the dn