create a database + populate it with the contents of a dataset

[.paul.]

i've written a dynamic dataset.
now i want to create a new database (.mdb) + fill it with the tables, columns and all the information in my dataset.

so far i've created the database.
can i use a dataadapter for this?

 

[zekeman]

The great thing about ADO.Net is that the create table command is an
'industry standard' SQL statement. Is a general idea:

Dim conn As New SqlConnection("Data Source=computer_name;" + "Initial Catalog=database_name;" + "User ID=sa;" + "Password=pass;")
Dim cmd As New SqlCommand
cmd.CommandTimeout = 60 '
cmd.Connection = conn ' Sets wich connection you want to use
cmd.CommandType = CommandType.Text ' Sets the command type to use
cmd.CommandText = "CREATE TABLE simplesql " + "(" + "simple_id int," + "simple_text text" + ")"
Try
conn.Open() ' Open the connection if it fails 
If conn.State = ConnectionState.Open Then
' Execute the query for this nothing is returned
cmd.ExecuteScalar()
Console.WriteLine("Table is Created")
End If
Catch exp As Exception
Console.Write(exp.Message)
Finally
conn.Close() ' close the connection
End Try

 

[Paszt]

I don't believe Access supports the CREATE TABLE syntax.
To my knowledge, the only way to create Access databases and tables etc. is to use the Microsoft Jet OLE DB Provider and Microsoft ADO Ext. 2.7 for DDL and Security (ADOX) with the COM Interop layer.
Here is an example: Create a Microsoft Access Database Using ADOX and Visual Basic .NET. The example show how to create tables as well as a database.

I always suggest trying to move away from Access and towards SQL server. The express edition is free.

 

[cjard]

I don't believe Access supports the CREATE TABLE syntax.

I thought so too, once.. I was much surprised to find that it does have its own, non-standard DDL SQL syntax

Fundamental:
http://msdn2.microsoft.com/en-us/library/aa140011(office.10).aspx

Intermediate:
http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

Advanced:
http://msdn2.microsoft.com/en-us/library/aa139977(office.10).aspx

Jet DDL crops up in each. The CREATE TABLE is fundamental, ALTER TABLE is intermediate. Have a read of all three