Database Search

TechHome

• ADO
• Automating Access Tutorial
• Automating Excel Tutorial
• WINBATCH OLE PROGRAMMING - Part 1
• WINBATCH OLE PROGRAMMING - Part 2
• WINBATCH OLE PROGRAMMING - Part 3

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Automating Access Tutorial

 Keywords:  

USING WINBATCH WITH MICROSOFT ACCESS

This is a series of scripts to help introduce Winbatch users to scripting with Microsoft Access. This assumes you've had "beginner's level" exposure to Access, you know what it is and how to use it interactively. Most of the scripts here are designed to show you how to replicate the basic steps you do everyday by hand, with Winbatch and OLE, so you can understand the principles involved and then use scripts you create to automate the tedious tasks involved. It helps to have a basic knowledge of SQL -- Structured Query Language.

We'll need a database to work with, so our first script will create a brand-new database for us. Once created, the script will then build a table, then further scripts will populate it with data and manipulate it (including showing you how to import and export data.)

Note: there are a few UDFs included to assist with some of the more laborious tasks. For the moment don't worry about them, as our purpose is to get you going using Winbatch and MS Access. At the end of this section, I'll discuss the UDFs in some detail. Also, you'll notice a list of GOSUBs commented out before the EXIT statement on the script. We'll be discussing and running each script one at a time, so what you'll do is run a script, comment it out (by placing a semi-colon before it like the others) then "uncommenting" the next script in line. This will allow you to run each script without having to switch back and forth between files. One last thing: this won't be an in-depth study of MS Access and Winbatch, so don't expect to learn everything. Once you understand the principles here, it will be up to you to learn more about the processes involved. The same goes for my programming style, so bear with it, learn the basic techniques and then you're free to do things your own way.

These scripts are written using: Winbatch 2004E and MS Access 2002.

First thing you need to do is look at Access Script 1.0 and change the path to it. I suggest you leave the name the same as I'll be refering to "Winbatch Test.mdb" throughout this document. Place the database in a folder you use for coding.

ACCESS SCRIPT 1.0

The first script creates a brand-new database called WINBATCH TEST.MDB, creates a new table called EMPLOYEES, and creates 16 fields within that table. This will help you when you need to create MS Access objects "on the fly" something that happens from time to time in coding.

The first line of the subroutine checks to see if a database by that name exists first and if it does it deletes it. You'll get an error in MS Access if you try to create something that already exists.

   If FileExist(dbname) Then FileDelete(dbname)

Next, the following code creates an instance of the MS Access application, called "Access"

   Access = ObjectOpen("Access.Application")

The Access.NewCurrentDatabase(dbname) creates the new database. We then tell Access to make the window visible so we can see what's going on. Once that's done, we create an object called "db" (short for database) and we tell Access to make it equal to the current database.

   Access.NewCurrentDatabase(dbname)
   Access.Visible = @TRUE
   db = Access.CurrentDb

These steps are the same as opening the MS Access application by hand, and clicking on the FILE menu on NEW, to create a new database. Once you do that, you name it and you're left with a empty database. As with most databases, the first step you'll probably do is create a table to hold data, so we do that with the following statement:

   tdf   = db.CreateTableDef("Employees")

This creates a TABLE DEFINITION within the current database called EMPLOYEES. We reference this OLE object in Winbatch with "tdf" (short for TABLE DEFINITION.)

For the moment, ignore the two commented lines. These are for later, you can uncomment them and look at the data they provide. This will be helpful when you create your own tables with different parameters.

When you create a table in MS Access, each field has it's own TYPE, which deals with the data it contains. You'll see the following statements next:

#DefineFunction CreateAndAppendField(tdf, FieldName, FieldType, FieldLength)
   fld = tdf.CreateField(FieldName, FieldType, FieldLength)
   If FieldType == 10 || FieldType == 12 Then fld.AllowZeroLength = ObjectType("BOOL", -1)
   tdf.Fields.Append(fld)
   fld = 0
   Return
#EndFunction

;   set the constants for the field types...
dbLong = 4
dbText = 10
dbMemo = 12
dbDate = 8

;Call CreateAndAppendField UDF
CreateAndAppendField(tdf, "EmployeeID",      dbLong, 4)

These are simply descriptive variable names to help the coder understand what they're used for. For our table, we'll be creating fields that are LONG (long numbers); TEXT (short text up to 255 characters in length); MEMO (huge text fields) and DATE with which we can store DATE information. Don't forget, that since we're using Winbatch to control MS Access, all the formats will have to reflect what MS Access is familiar with, so Winbatch dates that use the YYYY:MM:DD:HH:MM:SS won't work here.

The CreateAndAppendField() function is simply a shortcut so we don't have to repeat the same steps to create a field over and over, which would make our script larger and be redundant. Each call to the function takes the existing TABLE DEFINITION and appends a new field, which we specified to it.

Now, once we've created all the fields, we need to take this TABLE DEFINITION and append it to the collection of table definitions that our database has. In this case, since we have a blank database, it's the first and only one.

   db.TableDefs.Append(tdf)
;
   ObjectClose(tdf)
   ObjectClose(db)
   Access.quit
;
   ObjectClose(Access)

Once we append it to the database, we can get rid of the objects. We do this by using the ObjectClose() function. This doesn't delete the objects, it simply closes the ones we had open, so our database is still in one piece. Then we need to .quit Access, just as if you'd exited from its menu. Once we no longer need the application, we close the Access object and are done.

Now, if you didn't have any errors, you should be able to open your windows explorer, move to the path you specified, and open the WINBATCH TEST.MDB file and inspect it. You should see a databse, containing a single table called EMPLOYEES with no records.

ACCESS SCRIPT 1.1

Now we need some data. I've exported some of the data from the Northwind Database (NWIND.MDB) that Microsoft provides its customers. Please cut & paste the following into a file, which you'll save as: "C:\NorthwindEmployees.txt" It's small and you can delete it later.

