mdb from schema

I have a schema.xsd that layouts multiple tables in my strong-typed dataset. I want to save this dataset to a completely new .mdb file with some VB code. Anyone know how?
LVL 4
srcalcAsked:
Who is Participating?
 
User111111Commented:
I'm not sure if this would work for dynamic db schemas, but it might work if you need to continually recreate a database from the same schema.

use adox to create your database based on your schema file.  you could parse the schema programatically or you could manually read it and replace the below values....

Private Sub cmdCreate_Click()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim con As ADODB.Connection

    ' Delete the database if it already exists.
    On Error Resume Next
    Kill txtDatabaseName.Text
    On Error GoTo 0

    ' Create the new database.
    Set cat = New ADOX.Catalog
    cat.Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & txtDatabaseName.Text & ";"

    ' Create a new table.
    Set tbl = New ADOX.Table
    tbl.Name = "TestTable"
    tbl.Columns.Append "FirstName", adVarWChar, 40
    tbl.Columns.Append "LastName", adVarWChar, 40
    tbl.Columns.Append "Birthdate", adDate
    tbl.Columns.Append "Weight", adInteger
    cat.Tables.Append tbl

    ' Connect to the database.
    Set con = cat.ActiveConnection

    ' Insert records.
    con.Execute "INSERT INTO TestTable VALUES ('Andy', " & _
        "'Able', '1 Jan 1980', '150')"
    con.Execute "INSERT INTO TestTable VALUES ('Betty', " & _
        "'Baker', #2/22/1990#, 70)"

    ' Close the database connection.
    con.Close
    Set con = Nothing
    Set tbl = Nothing
    Set cat = Nothing

    MsgBox "Done"
End Sub
0
 
srcalcAuthor Commented:
is that vb .net? I cant seem to find the classes that you used:

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim con As ADODB.Connection
0
 
User111111Commented:
add a reference to your project, Microsoft ADO Ext. 2.6 for DLL and Security or something similar if you don't have that exact version.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
srcalcAuthor Commented:
OK, is it possible to do with System.Data.OLeDB so I don't have to use a reference?
0
 
User111111Commented:
nope, there is no current built-in way to dynamically make a new access db in vb.net
0
 
srcalcAuthor Commented:
ok let me fool around with that code and get back to you
0
 
srcalcAuthor Commented:
OK thanks for your input!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.