{"id":54373,"date":"2009-02-17T21:13:00","date_gmt":"2009-02-17T21:13:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2009\/02\/17\/hey-scripting-guy-how-can-i-create-a-database-with-more-than-one-table\/"},"modified":"2009-02-17T21:13:00","modified_gmt":"2009-02-17T21:13:00","slug":"hey-scripting-guy-how-can-i-create-a-database-with-more-than-one-table","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-create-a-database-with-more-than-one-table\/","title":{"rendered":"Hey, Scripting Guy! How Can I Create a Database with More Than One Table?"},"content":{"rendered":"

\"Hey, <\/h2>\n

Hey, Scripting Guy! Being able to create a database from a script is nice, and I appreciated your article yesterday. But I need to have a database with more than one table in it. Obviously this can be done, but I am at a loss as to how to go about it. I am not a real programmer; do you have any tips for me?.<\/p>\n

– GF<\/p>\n

\"Spacer\"\"Hey,<\/p>\n

Hi GF,<\/p>\n

You need to do what? Sorry not really paying attention. I am watching The Guild<\/i> on MSN<\/a>. Dude, those people need to get a life. Speaking of having a life, back when I was a network administrator, I worked all the time doing repetitive tasks. It was not uncommon for me to put in 90 to 100 hours a week. I am not exaggerating at all. We actually had a thing we called the Centurion Club for people who worked more than 100 hours in a week. I was afraid I was going to become a lifetime member of that thing. One of the things I was always being called upon to do back then was create various databases for users, or to track data for inventory, auditing, and compliance checks. So this script should help you to gain control of both your network and your life.<\/p>\n\n<\/thead>\n\n\n
\n

This week we will be talking about scripting Microsoft Office Access. The Databases hub<\/a> is a great jumping-off point because it highlights the available Office Access scripts we have in the TechNet Script Center. There are also links to the Office Access scripts we have in the Script Center Script Repository<\/a> and on the Community-Submitted Scripts Center<\/a>. When taken as a whole, there are tons of examples for you to review. For information about downloading and installing Windows PowerShell, you can go to the Windows PowerShell Scripting Hub<\/a>.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

The CreateAccessDataBaseAndMultipleTables.ps1<\/b> script is seen here.<\/p>\n

Function Check-Path($Db)\n{\n If(!(Test-Path -path (Split-Path -path $Db -parent)))\n   {\n     Throw \"$(Split-Path -path $Db -parent) Does not Exist\"\n   }\n  ELSE\n  {\n   If(Test-Path -Path $Db)\n     {\n      Throw \"$db already exists\"\n     }\n  }\n} #End Check-Path\nFunction Create-DataBase($Db)\n{\n $application = New-Object -ComObject Access.Application\n $application.NewCurrentDataBase($Db,10)\n $application.CloseCurrentDataBase()\n $application.Quit()\n} #End Create-DataBase\nFunction Create-Table($tables)\n{\n For($i = 0 ;  $i -le $tables.count -1 ; $i ++)\n  {\n   $TableElement = $tables[$i].split(\",\")\n   $tableName = $tableElement[0]\n   $fields = [string]::Join(\",\",$tableElement,1,$tableElement.Count-1)\n   $command = \"Create Table $tableName `($fields`)\"\n   Invoke-ADOCommand -db $Db -command $command\n   $Fields=$null\n  } #End For $i\n} #End Create-Table\nFunction Invoke-ADOCommand($Db, $Command)\n{\n $connection = New-Object -ComObject ADODB.Connection\n $connection.Open(\"Provider= Microsoft.Jet.OLEDB.4.0;Data Source=$Db\" )\n $connection.Execute($command)\n $connection.Close()\n} #End Invoke-ADOCommand\n# *** Entry Point to Script ***\n$Db = \"C:\\FSO\\ComputerData.mdb\"\n$tables = Get-Content c:\\fso\\tables.txt\nCheck-Path -db $db\nCreate-DataBase -db $Db\nCreate-Table -tables $Tables\n<\/pre>\n
One way to create a couple of tables with multiple fields would be to essentially use exactly the same script we wrote yesterday<\/a>.<\/p>\n
We could then add some extra variables, and build the additional tables and fields. As seen here, we create two tables. The first table name is stored in the variable $table<\/b>, and the second one in $table1<\/b>. (Like all good computer things, we are zero based<\/a> and that is why the second table is named $table1<\/b>.) The fields for the first table are stored in $fields<\/b> and the fields for the second table are store in $fields1<\/b>. We then call the Invoke-ADOCommand<\/b> function with each of the different commands. The code is seen here:<\/p>\n
$Db = \"C:\\FSO\\ComputerData.mdb\"\n$table = \"Bios\"\n$table1 = \"Video\"\n$Fields = \"Record Counter, DateRun Date, SMBIOSBIOSVersion Text, Manufacturer Text, SerialNumber Text, Version Text\"\n$Fields1 = \"Record Counter, DateRun Date, AdapterCompatibility Text, AdapterDACType Text, AdapterRAM Text, Description\nText, DriverDate Text, DriverVersion Text\"\n$command = \"Create Table $table `($fields`)\"\n$command1 = \"Create Table $table1 `($fields1`)\"\nCheck-Path -db $db\nCreate-DataBase -db $Db\nInvoke-ADOCommand -db $Db -command $command\nInvoke-ADOCommand -db $db -command $command1\n<\/pre>\n
While the above approach works, and I have seen thousands of scripts written by students and others just learning to program that used this type of approach over the years, the problem is that it does not scale well at all. This is not to say that the approach is wrong; it may be perfectly acceptable for a quick one-off<\/a> script, but it would require four new lines of code for each additional table you wish to create. The four lines that would need to be added are listed here:<\/p>\n
$table = \"Bios\"\n$Fields = \"Record Counter, DateRun Date, SMBIOSBIOSVersion Text, Manufacturer Text, SerialNumber Text, Version Text\"\n$command = \"Create Table $table `($fields`)\"\nInvoke-ADOCommand -db $Db -command $command\n<\/pre>\n
Clearly, we need a better approach. The better approach would use looping technology, which refer to language statements that make the code go in loops. Looping types of statements in Windows PowerShell include: for<\/b>, foreach<\/b>, do<\/b>, do while<\/b>, do until<\/b>, and while<\/b>. There is also the ForEach-Object<\/b> cmdlet that performs looping for us when working within the Windows PowerShell pipeline. For more information about the basic syntax of these types of statements, check out the Windows PowerShell hub<\/a>.<\/p>\n
At first, I thought that I would place the table names and field names in a comma-separated values (CSV) file. This is seen in the image that follows this paragraph. The problem is we are basically back to the issue of hard-coded variables and names. We need to know how many columns we will have in our CSV file as we make our header columns for the file. If we were to limit ourselves to a fixed number of items, we are setting ourselves up for another Y2K disaster<\/a> (unless you happen to be a Y2K remediation specialist, you don’t want to live through that fiasco again).<\/p>\n
\"Image<\/p>\n
 <\/p>\n
The better way to approach this issue is to use an array. The cool thing about an array is we can easily find out how many elements are in the array, and we can easily iterate through the collection of elements by using either the For<\/b> statement or the ForEach<\/b> statement. For more information about this basic technique, see the Microsoft Press book, Microsoft Windows PowerShell Step By Step<\/a>.<\/p>\n
Because of the way we wrote the script yesterday, we only need to add one additional function to allow us to read a text file, convert it into an array by using the split<\/b> function, and then put it back together to create our new ADO command to allow us to create the table. The text file we will use is seen here:<\/p>\n
\"Image<\/p>\n
 <\/p>\n
The first thing we need to do is to create the Create-Table<\/b> function. Because the function will need to receive a text file that contains the table name and the fields, we specify an input parameter for the function. We use the Function<\/b> keyword and follow the verb-noun naming convention that is used with Windows PowerShell cmdlets. After we are in the function, inside the script block (delineated by curly brackets) we begin a for<\/b> loop to work with each line of the text that is in the $tables<\/b> input variable. We will talk about how the $tables<\/b> variable gets populated later. We use the count<\/b> property minus one to tell us how many lines of text is contained in the $tables<\/b> variable. This is because $tables<\/b> is an array of text and it begins counting at zero. The last thing the for<\/b> statement tells us is we will count by one. The ++<\/b> means add one to the value of $i<\/b> each time we progress through the loop. This is all seen here:<\/p>\n
Function Create-Table($tables)\n{\n For($i = 0 ;  $i -le $tables.count -1 ; $i ++)\n<\/pre>\n
Next we take the line of text that is represented by the current place in the text file, and we use the split<\/b> method to break it into an array. So we now have a text file that was broken into an array of lines. Now we are taking each line of the text file, and breaking it into an array that is separated by commas. We store this new array in the $TableElement<\/b> variable:<\/p>\n
$TableElement = $tables[$i].split(\",\")<\/pre>\n
After we have an array from the current line in the text file, we index into the array (that is we choose the item by element number) and select the first element. The first element in each line is the word we intend to use for the table name. We store this value in the variable named $tablename<\/b> to make it easy to remember and to understand:<\/p>\n
$tableName = $tableElement[0]<\/pre>\n
Now we need to put it back together. But we do not need the first element from the array. Because we have already used element 0 for the table name, we do not need a column in our database with the same name. In fact, we want the first column in the database to be a record number that is automatically created, but that is perhaps beside the point here. The cool thing is the static join<\/b> method from the string<\/b> class allows us to choose the starting point and the ending point in the array that we want to put back together. This is very powerful, and had no equivalent functionality in VBScript without writing your own function:<\/p>\n
$fields = [string]::Join(\",\",$tableElement,1,$tableElement.Count-1)<\/pre>\n
Finally we create our ADO<\/b> command and call the Invoke-ADOCommand<\/b> function. We then clear out the value of the $fields <\/b>variable to ensure that it is not polluted the next time through the loop. We then close out all the curly brackets. This is seen here:<\/p>\n
$command = \"Create Table $tableName `($fields`)\"\n   Invoke-ADOCommand -db $Db -command $command\n   $Fields=$null\n  } #End For $i\n} #End Create-Table\n<\/pre>\n
The entry point to the script is where we initialize the variables and call the functions in the appropriate order. The path to the newly created database is specified in the $Db<\/b> variable. The $tables<\/b> variable is populated by using Get-Content<\/b> to read the contents of the text file. We then check the path for the database, create the database, and then create the tables:<\/p>\n
$Db = \"C:\\FSO\\ComputerData.mdb\"\n$tables = Get-Content c:\\fso\\tables.txt\nCheck-Path -db $db\nCreate-DataBase -db $Db\nCreate-Table -tables $Tables\n<\/pre>\n
The newly created database is seen here:<\/p>\n
\"Image<\/p>\n
And so, GF, we come to the end of another \u201cHey, Scripting Guy!\u201d article. Luckily, MSN allows you to pause things, so I did not miss much of The Guild<\/i>. Join us again tomorrow as Office Access Database Week continues. <\/p>\n
Ed Wilson and Craig Liebendorfer, Scripting Guys<\/b><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! Being able to create a database from a script is nice, and I appreciated your article yesterday. But I need to have a database with more than one table in it. Obviously this can be done, but I am at a loss as to how to go about it. I am not […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[146,54,49,3,45],"class_list":["post-54373","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-databases","tag-microsoft-access","tag-office","tag-scripting-guy","tag-windows-powershell"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! Being able to create a database from a script is nice, and I appreciated your article yesterday. But I need to have a database with more than one table in it. Obviously this can be done, but I am at a loss as to how to go about it. I am not […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54373","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=54373"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54373\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=54373"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=54373"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=54373"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}