Creating an Access MDB from within VB

[mcsa]

is there any way to create an Access MDB file from Scratch......

i dont have any file structure or anything, that should only be created the first time when i run the app.

is this possible through ADO ?

point 2:

how can i ENTER data into a MDB file , using ADO ??

code would be helpful.....suggestions also welcomed

thanks

[MrGTI]

Yep. I have the code. But by the time i post the solution, 13 other people will have done the same thing.

[peet]

Originally posted by MrGTI
Yep. I have the code. But by the time i post the solution, 13 other people will have done the same thing.

hmmm... seems like u have to post it MrGTI

[MrGTI]

I guess so. Sorry about the delay .... i got busy here at work.

Here is the code. It uses a DAO connection but you shouldn't have too much trouble converting it to ADO. I would, but like i said, .... i got busy here at work.

'Programmed by: Peter Temporale ([email protected])
'               2000.01.25

'Note:  This project required that these items be checked on in the references section (Project, References):
'       *Microsoft DAO 2.5/3.51 Compatibility Library
'       *OLE Automation

'Variable           Type        Description
'=========================================================================
Dim sDatabaseName   As String   'I added this variable to make life easier

Option Explicit

Public Sub CreateNewDatabaseAlready()   'Now makes the database
'All code in this section was what i copied from ERwin. Only made 2 changes - see below

'  Starting Access Basic DAO Session...
Dim ERwinWorkspace As Workspace
Dim ERwinDatabase As Database
Dim ERwinTableDef As TableDef
Dim ERwinQueryDef As QueryDef
Dim ERwinIndex As Index
Dim ERwinField As Field
Dim ERwinRelation As Relation

Set ERwinWorkspace = DBEngine.Workspaces(0)

Set ERwinDatabase = ERwinWorkspace.OpenDatabase(sDatabaseName)

'  CREATE TABLE "Jobs"
Set ERwinTableDef = ERwinDatabase.CreateTableDef("Jobs")

'***Had to change DB_TEXT to dbText
Set ERwinField = ERwinTableDef.CreateField("JobIDnum", dbText, 18)

ERwinField.Required = True
ERwinTableDef.Fields.Append ERwinField
Set ERwinField = ERwinTableDef.CreateField("JobTitle", dbText, 18)
ERwinTableDef.Fields.Append ERwinField
Set ERwinField = ERwinTableDef.CreateField("JobDescription", dbText, 18)
ERwinTableDef.Fields.Append ERwinField
Set ERwinField = ERwinTableDef.CreateField("PayClass", dbText, 18)
ERwinTableDef.Fields.Append ERwinField
ERwinDatabase.TableDefs.Append ERwinTableDef

'  CREATE TABLE "People"
Set ERwinTableDef = ERwinDatabase.CreateTableDef("People")
Set ERwinField = ERwinTableDef.CreateField("studIDnum", dbText, 18)
ERwinField.Required = True
ERwinTableDef.Fields.Append ERwinField
Set ERwinField = ERwinTableDef.CreateField("JobIDnum", dbText, 18)
ERwinField.Required = True
ERwinTableDef.Fields.Append ERwinField
Set ERwinField = ERwinTableDef.CreateField("FirstName", dbText, 18)
ERwinTableDef.Fields.Append ERwinField
Set ERwinField = ERwinTableDef.CreateField("LastName", dbText, 18)
ERwinTableDef.Fields.Append ERwinField
Set ERwinField = ERwinTableDef.CreateField("Address", dbText, 18)
ERwinTableDef.Fields.Append ERwinField
Set ERwinField = ERwinTableDef.CreateField("Postal", dbText, 18)
ERwinTableDef.Fields.Append ERwinField
ERwinDatabase.TableDefs.Append ERwinTableDef

'  CREATE RELATIONSHIP "R/1"
Set ERwinRelation = ERwinDatabase.CreateRelation("R/1", "Jobs", "People")
Set ERwinField = ERwinRelation.CreateField("JobIDnum")
ERwinField.ForeignName = "JobIDnum"
ERwinRelation.Fields.Append ERwinField

