VB6 code to create a db and Table

I need help with this vb6 code. It worked at one time and created a database m086.mdb
However, now it gives me an ISAM error before the create. I can't figure out why.

Also, when it DID create the m086.mdb it would not continue and create a table in the new db. How do I then get the table into the m086.mdb?

Private Sub CreateClass_Click()
On Error GoTo ErrorCreateDB
Dim Cat As New ADOX.Catalog
Dim Tbl(5) As ADOX.Table
Dim Idx() As ADOX.Index
Dim msgErrR As Integer
Dim sCnn As String
NuevaDB$ = "m086"

sCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:reEngine Type=5;" + "Data Source=" + NuevaDB$ + ".mdb"

'error next statement:"can not find installable
' At one time I did NOT get this error now I do !

Cat.Create sCnn
'what code do I need HERE to create a table with specs below?

Set Tbl(5) = New ADOX.Table
Set Tbl(5).ParentCatalog = Cat
With Tbl(5)

.Name = "ClassList"

.Columns.Append "Password", adVarWChar, 5

.Columns.Append "StudenNumber", adInteger

.Columns("StudenNumber").Properties("AutoIncrement").Value = True

.Columns("StudenNumber").Properties("Nullable").Value = False

.Columns.Append "StudentFirst", adVarWChar, 25

.Columns("StudentFirst").Properties("Nullable").Value = False

.Columns.Append "StudentLast", adVarWChar, 25

.Columns("StudentLast").Properties("Nullable").Value = False

.Columns.Append "TeacherFirst", adVarWChar, 25

.Columns("TeacherFirst").Properties("Nullable").Value = False

.Columns.Append "TeacherLast", adVarWChar, 25

.Columns("TeacherLast").Properties("Nullable").Value = False
End With

ReDim Idx(1)
Set Idx(0) = New ADOX.Index

Idx(0).Name = "ClassID"

Idx(0).IndexNulls = adIndexNullsAllow

Idx(0).Unique = True

Idx(0).Columns.Append "Password"
Set Idx(1) = New ADOX.Index

Idx(1).Name = "PrimaryKey"

Idx(1).PrimaryKey = True

Idx(1).Unique = True

Idx(1).Columns.Append "StudenNumber"
For i = 0 To UBound(Idx)

Tbl(5).Indexes.Append Idx(i)
Next i
Cat.Tables.Append Tbl(5)
Set Cat = Nothing
Exit Sub


msgErrR = MsgBox(" Error No. " & Err & " " & vbCrLf & Error, vbCritical + vbAbortRetryIgnore, "Code Gen Error")

Select Case msgErrR

Case Is = vbAbort

If Not (Cat Is Nothing) Then

Set Cat = Nothing

End If

Exit Sub

Case Is = vbRetry

Resume Next

Case Is = vbIgnore


End Select
End Sub

Who is Participating?
riaz9Connect With a Mentor Commented:
You can try:

Dim catCatalog As New ADOX.Catalog, tblTable As ADOX.Table, colColumn As ADOX.Column

catCatalog.Create "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & Database

set tblTable = New ADOX.Table
tblTable.Name = "MyTable"

set colColumn = new ADOX.Column
colColumn.Name = "ColumnName"  '**
colColumn.Type = adVarWChar    '**
colColumn.DefinedSize = 50     '**
colColumn.Attributes = adColNullable  '**  - this still does not set Allow Zero Length Attribute to Zero (it only sets Required to False)

tblTable.Columns.Append colColumn   '**

'** repeat the code to add as many columns needed.

'for columns like datetime you don't need DefinedSize

catCatalog.Tables.Append tblTable

set tblTable = nothing: set colColumn = nothing: set catCatalog = nothing

hope this helps,

mathprofAuthor Commented:
Thanks Riaz9. I changed the code (see below) modifying a bit what you suggested.


strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" + NuevaDB$ + ".mdb"
    Set catNewDatabase = New ADOX.Catalog
    catNewDatabase.Create strConn
    Set catNewDatabase = Nothing
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.

All Courses

From novice to tech pro — start learning today.