Solved

How to create Access tables and relationships using VB

Posted on 2002-04-13
5
179 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

776 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