Solved

How to create Access tables and relationships using VB

Posted on 2002-04-13
5
177 Views
Last Modified: 2010-05-02
Hi

  Could someone advice me how I could create MS Access
tables and relationships using VB. I need to specify
the indexes and whether the field is required and whether to allow zero length too. Need to define primary keys with
auto number and without auto number. If I can use ADO that will be great.

Any help is greatly appreciated.

Thanks
0
Comment
Question by:b3cf
5 Comments
 
LVL 10

Expert Comment

by:smegghead
ID: 6938840
Use the 'CREATE TABLE' command, have a look in the help text for the syntax.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6938867
You can achieve all of this with the ADOX object model.

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6938902
Here is an example to create a new table using ADOX:

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
With cat
   .ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\db1.mdb"
   Set tbl = New ADOX.Table
   With tbl
      .Name = "Table1"
      .Columns.Append "ID", adInteger
      .Columns.Append "Description", adVarWChar, 50
      .Columns.Append "Created", adDate
   End With
   .Tables.Append tbl
End With
Set tbl = Nothing
Set cat = Nothing

Anthony
0
 
LVL 3

Accepted Solution

by:
dannic earned 50 total points
ID: 6939851
If you would like to use ADODB then you could try something like this.


Private Sub cmdCreateTable_Click()
                           
    'Variables
    Dim objCmd As ADODB.Command
    Dim strSQL As String
    Dim strDbConnection As String
    '/Variables
   
                         
    strDbConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=C:\temp\test.mdb;" & _
                      "Persist Security Info=False"
   
    strSQL = "CREATE TABLE myNewTable" & _
                            " (" & _
                            " ID int IDENTITY (1, 1) NOT NULL ," & _
                            " Age int , " & _
                            " FirstName Text (50) PRIMARY KEY, " & _
                            " LastName Text (50)" & _
                            " )"

    Set objCmd = CreateObject("ADODB.Command")
   
    With objCmd
        .ActiveConnection = strDbConnection
        .CommandText = strSQL
        .Execute
    End With
   
    If Not objCmd.ActiveConnection Is Nothing Then
        Set objCmd.ActiveConnection = Nothing
    End If
   
    Set objCmd = Nothing

End Sub

Good luck!
dannic
0
 

Author Comment

by:b3cf
ID: 6957899
Great. Thanx alot Dannic and others who contributed.
   
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now