VB.NET - Create new MS Access Database using ADOX-VBForums
Results 1 to 29 of 29

Thread: VB.NET - Create new MS Access Database using ADOX

  1. #1

    Thread Starter
    Elite Member
    Join Date
    Aug 2002
    Location
    RUH
    Posts
    8,081

    VB.NET - Create new MS Access Database using ADOX

    VB Code:
    1. Private Sub Command1_Click()
    2. 'Shows how to create an Access 2000 database and append tables, fields, indexes using ADOX. Don't forget
    3. 'a reference to ADOX (Microsoft ADO Ext. 2.x for DDL and Security)
    4.   Dim ADOXcatalog As New ADOX.Catalog
    5.   Dim ADOXtable As New Table
    6.   Dim ADOXindex As New ADOX.Index
    7.  
    8.    On Error GoTo errhandler
    9.   ADOXcatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "c:\newdata.mdb"
    10.  
    11.   On Error Resume Next
    12.  
    13.   ADOXcatalog.ActiveConnection = _
    14. "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
    15. & "c:\newdata.mdb"
    16.  
    17.   'name table, append fields to table
    18.   ADOXtable.Name = "Employees"
    19.   ADOXtable.Columns.Append "LastName", adVarWChar, 40
    20.   ADOXtable.Columns.Append "ID", adInteger
    21.   ADOXtable.Columns.Append "Department", adVarWChar, 20
    22.  
    23.   'append tables to database
    24.   ADOXcatalog.Tables.Append ADOXtable
    25.  
    26.  
    27.   'internal index on two fields
    28.   ADOXindex.Name = "TwoColumnsIndex" 'name of index
    29.   ADOXindex.Columns.Append "LastName"
    30.   ADOXindex.Columns.Append "ID"
    31.  
    32.   ADOXtable.Indexes.Append ADOXindex
    33. errhandler:
    34.   If Err.Number = -2147217897 Then
    35.     MsgBox "Database already exists"
    36. ElseIf Err.Number <> 0 Then
    37.     MsgBox "Err " & Err.Description & "; operation not complete"
    38. End If
    39. Set ADOXtable = Nothing
    40. Set ADOXindex = Nothing
    41. Set ADOXcatalog = Nothing
    42. End Sub
    Last edited by Pirate; Aug 10th, 2003 at 09:50 AM.

  2. #2
    Lively Member
    Join Date
    Jan 2003
    Posts
    69
    In vb.net i got some problems with these lines,

    Dim ADOXtable As New table()
    Type Table not defined

    ADOXtable.Name = "Employees"
    ADOXtable.Columns.Append("LastName", adVarWChar, 40)
    ADOXtable.Columns.Append("ID", adInteger)
    ADOXtable.Columns.Append("Department", adVarWChar, 20)

    adVarWchar And adInteger is not declared

    Please Help
    Thanks

  3. #3
    Addicted Member
    Join Date
    Feb 2002
    Location
    closed
    Posts
    196
    adVarWchar And adInteger are 'old' vb6 constants - VB.NEt may use ADOX.DataTypeEnum.adInteger 0r something. Prolly the same deal with your Table type i.e. ADOX.Table.

    Sorry dont have VS.NEt here so cant check but hope this is a lead.

    Cheers

  4. #4

    Thread Starter
    Elite Member
    Join Date
    Aug 2002
    Location
    RUH
    Posts
    8,081
    I thought there is something missing too . I should've mentioned that you need to map these variables to the ADOX.DataTypeEnum . Here is the .NET Code :

    VB Code:
    1. Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    2.         'Shows how to create an Access 2000 database and append tables, fields, indexes using ADOX. Don't forget
    3.         'a reference to ADOX (Microsoft ADO Ext. 2.x for DDL and Security)
    4.         Dim ADOXcatalog As New ADOX.Catalog
    5.         Dim ADOXtable As New ADOX.Table
    6.         Dim ADOXindex As New ADOX.Index
    7.  
    8.         On Error GoTo errhandler
    9.         ADOXcatalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "c:\newdata.mdb")
    10.  
    11.         On Error Resume Next
    12.  
    13.         ADOXcatalog.ActiveConnection = _
    14.       "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
    15.       & "c:\newdata.mdb"
    16.  
    17.         'name table, append fields to table
    18.         ADOXtable.Name = "Employees"
    19.         ADOXtable.Columns.Append("LastName", ADOX.DataTypeEnum.adVarWChar, 40)
    20.         ADOXtable.Columns.Append("ID", ADOX.DataTypeEnum.adInteger)
    21.         ADOXtable.Columns.Append("Department", ADOX.DataTypeEnum.adVarWChar, 20)
    22.  
    23.         'append tables to database
    24.         ADOXcatalog.Tables.Append(ADOXtable)
    25.  
    26.  
    27.         'internal index on two fields
    28.         'ADOXindex.Name = "TwoColumnsIndex" 'name of index
    29.         'ADOXindex.Columns.Append("LastName")
    30.         'ADOXindex.Columns.Append("ID")
    31.  
    32.         ADOXtable.Indexes.Append(ADOXindex)
    33. errhandler:
    34.         If Err.Number = -2147217897 Then
    35.             MsgBox("Database already exists")
    36.         ElseIf Err.Number <> 0 Then
    37.             MsgBox("Err " & Err.Description & "; operation not complete")
    38.         End If
    39.         ADOXtable = Nothing
    40.         ADOXindex = Nothing
    41.         ADOXcatalog = Nothing
    42.     End Sub
    43. End Class

  5. #5

    Thread Starter
    Elite Member
    Join Date
    Aug 2002
    Location
    RUH
    Posts
    8,081
    Since some guys have faced some problems with finding ADOX Library , I put it here . Just unpack and copy it in your project folder then reference it .
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    Jul 2001
    Posts
    283
    ok thanks pirate. i didnt even need the file. i just didnt know what it was talking baout when it said to reference the ADOX dll...

    so for everybody: go to project file menu, then go to add reference. on the com tab chose microsoft ado ext. ....

    thanks pirate for all the help...

  7. #7

    Thread Starter
    Elite Member
    Join Date
    Aug 2002
    Location
    RUH
    Posts
    8,081
    Sure no prob.

    This referenced dll exist on both XP , Win200 but I'm not sure about older versions of Windows (ME,Win98) nor WinServer 2003. .So I'd suggest to make a copy in your project folder , just in case you face some problems while deployment .

  8. #8

    Thread Starter
    Elite Member
    Join Date
    Aug 2002
    Location
    RUH
    Posts
    8,081
    I'll do some code update since it raises nasty error , although it's still working .

  9. #9

    Thread Starter
    Elite Member
    Join Date
    Aug 2002
    Location
    RUH
    Posts
    8,081
    1st update . . enjoy . Next update will include creating array of tables and columns and selecting datatype for each column .

    If you have any question , you can pester me .
    Attached Files Attached Files

  10. #10
    New Member
    Join Date
    Feb 2005
    Posts
    1

    Re: VB.NET - Create new MS Access Database using ADOX

    Quote Originally Posted by Pirate
    'name table, append fields to table
    ADOXtable.Name = "Employees"
    ADOXtable.Columns.Append("LastName", ADOX.DataTypeEnum.adVarWChar, 40)
    ADOXtable.Columns.Append("ID", ADOX.DataTypeEnum.adInteger)
    ADOXtable.Columns.Append("Department", ADOX.DataTypeEnum.adVarWChar, 20)
    i wanna ask. how to make columns "ID" as a primary key. or "Department" as foreign key..

    thank you very much

  11. #11
    New Member
    Join Date
    Mar 2005
    Posts
    9

    Re: VB.NET - Create new MS Access Database using ADOX

    I have a question about using ADOX. Is there a way to create the access file as a hidden file?

  12. #12

    Thread Starter
    Elite Member
    Join Date
    Aug 2002
    Location
    RUH
    Posts
    8,081
    Quote Originally Posted by rohcky
    I have a question about using ADOX. Is there a way to create the access file as a hidden file?
    Code:
     Call this method after you make sure the db is created .Probably after this line : 
     ADOXtable.Indexes.Append(ADOXindex)
    
     private void CreateMDBHidden(string MdbFile)
     		{
     			try 
     			{
     		    	if (File.Exists(MdbFile))File.SetAttributes(MdbFile,FileAttributes.Hidden);
     			}
     			catch ( Exception x)
     			{
     				MessageBox.Show(x.Message);
     			}			
     		}
    I didn't test the code yet but it looks it works .

  13. #13
    New Member
    Join Date
    Mar 2005
    Posts
    9

    Re: VB.NET - Create new MS Access Database using ADOX

    That did the trick. Thanks Pirate.

  14. #14
    Member
    Join Date
    Mar 2005
    Posts
    40

    Smile Re: VB.NET - Create new MS Access Database using ADOX

    Hello Pirate, I'v followed your code and it does work. But I have another question: how to create multiple tables and establish relationship between them?

  15. #15

    Thread Starter
    Elite Member
    Join Date
    Aug 2002
    Location
    RUH
    Posts
    8,081
    You can create an array of ADOX.Table obj that will be tables later . As for creating relations with ADOX lib , this docu will help you : http://msdn.microsoft.com/library/de...ireference.asp

  16. #16
    Member
    Join Date
    Mar 2005
    Posts
    40

    Smile Re: VB.NET - Create new MS Access Database using ADOX

    Thanks for the information!

    Btw, in the previous reply, you mentioned that you would create a table with setting the primary key and datatype. how is that going?

  17. #17
    Lively Member zen_master's Avatar
    Join Date
    Apr 2005
    Location
    Buffalo, NY
    Posts
    114

    Re: VB.NET - Create new MS Access Database using ADOX

    oOO..


    another piece of nice info for my future project!

  18. #18
    Hyperactive Member
    Join Date
    Aug 2005
    Location
    Cochin, India
    Posts
    350

    Re: VB.NET - Create new MS Access Database using ADOX

    What should i do if i want to create a field of BLOB datatype(to store images)?

  19. #19
    Frenzied Member dinosaur_uk's Avatar
    Join Date
    Sep 2004
    Location
    Jurassic Park
    Posts
    1,098

    Re: VB.NET - Create new MS Access Database using ADOX

    How do i add a primary key to the table?
    If you find my thread helpful, please remember to rate me

  20. #20
    Frenzied Member dinosaur_uk's Avatar
    Join Date
    Sep 2004
    Location
    Jurassic Park
    Posts
    1,098

    Re: VB.NET - Create new MS Access Database using ADOX

    Found it on MSDN!

    VB Code:
    1. Option Explicit
    2.  
    3. Private Sub Command1_Click()
    4. '
    5. ' This code adds a single-field Primary key
    6. '
    7. Dim Cn As ADODB.Connection, Cat As ADOX.Catalog, objTable As ADOX.Table
    8.  
    9.   Set Cn = New ADODB.Connection
    10.   Set Cat = New ADOX.Catalog
    11.   Set objTable = New ADOX.Table
    12.  
    13.   'Open the connection
    14.   Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
    15.  
    16.   'Open the Catalog
    17.   Set Cat.ActiveConnection = Cn
    18.  
    19.   'Create the table
    20.   objTable.Name = "Test_Table"
    21.  
    22.   'Create and Append a new field to the "Test_Table" Columns Collection
    23.   objTable.Columns.Append "PrimaryKey_Field", adInteger
    24.  
    25.   'Create and Append a new key. Note that we are merely passing
    26.   'the "PimaryKey_Field" column as the source of the primary key. This
    27.   'new Key will be Appended to the Keys Collection of "Test_Table"
    28.   objTable.Keys.Append "PrimaryKey", adKeyPrimary, "PrimaryKey_Field"
    29.  
    30.   'Append the newly created table to the Tables Collection
    31.   Cat.Tables.Append objTable
    32.  
    33. ' clean up objects
    34.   Set objKey = Nothing
    35.   Set objTable = Nothing
    36.   Set Cat = Nothing
    37.   Cn.Close
    38.   Set Cn = Nothing
    39. End Sub
    If you find my thread helpful, please remember to rate me

  21. #21
    Addicted Member dani2's Avatar
    Join Date
    Feb 2005
    Location
    Sibiu.ro
    Posts
    191

    Re: VB.NET - Create new MS Access Database using ADOX

    Does this work also for DBF fileS?

    thank you
    Home is where your Head is

  22. #22
    New Member
    Join Date
    Jan 2007
    Posts
    1

    Re: VB.NET - Create new MS Access Database using ADOX

    Hey great thread.

    Could someone tell me what would I need to add in order to make the columns I append allow Null values?

  23. #23
    Addicted Member
    Join Date
    Nov 2004
    Posts
    195

    Re: VB.NET - Create new MS Access Database using ADOX

    Pirate,
    I tried this code in VB.NET 2005 and get a "Type is Invalid." exception on the "Cat.Tables.Append objTable" line.

    Any ideas?

  24. #24
    PowerPoster JuggaloBrotha's Avatar
    Join Date
    Sep 2005
    Location
    Lansing, MI; USA
    Posts
    3,817

    Re: VB.NET - Create new MS Access Database using ADOX

    I already know how to make the fields in a new table and setting the primary key already

    how do I set the Primary Key field to AutoIncrement Integer? right now it's currently set as AdInteger, but what about the Auto Increment one?

  25. #25
    New Member Meisi's Avatar
    Join Date
    Apr 2007
    Posts
    1

    Re: VB.NET - Create new MS Access Database using ADOX

    I can create a Database with ADOX Class but i have some problem in disconnecting from created database... these codes can not disconnect the program from the created database:

    ADOXtable = Nothing

    ADOXindex = Nothing

    ADOXcatalog = Nothing

    Can anyone help me?

  26. #26
    Learning .Net danasegarane's Avatar
    Join Date
    Aug 2004
    Location
    VBForums
    Posts
    5,833

    Re: VB.NET - Create new MS Access Database using ADOX

    If you want to create the database with password, Then change the connection string as follows


    Code:
    ADOXcatalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "c:\newdata.mdb;Jet OLEDB:Database Password=test")
    Please mark you thread resolved using the Thread Tools as shown



    www.techreceipe.tk


  27. #27
    Lively Member
    Join Date
    Jul 2008
    Posts
    84

    Re: VB.NET - Create new MS Access Database using ADOX

    I get a "Value Does Not Fall Within Expected Range"

    Here Is My Code, I prbly messed it up.
    vb Code:
    1. Private Sub NewDatabaseToolStripMenuItem_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewDatabaseToolStripMenuItem.Click
    2.  
    3.  
    4.         Dim ADOXcatalog As New ADOX.Catalog
    5.  
    6.         Dim ADOXtable As New ADOX.Table
    7.  
    8.         Dim ADOXindex As New ADOX.Index
    9.  
    10.         On Error GoTo errhandler
    11.         ADOXcatalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Documents and Settings\Tdupre8863\Desktop\Accounts.mdb")
    12.         On Error Resume Next
    13.         ADOXcatalog.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Documents and Settings\Tdupre8863\Desktop\Accounts.mdb"
    14.         'name table, append fields to table
    15.  
    16.         ADOXtable.Name = "Tabel1"
    17.  
    18.         ADOXtable.Columns.Append("Account", ADOX.DataTypeEnum.adVarWChar, 40)
    19.  
    20.         ADOXtable.Columns.Append("Username", ADOX.DataTypeEnum.adVarWChar, 20)
    21.  
    22.         'append tables to database
    23.  
    24.         ADOXcatalog.Tables.Append(ADOXtable)
    25.  
    26.         'internal index on two fields
    27.  
    28.         'ADOXindex.Name = "TwoColumnsIndex" 'name of index
    29.  
    30.         'ADOXindex.Columns.Append("Account")
    31.  
    32.         'ADOXindex.Columns.Append("Username")
    33.  
    34.         ADOXtable.Indexes.Append(ADOXindex)
    35.         MsgBox("Accounts.mdb Created In C:/", MsgBoxStyle.Information, "Success")
    36.  
    37. errhandler:
    38.  
    39.         If Err.Number = -2147217897 Then
    40.  
    41.             MsgBox("Database already exists", MsgBoxStyle.Exclamation, "No Need")
    42.  
    43.         ElseIf Err.Number <> 0 Then
    44.  
    45.             MsgBox("Err " & Err.Description & "; operation not complete")
    46.  
    47.         End If
    48.  
    49.         ADOXtable = Nothing
    50.  
    51.         ADOXindex = Nothing
    52.  
    53.         ADOXcatalog = Nothing
    54.  
    55.     End Sub

  28. #28
    New Member
    Join Date
    May 2011
    Posts
    1

    Re: VB.NET - Create new MS Access Database using ADOX

    Hi anybody?

    pls. HELP me on how to create database.

    pls. help me to have a codes of this data:

    Entering Name and Address

    then the checklist like example,
    Gender:
    O Male
    O Female

    Age: ____

    and then SAVE, ADD, EDIT and DELETE.

    this what codes i need. Pls. help guys.

  29. #29
    PowerPoster JuggaloBrotha's Avatar
    Join Date
    Sep 2005
    Location
    Lansing, MI; USA
    Posts
    3,817

    Re: VB.NET - Create new MS Access Database using ADOX

    Quote Originally Posted by Jomz87 View Post
    Hi anybody?

    pls. HELP me on how to create database.

    pls. help me to have a codes of this data:

    Entering Name and Address

    then the checklist like example,
    Gender:
    O Male
    O Female

    Age: ____

    and then SAVE, ADD, EDIT and DELETE.

    this what codes i need. Pls. help guys.
    The last few years I've found it far easier to simply create an empty database (or one with a few tables), include it in the project, then whenever I need a new db created in the program I simply copy it to the desired location with a meaningful name, then if a few tables need to be created, I just use the CREATE TABLE sql statement. Completely cuts out the frustrations of using ADOX.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.