Creating Databases from Excel

   

In a sense, creating a database from the platform that Excel provides is very simple. With a reference to Data Access Objects (DAO) established in a VBA module (as described in Chapter 8, "Opening Databases"), you just execute the following statement:

 CreateDatabase Name:= "NewDB.mdb", _ Locale:=dbLangGeneral 

That's all there is to it. Executing that statement in VBA creates a new Access database named NewDB.mdb in the default directory. It has no tables or queries or any of the other structures that you normally find in an Access database, but it's there and ready to store them for you.

Of course, that's disingenuous. You need to prepare the ground first. You would want to check that there's not already a file named NewDB.mdb in the default directory, and if there is, you need to decide whether to delete it before creating a new one. You also need to decide what to do if the file exists and another user has it open.

This chapter shows you how to go about doing that. It does so using DAO as the toolkit, rather than ActiveX Data Objects (ADO). ADO wasn't initially designed to provide all the functionality needed to create a new database. An extension of ADO ADOX, short for ADO Extensions supports the creation of new tables and fields in an existing database, and it's discussed later in this chapter.

It's a little unusual to create a new database from Excel, but by no means unheard of. In particular, if you develop Office-based applications for your company or clients, you often have users who want to create their own Excel workbooks to store data. For data backup reasons, as well as for more effective storage and retrieval, these users often want to store data they put in their workbook in a true database.

You can meet their needs by providing an Excel template. The template is a workbook on which new workbooks are based. The template can include VBA code that establishes a new Access database, complete with tables and fields that enable it to shadow data that the user places in the new workbook, as shown in the following case study.



Managing Data with Microsoft Excel
Managing Data with Microsoft Excel
ISBN: 789731002
EAN: N/A
Year: 2003
Pages: 134

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net