"EmployeeID","LastName","FirstName","Title","TitleOfCourtesy","BirthDate","HireDate","Address","City","Region","PostalCode","Country","HomePhone","Extension","Notes","ReportsTo"
1,"Davolio","Nancy","Sales Representative","Ms.",12/8/1948 0:00:00,5/1/1992 0:00:00,"507 - 20th Ave. E. Apt. 2A","Seattle","WA","98122","USA","(206) 555-9857","5467","Education includes a BA in psychology from Colorado State University in 1970.  She also completed ""The Art of the Cold Call.""  Nancy is a member of Toastmasters International.",2
2,"Fuller","Andrew","Vice President, Sales","Dr.",2/19/1952 0:00:00,8/14/1992 0:00:00,"908 W. Capital Way","Tacoma","WA","98401","USA","(206) 555-9482","3457","Andrew received his BTS commercial in 1974 and a Ph.D. in international marketing from the University of Dallas in 1981.  He is fluent in French and Italian and reads German.  He joined the company as a sales representative, was promoted to sales manager in January 1992 and to vice president of sales in March 1993.  Andrew is a member of the Sales Management Roundtable, the Seattle Chamber of Commerce, and the Pacific Rim Importers Association.",2
3,"Leverling","Janet","Sales Representative","Ms.",8/30/1963 0:00:00,4/1/1992 0:00:00,"722 Moss Bay Blvd.","Kirkland","WA","98033","USA","(206) 555-3412","3355","Janet has a BS degree in chemistry from Boston College (1984).  She has also completed a certificate program in food retailing management.  Janet was hired as a sales associate in 1991 and promoted to sales representative in February 1992.",2
4,"Peacock","Margaret","Sales Representative","Mrs.",9/19/1937 0:00:00,5/3/1993 0:00:00,"4110 Old Redmond Rd.","Redmond","WA","98052","USA","(206) 555-8122","5176","Margaret holds a BA in English literature from Concordia College (1958) and an MA from the American Institute of Culinary Arts (1966).  She was assigned to the London office temporarily from July through November 1992.",2
5,"Buchanan","Steven","Sales Manager","Mr.",3/4/1955 0:00:00,10/17/1993 0:00:00,"14 Garrett Hill","London","","SW1 8JR","UK","(71) 555-4848","3453","Steven Buchanan graduated from St. Andrews University, Scotland, with a BSC degree in 1976.  Upon joining the company as a sales representative in 1992, he spent 6 months in an orientation program at the Seattle office and then returned to his permanent post in London.  He was promoted to sales manager in March 1993.  Mr. Buchanan has completed the courses ""Successful Telemarketing"" and ""International Sales Management.""  He is fluent in French.",2
6,"Suyama","Michael","Sales Representative","Mr.",7/2/1963 0:00:00,10/17/1993 0:00:00,"Coventry House Miner Rd.","London","","EC2 7JR","UK","(71) 555-7773","428","Michael is a graduate of Sussex University (MA, economics, 1983) and the University of California at Los Angeles (MBA, marketing, 1986).  He has also taken the courses ""Multi-Cultural Selling"" and ""Time Management for the Sales Professional.""  He is fluent in Japanese and can read and write French, Portuguese, and Spanish.",5
7,"King","Robert","Sales Representative","Mr.",5/29/1960 0:00:00,1/2/1994 0:00:00,"Edgeham Hollow Winchester Way","London","","RG1 9SP","UK","(71) 555-5598","465","Robert King served in the Peace Corps and traveled extensively before completing his degree in English at the University of Michigan in 1992, the year he joined the company.  After completing a course entitled ""Selling in Europe,"" he was transferred to the London office in March 1993.",5
8,"Callahan","Laura","Inside Sales Coordinator","Ms.",1/9/1958 0:00:00,3/5/1994 0:00:00,"4726 - 11th Ave. N.E.","Seattle","WA","98105","USA","(206) 555-1189","2344","Laura received a BA in psychology from the University of Washington.  She has also completed a course in business French.  She reads and writes French.",2
9,"Dodsworth","Anne","Sales Representative","Ms.",1/27/1966 0:00:00,11/15/1994 0:00:00,"7 Houndstooth Rd.","London","","WG2 7LT","UK","(71) 555-4444","452","Anne has a BA degree in English from St. Lawrence College.  She is fluent in French and German.",5

Once you've created the data file, the next script will take the data and "import" it into our new MS Access table.

First, make sure you comment out the previous GOSUB and uncomment the line: gosub InputAccessData, once that's done we're ready to proceed. The first few statements are self explanatory. They declare the datafile, get the data, place it inside the variable called EmployeeData and we then remove the CRLFs that separate each line and replace them with single LF characters, so we can use the Winbatch list functions.

   datafile = "C:\NorthwindEmployees.txt"
   EmployeeData = FileGet(datafile)
   EmployeeData = StrReplace(EmployeeData, @CRLF, @LF)
   ;

The next group of code looks familiar to the first script, except that since we already have a database made, we don't need to create a new one but just use the existing one. So using the command .OpenCurrentDatabase(dbname) tells MS Access that it doesn't need to make one, but instead open one that already exists. Once it's opened, we make it visible and get a reference to the current database.

   Access = ObjectOpen("Access.Application")
   Access.OpenCurrentDatabase(dbname)
   Access.Visible = @TRUE
   db = Access.CurrentDb

Here's something new:

   db.execute("delete * from Employees")

This command, uses the database's EXECUTE method, to execute a SQL command. If you're familiar with SQL, you'll understand this command tells the database to delete all records from the EMPLOYEES table. We do this so we don't have any duplication of data. Since our table is already empty it doesn't mean much, but later, if you run this script again, it won't duplicate any of the data.

Next we open a recordset:

   rs = db.OpenRecordset("Employees")

This simply tells the database to open the Employees table, and give us all the fields and open it in whatever order it's already in. This is where it's nice to know SQL, because you can issue commands to open a recordset pre-sorted, or to open it with only certain records and certain fields.

