[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

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?
0
srcalc
Asked:
srcalc
  • 4
  • 3
1 Solution
 
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now