'***and had to rem this line out to make it work
'ERwinDatabase.Relations.Append ERwinRelation

'  Terminating Access Basic DAO Session...
ERwinDatabase.Close
ERwinWorkspace.Close

End Sub

Private Sub cmdCreateNew_Click()
'All code in this procedure was missing, but probably contained in internal ERwin variables

'Variable       Type
'===========================
Dim wrkDefault  As Workspace
Dim dbsNew      As Database

'Tells user what is about to happen
    MsgBox "A new database will be created in the directory as listed below:" & vbCrLf & vbCrLf & sDatabaseName, vbInformation, "Create New Database"
'Get default Workspace.
    Set wrkDefault = DBEngine.Workspaces(0)
'Make sure there isn't already a file with the name of the new database
    If Dir(sDatabaseName) <> "" Then Kill sDatabaseName
'Create a new encrypted database with the specified collating order
    Set dbsNew = wrkDefault.CreateDatabase(sDatabaseName, dbLangGeneral, dbEncrypt)
'Now uses the ERwin code section to finish off the database
    Call CreateNewDatabaseAlready
'Beeps when done
    Beep
End Sub

Private Sub cmdExit_Click()
    End
End Sub

Private Sub Form_Load()
'Assigns the name of the database including the base directory
    sDatabaseName = App.Path & "\CreateDatabase.mdb"
End Sub

[peet]

'Add a reference to Microsoft ADO Ext. X.X For DLL And Security
 
Option Explicit
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
 
Private Sub Command1_Click()
    Set cat = New ADOX.Catalog
    Set tbl = New ADOX.Table
 
    ' create the db
    cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\newDB.mdb"
    
    With tbl
      .Name = "TestTable"
      ' Create fields and append them to the
      ' Columns collection of the new Table object.
      With .Columns
         .Append "ID COLUMN"
         .Append "SetName", adVarWChar, 255
         .Append "SetVal", adVarWChar, 255
         .Append "Description", adVarWChar, 255
      End With
   End With
   
   ' Add the new Table to the Tables collection of the database.
   cat.Tables.Append tbl
   
   Set cat = Nothing
   Set tbl = Nothing
    
End Sub

[mcsa]

thanks peet, mrgti,

i shall try out both the suggestions....

is ADOX a part of the standard VB Distrib. ? ours is a college install....and we all know how stingy THEY are!


is there no way to do it with plain-vanilla ADO ?

thanks again

[peet]

don't think u can create the database with plain ADO, but u can create and alter the tables using ADO and SQL,

If u have ADO installed, ADOX is also there.

[MrGTI]

I tried yours peet, and when i try to open the newly created .MDB file in Access i get an error - Unrecognized database format

Any ideas why? I have Access 97, but that shouldn't be a problem.

[peet]

Originally posted by MrGTI
I tried yours peet, and when i try to open the newly created .MDB file in Access i get an error - Unrecognized database format

Any ideas why? I have Access 97, but that shouldn't be a problem.

this line

cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\newDB.mdb"

generates an Access2000 db, not possible to open in Acc97 I think. (Microsoft.Jet.OLEDB.4.0 = acc2k)

not sure if this will work, but try

cat.Create "Provider=Microsoft.Jet.OLEDB.3.5;Data Source=c:\newDB.mdb"

(Microsoft.Jet.OLEDB.3.5 = acc97 well I hope )

[peet]

sorry MrGTI

Provider=Microsoft.Jet.OLEDB.3.51 not 3.5

[MrGTI]

Good news: The ADO database can be opened in Access 2000 (i sent it to my work account and tried it)

Bad news: My work machine doesn't have Microsoft ADO Ext. X.X For DLL And Security on it, so i can run the program.

It's strange because it's a Windows 2000 box, and it has almost everything on it from the factory, so you'd think MS would have included it.

[MrGTI]

