Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to create Access tables and relationships using VB

Posted on 2002-04-13
5
Medium Priority
?
184 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 200 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…

670 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