Create a Microsoft Access Database Using ADOX and Visual Basic .NET

Environment: ADO, Visual Basic .NET

HOW TO: Create a Microsoft Access Database Using ADOX and Visual Basic .NET

Summary

Programmers may have to create databases programmatically, but neither ActiveX Data Objects (ADO) nor ADO.NET provide the means to create Microsoft Access databases. However, you can create Access databases by using the Microsoft Jet OLE DB Provider and Microsoft ADO Ext. 2.7 for DDL and Security (ADOX) with the COM Interop layer.

Requirements

  • Microsoft Visual Basic .NET
  • ADO Ext. 2.7 for DDL and Security (ADOX)

This step-by-step example describes how to use ADOX and Visual Basic .NET to create an Access database on the fly.

Steps to Build Example

  1. Open a new Visual Basic .NET Console application.
  2. In Solution Explorer, right-click the References node, and then click Add Reference.
  3. In the Add Reference dialog box, click the COM tab, click Microsoft ADO Ext. 2.7 for DDL and Security, click Select to add it to the Selected Components section, and then click OK.
  4. Delete all of the code from the code window for Module1.vb.
  5. Copy the following code and paste it in the code window:
  6. Imports ADOX
    
    Module Module1
    
      Sub Main()
    
          Dim cat As Catalog = New Catalog()
    
          cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=D:\AccessDB\NewMDB.mdb;" & _
                     "Jet OLEDB:Engine Type=5")
    
          Console.WriteLine("Database Created Successfully")
    
          cat = Nothing
    
      End Sub
    
    End Module
    
  7. Change the path to the new .mdb file as appropriate. Make sure the folder provided in the path exists. Press F5 to build and run the project.

The new .mdb file is created in Access 2000 (Jet 4.0) format. For a different Jet format, read the following section.

HOWTO: Create a Table with Primary Key Through ADOX

Summary

ADOX is an extension to ActiveX Data Objects that allows the manipulation of the database schema. This section illustrates how to use ADOX to create a table and add a Primary Key.

More Information

Note: Not all OLE DB providers support the interfaces required to support ADOX methods. With those providers, you have to use Data Definition Queries or another object model to manipulate the database schema.

The first procedure in the followingexample creates a new table in an existing Microsoft Access database, creates a new field in that table, then creates a primary key index. When adding a single-field primary key, you do not need to use the ADOX Key object.

The second procedure utilizes the ADOX Key object to add a multiple field key to a table.

Steps to Create the Sample Application

In Microsoft Visual Basic 5.0 or 6.0, create a new Standard EXE project. Form1 is created by default. On the Project menu, select References to add the following type libraries:

  • Microsoft ActiveX Data Objects 2.1 Library
  • Microsoft ADO Ext. 2.1 for DDL and Security

Add two Command buttons (Command1 and Command2) and the following code to the Form1: Option Explicit:

Private Sub Command1_Click()
'
' This code adds a single-field Primary key
'
Dim Cn As ADODB.Connection, Cat As ADOX.Catalog, _
                            objTable As ADOX.Table

  Set Cn       = New ADODB.Connection
  Set Cat      = New ADOX.Catalog
  Set objTable = New ADOX.Table

  'Open the connection
  Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"

  'Open the Catalog
  Set Cat.ActiveConnection = Cn

  'Create the table
  objTable.Name = "Test_Table"

  'Create and Append a new field to the "Test_Table"
  'Columns Collection
  objTable.Columns.Append "PrimaryKey_Field", adInteger

  'Create and Append a new key. Note that we are merely passing
  'the "PimaryKey_Field" column as the source of the primary key.
  'Thi snew Key will be Appended to the Keys Collection of
  '"Test_Table"
  objTable.Keys.Append "PrimaryKey", adKeyPrimary, _
                       "PrimaryKey_Field"

  'Append the newly created table to the Tables Collection
  Cat.Tables.Append objTable

' clean up objects
  Set objKey = Nothing
  Set objTable = Nothing
  Set Cat = Nothing
  Cn.Close
  Set Cn = Nothing
End Sub

Private Sub Command2_Click()
'
' This code adds a multi-field Primary Key
'
Dim Cn As ADODB.Connection, Cat As ADOX.Catalog
Dim objTable As ADOX.Table, objKey As ADOX.Key

  Set Cn       = New ADODB.Connection
  Set Cat      = New ADOX.Catalog
  Set objTable = New ADOX.Table
  Set objKey   = New ADOX.Key

  Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
  Set Cat.ActiveConnection = Cn
  objTable.Name = "Test_Table2"

  objTable.Columns.Append "PrimaryKey_Field1", adInteger
  objTable.Columns.Append "PrimaryKey_Field2", adInteger

  objKey.Name = "PrimaryKey"
  objKey.Type = adKeyPrimary
  objKey.Columns.Append "PrimaryKey_Field1"
  objKey.Columns.Append "PrimaryKey_Field2"

  objTable.Keys.Append objKey

  Cat.Tables.Append objTable