Actually my home machine is Win2K as well. Maybe i need to install SS2K on my work machine ...... it's the only thing i have installed at home but not at work.

[peet]

Its got ADO but not ADOX ?

[Andreex]

Peet... I liked your code... but couldnt tried... ADOX? How do I know if I have that? I also have win2k and VB6 Enterprise Edition..... and this pc also had the ADO references... what do I need?

[Andreex]

FOUND IT!!! It is a little bit below the ADO refrences....

This is a good example... THANKS!

[peet]

Originally posted by Andreex
FOUND IT!!! It is a little bit below the ADO refrences....

This is a good example... THANKS!

glad u found it Andreex

[mcsa]

peet. mgti,

thanks a heap for all ur suggestions.

1. found ADOX in the References section - has ADOX 2.1 in mine
2. could NOT test out ur data, coz the Collg Net link was fried and i didnt have hard copy of ur code

i use Access97 for base compatibility.....hope it works, will post as soon as it does, which i hope it will..

thanks again

[MrGTI]

I found the missing referance. It was marked as MISSING: so i unchecked it, saved, and went back in to referances and it was now in the list.

Once i added it, everything worked. I did add in the extra code so i can switch between Access 97 and 2000

'Create the db using code for Access 2000
    adoCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDatabaseNameADO
'Create the db using code for Access 97
    'adoCat.Create "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & sDatabaseNameADO

[peet]

Ok mcsa as promissed, this sample shows how to use ADOX to create the Acc97DB, then using ADO and SQL to create the table.

enjoy

'Add a reference to Microsoft ADO Ext. X.X For DLL And Security
'Add a ref for ADO
Option Explicit
Private cat As ADOX.Catalog
Private cnn As New ADODB.Connection
 
Private Sub Command1_Click()
    Dim SQL As String
    Set cat = New ADOX.Catalog
    ' create the db
    cat.Create "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\newDB.mdb"
    Set cat = Nothing
    
    'create table using SQL instead of table def
    SQL = "CREATE TABLE TestTable (ID COUNTER, SetName TEXT(50), SetVal TEXT(255), Description TEXT(255))"
    
    'open the db and create the table using ADO and SQL
    cnn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\newDB.mdb"
    cnn.Execute SQL
    cnn.Close
End Sub

[vbvbvbvb]

How do I set the DateSource to App path?

This won't work.
cat.Create "Provider=Microsoft.Jet.OLEDB.3.51;Data Source= App.Path & \new.mdb"

Thanks

[peet]

Originally posted by vbvbvbvb
How do I set the DateSource to App path?

This won't work.
cat.Create "Provider=Microsoft.Jet.OLEDB.3.51;Data Source= App.Path & \new.mdb"

Thanks

