Hi,

I am new to VB.Net and was wondering is there any easiest way to create
and update an Access Database (.mdb) with the DataSet that I have populated
from ReadXml(). Basically, I don't want to manually create each table from
the DataTable with SQL create table command, and define constraints etc.

I would appreciate if you could provide some code snippets.

Regards
Sanjib

Sanjib -do I understand you correctly in that you don't have the table
schema defined in the database already? If not, you're going to have to
write some code to create those tables...just wanted to make sure I
understood you correctly before we went down that path.
"Sanjib Biswas" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I am new to VB.Net and was wondering is there any easiest way to create
> and update an Access Database (.mdb) with the DataSet that I have
> populated from ReadXml(). Basically, I don't want to manually create each
> table from the DataTable with SQL create table command, and define
> constraints etc.
>
> I would appreciate if you could provide some code snippets.
>
> Regards
> Sanjib
>

Hi Ryan,

Thank you for following up. I don't have the table schema defined in the
database but the DataSet has that information (XML file has the schema of all
the tables and associated data). In fact, I am dynamically creating an empty
access database with Catalog.create() method. And more over I want to make
this application as generic as possible so that as long as the XML file has
the schema of all the tables, I should be able to generate Access database
without any pain.

As you have mentioned that since table schema is not defined in the
database, I need to write code to manually create all the tables and various
relationships. Could you please tell me why can't we use the schema
definition from the XML file and create all the tables, associated columns,
and contraints in the access database?

I would have thought that as with OleDataAdapter we can populate DataSet,
similarly, from the DataSet we should be able to recreate the database
without having to create all the tables and various other relationships.

Regards
Sanjib

"W.G. Ryan - MVP" wrote:

> Sanjib -do I understand you correctly in that you don't have the table
> schema defined in the database already? If not, you're going to have to
> write some code to create those tables...just wanted to make sure I
> understood you correctly before we went down that path.
> "Sanjib Biswas" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi,
> >
> > I am new to VB.Net and was wondering is there any easiest way to create
> > and update an Access Database (.mdb) with the DataSet that I have
> > populated from ReadXml(). Basically, I don't want to manually create each
> > table from the DataTable with SQL create table command, and define
> > constraints etc.
> >
> > I would appreciate if you could provide some code snippets.
> >
> > Regards
> > Sanjib
> >
>
>
>

Sanjib,

What you ask does not exist in the current versions of Net.

The database is the leading factor at this moment not the XML schema.

If you want to build it, you will have to examine your schema completly with
your program and use the create, drop etc SQL commands with the commands
sqlnonquery

Sorry that I could not help you with an easy answer.

Cor

Hi Cor,

Thank you for the prompt reply. Is there any plan to have this feature
included in the future version of .Net?

In the mean time, will you be able to answer my following questions:

1. How to retrieve attribute type "xs:string" and length "maxLength" from
DataSet?

<xs:element name="mpimpl" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>

Or

<xs:element name="ALARM_NBR" type="xs:double" minOccurs="0" />

2. And how to retrieve constraints information from DataSet?

<xs:unique name="Constraint1" msdata:PrimaryKey="true">
<xs:selector xpath=".//dim" />
<xs:field xpath="class_name" />
</xs:unique>
<xs:unique name="mated_Constraint1" msdata:ConstraintName="Constraint1"
msdata:PrimaryKey="true">
<xs:selector xpath=".//mated" />
<xs:field xpath="class_name" />
</xs:unique>

Regards
Sanjib


"Cor Ligthert [MVP]" wrote:

> Sanjib,
>
> What you ask does not exist in the current versions of Net.
>
> The database is the leading factor at this moment not the XML schema.
>
> If you want to build it, you will have to examine your schema completly with
> your program and use the create, drop etc SQL commands with the commands
> sqlnonquery
>
> Sorry that I could not help you with an easy answer.
>
> Cor

Sanjib,

> Thank you for the prompt reply. Is there any plan to have this feature
> included in the future version of .Net?

Not that I know.

The information you are looking for is probably in this page.

http://msdn.microsoft.com/library/de...mberstopic.asp

I hope this helps,

Cor

Cor,

Thank you for the link. I have noticed that "Memo" field in the Access
database has been turned into "xs:string" in the schema section of the XML
file. So when I construct create table request [col name] TEXT (536870910);
passing maxLength value, I get an error message saying "col name" is too
large. So, I guess I should have sent [col name] Memo in this case. But my
question is, if I see in the XML schema, a child nodes data type is
"xs:string" and length is > 255, is it a Memo field in the Access database?

Is there any other similar assumptions I have to make while converting data
types from XML schema to access data types?

- <xs:restriction base="xs:string">
<xs:maxLength value="536870910" />
</xs:restriction>

I have an object "dt" of DataTable of the empty access database, first I
create the table by looping through all the tables from dataset (from XML)
(outer For loop) and inside that I copy DataRow from dataset to dt (inner For
loop).

' create empty access database
dbCatalog.Create(strCmd)

' get a handler to db connection and open it
dbConn = New OleDbConnection(strCmd)
dbConn.Open()

' get the DataTable
dt = dbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object()
{Nothing, Nothing, Nothing, Nothing})

For i As Integer = 0 To dataset.Tables.Count - 1
< create table in the Access database>
Dim row As DataRow
For j As Integer = 0 To datatable.Rows.Count - 1
row = dt.NewRow()
dt.Rows.InsertAt(datatable.Rows.Item(j), j)
Next j
dt.AcceptChanges()
Next i

dbConn.Close()
dbCatalog = Nothing
dbConn = Nothing
dt = Nothing

What am I missing here? I am sure there is... because I can only see that
the tables are created in the Access database without any rows. Could you
please point me the errors?

Regards
Sanjib

"Cor Ligthert [MVP]" wrote:

> Sanjib,
>
> > Thank you for the prompt reply. Is there any plan to have this feature
> > included in the future version of .Net?
>
> Not that I know.
>
> The information you are looking for is probably in this page.
>
> http://msdn.microsoft.com/library/de...mberstopic.asp
>
> I hope this helps,
>
> Cor
>
>
>

Sanjib,

I think that what you are searching for is in this sample.

http://www.vb-tips.com/default.aspx?...6-fc0d5c470f53

I hope this helps,

Cor