Datatable to mdb?

[HeavenCore]

Hi i have a datatable which is generated by choosing options (so the number of columns and rows returned will always be different) the data is coming from an ACT! sage database, and needs saving into an already existing mdb database (will obviously need the new table to created in the mdb automatically too)

kinda struggling how to do this? any ideas?

Populating the dataset with VB.Net

Dim myDataReader As OleDbDataReader = Nothing 
Dim myOleDbConnection As OleDbConnection = New OleDbConnection("Provider=ACTOLEDB.1;Data Source=L:\eVentura.pad;User ID=""random"";Password=random,je") 
Dim myOleDbCommand As OleDbCommand = New OleDbCommand("SELECT * FROM VRP_Contact", myOleDbConnection) 
myOleDbConnection.Open 
myDataReader = myOleDbCommand.ExecuteReader 
Dim ds As DataSet = New DataSet 
ds.Load(myDataReader)

 

[cjard]

number of rows isnt an issue. Varying number of columns is at best a bad idea. You could make your life a lot easier by just having one table with e.g. 100 columns or had a standard name/value pair table (2 columns, N rows, your 10 column, 10 row table from sage would be pivoted to name/value pair, 100 rows of)

Though VS has provisions for making a datatable from a database schema
it doesnt have provisons for the contra (schema from datatable) and you will have to do a lot of DDL yourself..

I.e. something like:

Dim ddl as New StringBuilder("CREATE TABLE whatever(")
ForEach dc as DataColumn in MyDataTable.Columns
  ddl.AppendFormat("{0} VARCHAR({1})," dc.Name, dc.MaxLength)
Next dc

There s alot more work than that.. but good luck!
If you want to tell me more about why you think this is a good idea, or the actual problem youre trying to solve, i might be able to offer some advice as to a different, easier solution