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.
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
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…
- Microsoft Office
- Office 365
- Microsoft Access
- VBA
- Microsoft Excel
- Visual Basic Classic
Suggested Courses
Course of the Month5 days, left to enroll
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.