Hi All

Can I get Perl to create simple MS access table and a query?

Some background information; I have a script that gathers data from a number of sources, then the same script populates an MS Access database. However, before I can use the script I need to create couple of simple tables, ready to receive the data, and a query which basically tie those two tables together like this:

Table1 (saved it as Folders table)

Field Name ........Type 
Folder_ID     Auto-Number (Long Integer, also it is the Key value so n
+o duplicates are allowed)
Folder        Text (Field size 255, Required =>Yes, Allow Zero length 
+=> No)

[download]
Table2 (saved it as Perms table)

Field Name ........Type 
Perms_ID    Auto-Number (Long Integer, also it is the Key value so no 
+duplicates are allowed)
Account    Text (Field size 255, Required =>Yes, Allow Zero length => 
+No)
Type        Text (Field size 50, Required =>Yes, Allow Zero length => 
+Yes)
Folder_ID     Number (Long Integer, the Key value from table1)

[download]
Query (saved it as Main_query) 
Folder    from table1 (Folders_table)
Account      from table2 (Perms_table)    
Type      from table2 (Perms_table)

[download]
The relationship between ‘table1’ and ‘table2’ is one-to many, i.e. Any one folder can have many accounts accessing it.

This is the only bit that I have to set up manually. Can someone please advise me on how can I automate this manual process?

Also, If I wanted to flush the data (i.e. deleting all data but keeping the table and query designs), is there away to automate this process? so that the script will check first to see if there was any data in the database (the dsn name for the database is Perms_db)if data exits then delete it all and then populate the databse or just populate the database.

I have searched PM for ‘Creating MS Access tables’, unfortunately I couldn't obtain an example that fits my situation.

Many Thanks in advance.

In reply to Automating the creation process of MS Access Tables by blackadder

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.