Now, since we want to input all the data, we're going to loop through it line by line, extract the current line and insert it into the table. Since the data contains the "header information" (field names), we don't need that, so we'll start with record 2 and work on from there.

For x = 2 To ItemCount(EmployeeData, @LF)
;
   ThisLine  = ItemExtract(x, EmployeeData, @LF)

You'll notice the command:

   rs.addnew

This tells MS Access to add a new record to the end of the recordset (a blank one.) We then instruct MS Access to populate the record with data and instruct which field gets what data.

We use the ItemExtractCSV() function since it's handy for extracting data that's inside quotes, like most CSV data is. So we tell Access to place each item of data, by position into its corresponding field.

   rs.fields("EmployeeID").value      = ItemExtractCSV(1,  ThisLine, 0, ",")
   rs.fields("LastName").value        = ItemExtractCSV(2,  ThisLine, 0, ",")
   rs.fields("FirstName").value       = ItemExtractCSV(3,  ThisLine, 0, ",")
   rs.fields("Title").value           = ItemExtractCSV(4,  ThisLine, 0, ",")
   rs.fields("TitleOfCourtesy").value = ItemExtractCSV(5,  ThisLine, 0, ",")
   rs.fields("BirthDate").value       = ItemExtractCSV(6,  ThisLine, 0, ",")
   rs.fields("HireDate").value        = ItemExtractCSV(7,  ThisLine, 0, ",")
   rs.fields("Address").value         = ItemExtractCSV(8,  ThisLine, 0, ",")
   rs.fields("City").value            = ItemExtractCSV(9,  ThisLine, 0, ",")
   rs.fields("Region").value          = ItemExtractCSV(10, ThisLine, 0, ",")
   rs.fields("PostalCode").value      = ItemExtractCSV(11, ThisLine, 0, ",")
   rs.fields("Country").value         = ItemExtractCSV(12, ThisLine, 0, ",")
   rs.fields("HomePhone").value       = ItemExtractCSV(13, ThisLine, 0, ",")
   rs.fields("Extension").value       = ItemExtractCSV(14, ThisLine, 0, ",")
   rs.fields("Notes").value           = ItemExtractCSV(15, ThisLine, 0, ",")
   rs.fields("ReportsTo").value       = ItemExtractCSV(16, ThisLine, 0, ",")

Once a record is populated, we need to save it, so we issue the .update command. This saves the data, and updates the recordset to include the new data.

rs.update

Now, after you run the script, you should be able to use the windows explorer, open the database by hand and see the new data, inside the table.

ACCESS SCRIPT 1.2

Next, you may need to examine data via a script. The idea is to be able to open MS Access, open the database where your data resides and then issue a SQL command to retrieve whatever you need and do some work with it. By now you should be familiar with the first few lines from the script so I won't go over that. Let's look at issuing a more complex SQL statement and how it's done:

   sqlstr = "select [EmployeeID],[LastName],[FirstName] from [Employees]"
   rs = db.OpenRecordSet(sqlstr)

We simply assign the SQL statment to a variable. In this case we only want our recordset to contain 3 fields. You'll note that the field names are inside brackets. These are delimiters, similar to using double-quotes to delimit a string. If the field names contain spaces simply place brackets around them, otherwise they're not needed, so using EmployeeID and [EmployeeID] are the same. We simply pass this string to the .OpenRecordSet() method, the db object performs the SQL statement and retrieves our fields and records.

Recordsets are similar to files. They have a beginning (first record) and end point, the last record (called the END OF FILE, which is abbreviated .eof, something you're familiar with if you've used the FILEREAD() function in Winbatch.) The .eof is a boolean value meaning that as long as the EOF hasn't been reached, it's value is zero (0) and when the .eof is reached, it becomes true or equals one (1).

So to loop through the recordset we use:

;
   While !rs.eof
      Message("Debug", StrCat("Employee ID: ", rs.fields("EmployeeID").value, @CRLF, "First Name: ", rs.fields("FirstName").value, @CRLF, "Last Name: ", rs.fields("LastName").value))
      rs.movenext
   EndWhile
   ;
   rs.close

You could also use: While rs.eof == 0, whichever you prefer. Anyway, our script moves through the recordset displaying the value of each field we specified, then moving to the next record via the .movenext command. Once the .eof is reached, the loop ends and the recordset is closed.

It's good practice to close up OLE objects you're no longer using, so:

      ObjectClose(rs)
      ObjectClose(db)
      ObjectClose(Access)
   Return

are issued before the script ends.

ACCESS SCRIPT 1.3

Naturally, when you have a database, the data tends to fluctuate and new data is added. Here's how to add a single new record to existing data. It's very similar to ACCESS SCRIPT 1.1, but it differs slightly. Instead of just opening the table and having to append data for 16 fields, we'll just grab 3 fields and add those.

   sqlstr = "select [EmployeeID],[LastName],[FirstName] from [Employees]"
   rs = db.OpenRecordSet(sqlstr)
   ;
   mydata = "00023456,Jones,Fred"
   ;
   rs.addnew
   ;
   rs.fields("FirstName").value   = ItemExtractCSV(3, mydata, 0, ",")
   rs.fields("EmployeeID").value  = ItemExtractCSV(1, mydata, 0, ",")
   rs.fields("LastName").value    = ItemExtractCSV(2, mydata, 0, ",")
   ;
   rs.update
   ;
   rs.close

Once you run it, you can open the database by hand a view the new record. You'll notice only the fields we specified have data in them and the others are blank. This may come in handy if you've got a huge table (with dozens of fields) but only need to input data into a few of them.

ACCESS SCRIPT 1.4

Of course you may need to remove data. This script grabs the same data, but then moves through the recordset and compares the values of last names. Once it finds a match it deletes the record.

   While !rs.eof
      If rs.fields("LastName").value == "Jones"
         rs.edit
         rs.delete
      EndIf
      rs.movenext
   EndWhile
   ;
   rs.close

You'll notice the command: rs.edit

