Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How to create Access tables and relationships using VB
Posted on 2002-04-13
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
[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
2
- +1
5 Comments
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.
There is also a companion Debug Toolbar that looks like the followin…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month5 days, 3 hours left to enroll
Earn Certification
CompTIA IT Fundamentals
$49.00$44.10
636 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.