Creating/Deleting An Access Database At Run Time 1

NeilFrank · Jul 9, 2000

Although I know how to: (a) at design time, write code so that a Table is added and/or deleted to a pre-existing MS Access Database at run time;  and (b) at design time, create an MS Access Database (using VisData) I'd like to find out how to: (c) write code so that an MS Access Database, ie not simply a Table, is added and/or deleted at run time. Any hints/leads ?? TIA /Frank

willcas · Jul 10, 2000

hi Frank all you need to do is to create the database (I'll use DAO as its Access)  object and  use a normal SQL script to create the table and then using the tabledef object set the various field properties such as AllowZeroLength and DefaultValue Dim oMacroDatabase  as Database Dim  oTableDef as tabledef Dim  sPath as String 'sPath is the path and Filename of your database  Set oMacroDatabase = DBEngine.Workspaces(0).CreateDatabase(sPath, dbLangGeneral)      'Create the table fields, Constraints,Index's,Primary Key     sSQL = " CREATE TABLE ClinicalTrial ( ClinicalTrialId INTEGER ,"     sSQL = sSQL & " ClinicalTrialName  TEXT(15),"     sSQL = sSQL & " ClinicalTrialDescription  TEXT (255),"     sSQL = sSQL & " PhaseId SMALLINT , StatusId SMALLINT ,"     sSQL = sSQL & " Keywords TEXT (255), ExpectedRecruitment INTEGER ,"     sSQL = sSQL & " ActualRecruitment INTEGER ,"     sSQL = sSQL & " TrialTypeId  SMALLINT ,"     sSQL = sSQL & " CONSTRAINT ClinicalTrialName UNIQUE (ClinicalTrialName),"     sSQL = sSQL & " CONSTRAINT ClinTrial_PrimaryKey PRIMARY KEY"     sSQL = sSQL & " (ClinicalTrialID))"     oMacroDatabase.Execute sSQL, dbFailOnError     sSQL = " CREATE UNIQUE INDEX idx_ClinicalTrialId "     sSQL = sSQL & "ON ClinicalTrial ( ClinicalTrialId )"     oMacroDatabase.Execute sSQL, dbFailOnError          sSQL = " CREATE UNIQUE INDEX idx_ClinicalTrialName "     sSQL = sSQL & "ON ClinicalTrial ( ClinicalTrialName )"     oMacroDatabase.Execute sSQL, dbFailOnError         sSQL = " CREATE INDEX idx_PhaseId "     sSQL = sSQL & "ON ClinicalTrial ( PhaseId )"     oMacroDatabase.Execute sSQL, dbFailOnError     sSQL = " CREATE INDEX idx_StatusId "     sSQL = sSQL & "ON ClinicalTrial ( StatusId )"     oMacroDatabase.Execute sSQL, dbFailOnError      '------------------------------------------------------------------------------------- 'To access the field properties refresh the database so that tabledefs collection ' recognises the ClinicalTrial tabel as a valid table '------------------------------------------------------------------------------------- oMacroDatabase.TableDefs.Refresh    Set oTableDef = oMacroDatabase.TableDefs("ClinicalTrial&quot

With oTableDef           .Fields("ClinicalTrialId&quot

.DefaultValue = 0           .Fields("ClinicalTrialId&quot

.Required = False           .Fields("ClinicalTrialName&quot

.AllowZeroLength = False           .Fields("ClinicalTrialDescription&quot

.AllowZeroLength = True           .Fields("ClinicalTrialDescription&quot

.Required = False           .Fields("PhaseId&quot

.DefaultValue = 0           .Fields("PhaseId&quot

.Required = False           .Fields("StatusId&quot

.DefaultValue = 0           .Fields("StatusId&quot

.Required = False           .Fields("Keywords&quot

.AllowZeroLength = True           .Fields("Keywords&quot

.Required = False           .Fields("ExpectedRecruitment&quot

.DefaultValue = 0           .Fields("ExpectedRecruitment&quot

.Required = False           .Fields("ActualRecruitment&quot

.DefaultValue = 0           .Fields("ActualRecruitment&quot

.Required = False           .Fields("TrialTypeId&quot

.DefaultValue = 0           .Fields("TrialTypeId&quot

.Required = False     End With I've just used one of the tables from my database so just change the field names and datatypes as required... To delete a table just use the Drop table statement but this will mean you lose all data in the table as well. Good luck Will

Vince · Jul 10, 2000

You can you the CreateDatabase using either ADO or DAO through code The DAO example is in the help files in Visual Basic This is the source for doing the same thing in ADO Option Explicit 'Dimension the objects Dim cn As ADODB.Connection Dim nCat As ADOX.Catalog Private Sub Form_Load() 'Set the objects Dim ntab As New ADOX.Table    Set nCat = New ADOX.Catalog 'Set the connection object = the return value of the Catalogue Create method      Set cn = nCat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\testcreate.mdb&quot

'Append the columns and properties to the table object             ntab.Name = "test" ntab.Columns.Append "TestID" ntab.Columns.Append "Testing" 'Append the table to the catalogue nCat.Tables.Append (ntab) End Sub

NeilFrank · Jul 10, 2000

Thanks Will and Vince for your speedy and informative responses.  I'm about to try them out.  More to follow... /Frank

listener22 · Jul 10, 2000

Another way is to declare an access object within vb.   (You can do the same thing with an excel object in vb.) Make sure that your project references include access. Then you can open the access object and use its methods (vb will list them for you) to create, populate, and delete the database.

NeilFrank · Jul 10, 2000

Could you be a little more specific, Listener22, or suggest a source that discusses this technique?

NeilFrank · Jul 11, 2000

Vince, How do I set things up so that I can create a ADOX.Catalog Object?  Nothing intuitive I've tried under Project/References or Project/Components seems to work. In Help, Catalog Objects are discussed under Microsoft Data Access Components 2.5 SDK Beta - ADOX.  Does this not come with the Microsoft VB6 Professional Edition? /Frank

NeilFrank · Jul 11, 2000

A follow-up question for Will: I'm able to create my Database (Chronology) and populate it with a Table, as you described. I can then set up my connection to this Database as folows:     Set mcnChronology = New ADODB.Connection     mcnChronology.ConnectionString = "Provider = " _      & "Microsoft.Jet.OLEDB.3.51; " _      & "Data Source=C:\Frank\PPPWizard\Chronology.mdb"     mcnChronology.CursorLocation = adUseClient However, when I next attempt to open the connection, ie:     mcnChronology.Open I get the following error message: 'could not use "; file is already in use' Any idea as to what's going on? BTW, I'm using Access 2000 in my ap, but notice that the database created by your code is the Access '97 vintage.  In response to this, when I set up the ConnectionString property for mcnChronology, I used 'Provider = Microsoft.Jet.OLEDB.3.51;' instead of 'Provider = Microsoft.Jet.OLEDB.4.0;' but this didn't help. TIA /Frank

listener22 · Jul 11, 2000

Back to the use of an access object, in visual basic 5 help under 'CreateObject Function', there is an example using excel, rather than access.  The important thing is to include Microsoft Access Object Library as a project reference.   The code would start with: Dim myAccess As Access.Application   [or As Object] Set myAccess = CreateObject("Access.Application&quot

Thereafter, methods of the access object such as myAccess.DBEngine.CreateDatabase myAccess.myAccess.CodeDb.CreateTableDef.CreateField can be used, and  .visible can be set to true during development.   It's been awhile, but I have seen this method used to make many features of access available from within vb5.  One disadvantage of the technique is that the version of access you have included in the project references must exist on the target computer.

NeilFrank · Jul 12, 2000

It's Frank, again. I've figured out how to prevent the 'File already in use' error I described yesterday when attempting to open a connection to my newly created 'Chronology' Database. All I needed to do was to close this Database as soon as it was created, ie          mobjTempDatabase.Close I can now connect to this Database     Set mcnChronology = New ADODB.Connection     mcnChronology.ConnectionString = "Provider = " _      & "Microsoft.Jet.OLEDB.3.51; " _      & "Data Source=C:\Frank\PPPWizard\Chronology.mdb"     mcnChronology.CursorLocation = adUseClient     mcnChronology.Open and open my Recordset          Set mrsChronology = New ADODB.Recordset     Call mrsChronology.Open("Chronology", mcnChronology, adOpenStatic, adLockPessimistic, adCmdTable) However, one problem remains to be solved.  I need to delete (not simply close) my Database after I've finished using it.  How do I do this???? TIA /Frank (<A HREF="mailto:nfrank@pol.net">nfrank@pol.net</A>)

Vince · Jul 14, 2000

sorry it took so long to get back The reference to use the catalogue object ADOX is the Microsoft ADO Ext. 2.1 for DDL and Security although why it's called this I don't know hope this is usefull

NeilFrank · Jul 14, 2000

Thanks for the catalogue object reference, Vince.  It did the trick, to an extent. However, permit me 2 additional questions: 1 - Using the precise code you suggested, I can see that a Database is created, and the code to define a Table and add fields seems to fly.   However, when the line      nCat.Tables.Append(ntab) is executed, it causes a run time error: 'the application is using arguments of the wrong type, are out of acceptable range, or in conflict with one another'.  What gives? 2 - In my program, I need to delete the Database after it has served its purpose (namely, printing out the contents of a ListView).  Is there some way to delete the entire Database at runtime? I've hit my creative wall with both of these problems. /Frank

Jonathan · Jul 17, 2000

Surely in order to delete the database you only need to delete the mdb file?  Using the Scripting.FileSystemObject (you need a reference to the Scripting Runtime library to use this code, but you could do it some other way)... Dim fso As Scripting.FileSystemObject Set fso = CreateObject("Scripting.FileSystemObject&quot

fso.DeleteFile("C:\Frank\PPPWizard\Chronology.mdb", True) Set fso = Nothing Hope this is useful, Jon Jonathan <a href=mailto:j.w.george@virginnet.co.uk>j.w.george@virginnet.co.uk</a> <a href= > </a> Working with: Visual Basic 6, Access 97, Visual Interdev 6, VBScript, Active Server Pages, SQL Server 6.5, Oracle 7

WilMead · Jul 18, 2000

Let's see how far I can stuff my foot... The other way for a deletion is a plain and simple KILL IF ("" <> DIR("C:\Frank\PPPWizard\Chronology.mdb&quot

) THEN    KILL "C:\Frank\PPPWizard\Chronology.mdb" END IF Wil Mead <a href=mailto:wmead@optonline.net>wmead@optonline.net</a> <a href= > </a>

RonRegan · Jul 18, 2000

Here's some code I found in help. Sub CreateDatabaseX()     Dim wrkDefault As Workspace     Dim dbsNew As Database     Dim prpLoop As Property     ' 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("c:\NewDB.mdb&quot

<> "" Then Kill "c:\NewDB.mdb"     ' Create a new encrypted database with the specified     ' collating order.     Set dbsNew = wrkDefault.CreateDatabase("c:\NewDB.mdb", _         dbLangGeneral, dbVersion20)     dbsNew.Close End Sub I tried using "dbVersion30" and "dbVersion40" and when I tried to open the mdb with access, got "incompatible file format". Also, I had to include "Microsoft DAO 3.6 Object Library" as a reference. hth Ron <a href=mailto: > </a> <a href= > </a> Independent Consultant 
Tampa Bay, Florida

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Creating/Deleting An Access Database At Run Time 1

NeilFrank

Programmer

willcas

Programmer

Vince

Programmer

NeilFrank

Programmer

listener22

Programmer

NeilFrank

Programmer

NeilFrank

Programmer

NeilFrank

Programmer

listener22

Programmer

NeilFrank

Programmer

Vince

Programmer

NeilFrank

Programmer

Jonathan

Programmer

WilMead

IS-IT--Management

RonRegan

Programmer

Similar threads

Part and Inventory Search

Sponsor