General discussion

Locked

Database creation

By mansuk ·
Hi all, I have just started writing a program which involves creating a MS Access database during runtime using ADO. can any body help me

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Database creation

by Peyison In reply to Database creation

You can't create a database using ADO. You can copy a blank database, but if you want to create one you'll have to use DAO. Let me know if you want more info on creating one with DAO.

Collapse -

Database creation

by Peyison In reply to Database creation

Just to clarify answer #3 - it does matter if you're using ADO or DAO. ADO does not have the functions available to create a new database, but DAO does. Access has several DDL (Data Definition Language) commands that can be used to create tables, indexes, etc., e.g. CreateTable and CreateIndex. Access also has a method called CreateDatabase that allows you to create a new database file. Access provides everything you need to create new databases from code. These commands are only available via DAO, not ADO (neither of which are extensions of VBA).

Collapse -

Database creation

by mansuk In reply to Database creation
Collapse -

Database creation

by donq In reply to Database creation

ADO is a sub-set of Visual Basic for applications designed to communicate with any ODBC database product driver installed on your computer. You can create a database using pure Visual Basic, or manage an existing (Microsoft) database using DAO or ADO but each is merely extensions of the full Visual Basic Programming language.

Collapse -

Database creation

by mansuk In reply to Database creation
Collapse -

Database creation

by Bob Sellman In reply to Database creation

As mentioned in the other answers, ADO is a subset of VBA and is not really available to create a database "during runtime".

It sounds to me like you need to answer some basic questions first. I'm going to make some assumptions that, even if notcorrect, will hopefully point you in the right direction.

And it doesn't matter if you are using DAO or ADO.

My basic assumption is that you want to create a framework to permit users to save a variety of data, dependent upon their individual or group needs. There are a number of approaches to take. One way is to build a basic database that lets users set up the labels for each column in the tables. This means that you will also need a table with columns like:

tablename (name of table)

columnname (internal name of column)

ColumnTitle (the description displayed to the user. For example: "Last name")
MaxSize (the maximum number of characters for text columns)

Type (if you want to set up the ability to handle dates, numbers, etc., particularly if you save everything as text and do conversions where necessary for processing)

Decimals (working with Type column for number of places for numbers)

This would entail some kind of administrative front end to enable users to set up these values before actually entering data. You'll also need to build some "generic" type forms and, perhaps, reports so users could use the data.

What I'm proposing can get complex very quickly. The easier way is to determine eachset of users' needs and build an Access database (using ADO or DAO, doesn't matter) tailored around their needs.

Collapse -

Database creation

by mansuk In reply to Database creation

Poster rated this answer

Collapse -

Database creation

by patrick.lewis In reply to Database creation

I have only done this using DAO! Here is a sample of code to get you started, taken directly from the msdn web site. In order to use this code, you will need to add a reference to the Microsoft DAO Object Library. You can do this by selecting the Project pull down, and selecting References.

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("NewDB.mdb") <> "" Then Kill "NewDB.mdb"
' Create a new encrypted database with the specified
' collating order.
Set dbsNew = wrkDefault.CreateDatabase("NewDB.mdb", _
dbLangGeneral, dbEncrypt)
With dbsNew
Debug.Print "Properties of " & .Name
' Enumerate the Properties collection of the new
' Database object.
For Each prpLoop In .Properties If prpLoop <> "" Then Debug.Print " " & _
prpLoop.Name & " = " & prpLoop
Next prpLoop
End With
dbsNew.Close
End Sub

Collapse -

Database creation

by mansuk In reply to Database creation

Poster rated this answer

Collapse -

Database creation

by mansuk In reply to Database creation

This question was closed by the author

Back to Web Development Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums