ActiveX component for manipulating with databases?

Hello guys,
I have a vb6 app (RAD tool) in which I would like to add manipulation of database object for my users.
Creating tables, views, fields, maybe relationships for Access databases, MySQL & MSSQL.
Does anyone know of any ActiveX or something that I could include in my app so that users can create tables, views, fields and maybe also relationships?
I know about "Active Query Builder for ActiveX" from activequerybuilder.com but it only allows to create SQL queries on existing databases and not manipulating the database objects.

Thanks for your help,

Davor

Quote:

---

Originally Posted by Davor Geci

Hello guys,
I have a vb6 app (RAD tool) in which I would like to add manipulation of database object for my users.
Creating tables, views, fields, maybe relationships for Access databases, MySQL & MSSQL.
Does anyone know of any ActiveX or something that I could include in my app so that users can create tables, views, fields and maybe also relationships?
I know about "Active Query Builder for ActiveX" from activequerybuilder.com but it only allows to create SQL queries on existing databases and not manipulating the database objects.

Thanks for your help,

Davor

---

I don't know of a universal one. For access you can use adox, for sql you can use sqldmo (deprecated) or smo.

All of the databases do support manipulation via sql commands

SQL Code:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

the syntax may be slightly different between systems so you can do something like this:

VB6 Code:

public function AddTable(tableName as string)
    Select case databaseType
        case "Access"
              sql = "create table....."
        case "MSSQL"
              sql = "create table (.....)"
        case "MySQL"
              sql = "create table(.....)"
    end select
end function

That's all still stuff on the back end though... I think the OP is looking for something that hte END USER can use, something simple and graphically, something that hides that back end stuff from the user.... which I don't think really exists. I'm not sure, other than creating views and running them, I don't know if I'd want end users to have the ability to manipulate the database.

-tg

Hi Davor,

I would agree with tg, letting the User work with the Tables etc..
don't think I would allow that.

well anyway
In order to alter and see what the Tables look like you have to read the Database first.
this is For Access

you will need to set a Ref. to ADOX
you need on a Form 3 Listboxes and a Textbox

place this in the Form

Code:

---

Option Explicit
 
Dim cn As ADODB.Connection
Dim CnX As ADOX.Catalog
 

Private Sub Form_Load()

  Dim strPathToDB As String
  Dim i As Long
      Set cn = New ADODB.Connection
      Set CnX = New ADOX.Catalog
      strPathToDB = "E:\Northwind.mdb"
      With cn
        .CursorLocation = adUseClient
        .Mode = adModeShareDenyNone
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = strPathToDB
        .Open
      End With
      CnX.ActiveConnection = cn

' Load Tables
      List1.Clear
      For i = 0 To CnX.Tables.Count - 1
        If CnX.Tables(i).Type = "TABLE" Then
            List1.AddItem CnX.Tables(i).Name
        End If
      Next
     
     
End Sub
 
Private Sub List1_Click()
'Load Columns
Dim i As Integer
Dim Cat As New ADOX.Catalog

  List2.Visible = True
  List2.Clear
  Set Cat.ActiveConnection = cn
  With Cat
        For i = 0 To .Tables(List1.Text).Columns.Count - 1
            List2.AddItem .Tables(List1.Text).Columns(i).Name
        Next i
  End With
  Set Cat = Nothing
End Sub


Private Sub List2_Click()
' Get Info on Column
Dim i As Integer
Dim Cat As New ADOX.Catalog
Dim CName As String
Dim TName As String

TName = List1.Text
CName = List2.Text
 
  List3.Visible = True
  List3.Clear
  Set Cat.ActiveConnection = cn
  With Cat
      For i = 0 To .Tables(TName).Columns(CName).Properties.Count - 1
            List3.AddItem .Tables(TName).Columns(CName).Properties(i).Name & " - :" & .Tables(TName).Columns(CName).Properties(i).Value
        Next i
        Text1 = cType(Cat.Tables(TName).Columns(CName).Type)
  End With
  Set Cat = Nothing
End Sub