' clean up objects
  Set objKey   = Nothing
  Set objTable = Nothing
  Set Cat      = Nothing
  Cn.Close
  Set Cn       = Nothing
End Sub
Note: You might have to adjust the connect string to point to a valid Jet database.

Run the application and click the Command buttons. You can check the table definitions for Test_Table and TestTable2 in Microsoft Access 97, Microsoft Access 2000, or the Visual Basic Visual Data Manager add-in.



Comments

  • the cheapest clarisonic pro with corps brush jumble sale in the ebay

    Posted by iouwanzi on 06/06/2013 03:58am

    [url=http://www.miaclarisonicaustralia.org/]clarisonic australia[/url] Je voudrais que celui qui crée mon vos cheveux aspect extraordinaire, ainsi que que j’ai pu utilisation pour vous aider à redresser et faire de superbes boucles. C’est pour quelle raison mon conjoint et j’ai décidé de commander quelques autre ghd Gold styler.Qu’ils sont sérieusement incroyables, avec, on dirait qu’ils soient uniquement en conséquence substantielle dans un an ou deux, sauf si la rend plue ramasser la fonction ! Qu’ils sont dignes du plus abordable par opposition à travers Aus, ainsi que leur prix, très dernière longtemps. ghd Gold styler ont un instrument d’authentification très bon sur les sites Web pour vous assurer que vous avez une totale fiabilité fer plat. [url=http://www.miaclarisonicaustralia.org/]clarisonic mia[/url] Vous le trouverez légitimes, la plupart d’entre nous ne considérerons pas un facteur de que chacun d’entre nous n’ont aucune idée après la plupart des ! Conçu pour (certains) exactement qui ne connaissent pas le véritable label, que ne devrait pas la touche panique, comme je l’expliquerai avec plusieurs phrases ce qu’il arrive à être. Mon partenaire et moi est contacté par ghd pas cher à savoir dans le cas que je voulais partager avec vous leurs marchandises et sont utilisées pour n’hésitez ne pas nécessairement 1 plus loin que j’ai votre am loyal pour les années. [url=http://www.miaclarisonicaustralia.org/]clarisonic mia online[/url] Je voudrais que celui qui crée mon vos cheveux aspect extraordinaire, ainsi que que j’ai pu utilisation pour vous aider à redresser et faire de superbes boucles. C’est pour quelle raison mon conjoint et j’ai décidé de commander quelques autre ghd Gold styler.Qu’ils sont sérieusement incroyables, avec, on dirait qu’ils soient uniquement en conséquence substantielle dans un an ou deux, sauf si la rend plue ramasser la fonction ! Qu’ils sont dignes du plus abordable par opposition à travers Aus, ainsi que leur prix, très dernière longtemps. ghd Gold styler ont un instrument d’authentification très bon sur les sites Web pour vous assurer que vous avez une totale fiabilité fer plat.

    Reply
  • En anden vigtig ting er at et ghd glattejern ikke bliver sÃ¥ varmt som andre glattejern

    Posted by motherdhmm on 05/30/2013 12:00pm

    [url=http://www.blog.cheapbeatsbydre.co.nz/]beats by dre[/url] Den mest kendte er ghd IV Salon Styler som bliver brugt verden over på blandt andet modeshows og catwalk. Fordelen ved en IV Salon Styler er de brede plader. Det betyder at man kan glatte større områder af gangen og dermed bliver hurtigere færdige. Ulempen er at de brede plader f.eks. ikke er optimale til at style håret. Vi du både kunne glatte dit hår og samtidig bruge dit ghd glattejern til at lave f.eks. krøller, så er ghd IV styler et oplagt valg. Den kan det hele og glatter også dit hår perfekt. Men den er lidt langsommere end hvis man vælger den brede salon styler fra ghd. Endelig er der også den helt smalle ghd IV Mini Styler. [url=http://www.blog.cheapbeatsbydre.co.nz/]beats by dre nz[/url] Ghd sammenlignet med tidligere produkter, denne generation af glat hår med ekstra slankt design, mode af den ydre uanstændige og nogle geniale nye unikke sted i glat hår, hårpleje og styling har gjort store forbedringer. GHD IV styler primære unikt sted: mere vækst i den keramiske varme plader. MK4 Ghd Opvarmningen er færdig vil der være et bip for at gøre dig opmærksom på at organisere ansøgning. Unik og holdbart cover design, ikke noget på egen hånd efter 15 minutter lukkede specielle kabler designet til at opretholde den fugtige keramiske feber tabletter fra skade på miljøet. [url=http://www.blog.cheapbeatsbydre.co.nz/]beats by dre[/url] Alle de bedste Glattejern lidelse artikler kan blive akklimatiseret med hårudglatningsmiddel at tilpasse hair.Another en masse fantastiske amore af GHD Glattejern Sale sin slå vilkårlig spænding fordel, der giver dig mulighed for at bruge håret i ethvert land og få ovenstående kølige GHD realisering bagefter eventuelle ophør på grund af spændingen complication.Many organer vil købe ghd, så vil de spare mere, på ovennævnte tidspunkt, de vil opnå selv bliver tilføjet charmerende.

    Reply
  • http://tinyurl.com/aygo9o9

    Posted by marco on 12/04/2012 02:36pm

    http://tinyurl.com/aygo9o9

    Reply
  • New one

    Posted by snareenactina on 11/10/2012 12:53pm

    workerlaurie Calgary is head office for many major oil and gas related companies, and many financial service business have grown up around them. Retail sales last month generated their strongest performance since February, but a dramatic return to healthier consumer spending isn't likely just yet. Browse White House visitor logs petroleum This is simply not true. Taxes give money value. The more vibrant the economy, the more efficient the tax collection, the more valuable the currency. benazir You can also look at gaps within the 'big four' countries/regions: China, India, USA and the EU. kengs ESDS is in four parts: sublimation Further measures included: rftotal The Economic Justice requires that the national capital should be distributed equally among the people for not only smooth operation of economy but also elimination of poverty. Both Adam Smith and Karl Marx misguided the world in the name of Capitalsim and Communism. The Natural Law requires PEOPL'S DIRECT OWNERSHIP OF CAPITAL to eetablish an Ideal Society for which mankind has been struggling for thousands of year since genesis of civilization. mjpeg Important European herbivores are snails, larvae, fish, different birds, and mammals, like rodents, deer and roe deer, boars, and living in the mountains, marmots, steinbocks, chamois among others. shirai UT Dallas undergraduates who meet the requirements for admission to graduate school and have at least a 3.25 UT Dallas cumulative GPA should consider the Fast-Track option. Fast-Track allows students to take up to 15 credit hours of graduate courses their senior year that count toward both their bachelor's degree and a master's degree. Students who successfully complete the Fast-Track requirements are not required to take the Graduate Record Exam (GRE) before entering the master's program.

    Reply
  • ado.net with vb.net code

    Posted by Vaibhav Chawla on 09/12/2004 07:53am

    please give me that

    Reply
  • VB.NET

    Posted by Legacy on 01/19/2004 12:00am

    Originally posted by: praveen

    Hi,

    can you suggest how to create table programmatically using VB.NET.

    The same code which is used to create table in VB5.0 can we write using VB.NET.

    please suggest me in this regard

    reagrds

    praveen

    • password???

      Posted by niteen_padwal on 07/04/2007 02:39am

      crating database using this code works....thanx
      but is it possible to set password to that database?

      Reply
    • OK

      Posted by parvezhakim12 on 12/12/2004 12:28pm

      just use like sql = "create table XY( p IDENTITY, q MONEY)" cmd.command text = sql cmd.executenonquery .... Parvez Parvez

      Reply
    • Parvez Here

      Posted by parvezhakim12 on 12/12/2004 12:26pm

      Yes.... it is possible str = "CREATE TABLE EMP(......" dim cmd as new oledbcommand with cmd .connection = cn .commandtext = sql .executenonquery end with

      Reply
    • Autoincrement

      Posted by invid on 06/30/2004 02:07pm

      I have adapted this code to work in C#, but I am wondering, how to set the column as Autoincrement. I have seen samples, but I get generic database errors when I try them.

      Reply
    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Today's agile organizations pose operations teams with a tremendous challenge: to deploy new releases to production immediately after development and testing is completed. To ensure that applications are deployed successfully, an automatic and transparent process is required. We refer to this process as Zero Touch Deployment™. This white paper reviews two approaches to Zero Touch Deployment--a script-based solution and a release automation platform. The article discusses how each can solve the key …

  • Learn How A Global Entertainment Company Saw a 448% ROI Every business today uses software to manage systems, deliver products, and empower employees to do their jobs. But software inevitably breaks, and when it does, businesses lose money -- in the form of dissatisfied customers, missed SLAs or lost productivity. PagerDuty, an operations performance platform, solves this problem by helping operations engineers and developers more effectively manage and resolve incidents across a company's global operations. …

Most Popular Programming Stories

More for Developers

RSS Feeds