Creating Access Data Dase in VB 6

[usakjw]

I need to create a prgram that will create a new MDB file. I have created Word and Excel documents but not a Access file. Is their a site that explains how to do this. I usually create Macro to find out how to do things I'm not sure about. However access will not allow me to record on.

I need to know not only how to create the MDB file, but the table, how to add fields and edit them as well. An other useful information would be helpful.

KJW

 

[CassandraR]

Hi KJW:

I suggest looking at thread222-910257.

Cassandra


Cassie
PIII-500Mhz-128MB
Win98SE-VB6SP5

 

[usakjw]

I have tried doing as the above thread suggested. In regards to the Using ADOX I get Invalid Outside Procedure when place at the Modular declarations. And Variable not Definded with in my function.

KJW

 

[gmmastros]

You probably need to add a reference to ADOX.

Click Project -> References

Add "Microsoft ADO Ext. 2.7 for DDL and Security"



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[CassandraR]

Hi KJW:

Here is the code from one of my old projects. Hopefully it helps. Please remember to add the Project References to the ADOX.

Cassie

Option Explicit

'
'   Create the main database using VB code.
'
Public Sub CreateMainDB(strDBName As String, strPassword As String)
    Dim I As Integer, strSQL As String
    '
    '   Ensure that we have a name for the database.  Otherwise, bail out.
    '
    If strDBName = "" Then
        MsgBox "Database name was not passed to CreateMainDB routine.  Aborting.", _
                vbCritical, FORM_CAPTION
        Exit Sub
    End If
    '
    '   Ensure that the file does not already exist.  If it does, ask the user whether to
    '   delete the existing file.  If no, then bail out.
    '
    If Dir(strDBName) <> "" Then
        I = MsgBox("File already exists.  Overwrite?", vbQuestion + vbYesNo, FORM_CAPTION)
        If I <> vbYes Then Exit Sub
        Kill strDBName
    End If
    '
    '   Create the new database.
    '
    strPassword = Left(Trim(strPassword), 12)

    strSQL = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBName & _
        ";Jet OLEDB:Database Password='" & strPassword & "';"
    Set catCat = New ADOX.Catalog
    catCat.Create strSQL
    '
    '   Set up our ADO connection to the database.
    '
    Set cnnMainDB = New ADODB.Connection
    cnnMainDB.ConnectionString = strSQL
    cnnMainDB.Open
    '
    '   Create the Person table within the database.
    '
    strSQL = "CREATE TABLE Person (" & _
        "RecordNumber INTEGER NOT NULL PRIMARY KEY, " & _
        "BirthSurname INTEGER, " & _
        "BirthGivenNames VARCHAR(255), " & _
        "BirthChristenedNames VARCHAR(255), " & _
        "BirthSex INTEGER," & _
        "Surname VARCHAR(255), " & _
        "GivenNames VARCHAR(255), " & _
        "ChristenedNames VARCHAR(255), " & _
        "Sex" & _
        "DateOfBirth" & _
        "DateOfDeath" & _
        "DateOfMarriageStarts VARCHAR(255), " & _
        "DateOfMarriageEnds VARCHAR(255), " & _
        "PlaceOfBirth VARCHAR(255), " & _
        "PlaceOfDeath VARCHAR(255), " & _
        "PlaceOfMarriageStarts VARCHAR(255), " & _
        "Occupation INTEGER, " & _
        "Comments INTEGER, " & _
        "Father INTEGER, " & _
        "Mother INTEGER, " & _
        "Spouse INTEGER, " & _
        "GraphX INTEGER, " & _
        "GraphY INTEGER);"
    cnnMainDB.Execute strSQL
    
    strSQL = "CREATE TABLE Spouses (" & _
        "RecordNumber INTEGER NOT NULL PRIMARY KEY, " & _
        "Spouse1 INTEGER, " & _
        "Spouse2 INTEGER);"
    cnnMainDB.Execute strSQL
        
    strSQL = "CREATE TABLE Names (" & _
        "RecordNumber INTEGER NOT NULL PRIMARY KEY, " & _
        "Name VARCHAR(255));"
    cnnMainDB.Execute strSQL
    
    strSQL = "CREATE TABLE Occupations (" & _
        "RecordNumber INTEGER NOT NULL PRIMARY KEY, " & _
        "Occupation VARCHAR(255));"
    cnnMainDB.Execute strSQL
    
    strSQL = "CREATE TABLE Comments (" & _
        "RecordNumber INTEGER NOT NULL PRIMARY KEY, " & _
        "Comment VARCHAR(255));"
    cnnMainDB.Execute strSQL

    strSQL = "CREATE TABLE Places (" & _
        "RecordNumber INTEGER NOT NULL PRIMARY KEY, " & _
        "Place VARCHAR(255));"
    cnnMainDB.Execute strSQL
    '
    '-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
    '   Tidy up and leave.
    '
    If cnnMainDB.State <> adStateClosed Then cnnMainDB.Close
    Set cnnMainDB = Nothing
    Set catCat = Nothing
    '
    '   Update the form.
    '
    MsgBox "Project (main) database created.", vbInformation, FORM_CAPTION
    lblPTDC(0).Caption = "Main Database exists." & vbCrLf & "(" & strDBName & ")"
    '
End Sub
'
'-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
'

Cassie
PIII-500Mhz-128MB
Win98SE-VB6SP5