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


  • Cloud Services Starting To Overtake On-Prem Database Management Systems

    Database management system (DBMS) growth is happening more on the cloud services side than on the traditional "on-premises" side, according to a report by Gartner Inc.

  • How To Replace an Aging Domain Controller

    If the hardware behind your domain controllers has become outdated, here's a step-by-step guide to performing a hardware refresh.

  • Azure Backup for SQL Server 2008 Available at Preview Stage

    Microsoft added the option of using the Azure Backup service to provide recovery support for SQL Server 2008 and SQL Server 2008 R2 when those workloads are hosted on Azure virtual machines.

  • Microsoft Suggests Disabling Old Protocols with Exchange Server 2019

    Exchange Server 2019 with Cumulative Update 2 (CU2) can help organizations rid themselves of old authentication protocols, which constitute a potential security risk.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.