How to create Access tables and relationships using VB

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
b3cfAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
dannicConnect With a Mentor Commented:
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
 
smeggheadCommented:
Use the 'CREATE TABLE' command, have a look in the help text for the syntax.
0
 
Anthony PerkinsCommented:
You can achieve all of this with the ADOX object model.

Anthony
0
 
Anthony PerkinsCommented:
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
 
b3cfAuthor Commented:
Great. Thanx alot Dannic and others who contributed.
   
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.