Advice on creating a DataTable to place into Blank MS Access DB file

[JuLiE Dxer]

I'm wondering what's the appropriate way to do the following:

My app programmatically creates a DataTable and its schema (shema
varies), places it into an empty DataSet. Then, it reads from file,
does some work, then populates the DataTable with many rows. Then, it
copies a blank Access DB file over to the destination file. Here's the
main thing on my mind, after establishing a connection to this empty
database file, should I just create a blank table with the same schema
in the file or is there a way to just update the empty database file
with my already populaed DataTable ?


I'd like to be able to just update the Access DB file with my
populated DataTable. Is this possible?

 

[Cor Ligthert]

Julie,

Why do you not just use this one before you start filling your datatable?
http://msdn.microsoft.com/library/d...ystemdataidataadapterclassfillschematopic.asp

I hope that it helps?

Cor

 

[JuLiE Dxer]

I have a DataTable schema already created programmatically,
dynamically and added to a DataSet.

The trick is placing this created DataTable into a blank database file
where no tables exist yet.

 

[Cor Ligthert]

Julie,

You cannot, you have to create the datatable using the SQL CREATE TABLE

And as I forever write with this or make it yourself generic (what should be
possible) and than sent the code to this newsgroup, than we can supply it
the next time.

Sorry this has always the same answer.

Beneath a sample how to make Access tables in Adonet.

\\\
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" &
_
" Data Source="FullPath";User Id=admin;Password=;")
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE persons ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"BirthDate datetime," & _
"IdCountry int," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
conn.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
conn.Dispose()
End Sub
End Class
///

I hope this helps?

Cor

 

[james]

Cor, I think you are leaving out ADOX, which can do what Julie is talking about. I know that ADOX is not the ADO.NET way of
doing things, but, sometimes you have to do something like that.
For instance, I have a very old ( in computer years) DOS based database programs that used 63 individual data files (old
Dataflex 3.1d .DAT format) that each contain a single table. And none of those tables have the same formating( Schema). I have
been able to export all of those individual files & their associated tables to CSV format and then read them into a utility
application that I am still working on,
and use the Dataset that is filled by the CSV file as a template to build a new Access database with a table that has the same
structure as the Dataset built from the CSV file. And I am building the Access database and Table with ADOX. And then filling
the new table from the CSV file. All without having to know ahead of time the exact format of the original file(s).
That means I have to step thru the dataset and get all the Column Header names, their types, Field Sizes etc. and apply that to
the new Table that I am creating and then step thru each row in the Dataset and add the actual data to the new Table. I have
run into a few problems with this but, it is mostly because of my lack of knowledge of ADOX and the methods to use it.
So, there are other ways besides, using SQL statements to build a new table and database. And without having to know the exact
details ahead of time when you create the SQL statements to build the new table.
james

Julie,

You cannot, you have to create the datatable using the SQL CREATE TABLE

And as I forever write with this or make it yourself generic (what should be possible) and than sent the code to this
newsgroup, than we can supply it the next time.

Sorry this has always the same answer.

Beneath a sample how to make Access tables in Adonet.

\\\
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
" Data Source="FullPath";User Id=admin;Password=;")
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE persons ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"BirthDate datetime," & _
"IdCountry int," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
conn.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
conn.Dispose()
End Sub
End Class
///

I hope this helps?

Cor

I have a DataTable schema already created programmatically,
dynamically and added to a DataSet.

The trick is placing this created DataTable into a blank database file
where no tables exist yet.

 

[JuLiE Dxer]

Thanks for reply. I don't speak VB though (I'm a C#'er) but see what
you're doing. I've done this before different times and was hoping to
avoid it.

Thanks :0)

Julie,

You cannot, you have to create the datatable using the SQL CREATE TABLE

And as I forever write with this or make it yourself generic (what should be
possible) and than sent the code to this newsgroup, than we can supply it
the next time.

Sorry this has always the same answer.

Beneath a sample how to make Access tables in Adonet.

\\\
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" &
_
" Data Source="FullPath";User Id=admin;Password=;")
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE persons ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"BirthDate datetime," & _
"IdCountry int," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
conn.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
conn.Dispose()
End Sub
End Class
///

I hope this helps?

Cor

I have a DataTable schema already created programmatically,
dynamically and added to a DataSet.

The trick is placing this created DataTable into a blank database file
where no tables exist yet.

 

[JuLiE Dxer]

James,

I've used the ADOX way once in the past but was strongly discouraged
from it due to the .mdb file being like 6x bigger than one that had a
table already in it with the same schema.