Public Function cType(ByVal Value As ADOX.DataTypeEnum) As String
  Select Case Value
    Case adTinyInt: cType = "adTinyInt"
    Case adSmallInt: cType = "adSmallInt"
    Case adInteger: cType = "adInteger"
    Case adBigInt: cType = "adBigInt"
    Case adUnsignedTinyInt: cType = "adUnsignedTinyInt"
    Case adUnsignedSmallInt: cType = "adUnsignedSmallInt"
    Case adUnsignedInt: cType = "adUnsignedInt"
    Case adUnsignedBigInt: cType = "adUnsignedBigInt"
    Case adSingle: cType = "adSingle"
    Case adDouble: cType = "adDouble"
    Case adCurrency: cType = "adCurrency"
    Case adDecimal: cType = "adDecimal"
    Case adNumeric: cType = "adNumeric"
    Case adBoolean: cType = "adBoolean"
    Case adUserDefined: cType = "adUserDefined"
    Case adVariant: cType = "adVariant"
    Case adGUID: cType = "adGUID"
    Case adDate: cType = "adDate"
    Case adDBDate: cType = "adDBDate"
    Case adDBTime: cType = "adDBTime"
    Case adDBTimeStamp: cType = "adDBTimeStamp"
    Case adBSTR: cType = "adBSTR"
    Case adChar: cType = "adChar"
    Case adVarChar: cType = "adVarChar"
    Case adLongVarChar: cType = "adLongVarChar"
    Case adWChar: cType = "adWChar"
    Case adVarWChar: cType = "adVarWChar"
    Case adLongVarWChar: cType = "adLongVarWChar"
    Case adBinary: cType = "adBinary"
    Case adVarBinary: cType = "adVarBinary"
    Case adLongVarBinary: cType = "adLongVarBinary"
    Case Else: cType = Value
  End Select
End Function

---

this will read all the Information in the Table


HTH

Quote:

---

Originally Posted by techgnome

I think the OP is looking for something that hte END USER can use, something simple and graphically, something that hides that back end stuff from the user

---

I think you are right and I don't think such a tool exists. Seems like he might just want to give them a lesson on how to use access, ssms, mysql workbench and let them have at it or roll his own tool.

Thanks for your replies guys.
It is a commercial project, so I didn't want to promote it, but if moderators allow me to explain what I'm searching for I will explain what I'm doing.
I'm the author of a tool for creating so-called Virtual Forms for Excel (VBA, VB6, Python,...., or any language that supports ActiveX).
https://www.virtual-forms.com
Now the user needs a database (Access, MySQL, MSSQL or Excel workbook)
Then the user (amateur developer) clicks a button to scan the Table or View and the tool creates the form for CRUD (create, read, update and delete records)
And he can also create Master-Detail forms by only providing the related tables and the keys.

What I would like to also include in this tool, is a simple database editor where a user can:

• create a new database (or edit existing one)
• create or edit existing table or view
• edit or create a new field (and maybe index)
• create relationships,....

Thank you for your suggestions and moderators to allow me to explain.
Davor

Here's how I handle this situation. First, we've just got to recognize that MS-Access has one of the best Visual SQL builders around, especially if we're talking about read-only Snapshot queries.

What I do is provide my users with a "Queries" MS-Access file that has linked tables to the primary database. And, when they call up that ...Queries database, they only see the following form:

Attachment 163499

There's just a bit of code behind that button that starts a Snapshot query with the primary table that most everything else connects to.

With that approach, the users stay out of trouble. And, so long as they use that button, there's no chance of them making any bogus changes to the data.

Regarding an ActiveX control, it just seems like you'd be re-inventing the wheel, and it'd never be anywhere close to the MS-Access SQL builder.

Good Luck,
Elroy

EDIT1: Now that I see your post #6, I'm not sure that solution will work for you. However, you've got some work in front of you if you want to compete with the MS-Access query engine. Good Luck.

Quote:

---

Originally Posted by Davor Geci

Thanks for your replies guys.
It is a commercial project, so I didn't want to promote it, but if moderators allow me to explain what I'm searching for I will explain what I'm doing.
I'm the author of a tool for creating so-called Virtual Forms for Excel (VBA, VB6, Python,...., or any language that supports ActiveX).
https://www.virtual-forms.com
Now the user needs a database (Access, MySQL, MSSQL or Excel workbook)
Then the user (amateur developer) clicks a button to scan the Table or View and the tool creates the form for CRUD (create, read, update and delete records)
And he can also create Master-Detail forms by only providing the related tables and the keys.

