VBScript (JScript/Powershell?) routine to build a file system directory structure on a network server based on details queried from an ODBC database source.

Overview: VBScript (JScript/Powershell?) routine to build a file system directory structure on a network server based on details queried from an ODBC database source. Detail: Key system is Netsuite (CRM / accounting system) - this stores customers information. For each customer in Netsuite we need to automatically create a directory structure on the local network server in order that relevant files for each customer can be filed away . The customers records in Netsuite is accessible via an ODBC interface (see below for sample) The routine is to be run without user interaction on a scheduled basis on a Windows 2003 server via the scheduler (running via the Windows Script host). The database stores the last modification date of a record (stored as UTC timestamp) and hence the SQL query can be restricted just to fetch new or modified records since the last time the routine was run. The script would need to record the last run time in a local file system file or registry setting (no write access to the database is available) For each customer record modified since the last time the routine was run, we need to create the following directories if they do not already exist: <servername><sharename><businessentity><companyname> <servername><sharename><businessentity><companyname>Letters <servername><sharename><businessentity><companyname>Orders <servername><sharename><businessentity><companyname>Payments - Receipts <servername><sharename><businessentity><companyname>Proposals <servername><sharename><businessentity><companyname>Site Survey - QAV <servername> = set via a variable in the script <sharename> = set via a variable in the script <businessentity> = field retrieved via SQL query <companyname> = field retrieved via SQL query Ideally the script would log activity into a text file (start of processing, count of records retrieved, each <companyname> with status (i.e. directories created or directories not created, end of processing). I have had a quick play with VBScript to prove that it was doable and this is how far I got (the limit of my knowledge on VBScript and ADODB): On Error Resume Next set conn = CreateObject("ADODB.Connection") [login to view URL] "dsn=[login to view URL];UID=USERNAME;PWD=PASSWORD" set rs=CreateObject("ADODB.recordset") [login to view URL] "SELECT REPLACE([login to view URL],' ' ,'_') AS BUSINESSENTITY, REPLACE(COMPANYNAME,' ' ,'_') AS COMPANYNAME, CUSTOMER_ID, LAST_MODIFIED_DATE FROM CUSTOMERS, DEPARTMENTS WHERE CATEGORY_0='CUSTOMER' AND CUSTOMERS.BUSINESS_ENTITY_ID=DEPARTMENTS.DEPARTMENT_ID", conn for each x in [login to view URL] msgbox(x.name&"="&[login to view URL]) next [login to view URL] set rs=Nothing set conn=Nothing The above "works" and displays the relevant field names and values as a popup except it only seems to return the results for a single record - presumably somehow you need to scroll through the records but that's beyond my knowledge of ADODB. The SQL query above returns the two relevant fields BUSINESSENTITY and COMPANYNAME in the correct format. It also returns the LAST_MODIFIED_DATE, but obviously we need to change the WHERE clause to only return those where the LAST_MODIFIED_DATE is > then the last time the routine was run. No need to worry about changes of company names / deletions of customer records or any other such cases.