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


  • Spaceflight Training in the Middle of a Pandemic

    Surprisingly, the worldwide COVID-19 lockdown has hardly slowed down the space training process for Brien. In fact, it has accelerated it.

  • Surface and ARM: Why Microsoft Shouldn't Follow Apple's Lead and Dump Intel

    Microsoft's current Surface flagship, the Surface Pro X, already runs on ARM. But as the ill-fated Surface RT showed, going all-in on ARM never did Microsoft many favors.

  • IT Security Isn't Supposed To Be Easy

    Joey explains why it's worth it to endure a little inconvenience for the long-term benefits of a password manager and multifactor authentication.

  • Microsoft Makes It Easier To Self-Provision PCs via Windows Autopilot When VPNs Are Used

    Microsoft announced this week that the Windows Autopilot service used with Microsoft Intune now supports enrolling devices, even in cases where virtual private networks (VPNs) might get in the way.

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.