What I would like to also include in this tool, is a simple database editor where a user can:

• create a new database (or edit existing one)
• create or edit existing table or view
• edit or create a new field (and maybe index)
• create relationships,....

Thank you for your suggestions and moderators to allow me to explain.
Davor

---

well I don't mean to be rude, but do you want finished Code or only suggestions

you more or less want to create the - MS-Access query engine- with additional options to Edit/Alter Tables
and the VB6 Class-Builder for creating Forms (Master-Detail Forms)

I don't know what other suggestions you would need in order to recreate what M$ has done already
you can see how M$ done it, so you have the GUI in front of you

it's a Ton of work to do that for all DB-Systems,


well I have posted this in another Thread to give you an Idea...

the sample will create a Database and a Table with Index

for Access-MDB
in a Class

Code:

---

'Class Name : clsMDB_Tools
'reference to  : ADOX and ADO


'Functions:
'    AddColumn
'    AlterColumn
'    ColumnCount
'    ColumnType
'    CompactMDB
'    ConnectionOpen      Connection to extern MDB
'    CopyMdbTableStructure
'    CopyMdbTableIndexe
'    CreateIndex
'    CreateMDB
'    CreatePrimaryIndex
'    CT_AddFld
'    CT_CreateTable
'    CurrentUserCount
'    DataType
'    DropColumn
'    DropIndex
'    DropTable
'    ExistColumn
'    ExistIndex
'    ExistTable
'    GetProviderFromLink
'    IndexCount
'    IndexInfo
'    LinkTableCreate
'    LinkTableDelete
'    RenameColum
'    RenameTable
'    TableCount
'    TableExport2MDB
'    TableImportFromMDB
'    TableInfo



Option Explicit

Public Enum mdbExpImpTblEnum
  DropExpImpTableIfExist = 1
  DropLinkTableIfExist = 2
  CreateIndexTables = 4
End Enum

Public Enum adoDBCreateOptions
  Access_97 = 1
  Access_2000 = 2
  'etc....
End Enum

Public Enum adoColumnSortOrder
  byColumnName = 1
  byOrdinalPosition = 2
End Enum

Public Enum SqlIndexOptions
  sqlIndexUnique = 1
  sqlIndexClustered = 2
End Enum

Public Enum sqlFieldTypes
  sqlFieldInt = 1
  sqlFieldSingle = 2
  sqlFieldDouble = 3
  sqlFieldCurrency = 4
  sqlFieldDate = 5
  sqlFieldBoolean = 6
  sqlFieldTextFix = 7
  sqlFieldTextVar = 8
  sqlFieldMemo = 9
  sqlFieldByte = 10
End Enum

Public Enum sqlFieldTypeOptions
  sqlFieldNull = 1
  sqlFieldNotNull = 2
  sqlFieldAutoIncrement = 4
  sqlFieldPrimaryKey = 8
End Enum

Private FldList() As String

Private Sub Class_Initialize()
      ReDim FldList(0)
End Sub

Public Function CreateMDB(dbName As String, _
                          dbType As adoDBCreateOptions, _
                          Optional Password As String = "", _
                          Optional ErrNumber As Long, _
                          Optional ErrDescription As String, _
                          Optional ShowErrorMsg As String = True) _
                          As Boolean

  Dim Cat As ADOX.Catalog
  Dim strConnect As String
 
      If dbType = Access_97 Then
        strConnect = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
                      "Jet OLEDB:Engine Type=4;" & _
                      "Data Source=" & dbName
      ElseIf dbType = Access_2000 Then
        strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Jet OLEDB:Engine Type=5;" & _
                      "Data Source=" & dbName
    ' Elseif dbType
    ' etc....
   
      End If
      If Len(Password) > 0 Then
        strConnect = strConnect & ";Jet OLEDB:Database Password=" & _
        Password
      End If
     
      On Error GoTo Fehler
      Set Cat = New ADOX.Catalog
     
      Cat.Create strConnect
      CreateMDB = True
     