This places the recordset into edit mode, which means it can be changed. In this case, we simply delete the record that matches our test.

Note: There are more efficient methods for finding a single record, or for deleting records. Make sure you do your homework to optimize your script's performance.

ACCESS SCRIPT 1.5

Before running this script, go back, and re-run script 1.3 and put Fred Jones back into the table.

This is very similar to the last script, but instead, once it finds the record we need, it changes the value of the data.

   While !rs.eof
      If rs.fields("LastName").value == "Jones" && rs.fields("FirstName").value == "Fred"
         rs.edit
         rs.fields("FirstName").value = "Freddie"
         rs.update
      EndIf
      rs.movenext
   EndWhile
   ;
   rs.close

Once we put the recordset in the edit mode, all the data can be changed. If you wanted to add/change other data it's all there. We issue the .update command to tell the recordset to save the data.

ACCESS SCRIPT 1.6

First before running this script, go back, and re-run script 1.4 and delete Freddie Jones from the table.

Okay, I mentioned there being more efficient methods for doing different operations. This is one of them, and it utilizes the .execute method. You don't have to open a recordset and write out all the fields. You can insert the data via a single statement.

   sqlstr = "insert into [Employees] ([EmployeeID],[LastName],[FirstName]) values (23456, 'Jones', 'Fred')"
   db.Execute(sqlstr)

It simply inserts the values we specified into the corresponding fields in the same order. If you had a whole batch of data (say 10,000 or 100,000 or 2,000,000 records) you could build a UDF to call as you loop through the data. You could pass it data, and have the UDF build the sqlstr and perform the db.Execute(). In some situations it could be a nice timesaver.

ACCESS SCRIPT 1.7

In a similar vein, you can issue a SQL command to change data in a table.

   sqlstr = "update [Employees] set [FirstName] = 'Freddie' where [LastName]= 'Jones'"
   db.Execute(sqlstr)

You have to be careful, whenever you issue SQL commands. The above command will change anybody's first name to Freddie, who has the last name of Jones; so if the table contained Carmen Jones, Frank Jones and Reg Jones, they would all have their first names changed. This can be useful though, especially if you have hundreds or thousands of records you need to change wholesale. It's much faster than opening a recordset and looping through each record, changing and updating them one by one. Just be careful.

ACCESS SCRIPT 1.8

Naturally when you have a database, at some point in time you may need to export it, whether it's to another program like Excel to do complex mathematics formulas on it, or maybe to another fellow employee that needs to examine your data.

MS Access has a method called the DO COMMAND, which looks like .DoCmd in coding. This versatile command can open Forms, Queries, run macros and do other things. Since I knew VBA I didn't use .DoCmd a whole lot, if there were complex tasks to perform, I simply used Access Basic or Winbatch to do most of the things we've done in the other scripts. One thing that .DoCmd is very useful for is for Exporting and Importing data wholesale.

You can open a recordset, and loop through the data one record at a time, but in most cases, especially if you have thousands of records you'll want MS Access to do most of the work. It's usually faster. In this case we'll use .DoCmd.TransferText to export data.

This script will look familiar. We'll specify an outfile file as seen below, delete it if it already exists and overwrite it with a new version.

   outputfile = "c:\NorthwindEmployees.txt"
   If FileExist(outputfile) Then FileDelete(outputfile)
;

You'll notice some constant variables we've prepared, just like when we were building our table.

   acExportDelim = 2

The acExportDelim = 2 means that we want to use MS Access's internal specification for CSV files. These are similar to the one we created by hand. The next parameter is empty, that's why we use the "". The third parameter is "Employees", which means we want to export the Employees table. The outputfile is straightforward, and the last, the @TRUE tells Access to export the field names. This is handy if someone, not familiar with your table needs to look at the data.

   Access.DoCmd.TransferText(acExportDelim, "","Employees", outputfile, @TRUE)

Once the command is issued the export happens pretty fast. This is a much better way of exporting lots of data than looping through a recordset. The only drawback of this method, is that it exports ALL the records. If you need to export only a portion of the table, make sure you examine the MS Access help file under .TransferText and see how to export a query.

ACCESS SCRIPT 1.9

Scripting MS Access is a nice alternative to doing things by hand. Once you have your scripts debugged and running correctly, you may need to simply schedule them using the Windows Scheduler. The only problem with our scripts was that the MS Access window shows onscreen. I'm using MS Office 2002 and even when I specify that .visible = @FALSE it still displays the window on screen. What if you're working on something else when your script kicks off? It can be distracting. What if you're running scripts on someone's machine, but you don't want them knowing how you're performing your duties. Script 1.9 can help you with that. It uses DAO -- Data Access Objects which is intregal to MS Access. However, since you're not opening the "Access.Application" there's no database window to open and show, which means you scripts can execute silently and quietly without disturbing you or other users.

It works very similar to "Access.Application" but slightly different. I have the latest version of DAO, version 36. You can look in your PC's registry to see which version you have. If you have Office 97, you should have DAO.DBEngine.35, so you won't have to change your scripts too much.

   dao = ObjectOpen("DAO.DBEngine.36")
   ws = dao.CreateWorkspace("JetWorkspace", "admin", "")
   db = ws.OpenDatabase(dbname)

You simply open a DBEngine object, create a workspace within it. Access uses Microsoft Jet, so we specify "JetWorkspace" there. Next is "admin" which is the default username for any MS Access database, and the "" is the password. If your database uses usernames and passwords then you can change these to match. Once you've created the workspace you open the database within the workspace and it returns a reference to the database object db.

   sqlstr = "select [EmployeeID],[LastName],[FirstName] from [Employees]"
   rs = db.OpenRecordSet(sqlstr)
   ;
   While !rs.eof
      Message("Debug", StrCat("Employee ID: ", rs.fields("EmployeeID").value, @CRLF, "First Name: ", rs.fields("FirstName").value, @CRLF, "Last Name: ", rs.fields("LastName").value))
      rs.movenext
   EndWhile
   ;
   rs.close
   ;
   ObjectClose(rs)
   ObjectClose(db)
   ObjectClose(dao)