think it should be like this (if u didn't just do a typo that is )

cat.Create "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & App.Path & "\new.mdb"

[vbvbvbvb]

Thanks peet.

And how do I add more fields into the table?

With new
         .Name = "Table"
         ' Create fields and append them to the
         ' Columns collection of the new Table object.
         With .Columns
            .Append "ID COLUMN"
            .Append "SetName", adVarWChar, 255
            .Append "SetVal", adVarWChar, 255
            .Append "Description", adVarWChar, 255
         End With
End With

Much appreciated!

[peet]

Originally posted by vbvbvbvb
Thanks peet.

And how do I add more fields into the table?

....

what do u mean?

[vbvbvbvb]

Using your code, I can create a new table but it is empty (no fields or columm). I want to create a table with some fileds say FieldA, fieldB....

[peet]

Originally posted by vbvbvbvb
Thanks peet.

And how do I add more fields into the table?

With new
         .Name = "Table"
         ' Create fields and append them to the
         ' Columns collection of the new Table object.
         With .Columns
            .Append "ID COLUMN"
            .Append "SetName", adVarWChar, 255
            .Append "SetVal", adVarWChar, 255
            .Append "Description", adVarWChar, 255
         End With
End With

Much appreciated!

oh I see... been so many posts in this thread

u do as u suggested u'r self abow, or u can use SQL and conn.Execute

'create table using SQL instead of table def
    SQL = "CREATE TABLE TestTable (ID COUNTER, SetName TEXT(50), SetVal TEXT(255), Description TEXT(255))"
    
    'open the db and create the table using ADO and SQL
    cnn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\newDB.mdb"
    cnn.Execute SQL
    cnn.Close

u'r choice

[vbvbvbvb]

Well, when I open the table, nothing in there.

[peet]

hmm... lets rewind... show me the exact code u use please

[vbvbvbvb]

I used your own code:

Option Explicit
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
 
Private Sub Command1_Click()
    Set cat = New ADOX.Catalog
    Set tbl = New ADOX.Table
 
    ' create the db
    cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\newDB.mdb"
    
    With tbl
      .Name = "TestTable"
      ' Create fields and append them to the
      ' Columns collection of the new Table object.
      With .Columns
         .Append "ID COLUMN"
         .Append "SetName", adVarWChar, 255
         .Append "SetVal", adVarWChar, 255
         .Append "Description", adVarWChar, 255
      End With
   End With
   
   ' Add the new Table to the Tables collection of the database.
   cat.Tables.Append tbl
   
   Set cat = Nothing
   Set tbl = Nothing
    
End Sub

Or your code is to create an empty table? If I want my table has these fields: ID, LastName, FirstName, how can I do that?

Many thanks.

[peet]

Option Explicit
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
 
Private Sub Command1_Click()
    Set cat = New ADOX.Catalog
    Set tbl = New ADOX.Table
 
    ' create the db
    cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\newDB.mdb"
    
    With tbl
      .Name = "TestTable"
      ' Create fields and append them to the
      ' Columns collection of the new Table object.
      With .Columns
         .Append "ID"
         .Append "LastName", adVarWChar, 255
         .Append "FirstName", adVarWChar, 255
      End With
   End With
   
   ' Add the new Table to the Tables collection of the database.
   cat.Tables.Append tbl
   
   Set cat = Nothing
   Set tbl = Nothing
    
End Sub

This creates a table called TestTable with the fields ID, LastName and FristName.

I tested it to make sure

[vbvbvbvb]

Thanks for being so patient, peet.

When I click on the newDB.mdb, the "Open" button is not enable for me to open the table.

Oh, and I don't see the table name "TestTable" any where.

I must miss something here.

[peet]

no problem mate

are u telling me u are not able to open the database ?

this is probably a stupid question, but do u have Access installed ?

what version, and what happen if u doubleclick the file ?

I'm a bit confused

[vbvbvbvb]

Ok, finally, your SQL works for me.

'Add a reference to Microsoft ADO Ext. X.X For DLL And Security
'Add a ref for ADO
Option Explicit
Private cat As ADOX.Catalog
Private cnn As New ADODB.Connection
 
Private Sub Command1_Click()
    Dim SQL As String
    Set cat = New ADOX.Catalog
    ' create the db
    cat.Create "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\newDB.mdb"
    Set cat = Nothing
    
    'create table using SQL instead of table def
    SQL = "CREATE TABLE TestTable (ID COUNTER, FirstName TEXT(50), LastName TEXT(255), Description TEXT(255))"
    
    'open the db and create the table using ADO and SQL
    cnn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\newDB.mdb"
    cnn.Execute SQL
    cnn.Close
End Sub

I have another Oracle table open (I can print to debug window to see the fields of this Orc Table)

And I use this to insert export fields from Oracle to Access but it doesn't work. When debugging, I found that cat.Tables(".... <ADO could not find the object....> I still let cnn open

With OracleTable
   .MoveFirst
   Do until .EOF
      cat.Tables("TestTable").Columns("FirstName") = .Fields("KeyType").Value
      .............
      .MoveNext
   Loop

[vbvbvbvb]

I am almost there.... Some one please help !!!

Thanks a bunch.