Fehler:
      If Err.Number <> 0 Then
        ErrNumber = Err.Number
        ErrDescription = Err.Description
        If ShowErrorMsg Then
            FehlerAnzeige Err.Number, Err.Description, "CreateMDB " & _
                          dbName
        End If
      End If
      Set Cat = Nothing
      On Error GoTo 0
End Function

Public Property Get DataType(Typenumber As Long) As String

  Dim s As String
     
      Select Case Typenumber
        Case 0: s = "Empty"
        Case 16: s = "TinyInt"
        Case 2: s = "SmallInt"
        Case 3: s = "Integer"
        Case 20: s = "BigInt"
        Case 17: s = "UnsignedTinyInt"
        Case 18: s = "UnsignedSmallInt"
        Case 19: s = "UnsignedInt"
        Case 21: s = "UnsignedBigInt"
        Case 4: s = "Single"
        Case 5: s = "Double"
        Case 6: s = "Currency"
        Case 14: s = "Decimal"
        Case 131: s = "Numeric"
        Case 11: s = "Boolean"
        Case 10: s = "Error"
        Case 132: s = "UserDefined"
        Case 12: s = "Variant"
        Case 9: s = "IDispatch"
        Case 13: s = "Unknown"
        Case 72: s = "GUID"
        Case 7: s = "Date"
        Case 133: s = "DBDate"
        Case 134: s = "DBTime"
        Case 135: s = "DBTimeStamp"
        Case 8: s = "BSTR"
        Case 129: s = "Char"
        Case 200: s = "VarChar"
        Case 201: s = "LongVarChar"
        Case 130: s = "WChar"
        Case 202: s = "VarWChar"
        Case 203: s = "LongVarWChar"
        Case 128: s = "Binary"
        Case 204: s = "VarBinary"
        Case 205: s = "LongVarBinary"
        Case Else: s = "Unknown"
      End Select
     
      DataType = s
     
'http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=122
'
''---- DataTypeEnum Values ----
'Const adEmpty = 0
'Const adTinyInt = 16
'Const adSmallInt = 2
'Const adInteger = 3
'Const adBigInt = 20
'Const adUnsignedTinyInt = 17
'Const adUnsignedSmallInt = 18
'Const adUnsignedInt = 19
'Const adUnsignedBigInt = 21
'Const adSingle = 4
'Const adDouble = 5
'Const adCurrency = 6
'Const adDecimal = 14
'Const adNumeric = 131
'Const adBoolean = 11
'Const adError = 10
'Const adUserDefined = 132
'Const adVariant = 12
'Const adIDispatch = 9
'Const adIUnknown = 13
'Const adGUID = 72
'Const adDate = 7
'Const adDBDate = 133
'Const adDBTime = 134
'Const adDBTimeStamp = 135
'Const adBSTR = 8
'Const adChar = 129
'Const adVarChar = 200
'Const adLongVarChar = 201
'Const adWChar = 130
'Const adVarWChar = 202
'Const adLongVarWChar = 203
'Const adBinary = 128
'Const adVarBinary = 204
'Const adLongVarBinary = 205
'
End Property

'Feld zu Feldliste für CreateTable
Public Sub CT_AddFld(FieldName As String, _
                    FieldType As sqlFieldTypes, _
                    Optional FieldLen As Long = 0, _
                    Optional Options As sqlFieldTypeOptions = 0)
                   
  Dim s As String
  Dim i As Long
 
      'FieldType in Text
      s = GetSqlFieldType(FieldType)
     
      s = FieldName & Space(1) & s
      If (FieldType = sqlFieldTextFix) Or (FieldType = sqlFieldTextVar) Then
        s = s & "(" & FieldLen & ")"
      End If
     
      If (Options And sqlFieldNull) = sqlFieldNull Then
        s = s & " NULL"
      End If
      If (Options And sqlFieldNotNull) = sqlFieldNotNull Then
        s = s & " Not NULL"
      End If
      If (Options And sqlFieldAutoIncrement) = _
          sqlFieldAutoIncrement Then
        s = s & " Identity"
      End If
      If (Options And sqlFieldPrimaryKey) = _
          sqlFieldPrimaryKey Then
        s = s & " Primary Key "
      End If
     
      i = UBound(FldList)
      If i = 0 Then
        If Len(FldList(i)) > 0 Then
            i = i + 1
        End If
      Else
        i = i + 1
      End If
     
      ReDim Preserve FldList(i)
      FldList(i) = s
