Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
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
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
Asked:
2
- +1
1 Solution
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.OL
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
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New ADOX.Catalog
With cat
.ActiveConnection = "Provider=Microsoft.Jet.OL
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
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.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
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
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.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now