Mr. Script

Easy Database Creation

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

Greetings, friends; Mr. Script at your service! Like the magazine, this column has a new name. We tossed around "Dr. Script" and I really, really wanted to be "Captain Script," but in the end we settled on "Mr. Script."

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).

Table 1: Mapping Access datatypes to
datatype-similar ADO values.

Access Datatype ADODB Value
Autonumber COUNTER
Text TEXT(x)
Numeric Long
Integer (decimals
not allowed)
Numeric Double
(decimals allowed)

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


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.

About the Author

Chris Brooke, MCSE, is a contributing editor for Redmond magazine and director of enterprise technology for ComponentSource. He specializes in development, integration services and network/Internet administration. Send questions or your favorite scripts to [email protected].


  • Microsoft Warns IT Pros on Windows Netlogon Fix Coming Next Month

    Microsoft on Thursday issued a reminder to organizations to ensure that their systems are properly patched for a "Critical"-rated Windows Netlogon vulnerability before next month's "update Tuesday" patch distribution arrives.

  • Microsoft Nudging Skype for Business Users to Teams

    Microsoft on Thursday announced some perks and prods for Skype for Business unified communications users, with the aim of moving them to the Microsoft Teams collaboration service instead.

  • How To Improve Windows 10's Sound and Video Quality

    Windows 10 comes with built-in tools that can help users get the most out of their sound and video hardware.

  • Microsoft Offers More 'Solorigate' Advice Using Microsoft 365 Defender Tools

    Microsoft issued yet another article with advice on how to use its Microsoft 365 Defender suite of tools to protect against "Solorigate" advanced persistent threat types of attacks in a Thursday announcement.

comments powered by Disqus