End Sub

Public Function CT_CreateTable(ActConn As ADODB.Connection, _
                              TableName As String, _
                              Optional ErrNumber As Long, _
                              Optional ErrDescription As String, _
                              Optional ShowErrorMsg _
                              As Boolean = True) As Boolean
  Dim sSQL As String
      On Error GoTo 0
      sSQL = "Create Table " & TableName & _
            " (" & Join(FldList, ", ") & ")"
      ActConn.Execute sSQL
      CT_CreateTable = True
Fehler:
      If Err.Number <> 0 Then
        ErrNumber = Err.Number
        ErrDescription = Err.Description
        If ShowErrorMsg Then
            FehlerAnzeige Err.Number, Err.Description, "CT_CreateTable"
        End If
      End If
      On Error GoTo 0
      ReDim FldList(0)
End Function

Public Function CreatePrimaryKeyMDB(ActConn As ADODB.Connection, _
                          TableName As String, _
                          FieldName As String, _
                          Optional IndexName As String _
                          = "PrimaryKey", _
                          Optional ErrNumber As Long = 0, _
                          Optional ErrDescription As String = "", _
                          Optional ShowErrorMsg As Boolean = False) _
                          As Boolean
  Dim sSQL As String
      sSQL = "Alter Table " & TableName & _
            " Add Constraint  " & IndexName & _
            " Primary Key (" & FieldName & ")"
'Debug.Print sSQL
      ActConn.Execute sSQL
      CreatePrimaryKeyMDB = True
Fehler:
      If Err.Number <> 0 Then
        ErrNumber = Err.Number
        ErrDescription = Err.Description
        If ShowErrorMsg Then
            FehlerAnzeige Err.Number, Err.Description, "CreateIndex"
        End If
      End If
      On Error GoTo 0
End Function

Private Sub FehlerAnzeige(ErrNumber As Long, _
                          ErrDescription As String, _
                          Optional Titel As String = "")
  Dim Msg As String
      Msg = "Fehler " & ErrNumber & vbCrLf & vbCrLf & _
            ErrDescription
      If Len(Titel) > 0 Then
        MsgBox Msg, vbCritical, Titel
      Else
        MsgBox Msg, vbCritical
      End If
      'Sql-String to LogFile
End Sub

Public Function CreateIndex(ActConn As ADODB.Connection, _
                            IndexName As String, _
                            TableName As String, _
                            FieldsAndSort As String, _
                            Optional Flags As SqlIndexOptions, _
                            Optional ErrNumber As Long = 0, _
                            Optional ErrDescription As String = "", _
                            Optional ShowErrorMsg As Boolean = False) _
                            As Boolean
  Dim sSQL As String
  Dim Options As String
      'Flags
      If (Flags And sqlIndexUnique) = sqlIndexUnique Then
        Options = "UNIQUE "
      End If
      If (Flags And sqlIndexClustered) = sqlIndexClustered Then
        Options = Options & "CLUSTERED "
      End If
 
      On Error GoTo Fehler
      sSQL = "Create " & Options & " Index " & IndexName & _
            " On " & TableName & " (" & FieldsAndSort & ")"
'Debug.Print sSQL
      ActConn.Execute sSQL
      CreateIndex = True
Fehler:
      If Err.Number <> 0 Then
        ErrNumber = Err.Number
        ErrDescription = Err.Description
        If ShowErrorMsg Then
            FehlerAnzeige Err.Number, Err.Description, "CreateIndex"
        End If
      End If
      On Error GoTo 0
End Function

