Solved

How to create Access tables and relationships using VB

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…

740 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