From this point on, it's virtually identical to our MS Access scripts. When you run the script, you should only see the Winbatch icon and the Message() function when it displays the data on the screen. I've included a DAO version of the scripts provided for Access so you can see how to code scripts you want to run hidden.

Note: there may be some discrepancies when using DAO, for instance: the .DoCmd object, is part of "Access.Application" and not DAO. So you'll be forced to use the Access version if you want to use the .DoCmd object. Consult the MS Access help file if you run into any difficulties.

ACCESS SCRIPT 1.10

Occaisionally, you may need to export data to a file that you can use immediately. Once again the .DoCmd provides us with the means to do so, in this case we'll be sending data directly from Access to Excel. The dangers of doing things behind the scenes are many, mainly that the job never kicked off, or that the data you're importing isn't there, or any of a host of others. Generally I build my scripts with certain safeguards built-in, but before you send out data, you may need to "eyeball" it first.

   acOutputTable = 0
   acFormatXLS   = "Microsoft Excel (*.xls)"
   Access.DoCmd.OutputTo(acOutputTable, "Employees", acFormatXLS, outputfile, @TRUE)

When you run this script, it immediately opens Excel, inserts the data and formats it, using Access's defaults. Once again I'll point you towards MS Access's help file to learn more about the .OutputTo command and just what you can use it for.

ACCESS SCRIPT 1.11

As I mentioned earlier, Access gets mad if you try to create something that already exists. While Winbatch can test for the existence of a file (such as using FileExist() on an .mdb file) it can't help you too much once you're inside of Access. However, you can use OLE to inspect the various collections that MS Access has to see if a particular table or query (or other types of objects) exist before you go to work on it.

Access keeps a collection of each type of object it deals with (tables, queries, forms, reports, modules, etc.) so you simply have to find the corresponding collection and inspect it. The following code:

   count = Access.CurrentData.AllTables.Count
   Message("Tables", count)
   For x = 0 To count-1
      If StrSub(Access.CurrentData.AllTables.item(x).name, 1, 4) <> "MSys" Then Message("Debug", Access.CurrentData.AllTables.item(x).name)
   Next
;

Tells Access to give us a .count of all tables it has. When you run the script and see the display you'll notice it has many more tables than it shows us. These are hidden tables the Access system uses to keep track of its work for this database. The script simply loops through the collection and displays the name of the table if it's not a system table (which always start with "MSys".) This can be helpful to add to your script so it doesn't go trying to open a table that isn't there (producing an error and causing the script to end.)

You can do the same with the Queries:

   count = Access.CurrentData.AllQueries.Count
   Message("Queries", count)
   For x = 0 To count-1
      Message("Debug", Access.CurrentData.AllQueries.item(x).name)
   Next

There shouldn't be any hidden queries in our database, but it is possible to hide objects from users. Again this is helpful to safeguard your script from erroring out unexpectedly.

ACCESS SCRIPT 1.12

Scripting can help you out immensely, even when you have to produce items that are done interactively. For instance, say you had to keep a table of Employees for your department. You periodically have to use subsets of the table data to send memos or reports, sending them out in groups according to last name. The work is done infrequently and when you're not around (day off, vacation, sick day) another member of your team does the work in these situations. You can't schedule the script to run, so you simply run the script once and anyone else who uses the database can simply open the query and get the information.

This last script shows you how to create query definitions dynamically, using Winbatch. Once created, they'll work no matter how much the data changes.

Our plan is to create a query for each letter of the alphabet for each of our employee's last names. If no one's last name starts with "X" don't create a query. Our first step is to open the table and look at each last name, grab the first letter off it and see if it's already in our list. If it isn't, it adds it to our list and continues on. Remember this isn't the most efficient way to do this, but we've only got 10 employees so...

   rs = db.OpenRecordset("Employees")
   LetterStr = ""
   While rs.eof == 0
      FirstLetter = StrSub(rs.fields("LastName").value, 1, 1)
      If !StrIndexNC(LetterStr, FirstLetter, 1, @FWDSCAN) Then LetterStr = StrCat(LetterStr, FirstLetter)
      rs.movenext
   EndWhile
   rs.close
   ObjectClose(rs)

Now that we have a unique list of all the first letters of the last names, we move through that list, extract each letter in turn and build a query based on it. We also give each query a different name to avoid any errors.

   For x = 1 To StrLen(LetterStr)
      ThisLetter = StrSub(LetterStr, x, 1)
      sqlstr = StrCat("select [EmployeeID], [FirstName], [LastName] from [Employees] where [LastName] like '", ThisLetter, "*' order by [LastName] asc")
;      message("Debug", sqlstr)
      db.createquerydef("Last Name Starts with %ThisLetter%", sqlstr)
   Next

Similar to creating tables, we use the db object to create each query definition. If the query already exists you'll get an error. You can loop through the .AllQueries collection to check names before you try and create them, just like we looked at them in the last script.

Now open up Access by hand and look at the Queries tab. You should see all the new ones. Now close Access and if you re-run script 1.11 you'll see their names displayed on screen. This can be helpful if you need to check and see if a collection object exists before executing portions of your script.

This collection of scripts should provide a good starting point in using Winbatch with MS Access. I whole-heartedly suggest that if you're not familiar with using SQL -- Structured Query Language -- you buy a book on it. Even a rudimentary knowledge of it can save you lots of time and you'll be amazed by it's power and versatility.

ACCESS SCRIPT 1.13

This is simply a sample of using DAO -- Data Access Objects to show you how to build and view a recordset dynamically. You'll notice in the script the variable sqlstr is commented out. You can uncomment this line, and comment the line below it to see that no matter what type of SQL statement you provide, the script will find all the fields and data in the recordset and display them. This is handy for when you have to deal with a recordset with unknown parameters, such as field names. Also, you can change the order of the SELECT statement, so that the last column is first in the statement (place them in a random order) then when the records are displayed, the data corresponds to the order in the SQL statement. Very handy when you need data in a certain order.

