[RESOLVED] Creating Access database, and tables at runtime

[I_Love_My_Vans]

am making an app which uses a access database, but if the current database becomes corrupt or unreadable, because the user messing with the format of the database...

How would i make a Database in VB, with tables and relationships.

Or is that going to be difficult?

Any help appreciated
ilmVBBBBBBBBB

'

Post No. 300

[Hack]

In SQL there is CREATE DATABASE...however, to run that from VB, you would need to be connected to a database, so that isn't going to buy you anything.

I don't think you can create the database, but you certainly could create the tables. If you are worried about database corruption, then maybe you should create and empty database, and ship it with your program.

You can also create indexs and primary keys.

The only thing I don't think you would be able to do is create the database itself.

[DaveBo]

Go to
http://www.4guysfromrolla.com/webtech/013101-1.shtml
Works like a charm.
Cut-n-paste the examples there.
You need to add the reference to "Microsoft ADO ext. ..."
and remove the "Server." from the CreateObject (that's for ASP)

I don't know about the relationships though.

have fun, DaveBo

[DaveBo]

You could also just keep a "golden" copy somewhere, and when the live copy gets corrupted just copy the golden one over it.

[Hack]

Ahhhhhhhhhh...you use the Catelog object to create the database, and then just standard SQL to create the tables and whatnot.

Very Cool.

[anotherVBnewbie]

You can also use ".tables.append" to create tables on the fly..Here's an example of something I'm using.

Option Compare Text
Private catMake As ADOX.Catalog
Private tblMake As ADOX.Table
 
Public Function createDb(Optional Jetv3xNot4 As Boolean = True) As Boolean
    On Error GoTo Err_Hand
 
    Dim sPath As String
    createDb = False
    sPath = "path\dbName.mdb"
    
    dbConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sPath
    
    
    If Jetv3xNot4 Then
        dbConnStr = dbConnStr & ";Jet OLEDB:Engine Type=4"
    End If
 
    Set catMake = New ADOX.Catalog
    catMake.Create dbConnStr
 
    If MadetblList = False Then
        GoTo CleanUp
    End If
 
    createDb = True
 
 
CleanUp:
   Set catMake = Nothing
   
   Exit Function
 
Err_Hand:
MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf & "Source: MadeNewADOJetDB"
Err.Clear
GoTo CleanUp
 
End Function
 
Private Function MadetblList() As Boolean
    On Error GoTo Err_Hand
 
    Dim colMake As Column
    Dim idxMake As Index
    Set tblMake = New Table
 
    With tblMake
        .name = "tblList"
        .ParentCatalog = catMake
 
        Set colMake = New Column
        colMake.name = "Email"
        colMake.Type = adVarWChar
        .Columns.Append colMake
        colMake.DefinedSize = 255
        colMake.Properties("Autoincrement") = False
        colMake.Properties("Nullable") = True
        colMake.Properties("Fixed Length") = False
        colMake.Properties("Jet OLEDB:IISAM Not Last Column") = False
        colMake.Properties("Jet OLEDB:AutoGenerate") = False
        colMake.Properties("Jet OLEDB:One BLOB per Page") = False
        colMake.Properties("Jet OLEDB:Compressed UNICODE Strings") = True
        colMake.Properties("Jet OLEDB:Allow Zero Length") = True
        colMake.Properties("Jet OLEDB:Hyperlink") = False
        
        Set colMake = New Column
        colMake.name = "allowDeny"
        colMake.Type = adVarWChar
        .Columns.Append colMake
        colMake.DefinedSize = 25
        colMake.Properties("Autoincrement") = False
        colMake.Properties("Nullable") = False
        colMake.Properties("Fixed Length") = False
        colMake.Properties("Jet OLEDB:IISAM Not Last Column") = False
        colMake.Properties("Jet OLEDB:AutoGenerate") = False
        colMake.Properties("Jet OLEDB:One BLOB per Page") = False
        colMake.Properties("Jet OLEDB:Compressed UNICODE Strings") = True
        colMake.Properties("Jet OLEDB:Allow Zero Length") = True
        colMake.Properties("Jet OLEDB:Hyperlink") = False
        
        Set colMake = New Column
        colMake.name = "ID"
        colMake.Type = adInteger
        .Columns.Append colMake
        colMake.DefinedSize = 10
        colMake.Precision = 10
        colMake.Properties("Autoincrement") = True
        colMake.Properties("Nullable") = True
        colMake.Properties("Fixed Length") = True
        colMake.Properties("Jet OLEDB:IISAM Not Last Column") = False
        colMake.Properties("Jet OLEDB:AutoGenerate") = False
        colMake.Properties("Jet OLEDB:One BLOB per Page") = False
        colMake.Properties("Jet OLEDB:Compressed UNICODE Strings") = False
        colMake.Properties("Jet OLEDB:Allow Zero Length") = False
        colMake.Properties("Jet OLEDB:Hyperlink") = False
        
        Set idxMake = New Index
        idxMake.name = "PrimaryKey"
        idxMake.Clustered = False
        idxMake.IndexNulls = adIndexNullsDisallow
        idxMake.PrimaryKey = True
        idxMake.Unique = True
        Set colMake = New Column
        colMake.name = "ID"
        colMake.SortOrder = adSortAscending
        idxMake.Columns.Append colMake
        tblMake.Indexes.Append idxMake
        
        Set idxMake = New Index
        idxMake.name = "UniqueStr"
        idxMake.Clustered = False
        idxMake.IndexNulls = adIndexNullsDisallow
        idxMake.PrimaryKey = False
        idxMake.Unique = True
        Set colMake = New Column
        colMake.name = "Email"
        colMake.SortOrder = adSortAscending
        idxMake.Columns.Append colMake
        tblMake.Indexes.Append idxMake
    End With
 
    catMake.Tables.Append tblMake
 
    Set tblMake = Nothing
    Set idxMake = Nothing
    Set colMake = Nothing
 
    MadetblList = True
 
    Exit Function
 
Err_Hand:
    Set tblMake = Nothing
    Set idxMake = Nothing
    Set colMake = Nothing
 
    MsgBox Err.Number & vbCr & Err.Description & vbCr & "MadetblList"
    Err.Clear
End Function

[I_Love_My_Vans]

Cheers guys, sounds good. The golden idea sounds good. I am making my app at the moment, so will have to explore routes such as preventing rather then recovering.

cheers guys,

[anotherVBnewbie]

YOu might not still be monitoring this thread but...Jet4 has a "repair" option..I've never used it so I can't say how effective it is but it might be worth trying before you replace a current db with an older version...

[dee-u]

am making an app which uses a access database, but if the current database becomes corrupt or unreadable, because the user messing with the format of the database...

How would i make a Database in VB, with tables and relationships.

Or is that going to be difficult?

Any help appreciated
ilmVBBBBBBBBB

'

Post No. 300

Are you using VB6.0 as your Front-End? How come the user could mess the format of your database? And what's the sense of recreating the database if the data in it is lost? You could just have a back-up copy of your database without any records if you want...