Mr. Script

Easy Database Creation

Three scripts that will make creating and populating your databases a breeze.

• By Chris Brooke
• 10/01/2004

Whatever the name, the mission's the same: to bring you a variety of scripts to make your administrative life easier and to answer your scripting questions. The way I see it, the latter should trump the former. If I receive a question from a reader—particularly one that's especially relevant to a recent topic—then I'm honor-bound to put aside any previous ideas and respond to said question. Because, as the saying goes, if one person asks a question, many others are thinking it.

So, rather than discuss Active Directory migration tasks that can benefit from scripting, as planned (I promise we'll return to this subject later), I instead will respond to Kevin Jones, who asks this question about a topic I addressed some months ago in my column on using AcitveX Data Objects (ADO) in a scripting environment:

I was wondering if there's a way to script creation of ODBC data sources on Windows XP workstations. We're installing a new association management package that requires three data sources on each client machine. Even a locally run script that I could run through RDS would save a considerable amount of time.

When I read his question, I experienced one of those forehead slapping "aha!" moments. Of course you want to do that. Moreover, you certainly want to know how to script creation of the databases, too. How could I have left out even a passing reference to such an obvious aspect of ADO scripting? I have no excuse. Please forgive me.

I shall try to redeem myself by demonstrating both, starting at the beginning: Creating a database from scratch. Script 1 creates the database file. In this case, it creates an Access .MDB.

Script 1. Create the database file.

Set objConn=CreateObject("ADOX.Catalog")
objConn.Create _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\MyData.mdb"

Not much to it, is there? You now have an empty .MDB file in the root of C: (or wherever you may wish to place it).

Now, you need to create tables to hold your data. Before you can do that, you must map the datatypes used by Access to the datatype values used by the ADODB engine in our script, shown in Table 1. Now that you know how to set up the various fields, use Script 2 to create the database table.

Script 2: Create the table.

Set objConn=CreateObject("ADODB.Connection")
objConn.Open _
      "Provider=Microsoft.Jet.OLEDB.4.0; " & _
      "Data Source=c:\MyData.MDB"

objConn.Execute "CREATE TABLE MyTable(" & _
      "RecID COUNTER ," & _
      "Computername TEXT(15) ," & _
      "IPAddress TEXT(15) ," & _
      "Created DATETIME ." & _
      "Notes MEMO)"

Again, pretty straightforward. Just make sure you specify text field lengths that are long enough for any eventuality.

Finally, you can use Script 3 to put data into the new database.

Script 3: Populating tables with your data.

Const adOpenStatic=3
Const adLockOptimistic=3
Set objConn=CreateObject("ADODB.Connection")
Set objRS=CreateObject("ADODB.Recordset")
objConn.Open _
      "Provider=Microsoft.Jet.OLEDB.4.0; " & _
      "Data Source=c:\MyData.MDB"
objRS.Open "SELECT * FROM MyTable" , _
objConn, adOpenStatic, adLockOptimistic

objRS.AddNew
      objRS("Computername")="Server1"
      objRS("IPAddress")="192.168.1.10"
objRS("Created")=Now
objRS.Update
objRS.Close
objConn.Close

And so on, and so on. We can do this all day long, creating multiple records inside multiple tables inside multiple databases. Not bad, eh? In fact, we could stop here and build some really powerful scripts that get data from and/or place data into a variety of databases. This is because we can always connect to the data by directly specifying the Jet provider in the connection string.

However, as we discussed in the August issue, connecting via a Data Source Name (DSN) is so much more elegant. Plus, to truly answer the question that prompted this little foray, we need to write a script to create the DSNs. This task is easy, yet fraught with danger.

At its simplest level, a DSN is just a registry entry. Exactly where it is stored in the registry depends upon whether it is a System DSN, User DSN and so on. So, for instance, a User DSN pointing to the database we created above is stored in the registry under HKEY_ CURRENT_USER\Software\ODBC\ODBC.INI\ MyDSN.

When you use the ODBC applet in the control panel to create a new DSN, all it does is create these registry entries. In the course of normal operations, it's probably better that you use the ODBC applet when creating DSNs—you're less likely to make a mistake. However, if, like Kevin, you need to create DSNs via scripting, do so with the same reverence with which you approach any task regarding the registry—with the understanding that you could do irrevocable damage to your computer if you make a mistake. Next month, I'll walk you through a script for safely creating DSNs.