Cor, I think you are leaving out ADOX, which can do what Julie is talking about. I know that ADOX is not the ADO.NET way of
doing things, but, sometimes you have to do something like that.
For instance, I have a very old ( in computer years) DOS based database programs that used 63 individual data files (old
Dataflex 3.1d .DAT format) that each contain a single table. And none of those tables have the same formating( Schema). I have
been able to export all of those individual files & their associated tables to CSV format and then read them into a utility
application that I am still working on,
and use the Dataset that is filled by the CSV file as a template to build a new Access database with a table that has the same
structure as the Dataset built from the CSV file. And I am building the Access database and Table with ADOX. And then filling
the new table from the CSV file. All without having to know ahead of time the exact format of the original file(s).
That means I have to step thru the dataset and get all the Column Header names, their types, Field Sizes etc. and apply that to
the new Table that I am creating and then step thru each row in the Dataset and add the actual data to the new Table. I have
run into a few problems with this but, it is mostly because of my lack of knowledge of ADOX and the methods to use it.
So, there are other ways besides, using SQL statements to build a new table and database. And without having to know the exact
details ahead of time when you create the SQL statements to build the new table.
james

Julie,

You cannot, you have to create the datatable using the SQL CREATE TABLE

And as I forever write with this or make it yourself generic (what should be possible) and than sent the code to this
newsgroup, than we can supply it the next time.

Sorry this has always the same answer.

Beneath a sample how to make Access tables in Adonet.

\\\
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
" Data Source="FullPath";User Id=admin;Password=;")
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE persons ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"BirthDate datetime," & _
"IdCountry int," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
conn.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
conn.Dispose()
End Sub
End Class
///

I hope this helps?

Cor

I have a DataTable schema already created programmatically,
dynamically and added to a DataSet.

The trick is placing this created DataTable into a blank database file
where no tables exist yet.

 

[Cor Ligthert]

James,

Thanks for this information, however it should be able to do it in AdoNet as
well.

When I understand you well, do you have first to make that CSV file, in the
same way you would be able to make a Create string in my opinion.

However it has to be done once and until now I did not see this as a generic
piece of software. I never took the time to make a sample from it, maybe I
do it once.

However correct me when I see what you wrote wrong?

Cor

Cor, I think you are leaving out ADOX, which can do what Julie is talking
about. I know that ADOX is not the ADO.NET way of doing things, but,
sometimes you have to do something like that.
For instance, I have a very old ( in computer years) DOS based database
programs that used 63 individual data files (old Dataflex 3.1d .DAT
format) that each contain a single table. And none of those tables have
the same formating( Schema). I have been able to export all of those
individual files & their associated tables to CSV format and then read
them into a utility application that I am still working on,
and use the Dataset that is filled by the CSV file as a template to build
a new Access database with a table that has the same structure as the
Dataset built from the CSV file. And I am building the Access database
and Table with ADOX. And then filling the new table from the CSV file.
All without having to know ahead of time the exact format of the original
file(s).
That means I have to step thru the dataset and get all the Column Header
names, their types, Field Sizes etc. and apply that to the new Table that
I am creating and then step thru each row in the Dataset and add the
actual data to the new Table. I have run into a few problems with this
but, it is mostly because of my lack of knowledge of ADOX and the methods
to use it.
So, there are other ways besides, using SQL statements to build a new
table and database. And without having to know the exact details ahead of
time when you create the SQL statements to build the new table.
james

Julie,

You cannot, you have to create the datatable using the SQL CREATE TABLE

And as I forever write with this or make it yourself generic (what should
be possible) and than sent the code to this newsgroup, than we can supply
it the next time.

Sorry this has always the same answer.

Beneath a sample how to make Access tables in Adonet.

\\\
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
& _
" Data Source="FullPath";User Id=admin;Password=;")
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE persons ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"BirthDate datetime," & _
"IdCountry int," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
conn.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
conn.Dispose()
End Sub
End Class
///

I hope this helps?

Cor

I have a DataTable schema already created programmatically,
dynamically and added to a DataSet.

The trick is placing this created DataTable into a blank database file
where no tables exist yet.

 

[Cor Ligthert]

Jullie,

It is strange most "good" VBNet programmers have not any problem with C#.

Maybe it is because the code set from VBNet is something more extended,
because there is as well the Microsoft.VisualBasic namespace. That can be
the reason, however I use the methods from that seldom (except the convert
functions) and they are not in the sample I showed you.

Cor

"JuLiE Dxer" >

Thanks for reply. I don't speak VB though (I'm a C#'er) but see what
you're doing. I've done this before different times and was hoping to
avoid it.

Thanks :0)

Julie,

You cannot, you have to create the datatable using the SQL CREATE TABLE

And as I forever write with this or make it yourself generic (what should
be
possible) and than sent the code to this newsgroup, than we can supply it
the next time.

Sorry this has always the same answer.

Beneath a sample how to make Access tables in Adonet.

\\\
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
&
_
" Data Source="FullPath";User Id=admin;Password=;")
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE persons ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"BirthDate datetime," & _
"IdCountry int," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
conn.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
conn.Dispose()
End Sub
End Class
///

I hope this helps?

Cor

I have a DataTable schema already created programmatically,
dynamically and added to a DataSet.

The trick is placing this created DataTable into a blank database file
where no tables exist yet.