THE USER DEFINED FUNCTIONS

The following function is handy when you need to look at constants used inside an application like MS Access and know their values for your Winbatch script. These are simply variables, that don't mean anything to Winbatch, but when used inside the application they belong to they have value. In most instances you won't need them all, just certain ones that apply to your script.

To get it to work, you need to call the function with a parameter, the name of an object collection, and the function will place any constants of that object into an array, clear the clipboard, then loop through the array and put the constant's name, an equal sign and its corresponding value.

You can keep this function handy for future OLE projects.

   #DefineFunction PlaceOLEConstantsOnClipboard(constantCollection)
      ClipPut("")
      aConstants = ObjectConstToArray(constantCollection)
      For x = 1 To aConstants[0,0]
         ClipAppend(StrCat(StrFix(aConstants[x,0], " ", 35), " = ", aConstants[x,1], @CRLF))
      Next
      Return
   #EndFunction

Next is the function that creates a field, then appends it to a table definition. We have to supply the function with three parameters: the name of the table definition, once it's created using tdf = db.CreateTableDef("Employees"); the name we wish to call the field; the field type (what sort of data it's going to hold) and finally it's length.

If you examine the second line of the function, you'll notice that it checks the FieldType parameter and if it equals 10 or 12 sets the field so that it will accept blank (null, or no data) values. This isn't possible with numeric fields, since they have to contain a number, be it negative, zero or positive. You'll also notice that you can't use dbText or dbMemo here, since any constants defined in the script are invisible inside the function. You can remedy this by declaring them inside the function (which I didn't) to save space. You'll also notice the line with : ObjectType("BOOL", -1). You can examine the Winbatch help to see the details on this function. In MS Access, the value for True is -1, whereas in Winbatch it's 1. You could have replaced everything to the right of the equal sign with -1. I did this just to make first time users aware of this capability.

   #DefineFunction CreateAndAppendField(tdf, FieldName, FieldType, FieldLength)
      fld = tdf.CreateField(FieldName, FieldType, FieldLength)
      If FieldType == 10 || FieldType == 12 Then fld.AllowZeroLength = ObjectType("BOOL", -1)
      tdf.Fields.Append(fld)
      ObjectClose(fld)
      Return
   #EndFunction

Lastly, after the field is appended to the table definition, we close the object. This doesn't delete the object, it simply closes it because it's no longer needed. This is a good habit to get into, that is closing objects when you're done with them.

#DefineFunction PlaceOLEConstantsOnClipboard(constantCollection)
   ClipPut("")
   aConstants = ObjectConstToArray(constantCollection)
   For x = 1 To aConstants[0,0]
      ClipAppend(StrCat(StrFix(aConstants[x,0], " ", 35), " = ", aConstants[x,1], @CRLF))
   Next
   Return
#EndFunction

#DefineFunction CreateAndAppendField(tdf, FieldName, FieldType, FieldLength)
   fld = tdf.CreateField(FieldName, FieldType, FieldLength)
   If FieldType == 10 || FieldType == 12 Then fld.AllowZeroLength = ObjectType("BOOL", -1)
   tdf.Fields.Append(fld)
   ObjectClose(fld)
   Return
#EndFunction

#DefineFunction CreateAccessDateFormat(wbtdate)
   Month      = ItemExtract(2, wbtdate, ":")
   Day        = ItemExtract(3, wbtdate, ":")
   Year       = ItemExtract(1, wbtdate, ":")
   Return(StrCat(Month, "/", Day, "/", Year))
#EndFunction

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

dbname = "C:\data\access\Winbatch Test.mdb"

GoSub NewAccessDatabase
;gosub InputAccessData
;gosub ViewRecords
;gosub InsertRecord
;gosub DeleteRecord
;gosub EditRecord
;gosub SQLInsertRecord
;gosub SQLUpdateRecord
;gosub DoCmdExport
;gosub DAOViewRecord
;gosub DoCmdOutputTo
;gosub ViewAccessObjects
;gosub DynamicallyCreateQueryDefs

;gosub DAODynamicViewRecords

Exit

;   Access Script 1.0
:NewAccessDatabase

   If FileExist(dbname) Then FileDelete(dbname)
;   
   Access = ObjectOpen("Access.Application")
   Access.NewCurrentDatabase(dbname)
   Access.Visible = @TRUE
   db = Access.CurrentDb
   tdf   = db.CreateTableDef("Employees")
;   
   ;AccConst = ObjectConstantsGet(Access)
   ;PlaceOLEConstantsOnClipboard(AccConst)
;   
   ;   set the constants for the field types...
   dbLong = 4
   dbText = 10
   dbMemo = 12
   dbDate = 8
;   
   CreateAndAppendField(tdf, "EmployeeID",      dbLong, 4)
   CreateAndAppendField(tdf, "LastName",        dbText, 20)
   CreateAndAppendField(tdf, "FirstName",       dbText, 10)
   CreateAndAppendField(tdf, "Title",           dbText, 30)
   CreateAndAppendField(tdf, "TitleOfCourtesy", dbText, 25)
   CreateAndAppendField(tdf, "BirthDate",       dbDate, 8)
   CreateAndAppendField(tdf, "HireDate",        dbDate, 8)
   CreateAndAppendField(tdf, "Address",         dbText, 60)
   CreateAndAppendField(tdf, "City",            dbText, 15)
   CreateAndAppendField(tdf, "Region",          dbText, 15)
   CreateAndAppendField(tdf, "PostalCode",      dbText, 10)
   CreateAndAppendField(tdf, "Country",         dbText, 15)
   CreateAndAppendField(tdf, "HomePhone",       dbText, 24)
   CreateAndAppendField(tdf, "Extension",       dbText, 4)
   CreateAndAppendField(tdf, "Notes",           dbMemo, 0)
   CreateAndAppendField(tdf, "ReportsTo",       dbLong, 4)
;   
   db.TableDefs.Append(tdf)
;   
   ObjectClose(tdf)
   ObjectClose(db)
   Access.quit
;   
   ObjectClose(Access)
;   
Return

;   Access Script 1.1
:InputAccessData
;
datafile = "C:\NorthwindEmployees.txt"
EmployeeData = FileGet(datafile)
EmployeeData = StrReplace(EmployeeData, @CRLF, @LF)
;
Access = ObjectOpen("Access.Application")
Access.OpenCurrentDatabase(dbname)
Access.Visible = @TRUE
db = Access.CurrentDb
;
db.execute("delete * from Employees")
;
rs = db.OpenRecordset("Employees")
For x = 2 To ItemCount(EmployeeData, @LF)
;
   ThisLine  = ItemExtract(x, EmployeeData, @LF)

;   BirthDate = itemextract(6,  ThisLine, "|")
;   BirthDate = CreateAccessDateFormat(BirthDate)
;   HireDate  = itemextract(7,  ThisLine, "|")
;   HireDate  = CreateAccessDateFormat(HireDate)
;
   rs.addnew
;
   rs.fields("EmployeeID").value      = ItemExtractCSV(1,  ThisLine, 0, ",")
   rs.fields("LastName").value        = ItemExtractCSV(2,  ThisLine, 0, ",")
   rs.fields("FirstName").value       = ItemExtractCSV(3,  ThisLine, 0, ",")
   rs.fields("Title").value           = ItemExtractCSV(4,  ThisLine, 0, ",")
   rs.fields("TitleOfCourtesy").value = ItemExtractCSV(5,  ThisLine, 0, ",")
   rs.fields("BirthDate").value       = ItemExtractCSV(6,  ThisLine, 0, ",")
   rs.fields("HireDate").value        = ItemExtractCSV(7,  ThisLine, 0, ",")
   rs.fields("Address").value         = ItemExtractCSV(8,  ThisLine, 0, ",")
   rs.fields("City").value            = ItemExtractCSV(9,  ThisLine, 0, ",")
   rs.fields("Region").value          = ItemExtractCSV(10, ThisLine, 0, ",")
   rs.fields("PostalCode").value      = ItemExtractCSV(11, ThisLine, 0, ",")
   rs.fields("Country").value         = ItemExtractCSV(12, ThisLine, 0, ",")
   rs.fields("HomePhone").value       = ItemExtractCSV(13, ThisLine, 0, ",")
   rs.fields("Extension").value       = ItemExtractCSV(14, ThisLine, 0, ",")
   rs.fields("Notes").value           = ItemExtractCSV(15, ThisLine, 0, ",")
   rs.fields("ReportsTo").value       = ItemExtractCSV(16, ThisLine, 0, ",")
;
   rs.update
;
Next
;
ObjectClose(rs)
ObjectClose(db)
Access.quit
;
ObjectClose(Access)
;
Return

;   Access Script 1.2
:ViewRecords
   Access = ObjectOpen("Access.Application")
   Access.OpenCurrentDatabase(dbname)
   Access.Visible = @TRUE
   db = Access.currentdb
   ;
   sqlstr = "select [EmployeeID],[LastName],[FirstName] from [Employees]"
   rs = db.OpenRecordSet(sqlstr)
   ;
   While !rs.eof
      Message("Debug", StrCat("Employee ID: ", rs.fields("EmployeeID").value, @CRLF, "First Name: ", rs.fields("FirstName").value, @CRLF, "Last Name: ", rs.fields("LastName").value))
      rs.movenext
   EndWhile
   ;
   rs.close
   ;
   ObjectClose(rs)
   ObjectClose(db)
   ObjectClose(Access)
Return

;   Access Script 1.3
:InsertRecord
   Access = ObjectOpen("Access.Application")
   Access.OpenCurrentDatabase(dbname)
   Access.Visible = @TRUE
   db = Access.currentdb
   ;
   sqlstr = "select [EmployeeID],[LastName],[FirstName] from [Employees]"
   rs = db.OpenRecordSet(sqlstr)
   ;
   mydata = "00023456,Jones,Fred"
   ;
   rs.addnew
   ;
   rs.fields("FirstName").value   = ItemExtractCSV(3, mydata, 0, ",")
   rs.fields("EmployeeID").value  = ItemExtractCSV(1, mydata, 0, ",") 
   rs.fields("LastName").value    = ItemExtractCSV(2, mydata, 0, ",")
   ;
   rs.update
   ;
   rs.close
   ;
   ObjectClose(rs)
   ObjectClose(db)
   ObjectClose(Access)
Return

;   Access Script 1.4
:DeleteRecord
   Access = ObjectOpen("Access.Application")
   Access.OpenCurrentDatabase(dbname)
   Access.Visible = @TRUE
   db = Access.currentdb
   ;
   sqlstr = "select [EmployeeID],[LastName],[FirstName] from [Employees]"
   rs = db.OpenRecordSet(sqlstr)
   ;
   While !rs.eof
      If rs.fields("LastName").value == "Jones"
         rs.edit
         rs.delete
      EndIf
      rs.movenext
   EndWhile
   ;
   rs.close
   ;
   ObjectClose(rs)
   ObjectClose(db)
   ObjectClose(Access)
Return

;Access Script 1.5
:EditRecord
   Access = ObjectOpen("Access.Application")
   Access.OpenCurrentDatabase(dbname)
   Access.Visible = @TRUE
   db = Access.currentdb
   ;
   sqlstr = "select [EmployeeID],[LastName],[FirstName] from [Employees]"
   rs = db.OpenRecordSet(sqlstr)
   ;
   While !rs.eof
      If rs.fields("LastName").value == "Jones" && rs.fields("FirstName").value == "Fred"
         rs.edit
         rs.fields("FirstName").value = "Freddie"
         rs.update
      EndIf
      rs.movenext
   EndWhile
   ;
   rs.close
   ;
   ObjectClose(rs)
   ObjectClose(db)
   ObjectClose(Access)
Return

;Access Script 1.6
:SQLInsertRecord
   Access = ObjectOpen("Access.Application")
   Access.OpenCurrentDatabase(dbname)
   Access.Visible = @TRUE
   db = Access.currentdb
   ;
   sqlstr = "insert into [Employees] ([EmployeeID],[LastName],[FirstName]) values (23456, 'Jones', 'Fred')"
   db.Execute(sqlstr)
   ;
   ObjectClose(db)
   ObjectClose(Access)
Return

;Access Script 1.7
:SQLUpdateRecord
   Access = ObjectOpen("Access.Application")
   Access.OpenCurrentDatabase(dbname)
   Access.Visible = @TRUE
   db = Access.currentdb
   ;
   sqlstr = "update [Employees] set [FirstName] = 'Freddie' where [LastName]= 'Jones'"
   db.Execute(sqlstr)
   ;
   ObjectClose(db)
   ObjectClose(Access)
Return

;Access Script 1.8
:DoCmdExport
   Access = ObjectOpen("Access.Application")
   Access.OpenCurrentDatabase(dbname)
   Access.Visible = @TRUE
;
   outputfile = "c:\NorthwindEmployees.txt"
   If FileExist(outputfile) Then FileDelete(outputfile)
;
   acExportDelim = 2
   acImportDelim = 0
   Access.DoCmd.TransferText(acExportDelim, "","Employees", outputfile, @TRUE)
   ObjectClose(Access)
Return

;   Access Script 1.9
:DAOViewRecord
   dao = ObjectOpen("DAO.DBEngine.36")
   ws = dao.CreateWorkspace("JetWorkspace", "admin", "")
   db = ws.OpenDatabase(dbname)
   ;
   sqlstr = "select [EmployeeID],[LastName],[FirstName] from [Employees]"
   rs = db.OpenRecordSet(sqlstr)
   ;
   While !rs.eof
      Message("Debug", StrCat("Employee ID: ", rs.fields("EmployeeID").value, @CRLF, "First Name: ", rs.fields("FirstName").value, @CRLF, "Last Name: ", rs.fields("LastName").value))
      rs.movenext
   EndWhile
   ;
   rs.close
   ;
   ObjectClose(rs)
   ObjectClose(db)
   ObjectClose(dao)
Return

;   Access Script 1.10
:DoCmdOutputTo
   Access = ObjectOpen("Access.Application")
   Access.OpenCurrentDatabase(dbname)
   Access.Visible = @TRUE
;
   outputfile = "c:\NorthwindEmployees.xls"
   If FileExist(outputfile) Then FileDelete(outputfile)
;
   acOutputTable = 0
   acFormatXLS   = "Microsoft Excel (*.xls)"
   Access.DoCmd.OutputTo(acOutputTable, "Employees", acFormatXLS, outputfile, @TRUE)
   Access.quit
   ObjectClose(Access)
Return

;   Access Script 1.11
:ViewAccessObjects
   Access = ObjectOpen("Access.Application")
   Access.OpenCurrentDatabase(dbname)
   Access.Visible = @TRUE
;
   count = Access.CurrentData.AllTables.Count
   Message("Tables", count)
   For x = 0 To count-1
      If StrSub(Access.CurrentData.AllTables.item(x).name, 1, 4) <> "MSys" Then Message("Debug", Access.CurrentData.AllTables.item(x).name)
   Next
;
   count = Access.CurrentData.AllQueries.Count
   Message("Queries", count)
   For x = 0 To count-1
      Message("Debug", Access.CurrentData.AllQueries.item(x).name)
   Next
;
   Access.quit
   ObjectClose(Access)
Return

;   Access Script 1.12
:DynamicallyCreateQueryDefs
   Access = ObjectOpen("Access.Application")
   Access.OpenCurrentDatabase(dbname)
   Access.Visible = @TRUE
   db = Access.CurrentDB
;
   rs = db.OpenRecordset("Employees")
   LetterStr = ""
   While rs.eof == 0
      FirstLetter = StrSub(rs.fields("LastName").value, 1, 1)
      If !StrIndexNC(LetterStr, FirstLetter, 1, @FWDSCAN) Then LetterStr = StrCat(LetterStr, FirstLetter)
      rs.movenext
   EndWhile
   rs.close
   ObjectClose(rs)
;
   For x = 1 To StrLen(LetterStr)
      ThisLetter = StrSub(LetterStr, x, 1)
      sqlstr = StrCat("select [EmployeeID], [FirstName], [LastName] from [Employees] where [LastName] like '", ThisLetter, "*' order by [LastName] asc")
;      message("Debug", sqlstr)
      db.createquerydef("Last Name Starts with %ThisLetter%", sqlstr)
   Next
;   
   Access.quit
   ObjectClose(Access)
Return

;   Access Script 1.13
:DAODynamicViewRecords
   dao = ObjectOpen("DAO.DBEngine.36")
   ws = dao.CreateWorkspace("JetWorkspace", "admin", "")
   db = ws.OpenDatabase(dbname)
   ;
;   sqlstr = "select [EmployeeID],[LastName],[FirstName] from [Employees]"
   sqlstr = "select * from [Employees]"
   rs = db.OpenRecordSet(sqlstr)
   ;
   While !rs.eof
;      Message("Debug", StrCat("Employee ID: ", rs.fields("EmployeeID").value, @CRLF, "First Name: ", rs.fields("FirstName").value, @CRLF, "Last Name: ", rs.fields("LastName").value))
      displayTxt = ""
      For x = 0 To rs.fields.count-1
         displayTxt = StrCat(displayTxt, rs.fields.item(x).name, ": ", rs.fields.item(x).value, @LF)
      Next
      Message("debug", displayTxt)
      rs.movenext
   EndWhile
   ;
   rs.close
   ;
   ObjectClose(rs)
   ObjectClose(db)
   ObjectClose(dao)
Return

Article ID:   W17139

File Created: 2014:06:17:13:57:46

Last Updated: 2014:06:17:13:57:46