Private Function GetSqlFieldType(FieldType As sqlFieldTypes) As String
  Dim s As String
      Select Case FieldType
        Case sqlFieldInt:      s = "Int"
        Case sqlFieldSingle:    s = "Single"
        Case sqlFieldDouble:    s = "Double"
        Case sqlFieldCurrency:  s = "Currency"
        Case sqlFieldDate:      s = "Date"
        Case sqlFieldBoolean:  s = "Logical"
        Case sqlFieldTextFix:  s = "Char"
        Case sqlFieldTextVar:  s = "VarChar"
        Case sqlFieldMemo:      s = "Text"
        Case sqlFieldByte:      s = "Byte"
        Case Else
      End Select
      GetSqlFieldType = s
End Function

---

in the Form

Code:

---

Option Explicit


Dim cSql As clsMDB_Tools

Dim cn As ADODB.Connection
Dim CnX As ADOX.Catalog

Private Sub Command1_Click()

Set cSql = New clsMDB_Tools

Dim strPathToDB As String
  Dim i As Long

      Set cn = New ADODB.Connection
      strPathToDB = "C:\ChrisTest.mdb"

      With cn
        .CursorLocation = adUseClient
        .Mode = adModeShareDenyNone
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = strPathToDB
        .Open
      End With




    Dim TableName As String
    TableName = "MyTable"

With cSql
        .CT_AddFld "MT_ID", sqlFieldInt, , sqlFieldAutoIncrement
        .CT_AddFld "MT_Name", sqlFieldTextVar, 35, sqlFieldNotNull
        .CT_AddFld "MT_Name2", sqlFieldTextFix, 35, sqlFieldNull
        .CT_AddFld "MT_Bezeichnung", sqlFieldTextVar
        .CT_AddFld "MT_Zahl", sqlFieldInt
        .CT_AddFld "MT_Single", sqlFieldSingle
        .CT_AddFld "MT_Double", sqlFieldDouble
        .CT_AddFld "MT_Geld", sqlFieldCurrency
        .CT_AddFld "MT_Datum", sqlFieldDate
        .CT_AddFld "MT_Bool", sqlFieldBoolean
        .CT_AddFld "MT_Memo", sqlFieldMemo
        .CT_CreateTable cn, TableName
        'PrimaryKey:
        .CreatePrimaryKeyMDB cn, TableName, "MT_ID", , , , True
        'Index(es)
        .CreateIndex cn, "MTName", TableName, "MT_Name, MT_Name2"
        .CreateIndex cn, "MTZahl", TableName, "MT_Zahl", _
                    sqlIndexUnique
     
End With

End Sub

Private Sub Form_Load()
Set cSql = New clsMDB_Tools
With cSql
.CreateMDB "C:\ChrisTest.mdb", Access_2000
End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
Set cSql = Nothing
End Sub

---

HTH

Elroy & ChrisE,
thanks for your replies.
I don't want to code it myself, as you have already said it's a ton of work.
But as I can see that most of RAD tools out there have this build in, I don't believe that they have code it from the ground up.
I was asking if someone of you does know of any ActiveX component or utility that I could buy and integrate this functionality into my app.
One example is the ActiveX control that I have mentioned earlier "Active Query Builder for ActiveX" it is an ActiveX control that we can include into our vb6 apps to be able to create queries written or visualy. But it doesn't support the editing of existing and creating of new database objects.
It would be a crazy idea from me if I would try to code it from the ground up. So I was thinking if anyone of you did hear of some commercial ActiveX component or maybe also something for .net that do something like this.
Thanks again guys,
Davor

Quote:

---

Originally Posted by Davor Geci

Elroy & ChrisE,
thanks for your replies.
I don't want to code it myself, as you have already said it's a ton of work.
But as I can see that most of RAD tools out there have this build in, I don't believe that they have code it from the ground up.
I was asking if someone of you does know of any ActiveX component or utility that I could buy and integrate this functionality into my app.
One example is the ActiveX control that I have mentioned earlier "Active Query Builder for ActiveX" it is an ActiveX control that we can include into our vb6 apps to be able to create queries written or visualy. But it doesn't support the editing of existing and creating of new database objects.
It would be a crazy idea from me if I would try to code it from the ground up. So I was thinking if anyone of you did hear of some commercial ActiveX component or maybe also something for .net that do something like this.
Thanks again guys,
Davor

---

Ah i see,
then sorry I missunderstood you.

I don't know of such a Component.

regards
Chris