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.
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.
Private Sub cmdCreateTable_Click()
'Variables
Dim objCmd As ADODB.Command
Dim strSQL As String
Dim strDbConnection As String
'/Variables
strDbConnection = "Provider=Microsoft.Jet.OL
"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.Comman
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